Starting SQL: Unparameterized Strings In Dynamic SQL

Roaster


It doesn’t take much to let a bad person get at your data. I mean, the internet. Right? What a mistake.

Most of the time, you need to parameterize your code to avoid SQL injection. Hooray, we did it.

But there are some aspects to dynamic SQL where you can’t use parameters, like database, schema, table and column names. Worse, if you need to pass in or build a list of columns, you can’t possibly parameterize each one.

These strings are where the biggest potential for problems lies, though. They’ll often be declared are some long variable length to safeguard against string truncation, which leaves plenty of room to tack on whatever additional payload you want the query to execute.

Not Dropping, Dumping


When it comes to permissions, it’s somewhat easier to disallow an app login from dropping databases or tables. Of course, it’s a bit of a joke expecting that sort of granularity from most applications.

They’ll all wanna do something that requires that privilege occasionally, so will be granted the privilege perpetually.

What a nice time that is.

But let’s face it, dropping things is at best a temporary disruption. Stealing data is forever, especially if you can just keep stealing it. For example, this query can be used to get the results of what’s in sys.tables along with regular query results. It doesn’t take a rocket surgeon to figure out how things line up.

DECLARE @SQLString NVARCHAR(MAX) = N'';
DECLARE @Filter NVARCHAR(MAX) = N'';
DECLARE @nl NCHAR(2) = NCHAR(13);
DECLARE @Title NVARCHAR(250) =  N''' 
UNION ALL 
SELECT t.object_id, 
       t.schema_id, 
       t.name, 
       SCHEMA_NAME(t.schema_id),
       t.create_date,
       t.modify_date,
       NULL
FROM sys.tables AS t --';

SET @SQLString = @SQLString + 
N'
SELECT TOP (5000) 
    p.OwnerUserId,
    p.Score,
    p.Tags,
    p.Title, 
    p.CreationDate, 
    p.LastActivityDate, 
    p.Body
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656 ' + @nl;

IF @Title IS NOT NULL 
   BEGIN SET @Filter = @Filter + N'   AND p.Title LIKE ''' + N'%' + @Title + N'%''' + @nl; END;

IF @Filter IS NOT NULL
   BEGIN SET @SQLString += @Filter; END;

SET @SQLString += N'   ORDER BY p.Score DESC;';

PRINT @SQLString;
EXEC (@SQLString);

And of course, after sys.tables you have sys.columns, and once you know which columns are in which table you want to go after, the rest is soft cheese.

Zero Injection Policy


If we wanted to not have that happen, we could write our SQL like this instead:

DECLARE @SQLString NVARCHAR(MAX) = N'';
DECLARE @Filter NVARCHAR(MAX) = N'';
DECLARE @nl NCHAR(2) = NCHAR(13);
DECLARE @Title NVARCHAR(250) =  N''' 
UNION ALL 
SELECT t.object_id, 
       t.schema_id, 
       t.name, 
       SCHEMA_NAME(t.schema_id),
       t.create_date,
       t.modify_date,
       NULL
FROM sys.tables AS t --';

SET @SQLString = @SQLString + 
N'
SELECT TOP (5000) 
    p.OwnerUserId,
    p.Score,
    p.Tags,
    p.Title, 
    p.CreationDate, 
    p.LastActivityDate, 
    p.Body
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656 ' + @nl;

IF @Title IS NOT NULL 
   BEGIN SET @Filter = @Filter + N'   AND p.Title LIKE %@Title% ' + @nl; END;

IF @Filter IS NOT NULL
   BEGIN SET @SQLString += @Filter; END;

SET @SQLString += N'   ORDER BY p.Score DESC;';

PRINT @SQLString;
EXEC sys.sp_executesql @SQLString, 
                       N'@Title NVARCHAR(250)',
                       @Title;

The difference in output is quite apparent:

the broom snapped in two

But What About!


Yes, all those object names. Terrible about them, huh?

Of course, we have QUOTENAME to save us from those, and examples of it aren’t terribly interesting. This time, you get a link to the docs page.

Thanks for reading!

A Word From Our Sponsors


Following on the GREAT SUCCESS of previous online events, I’m kicking things back off with another offering of my Premium Performance Tuning class, before I retire and remix the material.

To buy tickets, head over here. It’s going to be taking place Friday, October 23rd from 9am-5pm EST.

The normal ticket price is $300, but all September I’m running a 50% off sale with the coupon code “stillonline”.

Every purchase comes with access to my recorded video training for life, too.



Leave a Reply

Your email address will not be published. Required fields are marked *