Warning! Are your queries vulnerable to SQL injection?

Published on: 2017-08-29

Medicine 1” by marosh is licensed under CC BY-NC-ND 2.0.

Looking for a script to find possible SQL injection vulnerabilities on your server? Scroll to the bottom of this post.

Watch this content on YouTube.


OWASP names SQL injection as the #1 vulnerability for web applications. The infamy of this attack has even made its way into the popular XKCD comic.

What is SQL injection?

A SQL query is vulnerable to SQL injection if a user can run a query other than the one that was originally intended.

Sometimes SQL injection is easier to understand with an example. Let’s use this table of registered users:

And then let’s create a simple stored procedure that will query that table:

The important thing to note in the query above is that we are generating a dynamic SQL statement; that is, we are building the SQL query string, and then we are executing it.

Imagine this stored procedure is running in order to display a “Welcome <Full Name>!” message in our app — a website visitor types in their@ParmUserName and we execute the stored procedure to return their full name.

Here’s our code that calls the stored procedure:

And result:

Cool. No problems so far.

However, what if our user decides to pass in the following value as their username?

This funny looking parameter value returns this:

AHHHHHH!!!!

This user just hacked our website and viewed all of the users in our table.

In this specific case only our user’s full names were breached, but in other instances it’s just as easy for more sensitive data like passwords, social security numbers, and bank account numbers to be revealed as well (If you are looking for some fun, search for “SQL injection” on the Have I been pwned? list of Pwned websites to see all of the companies that aren’t doing a good job protecting your privacy).

So how did that example of SQL injection actually work?

Since our stored procedure executes a dynamically generated query, let’s look at what that generated query actually looks like for both of the parameters that were passed in:

Even though TFly37'' or 1=1-- doesn’t look like a intelligible input parameter, when its concatenated into our query it makes sense.

Our malicious user is basically writing their own SQL query, one that will return all of the names of our users instead of just their own. In many instances, the crafty hacker can go a step further and write additional injection code to reveal the contents of the entire user table (or other tables in the database)!

How do I prevent SQL injection?

Simple: don’t concatenate unsanitized user input data to your SQL queries.

In this example, this is easy to do: simply rewrite the stored procedure to not use dynamic SQL:

When you don’t have dynamic SQL, you can’t be affected by SQL injection attempts.

Avoiding dynamic SQL isn’t always possible though, so what can we do in those cases? Use sp_executesql:

sp_executesql allows for parameterization of your dynamic SQL, removing the possibility of unwanted code injection.

Are any of my current SQL queries vulnerable to SQL injection attacks?

So SQL injection is really bad and you don’t want to become like Sony or Yahoo. How do we check to see if we have any vulnerable queries on our server?

I wrote the query below to help you start searching. It is not perfect, but it does act as a good starting point for auditing potentially injectable queries:

I’ve found this script useful for myself, however if you find any issues with it please let me know, thanks!

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

How to Search and Destroy Non-SARGable Queries on Your Server

Published on: 2017-08-22

Unexpected SQL Server Performance Killers #3

Photo by Cibi Chakravarthi on Unsplash

In this series I explore scenarios that hurt SQL Server performance and show you how to avoid them. Pulled from my collection of “things I didn’t know I was doing wrong for years.”

Watch this post on YouTube.


Looking for a script to find non-SARGable queries on your server? Scroll to the bottom of this post.

What is a “SARGable” query?

Just because you add an index to your table doesn’t mean you get immediate performance improvement. A query running against that table needs to be written in such a way that it actually takes advantage of that index.

SARGable, or “Search Argument-able”, queries therefore are queries that are capable of utilizing indexes.

Examples please!

Okay let’s see some examples of SARGable and non-SARGable queries using my favorite beverage data.

There are non-clustered indexes on the Name and CreateDate columns

First, let’s look at a non-SARGable query:

Although this query correctly filters our rows to a specific date, it does so with this lousy execution plan:

SQL Server has to perform an Index Scan, or in other words has to check every single page of this index, to find our ‘2017–08–19’ date value.

SQL Server does this because it can’t immediately look at the value in the index and see if it is equal to the ‘2017–08–19’ date we supplied — we told it first to convert every value in our column/index to a CHAR(10) date string so that it can be compared as a string.

Since the SQL Server has to first convert every single date in our column/index to a CHAR(10) string, that means it ends up having to read every single page of our index to do so.

The better option here would be to leave the column/index value as a datetime2 datatype and instead convert the right hand of the operand to a datetime2:

Alternatively, SQL Server is smart enough to do this conversion implicitly for us if we just leave our ‘2017–08–19’ date as a string:

In this scenario SQL gives us an Index Seek because it doesn’t have to modify any values in the column/index in order to be able to compare it to the datetime2 value that ‘2017–08–19’ got converted to.

This means SQL only has to read what it needs to output to the results. Much more efficient.

One more example

Based on the last example we can assume that any function, explicit or implicit, that is running on the column side of an operator will result in a query that cannot make use of index seeks, making it non-SARGable.

That means that instead of doing something non-SARGable like this:

We want to make it SARGable by doing this instead:

In short, keep in mind whether SQL Server will have to modify the data in a column/index in order to compare it — if it does, your query probably isn’t SARGable and you are going to end up scanning instead of seeking.

OK, non-SARGable queries are bad…how do I check if I have any on my server?

The script below looks at cached query plans and searches them for any table or index scans. Next, it looks for scalar operators, and if it finds any it means we have ourselves a non-SARGable query. The fix is then to rewrite the query to be SARGable or add a missing index.

 

I’ve found this script useful for myself, but if you find any issues with it please let me know, thanks!

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

How to Eliminate Ugly Nested REPLACE() Functions

Published on: 2017-08-15

SQL in 60 Seconds #4

On white: Who you really are” by James Jordan is licensed under CC BY-ND 2.0

Prefer video? Watch this content on my YouTube channel!

How many times have you had to transform some column value and ended up stacking several nested SQL REPLACE() functions like this?

Ugly right? And that’s after careful formatting to try and make it look readable. I could have just left it as:

Here we only have 4 nested REPLACE functions. My shameful record is 29. I’m not proud of it, but sometimes it’s the only way to get things done.

Not only are these nested REPLACE() functions difficult to write, but they are difficult to read too.

Instead of suffering through all of that ugly nesting, what you can do instead is use CROSS APPLY:

Technically the CROSS APPLY solution uses more characters. But it is infinitely more readable.

And the server? The server doesn’t care about the additional characters —it still compiles it down to the same 1s and 0s:

So next time you have to nest several REPLACE() functions (or any other string functions), do yourself a favor and make it more readable by using CROSS APPLY instead. Your future self will thank you.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!