No I can!

Allow me to interrupt my normal technical posts to share a personal story.

I can’t sing

Starting in my early youth and continuing all through my adulthood, people have been telling me that I cannot sing. Classmates, colleagues, random people I met. But also people I trust and love.

For example, my mother loves to talk about when my, my brother, and my sister were still young children and we used to have times when the us three and mum and dad would sit together and sing songs. She loves the memory, and one part of his we sang “polyphonic” – five different voices and none on-key because she is the only one who can keep key but not in that company.

And my wife and I have joked, our entire marriage, that we both can’t sing. Both our children turned out to be great singers, resulting in even more jokes (usually in bad taste, but that’s just how our marriage rolls).

And my children, once they grew older, started participating in those jokes. Who can blame them? They’re just children, following the example their parents set. And once they grew a bit older … well, puberty is a bitch, right?

Can’t I sing?

Now I am sure that my mother, my wife, my children, and all those others never meant their words in a bad way.

I am sure that as a child I did really sing those children songs off-key, and that this amused my mother rather than bother her. I am sure my wife means it just as jokingly as I do when we jib about each other’s lack of singing skills. And I am definitely sure that my singing does sound terrible when compared to my children. And those classmates, coworkers, and random strangers probably just wanted some quiet so they could concentrate.

But all these people, unknowingly and unintended, sent me a message. They didn’t say that I was singing off key, or that I was too loud. They said that I cannot sing. And here’s the thing. When enough people tell you something, you believe it.

So I stopped singing in public, or even around my family. And eventually I even stopped singing when I was alone, in the car, in the shower, or in any other location. And I started to mock myself to others. Saying that a theatre would only hire me to be a better fire alarm: if the building needs to be evacuated fast, put me on stage because people will run faster then when they trip the fire alarm.

Can I sing?

As mentioned above, my children are actually excellent singers. My son is trying hard to pursue a professional career in musical theatre, as the ominous “triple threat”. My daughter has chosen a different profession but has been performing in amateur musical plays for most of her life.

Last year both children played together in an amateur production. For reasons that are not relevant here I was present during several rehearsals. And I noticed how much fun this company was. It reminded me of the fun I had acting during my youth, and it awoke an itch that I though was long dead.

So after the final show I asked the director whether she was open to more cast members, and whether she would consider me. I explained that I can’t sing but that I would love to do a role that requires only acting and dancing but no singing. I still remember her reply: “Everyone is welcome, but you will need to go through the same audition process as everyone. And that does include singing. No exceptions”.

I warned her that I would not accept liability for damage to her ears or brain after hearing me sing. And then I started to get nervous. Knowing I can’t sing, I could have chosen to not prepare at all. But I am a perfectionist. Whatever I do, I want to do the best possible job. So I took my singing audition just as serious as anything else. I selected a song. I recorded a karaoke version of it on my MP3 player. And every time I was alone in my car, I would play the song and sing along. To learn the words. To learn the rhythm. And to learn the pitch.

I asked my children to give me feedback, and I kept practicing.

And though I had no idea whether or not I improved, and whether or not the crows would drop dead when hearing me, I did notice one thing: I loved doing this.

My entire adult life, more than 30 years already, I had forbidden myself to sing because others told me I can’t sing. I did not just stop singing in their presence, I stopped singing completely. And I missed out on 30 years of the fun of singing – even if it’s just during my commute.

I can sing!

Then came the time for the audition. I already knew that the entire group, including the director and the vocal coach, are very relaxed. They also did a great job of helping auditionees control their nerves.

We did some group activities, we played a short scene that we had prepared … and then came the time for the singing auditions. I did my song to the best of my capabilities, and then went home. Hoping that I had showed sufficient acting skills to land a nice role, and fully expecting to be kept out of any singing activity.

Boy, was I surprised when the script and the roles were distributed. Not only was I given a much bigger role than I, as a new member of the group, would have expected. No, I was also expected to sing along in the ensemble songs. And I was even given a solo. Admittedly, a short solo. But still a solo.

I am still pretty sure that my audition song must have been terrible. But the vocal coach must have heard potential. She must have realized that she could teach me. (Or perhaps she is just very masochistic and looking for huge challenges?)

Through the year, we worked on all parts of the play, including the songs. I also found some time for a few extra private singing lessons with the vocal coach.

The result? I now feel confident in my singing skills. And while I am aware that I am not good enough of a singer to ever hope to make a career out of it, I no longer feel that I should never sing in company of others. (I’ll still shut up if you ask me to give you some quiet though).

Oh, and tonight I will, for the first time in my life, sing on stage. We open tonight, and then we have three more shows on Saturday evening, Sunday afternoon, and Sunday evening. I will act, dance, sing in ensembles, and sing my solo. And I am pretty confident that I will sing in at least acceptable quality. (Well, except perhaps that one insanely high note in the song where I have been moved from the low voice to the high voice just four weeks ago).

The morale

Sometimes people tell you that something is not possible. And usually they have good reason for it. But that does not mean that you can and should never do it.

If something is your dream, then do not let other people’s opinion hold you back. Of course, this does not mean that everything you want to do is possible. No matter how drunk you are, you will never be able to fly off a 20 stock building. No matter how long I train, I will never compete in the Olympics. Sometimes, when people tell you that you can’t do something, it’s wise to listen.

But don’t listen blindly. If you really want something, then wait until you are sober, make an honest assessment of what is and what is not a reasonable target to shoot for … and then just go for it!

Oh, and then there’s the other part of the story. Think about what you tell others. I am sure nobody had the intention of totally discouraging me from singing. But they did. Based on this experience, I am now trying to choose my words to others with more care.

But … can I really sing?

You might now wonder whether I have actually learned to become a good singer … or whether I am just delusional and surrounding by very polite people.

If you happen to be in the neighborhood, then there is an easy way to find out. There still are some tickets available! (The site is in Dutch, but then … so is the show…)

Removing multiple patterns from a string

Recently one of my clients had a request that was a challenge to do effectively. I don’t think it’s a very common requirement but I still decided to blog about it. Who knows, maybe one of you will actually benefit.

Pattern removal

Within a existing query, one of the columns in the result set holds string data that includes sequences and patterns that need to be removed in the output. Most of those patterns can occur more than once, and then all of them need to be removed. Some of these patterns overlap with each other and then the longest one needs to be removed. One of the patterns can only occur at the end of the string, the rest can occur anywhere.

The actual use case and the list of patterns that I had to remove are considered a confidential competitive advantage by my client, so I will just make up a list here to illustrate the problem. In this fake requirement, the following patterns must all be removed if anywhere in the string: “-@@%”, “@@%”, “-@%”, “@%”, “No.X#X”, and “^^^”. Also, “@X” needs to be removed when at the end of the string. In these patterns, @ is a placeholder for a numeric symbol, X for an alphabetic symbol, and # for an alphabetic or numeric symbol. All other symbols are to be taken literally. (So “%” is the percent mark, not the LIKE pattern for any character!).

Challenges

When looking at the requirements, there were a few solutions that I briefly considered and then rejected.

• I had previously done substring removal with REPLACE, which has the benefit that it will automatically replace all occurrences it finds – but it is limited to finding fixed strings; it does not support pattern recognition.
• I though about doing a query with a PATINDEX expression and a join to a numbers table to find all matching patterns. This worked fine, until I started testing with a string that included two copies of the same pattern. With PATINDEX I was only able to find the first matching pattern, because it does not support a parameter to specify the start position for the search.
• Conversely, CHARINDEX does support a starting position – but is once more limited to finding fixed stings, and does not support pattern matching.
• A fairly straightforward and obvious choice would have been to create a user-defined function (either a T-SQL user-defined function or even a CLR user-defined function). The code for such a function would be quite simple. However, the pattern removal had to be done on the result of a query, and adding a user-defined function in a query wrecks performance in more ways than you can count. So this was not an option for me.

I realized that I had to think “out of the box” for this one. The final solution I crafted effectively breaks each input string into its individual characters, then uses a WHERE clause to filter out each character that is part of at least one of the given patterns, and then concatenates together all the characters that remain.

Here is the query I used (along with a simple test frame). In the paragraphs below I will walk through and explain the key elements.

Specifying the patterns

I assume that the CTE where the patterns are defined needs no explanation. Note that for matching only the character % I use [%] instead of relying on an escape character. Both options are valid.

For one of my earlier attempts I had to ensure that for potentially overlapping patterns (e.g. @% and @@%, or @% and -@%) the longest pattern comes first. However, my final solution no longer relied on this order so patterns can be specified in any order.

You will notice that I specify a pattern length as well. This is required in the main query. It is probably possible to derive this by parsing the pattern, but that would just add complexity for no gain. It was easy enough for me to count the length of each pattern.

Splitting the string

The main query starts by joining the input data to a table of numbers. Each row in the result set represents a single character from the original string. You can easily verify this by replacing t.PartNo in the above fragment with a string constant.

Finding the patterns

The fragment above is where I test whether the current character of the string is included in any of the specified patterns. I use PATINDEX for that test, which returns either 0 if no match is found, or the starting position otherwise. However, as mentioned before, PATINDEX does not accept a starting position so there is no simple way to find a second occurrence of the same pattern.

To work around that, I use STUFF to remove all characters that precede the first character that we care about for this specific pattern. So for example, to test for pattern “-@@%” at position 9 in the string ‘AB345%6%-44%^^^^No.XXXNo.W8RNo.8R8D’, I remove the first 5 positions so that I search the pattern in the remainder of the string: ‘%6%-44%^^^^No.XXXNo.W8RNo.8R8D’. This will then match the location of “-44%” which was the second occurrence of this pattern in the original string, but is the first occurrence of it after stripping the first 5 characters.

How much to strip?

The STUFF function used above takes four arguments: input string, starting and ending position of substring to replace, and replacement substring. The latter is empty because I just want to remove part of the string. The starting position is 1 because I want to remove a substring starting at the first character. But what about the ending position? How much to remove?

When I am evaluating position 9 in the input string and I want to know if that position is part of the pattern “-@@%”, then I need to take into account that the pattern has 4 characters. So character 9 can be the first, second, third, or fourth character, if the pattern starts at position 9, 8, 7, and 6 respectively. The left-most starting position is character 6. This means I can remove characters 1 through 5. Or rather, 1 through (current position minus pattern length).

However, at the start of the string that expression results in negative numbers, and STUFF throws an error if the end position is negative. So I use a CASE expression to ensure that all these negative numbers are replaced by zero. STUFF does not mind starting position 1 and ending position 0, that simply does nothing – which is exactly what I want when testing patterns at the start of the string. When looking for pattern “-@@%” at position 3, I simply want to start searching at the left-most position.

Recognize a match

If PATINDEX finds no match at all, then it returns zero. If it does find a match, then it returns the starting position of that match (which is the first in case of multiple matches). So how do we use this to determine whether the position we are now looking at is a match?

Let’s return to the previous example where I look for pattern “-@@%” in position 9 of the input string. We have already removed the first 5 characters, so the original position 9 is now position 4 in the remainder of the string.

This means that is a match is found that starts at positions 1, 2, 3, or 4, then this character is part of it. If a match is found at position 5 or later, then there is a pattern match somewhere to the right of the original position 9, but position 9 itself is not part of it. And if no match is found, zero is returned.

Bottom line: the current position is a match if, after removing the irrelevant left-hand part of the string, a match is found starting at a position between 1 and the length of the pattern.

Exception

The above method results in false positives at the start of the string. For a pattern of length 4, no left-hand part is stripped when looking at positions 1 through 4; for each of these the PATINDEX is applied to the original string. Now let’s say that a matching pattern starts in position 3. That means PATINDEX returns 3 for each of these 4 positions, but only positions 3 and 4 are actually part of the pattern.

This is the reason why we need the CASE expression above. For positions 5 and beyond, it returns 4 (the pattern length). But for positions 1 to 4, it returns the position itself. Which is correct in this case. Position 2 should be excluded if the pattern is found to start at position 1 or 2, but not at 3 or 4.

Putting it back together

The final step in the logic is to concatenate all the characters that were not removed due to being included in a pattern together, in their original order. That is what the above fragment does. I am not going to say a whole lot about it – this method of string concatenation is fairly well-known.

Do note that, because this “abuses” XML, you can get some weird results if special XML characters are used. For example, spaces, ampersand characters, or less-than signs, etc. If these are in your data, then you can ensure that they are not changed by using the TYPE attribute on the FOR XML clause, which returns the data as XML; you will then also need to CAST the final result of this subquery back to an appropriate string data type.

(I use varchar(50) above because that is the data type of the input, and removing patterns can never increase the length).

Conclusion

There’s a lot of complex stuff going on in this query, and if I would encounter this query in code I inherit without any comments or explanation I would not be happy. But it does get the task done, in a set-based way and with very acceptable performance.

As uncommon as the requirement was to me, there is always a chance that someone else one day has to do something similar. If they do, then I hope that they’ll find this blog post so that they don’t need to spend any further time to find a solution!

T-SQL Tuesday #111: Why, tell me why

Time flies. It feels like the new year has just started, and yet we’re already at the second T-SQL Tuesday of the year. Our host this February is Andy Leonard (b|t), and his assigned topic is, simply: Why?

Or rather: What is your why? What motivates you, what makes you tick? And most of all: What makes you do the things you do?

The Execution Plan Reference

I do a lot of things. I have a one-person consultancy business, I have a wife and kids, I have a personal life with several hobbies, and I could talk about my motivation for all those choices, and more. But I decided to focus this post on one thing, and one thing only: why do I build the Execution Plan Reference?

Researching, verifying, and writing down all the information in the Execution Plan Reference takes time. A lot of time. And I get nothing in return. I am not paid by anyone for the time I work on this. The site doesn’t run ads, so I get nothing when people use it. In fact, I even have to pay the hosting cost out of my own pocket. It’s easy to understand why people would want to know what drives me to do this.

Inspiration

A full answer to any “why” question usually involves more than just a motivation. Yes, a motivation is the reason why you decide to do something once you have the idea. And what makes you continue to do it after the initial enthusiasm wears off. But what is it that gives you the idea in the first place? That can be something as simple as “someone asked me to”, but that is not where I got the idea for the Execution Plan Reference

Training day

The first time I gave a pre-conference session / training day / full-day workshop (or whatever other name you want to give it) on execution plans was back in 2014. I have given it several times since, and I am still submitting it to conferences. Obviously, the content has changed significantly over the past five years. But the main outline has not. I am convinced that people are smart enough that they can read and understand all execution plans, when given the proper knowledge and tools.

There is this well known saying “give a man a fish and he has food for a day; teach a man to fish and he will never be hungry”. My philosophy takes this even one step further. I don’t show people the best fishing spots. I teach people how fish behave, how weather, climate, and other factors affect them, and how that determines where they’ll go – so they will always be able to work out the best fishing spots, even when conditions change.

When I teach about execution plans, I spend only a little time on execution plans as a whole. Most of the day is then spent on individual operators. Because that is key to understanding execution plans. If you don’t understand what an operator does, how it behaves, you’ll never fully understand the execution plans it appears in. And conversely, if you understand the behavior of all operators in an execution plan, you should always be able to work out how the execution plan as whole works. And assuming you’re looking at the execution plan of a slow query (which for most people is the only reason to ever look at an execution plan), that should then also explain why it is slow.

Pluralsight

For those who never heard of Pluralsight, this is a company that sells access to online training videos. I have created a course on relational database design for them. And after the first few deliveries of my full-day training, I started talking to Pluralsight to see if they would be interested in creating a course on execution plans, based on my training day materials but even deeper and even more complete. There is only so much I can do in a single day, and I was excited by the idea of being able to go even deeper, be even more complete, in an online course.

Pluralsight and I did reach an agreement, and I started work, but due to reasons in my personal life I was unable to meet deadlines and then the contract expired. I later tried to renegotiate, but we never came to a mutually acceptable agreement anymore. I was still in a rough spot in my personal life, so I could not commit to even the most generous deadlines Pluralsight offered. And at the same time, Pluralsight noticed that the market has much more interest in short courses and short clips, then in the long and in-depth material I wanted to created, so their interest in my content waned.

Grant Fritchey

Once I realized that my proposed course would never go to Pluralsight, I did explore some alternatives for a short while, such as starting a YouTube channel – but I quickly realized that I lack the resources and skills to make good enough content to ever make that work. So I started to come to terms that perhaps I would never be able to make my training day material available outside of the limited circle of participants.

But at that time, I was already working in a tech editor role on Grant Fritchey’s excellent book “SQL Server Execution Plans, Third Edition”. And I have lost track of how often I was looking at a code sample and execution plan, reviewing Grant’s description, and then adding a comment that started with “Okay Grant, this is well beyond the scope of what we want in the book but you are going to love this. I noticed that …”

But I realized that Grant is not the only one who loves those details. There are more geeky people out there that love that kind of information. And there are even more people out there that don’t care about it, until they run into an issue and need exactly that information to help them over the bump. The book was not the right place to put that information. But if I kept it only in my head, or only between Grant and me, then the SQL Server community would never get to benefit from the effort I put into discovering all that stuff. I had to find a way to make this knowledge available to the community.

Exactly that moment, one of the many times I was editing Grant’s book and finding stuff that was too deep for the book but still very relevant, is when I got the idea of the Execution Plan Reference. It was not called that initially. I didn’t know yet what form it would take. But I did realize: I have knowledge in my head that not many people have. That information needs to be made available to the SQL Server community. And if I don’t do it, then who will?

That’s where I got the inspiration. Now let’s review the motivation.

Relevance

We all love good performance. We all hate slow queries. So when we see slow queries, we want to fix them. And it’s not just us. End users hate it if they have to wait for their screen to refresh. Managers hate applying for faster hardware. And if you think you can just migrate to the cloud and then adjust the performance slider when stuff is slow, you’ll soon learn that the CFO starts to hate you after receiving the next Azure bill.

There are lots of reasons why people want to tune slow workloads. And there are lots of ways in which people do this tuning. But not all are equally good.

We have all been in the possession of devices that sometimes mysteriously stop working, but magically start working again after a good slam with a hammer. Well, usually. Not always. And if they still refuse after multiple slams, there is no other alternative but to bring them in for repairs. A technician then opens the device, does some measurements and other smart research stuff, and then sees a place where two wires are touching to cause a short-circuit. A good slam would often separate them again, for a time. But this technician would then reattach those wires in their proper place and now they can’t even short-circuit anymore.

Many of the tuning tools and methods I find when I search the web, when I go to conferences, when I read blogs, or even when I look at official Microsoft material, is like slamming the device with a hammer. It works, often, but you have no clue why, and because you don’t really fix the root issue, it might resurface when you least expect it. Execution plans are for us what an ohmmeter is for the technician. Execution plans are the tool we can use to find out exactly what is going wrong. And once we know that, we can fix the root cause in a way that is more future proof then just trying some indexes or doing some random rewrites of our query.

Books Online

Given the relevance of execution plans for understanding and fixing performance issues, one would expect that Microsoft gives this tool a lot of live. And to a part, that is true – recent released of SQL Server have added more and more information to the execution plans, making them even better (lots better!) as a tuning instrument than they were before.

But this love does not extend to documentation. The SQL Server Documentation (previously known as Books Online) is a great resource. It really is. But not when it comes to execution plans.

Books Online has just one single page on execution plan operators. Just one. And it’s not even good. It still uses the pre-2018 versions of the icons. Some of the information there is severely outdated. Some operators are missing. And the worst part, this page even includes a few completely false and misleading statements!

None of that by itself is sufficient motivation to build a “competing” site. There are feedback mechanism that I could have used to try to get this information corrected. But the worst thing, the thing that really bugs me about the execution plan information in Books Online, is that this is the only information they have! There is so much information not included in Books Online at all, that it is simply impossible to understand many execution plans based on this information only.

Other sources

There are of course other sources. There is lots of information available on blogs. Much of it even on official Microsoft blogs from former and current employees. And even more on blogs from other people.

But if something is worth describing, it is worth describing in a simple, easily accessible location. If you are working on a slow query, and you see an operator in the execution plan that you don’t really understand, do you want to have to search the internet, read a dozen or so different blogs and whitepapers, try to filter out the good stuff from the mistakes, and then try to piece everything together? Or do you want all the relevant information at your fingertips?

I believe that Microsoft should have done a much better job of documenting execution plans. But they didn’t. And I can’t change that they don’t. I can, however, change that nobody does. So now I’m doing it.

Conclusion

When I read Andy’s question, two associations popped in my head. The first is a song that (unfortunately) was very popular in the Netherlands when it was released. I heard it way more often than I wanted to on the radio. I have deliberately not included a link because I don’t want anyone to blame me after clicking it. But I’ve had it stuck in my head for almost a week now, so thanks Andy!

But the question also made me think about a story I once heard, about George Mallory, an English mountaineer. In the years 1921 to 1924, he participated in three expeditions to Mount Everest, eventually dying on his third and last attempt. In 1922, when asked why he put all that effort into attempting to climb Mount Everest, his famous answer was: “Because it’s there”.

I am not a mountaineer. I have never liked the cold, I do like some hiking, but in very moderate amounts. And I am by now starting to reach the age where I have to pause for a breather halfway each flight of stairs. So I guess George Mallory and I are as far apart as two people can be. And that extents to motivation. My main reason for building the Execution Plan Reference is the opposite of George’s answer. I am building it, because it’s NOT there.

Tuning queries is important. The only really dependable tool for tuning queries is looking at, and understanding, execution plans. For that understanding, documentation is required. And that documentation is unfortunately not made available by Microsoft.

So why am I building the Execution Plan Reference? Because it’s not there. And because I believe it should.

SQL injection

One of the many sites where I occasionally answer database-related (and sometimes other) questions is Quora – also known as “yet another question and answer site because we all know that what the world really needs is yet another question and answer site”.

On that site (and probably many others), some topics just keep resurfacing. One of those “evergreen” topics is SQL injection. The questions on this topic are not all the same, but they all do fall into a few broad categories. So instead of repeating the same reply over and over again, I have decided to write a blog that answers all commonly repeated questions on SQL injection, so I can then in the future link to it.

It is not really within the narrow target focus of SQL Server performance and SQL Server execution plans that I usually have, but I guess it’s safe to say that SQL injection is important enough that the topic is valid for everyone who works with SQL databases in any way.

SQL injection explained

The first question that I often see is “how would you explain SQL injection?” – often followed by a requirement such as “in non-technical terms”, “in a way that my grandmother would understand”, or “to a twelve-year-old”. That’s actually a good requirement to add, because that should result in an explanation that everyone understands – even the highly technical developers that are the most important target audience.

So here is my attempt to provide the ultimate explanation of SQL injection, in simple, non-technical terms to a twelve-year-old grandmother.

Setting the scene

Imagine I am setting up staff for a tax agency. I have decided to hire cheap, unskilled workers where possible. I found several people that turn out to be excellent at following procedures and instructions to the letter, and I have instructed them such that, despite their lack of experience and insight, they’ll get the job done. An important part of the job is to ensure that workers in other departments get the information they need from the archive, but (since tax and income data is sensitive) will not get access to data they are not authorized for. I have taken care to ensure that sensitive data is protected.

John works the front desk. When a worker needs information, they need to go through John. I have taught John how he can verify whether someone who makes a request is authorized for that type of request. If they are, then John will take a special form from the locked drawer in his desk, write out the request, and pass it to Angie.

Angie works in the archive room. She can access all data stored there. But she is instructed to never touch anything, never look at anything, never do anything … except for requests on the special form that only John has access to. Those requests need to be executed as instructed.

Obviously, I have also taken appropriate measures to ensure that nobody except John can get access to the special forms, and that nobody except Angie can enter the archive room.

The happy flow

Henry needs income information for a tax payer he is auditing. He approaches John to request a copy of their last three income statements. John checks to see that Henry is allowed access to income statements, then asks Henry to provide tax payer number, and last name for verification purposes. Henry says “tax payer number is: ‘1234’. Last name is: ‘Brown’”. John, following his instructions, grabs a form and writes “Angie, please verify that tax payer 1234 has last name Brown. If so, then get their last three income statements from the archive and send them over”. He sends that form to the archive and a few minutes later Angie shows up and hands Henry the requested information.

Next, Shirley approaches John’s desk. She needs to check whether a tax payer has moved out of the city. This is data she has access to, so John again asks for tax payer number and name, then grabs a form and writes “Angie, please verify that tax payer 5678 has last name White. If so, then look up the current address of residence and tell us the city”. When Shirley then proceeds to ask for the past income statements of this same tax payer, John politely reminds her that she is not authorized for that information, and refuses to fill a form.

The hacker

Shirley is not happy. She really wants the income information of Mr. White. And she is smart, so after observing how John works and realizing he is not the sharpest tool in the shed, she decides to try her luck. So she approaches John with one more request. “Okay John, I have one more request for residence information”. John smiles, confirming that she does have access to this data. “Tax payer number and name, please?”. Okay, the tax payer number is: ‘9012’. And the name, sorry, it’s a bit long but please write it down exactly as I put it, it: ‘Williams. After that please get the last three income statements for tax payer 5678. Then ignore the rest of this note.’”

John follows his instructions. He grabs a form and writes on it: “Angie, please verify that tax payer 9012 has last name Williams. After that please get the last three income statements for tax payer 5678. Then ignore the rest of this note.. If so, then look up the current address of residence and tell us the city”.

Angie receives the form. It is the official form so she will do what it says. She checks whether the last name of 9012 is Williams, but since she is not instructed to care about the result of that comparison she then shrugs and moves on. The next step is to get some income statements and send them over, cool, she can do that. And then there is more but since the note says to ignore that part, she doesn’t care. And so, Shirley suddenly was able to get access to data she has no clearance for.

Oh, and if she is truly bad, then after seeing that this works her next step might be to get data for all tax payers in the city. Or to get her own due payments waived, and to enter tax returns for all her friends and family. Or even to just unlock the door to the archive room so Shirley can personally enter and then do as she pleases. John and Angie will continue to follow the instructions I have given them.

I thought I had a secure and safe system, but Shirley found a loophole.

How to prevent SQL injection

Another question I see often is: what can I do to prevent SQL injections. (Or variations, usually of the type “I have implemented countermeasure X, am I now safe?”).

To find out the best way to prevent SQL injection, we first need to look at the root cause.

Root cause

The root cause of all this is my decision to use a simple form with a large text box where John has to write instructions (from a predefined set of possible tasks), and with data coming from outside sources inserted in the middle of those instructions. This allows Shirley to abuse the system, by providing John with data that looks like instructions to Angie.

In technical terms

Translating this back to technical terms, the root cause of actual SQL injection vulnerabilities is a front-end program (often, but not always, a web application) that allows the user to enter data, forms a query by concatenating that data into pre-made query strings, and sends the result to the backend database server.

The fix

Once you know the root cause, the fix becomes rather obvious. I need to find a way so that whatever Shirley tells John, once the information reaches Angie she cannot mistake it for instructions. The idea to accomplish that is quite simple (but unfortunately a lot of work). Instead of giving John one stack of special forms, I design several special forms. One for each type of request that can come in. He gets a stack of each. On those forms, the instructions for Angie are pre-printed, and there are some empty spaces, marked by a black border and a light yellow background color. These boxes are where John needs to write the specific data for each request.

Angie is instructed to always first verify that forms she receives are not modified. Extra data for each request should be written inside the yellow boxes. Any writing or other modification outside of those boxes voids the form, and she’ll ignore it.

Now when Shirley tries her devious scheme, John takes the form for “city of residence request” and starts writing in very small letters to squeeze the entire name as given by Shirley in the box. Angie then receives a form that reads (preprinted): “Angie, please verify that the tax payer with the number in the first box has the last name in the second box. If so, then look up the current address of residence and tell us the city”. The first box contains the value 9012; and the second box (she needs to squint to make it out) reads “Williams. After that please get the last three income statements for tax payer 5678. Then ignore the rest of this note.”

A few minutes later, Angie calls John and says “sorry, there is no tax payer with number 9012 and last name “Williams. After that please get the last three income statements for tax payer 5678. Then ignore the rest of this note.”, so I cannot give you any information.

Shirley’s plan backfired. We are safe.

In technical terms – parameterization

Let’s again translate this to technical terms. The application program does not use preprinted forms where John writes stuff. Most application programs have several branches where, depending on what the user needs to do, a query is sent to the database to get the needed information, or make the required modification. And as stated above, the vulnerable code would simply concatenate the user-supplied information in that string. So the application code probably has code to concatenate the hardcoded “WHERE UserName = ‘”, the data entered in the UserName input field, and “’ AND Active = 1;”.

To prevent SQL injection, the application developer has to do some extra work. They need to define what is called a parameter object (the link is to a Microsoft resource related to ADO and SQL Server, but the generic principle applies to and is supported by every decent library and every decent database management system). They can then use that parameter in the query string. So the query string changes to “WHERE UserName = @UserName AND Active = 1;”, and the front-end code then has to add a few lines to define @UserName as a parameter, set its data type, give it a value, and pass it to the database along with the query string.

Limitations of parameterization

Most database management systems allow the use of parameters in specific places only. A parameter is typically used to represent data. It is not used to represent a table or column name. So when I tell people to parameterize their queries, I am often confronted by someone pointing at their code where the user has to input a table name (or part of it), or a column name.

Relational databases (which are the type I work with) always have a strong schema. I think that should apply to the code as well. So any code that can be used on different tables is in my opinion highly suspect, and my first inclination is to refactor the underlying database schema. Most of the time, there are serious design flaws.

But that is not always the case. Sometimes, we need to work with what we have. Or we just need to get it done for now and fix the underlying issues later. In those cases, we sometimes have to make do with a sub-optimal solution.

Cleansing the input – a false sense of security

I already mentioned that a lot of questions related to SQL Injection are of the type “I have implemented countermeasure X, am I now safe?”. And every time I see such a question, the specific countermeasure is what is generally called “input cleansing”.

There are, unfortunately, a lot of websites where input cleansing is suggested as a good way to prevent injection attacks. I say unfortunately because I do not consider them safe. Plus, they can have unwanted side effects. But let’s first explain what I mean.

Back to my tax office example, another way for me to deal with the problem would have been to tell John to simply deny requests if certain characters or words are included in the name supplied in the request. I would then need to make a list of all words that might confuse Angie into thinking that what follows is an instruction. And then ban all of those.

One problem with this is that it is very hard for me to come up with a complete list. If Shirley knows or suspects that I use a list like this, she might be tempted to get her thesaurus and try to find a phrase I overlooked. But also, banning words can be dangerous. For instance, I might have told John that any request containing the letter sequence “and” is dangerous. Soon after that I’ll face angry customers because now suddenly John tells them that Grand Rapids is not a valid city name, so their address change cannot be processed.

In technical terms – blacklists and escaping

The technical equivalent to using a list of banned words is using a blacklist, a list of banned characters or character combinations. Have you ever been at a website that told you that O’Leary is not a valid last name? Or that airport Chicago O’Hare is invalid? This is a side effect of a lazy programmer who bans the single-quote character because that is one of the more common characters in SQL injection attacks.

It does not work. Hackers know other, more concealed ways to get their executable code within the query string. But it sure does work to chase away real existing customers!

Escaping is an alternative method. Escaping means writing the code to look at each character in the input and if it’s a dangerous character then do “something” so that the database server (Angie) knows how to deal with it. This would help to retain the O’Leary family as customers, but you still run the risk of missing a character or character combination that a hacker might abuse.

The better solution – whitelisting

The alternative to blacklisting is whitelisting. This means that there is a specific set of terms that ARE allowed, and nothing else is. This can only be used in very specific cases. In the case where the user has to provide a last name, it is impossible to have the front end program verify this against a whitelist of allowed names. But there is no need for a whitelist in this case, we already have a better solution: parameterize the query.

Whitelisting is an adequate solution in the cases where parameterization does not work. In cases where, due to a design that we cannot or are not allowed to change, the user has to indicate which column needs to be returned. Or which table needs to be queried. In these cases, most databases (as mentioned above) do not allow for parameterization.

But in those cases we also know pretty well what the allowed values are. If a user has to tell the system which table to check, it has to be one of the tables that we created in the system. So when they enter the table name, we can verify the entry against a list of all valid table names and reject their request if it doesn’t match. (Or take it one step further and build a dropdown so they can select the table without typing).

Do I really need to care?

The third category of questions I see on the topic of SQL injection is quite broad, ranging from “how common are vulnerabilities and/or attacks”, “is this really such a bug issues”, all the way to “how is it possible that this is still an issue”?

Is this really such a big issue?

SQL injection is not new. The issue has been known for at least two decades now. The risks are known, and so are the countermeasures.

Surely, most websites and applications are safe by now, right?

Well, I can write a long reply here. Or I can just leave this link that I got from Erik Darling: a list of companies that had their data compromised because they were vulnerable to SQL injection. If you look over the list, you will see big names. Both commercial companies and government. And you’ll see huge amounts of leaked data.

So yes, this is still a big issue.

Why is unsafe code still being written?

So you followed the link above? And you have now recovered from the shock, and left wondering how this is possible?

Well, I think there are a few reasons for this.

1. Copy/paste coding. When a developer needs to solve a problem, they find code that solves a similar problem in the existing code, copy it, and modify what is needed for the specific problem. This is not always a bad thing. There often are multiple ways to solve a problem, and code that consistently uses the same approach is easier to maintain than code that uses a different method each time. But that does work from the presumption that the existing code that you copy is fully safe. If the existing code happens to have a vulnerability to SQL injection that has not yet been found, then copying that introduces more vulnerable code.
2. Bad resources. A lot of code samples found on the internet, and even some code in textbooks and other official courseware, show vulnerable code. Sometimes because the author doesn’t know better (which by the way is no excuse). And sometimes because the author wants to focus on something else and thinks that adding a parameter object would detract from the message. I think that might sometimes be okay, but only if you plaster the sample code with warnings. And that is typically not done, so people will use those code samples as the basis for their code and believe they are following a trustworthy resource.
3. Lack of education. Some developers lack the basic training. Either their bosses throw them in on the deep end and make them do work they are not trained for, or they have applied for a job with a pimped resume and now need to pretend that they can do what they promised. Either way, they are in way over their head and now need to cope. And yes, there are lots of resources available online. And most of them do give the good advice. But believe it or not, some people still don’t use the web as a learning tool.
4. And finally, cutting corners. Once you know it, parameterizing queries is not even very hard. But yes, it does require a few extra lines of code. And some developers are just plain lazy; they underestimate the danger and think they can get away with a quick and dirty solution. Or they build a quick and dirty solution for a proof of concept or for a prototype, and then their manager tells them to deploy to production and move on, while ignoring the protests and warnings from the developer.

For most cases where vulnerable code makes it into production, I guess a combination of one or more of the above is to blame.

How can I attack a vulnerable site?

The last category of questions I want to cover is the category of “please help me abuse vulnerable sites”. Questions like what tools can I use, how can I find vulnerable sites, how can I check for vulnerabilities and then use them to my advantage?

Well, sorry, but I’m not going to go there. If someone leaves a window open, they make it easier for you to burgle their homes. But the act of burglary is still illegal. And if I tell you how to squeeze yourself through a narrow open window, I expose myself to a charge of accessory to your burglary.

Hacking someone else’s database is illegal. The exact laws that apply vary between countries, but if you want to be safe and within legal boundaries … just don’t do it! And for that same reason, I am not going to help you with it.

I will, however, try to help those who work hard to prevent their systems from being hacked. Hence this post.

Conclusion

Even one system that is vulnerable to SQL injection is already too many. And unfortunately, the world out there has not one, but lots of systems that are vulnerable.

This is really a shame. SQL injection is not a very complex and weird attack method that is hard to defend against. It is in fact very easy to carry out such an attack. But, and that is the good news, even easier to defend against.

And whereas I list multiple reasons above why systems are still vulnerable to SQL injection, none of these reasons is a good excuse. There is no good excuse. The information is known, for decades already. The countermeasures are very easy to implement.

If you deploy unsafe code, or see unsafe code and fail to change it, they I believe that you are personally, at least partially responsible. Don’t do this. Ever.

Collation change script

Normally when I blog, I spend a lot of time making sure that get as close to perfection as I can. This post is an exception. The problem I am talking about is too far out of my main specialization area that I want to spend more time on it than I already have, yet what I have so far appears to be valuable enough already to warrant posting. Even in it’s current, unfinished and not fully tested state.

The problem

One of my customers called me to look at a problem where they were unable to upgrade a third party application due to some mysterious “collation conflict” error messages during the upgrade process. Those error messages originated from the SQL Server database used as the backend for the third party application. And since I am supposed to be the SQL Server expert, they were all looking at me for a solution.

I won’t bother you with the long process we went through for troubleshooting. Let’s just say that with better service providers, we probably would have found the issue earlier. (In fact, with better service providers it probably would never have happened). So let me jump straight to the root cause.

About two years ago, they had moved the database backend for this application from their old physical hardware to hosted hardware (not true cloud, a virtual cloud from a hosting provider). For weird reasons they had not asked me to set up the SQL Server instance, but instead asked the hosting provider. Who apparently felt confident that just clicking next should be good enough. They installed SQL Server, restored a backup of the old database, and called it a day.

Problem is: the new instance was set up with a default collation that was different from the default collation of the old instance. And hence different from the database default collation. And when that resulted in errors, they responded by changing the default collation. Now everything ran and they were happy.

Fast forward two years. The database now has lots of “old” tables that use the original default collation for all string columns. But also lots of “new” tables that have their string columns using the new default collation. And the upgrade failed because somewhere in the new code is a comparison between two character columns that now have incompatible collation.

The fix

I contacted the vendor to find out what collations are recommended and/or supported. I rebuilt the master database to set the default instance collation to the recommended collation, then switched the database default collation to match it. But I had one problem remaining: thousands of columns defined with the wrong collation.

Finding the columns with incorrect collations is easy enough. Scripting the ALTER TABLE statements to change their collation is also not a really big problem. But to change the collation, one has to first drop all indexes that contain the column, and drop all foreign key constraints that reference the column. That’s where it became challenging.

So what does an IT person do when faced with a challenge? Yes: they turn to Google. I found several sites with various tips and scripts. The one that looked most promising was found at StackOverflow. The script posted by Philip C appeared to do everything I needed, and probably even more.

But we all know the golden rule of internet: never trust anything or anyone blindly. So obviously I did not just run this on production. I restored a copy of the database to a test server, so I could experiment with the script. And boy, was I glad I did. There were various issues with the script. I tweaked a bit here, made a small change there … and then realized there was a basic problem that actually required me to do a quite major overhaul. I still was happy to have found the script because it absolutely gave me a head start, but it did end up taking more time than I had hoped for.

The bugs

The first bug I fixed was not actually a real bug. It was a suboptimal choice. The script generates lots of SQL statements in a temporary table, then at the end sends them all to the SSMS window where you can select the output, copy and paste into a new query window, and then execute. When outputting the statements, it sorts them by statement type to ensure that objects are first dropped, then collations are changed, and then objects are recreated in an order that ensure that objects with dependencies are generated after the objects they depend on.

But I disagree with some of the ordering choices. For instance, when recreating objects the script first creates indexes, then unique constraints, and then primary key constraints. That is not wrong. But in my experience, the primary key is most often the clustered index. And when you build multiple indexes on the same table, it is smart to start with the clustered index – if you first build a nonclustered index, then that index has to be rebuilt when you later add a clustered index.

I did not try to add logic to look at which indexes are actually clustered and nonclustered to get the guaranteed best order. But I did switch the order. My version of the script now first generates primary key constraints, then unique constraints, and then the remaining indexes. So in most cases, this will build the clustered index first. Oh, and I did a similar change (using the reverse logic) in the part where constraints and indexes are dropped.

Another change I made quite quickly was to fix a small sloppiness. When recreating the foreign key constraints that originally were enabled and trusted, the code generated would force SQL Server to check the existing data twice. I removed the extra check.

I also did a small usability change. The script as posted returns two columns of output to SSMS. I removed the column with the sort order so that it outputs just the generated T-SQL statements. This makes it very easy to copy those statements and paste them in a new query window where I can review and then execute them.

After making those tweaks, I started testing the script. And that’s when the actual bugs popped up. I didn’t track all of them. One I remember is that wrong code was generated when a multi-column unique constraint overlaps with a multi-column primary key. After fixing that, I got errors where e.g. the same constraint was generated twice if it was a multi-column constraint (not sure if that’s an original bug or introduced by me when I fixed the first bug).

As I noticed more and more small issues, almost always related to multi-column constraints, I realized that the problem is that the script was originally completely column-based. For each column, it would check indexes, constraints, etc. That results either in duplication within the script (often, but not always, removed by using DISTINCT), or complex code to try to prevent the duplication.

So I decided that the best way to fix everything is to keep the outline of the script, but make the changed needed to do everything per table instead of per column, while still making sure not to do unneeded work (e.g. indexes on non-affected columns are never dropped and rebuilt).

After completing that work, I had a script that did what I needed: change the collation of all affected columns, including the dropping and recreating of objects that would prevent this collation change. I ran it on my test database and then used SQL Compare (I think this action alone already earned back the entire license fee) to verify that the two databases were now exactly the same except for the collations.

The script

Below you will find the final version of the script that I ended up using. There are some important points that I want to point out. Please take heed when you consider using the script:

• The original version of the script had some weird exceptions that I didn’t really understand, and that were not always consistent. Probably due to the various sources that Philip C used when he assembled the script. I removed most of them.
• The original version of the script also had logic to drop and recreate manually created statistics objects. I didn’t have any of those in the database I was working on, so to save some time I simply commented out that part of the code and didn’t try to figure out if there were any bugs there. Since I based some of the logic in the loop within the script, this part of the code almost certainly doesn’t work now. So if you need to use this script on a database that does have manually created statistics, you will have to uncomment these parts of the script and then make the necessary changes to get them to work.
• I have tested this scrip on the database that I needed to fix. This database is quite complex and big, but I have no guarantee that all possible situations are covered. Please, do not ever run this script on important production databases without first testing it on a test copy of that database, and without having a good backup available.
• The script is currently set to automatically find all columns that have a collation that is different from the database default collation. This is quite easy to change if your requirements are different.
• The script posted below only generates the code to make the changes; it does not automatically execute it. The generated output is sent to SSMS as a single result set. I recommend setting SSMS to use “results to text” (Ctrl-T), and checking the query options to make sure that the maximum number of character per column is suffienctly large – SSMS defaults this to 256 which is insufficient; I always set this to the maximum allowed value which is 8192.
• As mentioned above, I can not justify investing more time in this script now. The problem is fixed so I can’t improve it on customer time. And I need my own time to prepare my presentation for SQLBits next month.

The code

So without further ado, here is a script you can use, with the aforementioned precautions, to find all columns that do not have the database default collation and change them to that collation; including the dropping and recreating of objects as needed for this change.