Starting SQL: What Happens When You Don’t Parameterize SQL Server Queries?

Posted on by Erik Darling

Nothing, Nothing, Nothing

Aside from the obvious perils of SQL Injection, parameterizing queries can be helpful in other ways.

Not to downplay SQL Injection at all, it’s just that fixing code is only one part of the equation. Vendor applications often need to do some pretty administrative things, and require elevated permissions.

Just last week I was looking at an application that had a stored procedure which took a parameter that was the name of an executable. There were no checks on the name. You could put in quite not-figuratively anything you wanted, and xp_cmdshell would run it.

All the parameterization in the world won’t help that.

Well, you get what you get.

Of Parameters And Plans

This can happen in three common ways:

  • You have an interface that accepts user-entered values
  • You have an ORM where you haven’t explicitly defined parameter types and precisions
  • You have dynamic SQL where values are concatenated into strings

Since I spend all my time in the database, I’m going to show you the third one. I wouldn’t normally format a query like this, but I’m trying to keep it web-friendly.

DECLARE @StartDate DATETIME = '20130101';
WHILE @StartDate < '20140101'
BEGIN
DECLARE @NoParams4u NVARCHAR(MAX) = 
N'
SELECT SUM(c.Score) AS TotalScore
FROM dbo.Comments AS c
WHERE c.CreationDate 
    BETWEEN CONVERT(DATETIME, ''' + RTRIM(@StartDate) + ''') 
    AND CONVERT(DATETIME, ''' + RTRIM(DATEADD(DAY, 11, @StartDate)) + ''')
AND 1 = (SELECT 1);
';

EXEC sys.sp_executesql @NoParams4u;
RAISERROR('%s', 0, 1, @NoParams4u);

SET @StartDate = DATEADD(DAY, 11, @StartDate);

END
GO

To make things interesting, I’ve created a non-covering index on the Comments table:

CREATE INDEX c ON dbo.Comments(CreationDate);

I’ve also had to use an oddly specific number of day increment in order to get some good plan variety because of that index. If you ever wonder why some blog posts take three hours to write, the most likely cause is finding the right number.

The first thing you should notice is that even using sp_executesql doesn’t help when our code isn’t parameterized. The second thing you’ll probably notice is 1 = (SELECT 1) at the end of the query. Past me has answers to all those questions, if you click on the links.

Bouquet

If I run that loop, I get back 34 results. Each query gets an individually compiled query plan, though there are only three “different” plans used.

SQL Server Query Plan
eyeful

Notice that each plan has different literal date values passed in to it, and different estimates. If we created a covering index, we’d get plan stability across executions, but then we’d still have to compile it when these literal values get passed in. I did it like this to reinforce my point.

We can validate that by looking in the plan cache and surrounding DMVs, using sp_BlitzCache.

Planimal Activist

If you have code like this, one good way to find culprits is by running it like this:

EXEC sp_BlitzCache @SortOrder = 'query hash';

A zoomed-in snapshot of the part of the results that prove my point looks about like this:

SQL Server Query Results
aye yi yi

This is just the top 10 results. You can see a warning about multiple plans, and that each query has a single execution.

I mentioned before that if we add a covering index, we’ll get plan stability. That’s true, but specific to this demo on SQL Server 2019, there’s an alternate plan available for executions that qualify for Batch Mode On Rowstore (BMOR):

SQL Server Query Plan
i’m in it

In prior versions of SQL Server in row mode only plans, we could only have a stream aggregate. But even getting the same plan most of the time, we still need to compile it every time. SQL Server still thinks each of these queries is “new” and needs to get a plan compiled.

Fixing It

There are two options for fixing this. If you need to do it quickly, at scale, the database level option FORCED PARAMETERIZATION can take care of most of these problems. Just make sure you read up on the limitations. To fix it for just a couple problem queries, you need to fix the dynamic SQL.

DECLARE @NoParams4u NVARCHAR(MAX) = 
N'
SELECT SUM(c.Score) AS TotalScore
FROM dbo.Comments AS c
WHERE c.CreationDate BETWEEN @StartDate AND DATEADD(DAY, 11, @StartDate)
AND 1 = (SELECT 1);
';

EXEC sys.sp_executesql @NoParams4u, N'@StartDate DATETIME', @StartDate;

I’m not concatenating values into the string anymore, and I’m passing the @StartDate value in when I execute the dynamic SQL.

One point I want to make is that it’s generally safe to do date math on the parameter. I’m not doing date math on the column, which would generally be a bad idea.

But anyway, now our plan gets used 34 times.

SQL Server Query Results
damn family

In this case, plan reuse works out well. Every query is looking at a sufficiently narrow range of data to have it not matter, and the longest running execution is around 31ms.

But what about when that doesn’t work out? When can parameterization backfire? We’ll find out tomorrow!

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts