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:
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!
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.