Category: Dynamic SQL

One More Thing

I always try to impart on people that SQL injection isn’t necessarily about vandalizing or trashing data in some way.

Often it’s about getting data. One great way to figure out how difficult it might be to get that data is to figure out who you’re logged in as.

There’s a somewhat easy way to figure out if you’re logged in as sa.

Wanna see it?

Still Ill

	SELECT SUSER_SID();  
	SELECT CONVERT(INT, SUSER_SID()); 
	SELECT SUSER_NAME(1);
	
    DECLARE @Top INT = 1000 + (SELECT CONVERT(INT, SUSER_SID()));

	SELECT TOP (@Top)	       
    		u.Id, u.DisplayName, u.Reputation, u.CreationDate
    FROM dbo.Users AS u
    ORDER BY u.Reputation DESC;
    GO

It doesn’t even require dynamic SQL.

All you need is a user entry field to do something like pass in how many records you want returned.

The results of the first three selects looks like this:

This is always the case for the sa login.

If your app is logged in using it, the results of the TOP will return 1001 rows rather than 1000 rows.

If it’s a different login, the number could end up being positive or negative, and so a little bit more difficult to work with.

But hey! Things.

Validation

Be mindful of those input fields.

Lots of times, I’ll see people have what should be integer fields accept string values so users can use shortcut codes.

For example, let’s say we wanted someone to be able to select all available rows without making them memorize the integer maximum.

We might use a text field so someone could say “all” instead of 2147483647.

Then uh, you know.

Out comes ISNUMERIC and all its failings.

Thanks for reading!

Tinker Toy

Let’s say you have dynamic SQL that selects different different data based on some conditions.

Let’s also say that data needs to end up in a temp table.

Your options officially suck.

If you create the table outside dynamic SQL, you need to know which columns to use, and how many, to insert into the table.

You can’t do SELECT…INTO with an EXEC.

If you create the table inside dynamic SQL, you can’t use it outside the dynamic SQL.

But…

Altered Images

There’s a fun function in SQL Server 2012+, dm_exec_describe_first_result_set.

People mainly use it for stored procedures (I think?), but it can also work like this:

DECLARE @sql1 NVARCHAR(MAX) = N'SELECT TOP 10 * FROM dbo.Users AS u WHERE u.Reputation > @i';
DECLARE @sql2 NVARCHAR(MAX) = N'SELECT TOP 10 * FROM dbo.Posts AS p WHERE p.Score > @i';

SELECT column_ordinal, name, system_type_name
FROM   sys.dm_exec_describe_first_result_set(@sql1, NULL, 0);

SELECT column_ordinal, name, system_type_name
FROM   sys.dm_exec_describe_first_result_set(@sql2, NULL, 0);

The results for the Users table look like this:

Don’t Judge Me

The best way I’ve found to do this is to use that output to generate an ALTER TABLE to add the correct columns and data types.

Here’s a dummy stored procedure that does it:

CREATE OR ALTER PROCEDURE dbo.dynamic_temp ( @TableName NVARCHAR(128))
AS
    BEGIN
        SET NOCOUNT ON;

        CREATE TABLE #t ( Id INT );
        DECLARE @sql NVARCHAR(MAX) = N'';

        IF @TableName = N'Users'
            BEGIN
                SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Users AS u WHERE u.Reputation > @i';
            END;

        IF @TableName = N'Posts'
            BEGIN
                SET @sql = @sql + N'SELECT TOP 10 * FROM dbo.Posts AS p WHERE p.Score > @i';
            END;

        SELECT   column_ordinal, name, system_type_name
        INTO     #dfr
        FROM     sys.dm_exec_describe_first_result_set(@sql, NULL, 0)
        ORDER BY column_ordinal;

        DECLARE @alter NVARCHAR(MAX) = N'ALTER TABLE #t ADD ';

        SET @alter += STUFF((   SELECT   NCHAR(10) + d.name + N' ' + d.system_type_name + N','
                                FROM     #dfr AS d
                                WHERE    d.name <> N'Id'
                                ORDER BY d.column_ordinal
                                FOR XML PATH(N''), TYPE ).value(N'.[1]', N'NVARCHAR(4000)'), 1, 1, N'');

        SET @alter = LEFT(@alter, LEN(@alter) - 1);

        EXEC ( @alter );

        INSERT #t
        EXEC sys.sp_executesql @sql, N'@i INT', @i = 10000;

        SELECT *
        FROM   #t;

    END;
GO

I can execute it for either Users or Posts, and get back the results I want.

EXEC dbo.dynamic_temp @TableName = 'Users';
EXEC dbo.dynamic_temp @TableName = 'Posts';

So yeah, this is generally a pretty weird requirement.

It might even qualify as Bad Idea Spandex™

Thanks for reading!

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!