Dynamic SQL and SQL injection

When I wrote about the catch-all queries, someone asked why the dynamic SQL that I offered wasn’t vulnerable to SQL injection. I thought I’d go into the whys and the wherefores of that in a little bit more detail.

I’m just going to look at SQL injection from the aspect of dynamic SQL. The front-end code aspect has been dealt with hundreds of times, most recently here – http://www.simple-talk.com/community/blogs/philfactor/archive/2009/03/30/72651.aspx

The most important thing to realise with SQL Injection (and with all other forms of command injection) is that it requires that a user-inputted string be incorperated as part of a command that’s going to be executed. Not as part of a paramter value, but as part of the command itself.

Let me show you want I mean.

DECLARE @sSQL varchar(500)
SET @sSQL = 'SELECT * FROM sys.objects'

EXECUTE (@sSQL)

In this exeedingly simple example, there’s no possibility for SQL injection. There’s no user-inputted string that can become part of the command. Let’s look at two slightly more complex examples

Example 1:

DECLARE @inputParam VARCHAR(100) -- Assume this comes from user input
DECLARE @sSQL varchar(500)

SET @sSQL = 'SELECT * FROM '

IF @inputParam = 'Table1'
SET @sSQL = @sSQL + 'Table1'
IF @inputParam = 'Table2'
SET @sSQL = @sSQL + 'Table2'
IF @inputParam = 'Table3'
SET @sSQL = @sSQL + 'Table3'
IF @inputParam = 'Table4'
SET @sSQL = @sSQL + 'Table4'

EXECUTE (@sSQL)

Example 2:

DECLARE @inputParam VARCHAR(100) -- Assume this comes from user input
DECLARE @sSQL varchar(500)

SET @sSQL = 'SELECT * FROM ' + @inputParam

EXECUTE (@sSQL)

Now, what about these two examples? Let’s assume that someone’s trying a SQL injection attack and has passed, for @inputParam, a value of “Table1; Drop Table Table1 –”

In example 1, that value that’s passed in does not match any of the IF conditions. Hence, the resulting SQL that will get executed is ‘SELECT * FROM ‘. That’s going to throw a syntax error, but nothing more. The malicious statement did not get injected into the command that was run. Hence, no SQL injection here.

What about example 2? For the same value of @inputParam, the command that will be executed is ‘SELECT * FROM Table1; Drop Table Table1 –‘. When that’s run, assuming sufficient permissions, Table1 is going to be dropped. Not good.

In this case, because the input parameter was made a direct part of the string that was getting executed, there was a possibility of SQL injection; this example is vulnerable.

Now let’s look at a couple of examples similar to the one I gave in my previous post, ones with dynamic where clauses.

Example 1:

DECLARE @inputParam1 VARCHAR(100) -- Assume this comes from user input
DECLARE @inputParam2 VARCHAR(100) -- Assume this comes from user input
DECLARE @sSQL nvarchar(500)

SET @sSQL = 'SELECT * FROM SomeTable WHERE Active = 1 '
IF @inputParam1 IS NOT NULL
SET @sSQL = @sSQL + ' AND Column1 = @innerParameter1'
IF @inputParam2 IS NOT NULL
SET @sSQL = @sSQL + ' AND Column2 = @innerParameter2'

exec sp_executesql @sSQL, '@innerParameter1 varchar(100), @innerParameter2 varchar(100)', @innerParameter1 = @inputParam1, @innerParameter2 = @inputParam2

Example 2:

DECLARE @inputParam1 VARCHAR(100) -- Assume this comes from user input
DECLARE @inputParam2 VARCHAR(100) -- Assume this comes from user input
DECLARE @sSQL varchar(500)

SET @sSQL = 'SELECT * FROM SomeTable WHERE Active = 1 '
IF @inputParam1 IS NOT NULL
SET @sSQL = @sSQL + ' AND Column1 = ''' + @inputParam1 + ''''
IF @inputParam2 IS NOT NULL
SET @sSQL = @sSQL + ' AND Column2 = ''' + @inputParam2 + ''''

EXECUTE (@sSQL)

In the first example, the imput parameters never become a direct part of the string that is being executed. They are used to control what portions are added to the string and they are passed, as parameters, to sp_executesql, but they themselves are not incorperated into the string.

In the second example, the parameters are used to control what portions are added to the string but they are also directly concatenated into the string. So whatever’s inside the parameters will become part of the string that is going to be executed.

So, what happens in this case if a malicious user passes, for inputParam1, this: “abc’; drop table SomeTable;–” and leaves inputParameter2 blank

In the first example, since inputParam1 has a value and inputParam2 does not, the resulting SQL string is

SELECT * FROM SomeTable
WHERE Active = 1 AND Column1 = @innerParameter1

That is then executed by sp_executesql and the value with the attempted SQL injection is then passed as a parameter and the query executes looking for rows where Column1 has the actual value “abc’; drop table SomeTable;–” (which is quite unlikely to match anything). Since the input parameters did not become part of the string executed, there is no possibility for SQL injection here

What about the second example?

Well, in that example, if inputParam1 has the same value given in above and inputparam2 is blank, the resulting string that will be executed is

SELECT * FROM SomeTable
WHERE Active = 1 AND Column1 = 'abc'; drop table SomeTable;--'

Not good.

So, in summary, if a user-specified value is included as actual part of a SQL statement to be executed, it is vulnerable to SQL injection. If the parameters are used rather to control what the string looks like but are not made a direct part of it, then there is no opening for SQL injection. I hope this has cleared up at least a little bit of the confusion around the topic.