In the ideal world, you fully test how your SQL Server will handle upgrading to the latest version. You’re able to catch and mitigate any performance surprises early on.
In the real world, you might not be able to perform a good test before upgrading. You only learn about poor performance once you upgrade in production.
Does this scenario sound familiar? It’s what I’ve been thinking about recently as I have to deal with query performance issues after upgrading to SQL Server 2016.
Coming from SQL Server 2012, I know the new cardnality estimator added in 2014 is a major source of the problems I’m experiencing. While the new cardinality estimator improves performance of some queries, it has also made made some of my queries take hours to run instead of seconds.
Long-term, the solution is to revisit these queries, their stats, and the execution plans being generated for them to see what can be rewritten for better performance.
But ain’t nobody got time for that (at least when facing performance crippling production issues).
Short-term, put a band-aid on to stop the bleeding.
I could change the compatibility mode of the database to revert back to SQL Server 2012 (before the new cardinality estimator was introduced), but then I miss out on being able to use all of the new SQL Server 2016 improvements just because a few queries are misbehaving.
I could enable trace flag 9481 to have my queries use the old cardinality estimator, however as a developer I probably don’t have access to play with trace flags (and for good reason).
Starting with 2016 SP1, what I can do is use the legacy cardinality estimator query hint:
This hint is great because it doesn’t require developers to have any special permissions. It also allows SQL to use the old cardinality estimator for poor performing queries only – the rest of the server gets to benefit from the improvements brought on by the new cardinality estimator.
With time, I can revisit the queries that are using this hint to try to improve their performance, but in the mean time it’s a great way to fix regressed query performance due to the new cardinality estimator.
Although QUOTENAME() works well by adding brackets (by default) to sanitize quotes, it’s downside is that it will only output strings up to 128 characters long. If you are expecting parameters with values longer than that, you will have to use something like REPLACE(@TableName,'''','''''') instead to delimit single quotes (however, rolling your own logic like this is really hard to do securely and not recommended).
The account running any dynamic SQL queries should be locked down so that it won’t be able to perform any operations you don’t want it to do.
Taking this idea one step further, you can create another account with very limited permissions, and add EXECUTE AS to your stored procedure to run that code under the very limited account.
And then let’s create a simple stored procedure that will query that table:
CREATE PROCEDURE dbo.sp_GetFullName
SET@FullQuery='SELECT FullName FROM dbo.RegisteredUser WHERE UserName = '''+@ParmUserName+''''
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:
Cool. No problems so far.
However, what if our user decides to pass in the following value as their username?
EXEC dbo.sp_GetFullName'TFly37'' or 1=1 --'
This funny looking parameter value returns this:
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:
CREATE PROCEDURE dbo.sp_GetFullNameSafe
SELECT FullName FROM dbo.RegisteredUser WHERE UserName=@ParmUserName
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:
CREATE PROCEDURE dbo.sp_GetFullNameSafe2
SET@FullQuery=N'SELECT FullName FROM dbo.RegisteredUser WHERE UserName = @UserName'