What's a good way to protect dynamic SQL code against injections on SQL Server?

Question

I know dynamic SQL should never be a first option, and trust me it wasn't, but it's what's working now, and I'd like to find a way to at least offer some sort of protection against SQL injections, anything's better than nothing right? By the way I did search for related questions and found a bunch, but nothing using SPs on SQL Server.

The reason why dynamic SQL is needed in this application is because of several stored procedures that can be executed by the program, but they can select information from different databases which we don't know what they'll be named. We do know the names for these databases will be located in another database though, that's how previous systems have been deployed by the users.

So when the user runs the application, it shows him the databases he has access to, and when he selects one, the stored procedures are executed with the selected database, this via dynamic SQL.

At first I thought the only parameter that would be used dynamically would be the database, but looking at how the parameters are being sent, apparently they will all be dynamical, look at this example:

CREATE PROCEDURE testProc @myDatabase varchar(30), @myMonth varchar(10)
AS
BEGIN
DECLARE @sql nvarchar(1000)
SET @sql = 'SELECT * FROM '+@myDatabase+'.dbo.myTable WHERE Month='+@myMonth+''
EXECUTE sp_executesql @sql
END

Now this application is going to be used mostly if not only by people that are most likely not going to want to drop their own databases, and the options they will be given will be limited, however the application allows them to create more fields to filter data, and if they wanted to, they could just drop a drop here or there. I'm not saying they will, but it could happen, so is there a way I could make this at least a little bit more secure from the Procedure creation itself?

Already answered, see here: http://stackoverflow.com/questions/1246760/how-should-i-pass-a-table-name-into-a-stored-proc — RBarryYoung, Dec 27 '13 at 20:42
@RBarryYoung to be fair, it looks like the same answer to a different question :) — Stuart Ainsworth, Dec 27 '13 at 21:05

Ivan Golović · Answer 1 · 2013-12-28T07:56:09.930

You should use sp_executesql with parameters, that is one of the advantages of using sp_executesql over EXEC for executing dynamic SQL. Also, maximum length for database name in SQL Server 2008 is 128 characters so you might want to change @myDatabase length accordingly. I'm not sure whether it's possible to insert database name as variable in parametrized command string, but you can check if such database exists before executing SELECT statement. In your case that would be:

CREATE PROCEDURE testProc
    @myDatabase NVARCHAR(30),
    @myMonth VARCHAR(10)
AS
BEGIN

    DECLARE @sql NVARCHAR(1000)

    IF EXISTS (SELECT 1 FROM sys.databases AS db WHERE db.name = @myDatabase)
    BEGIN

        SET @sql = 'SELECT * FROM ' + @myDatabase + '.dbo.myTable WHERE Month=@myMonth';
        EXECUTE sp_executesql @sql, N'@myMonth VARCHAR(10)', @myMonth
    END

END
GO

When @myDatabase is compared to sys.databases.name it should be NVARCHAR since name is SYSNAME which is of type NVARCHAR(128). Dynamic SQL that takes parameters from user is insecure but if you must use it, sp_executesql procedure with parameters should be used. Following book has a chapter describing relation of SQL injection and dynamic SQL, and here are some quotes from it:

From the standpoint of a T-SQL developer, one of the most important methods is to parameterize the dynamic SQL generation and execution by using sp_executesql

Querying Microsoft® SQL Server 2012, Itzik Ben-Gan, Dejan Sarka, Ron Talmage (page 456).

The ability to parameterize means that sp_excutesql avoids simple concatenations like those used in the EXEC statement. As a result, it can be used to help prevent SQL injection.

Querying Microsoft® SQL Server 2012, Itzik Ben-Gan, Dejan Sarka, Ron Talmage (page 458).

What's a good way to protect dynamic SQL code against injections on SQL Server?

1 Answers1