Dynamic SQL – Page 3 – Darling Data

Just Using sp_executesql Doesn’t Make Dynamic SQL Safe From SQL Injection

Posted on June 10, 2019May 14, 2022 by Erik Darling

Safe Belt

A lot of people I’ve talked to about dynamic SQL have been under the misguided impression that just using sp_executesql will fix safety issues with SQL injection.

In reality, it’s only half the battle. The other half is learning how to act sober.

The gripes I hear about fully fixing dynamic SQL are:

The syntax is hard to remember (setting up and calling parameters)
It might lead to parameter sniffing issues

I can sympathize with both. Trading one problem for another problem generally isn’t something people get excited about.

Trading all the money in your company bank account to ransom your database probably isn’t something you’d get excited about either.

That’s not a very good lead on your rezoomay.

Holic

Here’s a trivial example:

CREATE TABLE dbo.DropMe(id INT);

DECLARE @DatabaseName sysname = N'';
SET @DatabaseName = N'S%'';DROP TABLE dbo.DropMe;--';

DECLARE @sql NVARCHAR(MAX) = N'
SELECT *
FROM sys.databases AS d
WHERE d.name LIKE ''%' + @DatabaseName + '%'';
';

PRINT @sql;
EXEC sys.sp_executesql @sql;

This will not only return a list of database names that contain S on my instance, but the printed SQL statement shows the whole string is executed.

SELECT *
FROM sys.databases AS d
WHERE d.name LIKE '%S%';DROP TABLE dbo.DropMe;--%';

Blue Flowers

The only way to not have that happen is to do this, and this is where people start complaining about remembering syntax:

CREATE TABLE dbo.DropMe(id INT);

DECLARE @DatabaseName sysname = N'';
SET @DatabaseName = N'S%'';DROP TABLE dbo.DropMe;--';

DECLARE @sql NVARCHAR(MAX) = N'
SELECT *
FROM sys.databases AS d
WHERE d.name LIKE ''%@iDatabaseName%'';
';

PRINT @sql;
EXEC sys.sp_executesql @sql, 
                       N'@iDatabaseName sysname', 
					   @iDatabaseName = @DatabaseName;

What prints out is this:

SELECT *
FROM sys.databases AS d
WHERE d.name LIKE '%@iDatabaseName%';

There’s also no search result returned, because no database is currently named ‘S%”;DROP TABLE dbo.DropMe;–‘.

But I get why people think this is annoying, because it is quirky at first.

If the string you use to encapsulate your parameters isn’t NVARCHAR, and/OR prefixed with N, you’ll get an error.
If you put your dynamic SQL variables on the wrong side of the equal sign, you’ll get an error.
And yes, if you’ve got skewed data, you’ll be more open to parameter sniffing.

The syntax stuff just takes a little getting used to, and performance stuff is often easier to fix than lost, stolen, or vandalized data.

Even if you’re real comfy with your backups, you’re still at risk of someone stealing confidential data.

Data Is A Liability

It’s really important that you review the personal data you collect to make sure it’s totally necessary.

It’s also really important for you to regularly archive data that you don’t actively need in your database.

For everything else, taking precautions like fixing unsafe dynamic SQL is just part of mitigating your data liabilities.

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.

Does sp_executesql WITH RECOMPILE Actually Recompile Query Plans In SQL Server?

Posted on February 18, 2019May 14, 2022 by Erik Darling

No, No It Doesn’t

But it’s fun to prove this stuff out.

Let’s take this index, and these queries.

CREATE INDEX ix_fraud ON dbo.Votes ( CreationDate );

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= '20101230';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= '20101231';

What a difference a day makes to a query plan!

Hard To Digest

Let’s paramaterize that!

DECLARE @creation_date DATETIME = '20101231';
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date;
'

EXEC sys.sp_executesql @sql, 
                       N'@i_creation_date DATETIME', 
                       @i_creation_date = @creation_date;

This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.

That’s an example of how parameters are sniffed.

Sometimes, that’s not a good thing. Like, if I passed in 2008-12-30, we probably wouldn’t like a lookup too much.

One common “solution” to parameter sniffing is to tack a recompile hint somewhere.

Recently, I saw someone use it like this:

DECLARE @creation_date DATETIME = '20101230';
DECLARE @sql NVARCHAR(MAX) = N''

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date;
'

EXEC sys.sp_executesql @sql, 
                       N'@i_creation_date DATETIME', 
                       @i_creation_date = @creation_date
                       WITH RECOMPILE;

Which… gives us the same plan. That doesn’t recompile the query that sp_executesql runs.

You can only do that by adding OPTION(RECOMPILE) to the query, like this:

SET @sql = @sql + N'
SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate >= @i_creation_date
OPTION(RECOMPILE);
'

A Dog Is A Cat

Chalk this one up to “maybe it wasn’t parameter sniffing” in the first place.

I don’t usually advocate for jumping right to recompile, mostly because it wipes the forensic trail from the plan cache.

There are some other potential issues, like plan compilation overhead, and there have been bugs around it in the past.

Thanks for reading!

Going Further

What Happens When You Don’t Parameterize Dynamic SQL In SQL Server?

Posted on January 30, 2019May 14, 2022 by Erik Darling

Unskinny Top

When I blogged about passing parameters to TOP, we ran into a problem that has many possible solutions.

Perhaps the least complex answer was just to fix the index. Nine times outta ten, that’s what I’d do.

Since I offered some other possible solutions, I do wanna talk about the pros and cons of them.

In this post, I’ll talk about using slightly less than fully parameterized dynamic SQL, which will, of course, terrify even people who live in Baltimore.

Disclaimer

While I’m not smart enough to figure out a SQL injection method without altering the stored procedure, that doesn’t mean it can’t happen.

It might be more difficult, but not impossible. Here’s our prize:

CREATE OR ALTER PROCEDURE dbo.top_sniffer (@top INT, @vtid INT)
AS
BEGIN

DECLARE @sql NVARCHAR(MAX) = N'';

SET @sql = @sql + N'
    SELECT   TOP (' + RTRIM(@top) + ')
             v.Id, 
             v.PostId, 
             v.UserId, 
             v.BountyAmount, 
             v.VoteTypeId, 
             v.CreationDate
    FROM     dbo.Votes AS v
    WHERE    v.VoteTypeId = @i_vtid
    ORDER BY v.CreationDate DESC;
    '

    PRINT @sql;
    EXEC sys.sp_executesql @sql, N'@i_vtid INT', @i_vtid = @vtid

END;

If we fully parameterize this, we’ll end up with the same problem we had before with plan reuse.

Since we don’t, we can can use a trick that works on filtered indexes.

But There’s Another Downside

Every different top will get a new plan. The upside is that plans with the same top may get reused, so it’s got a little something over recompile there.

So if I execute these:

/*Run me*/
EXEC dbo.top_sniffer @top = 1, @vtid = 4;

/*Run me*/
EXEC dbo.top_sniffer @top = 5000, @vtid = 4;

They each get their own plan:

And of course, their own plan cache entry.

SQL Server Query Results — I am your neighbor.

If lots of people look for lots of different TOPs (which you could cut down on by limiting the values your app will take, like via a dropdown), you can end up with a lot of plans kicking around.

Would I Do This?

Likely not, because of the potential risk, and the potential impact on the plan cache, but I thought it was interesting enough to follow up on.

Thanks for reading!