Troubles in Exec @stmt With Result Sets

This post was originally written and almost finished on the 10th of July 2012 and 5.5 years later, I decided that I rather publish it then rework further or keep on the ice (yeah, I have dozens of drafts – hehe).
With very minor addition/edition, to conserve the spirit of the original times, here we go …

A couple of days ago, I was presenting on the first SQLSaturday in China on the subject of the new T-SQL improvements in SQL Server 2012, and while talking about improvements for the EXEC statement, because a significant part of the audience was not introduced to the concept of SQL Injection.

Showing that the EXEC WITH RESULT SETS blocks unexpected & unrelated commands like when using an extra DML statement, such as an Update or Delete, I have got a question from one of the attendees, on if EXEC @stmt WITH RESULT SETS would still produce an error if I used a somewhat more destructive DROP TABLE command. I have honestly responded that I have never tried this combination (dang, this was an obvious candidate to try out), but that I expected the injected SQL statement to fail, because it was an extra command when just one command was specified in the result sets extension.
Oh well, I was quite wrong…
When I used DROP TABLE command, the SQL Server 2012 happily executed both commands, not rejecting and not producing any error message whatsoever.

Lets see this simple example, by creating simple test_table with 2 columns – an integer and a variable string column:

We need to insert a couple of rows to have some test data, and so here is the script with 3 simple rows insertion:

Testing the WITH RESULT SETS improvement here for the static statements (but which should be seen as a dynamic SQL injection attempts) – the first statement will select the string column name from the test_table:

and it works just fine without any problems as expected, because we have specified the RESULT SETS for the VARCHAR(50), the data type of our column i.

The second test would is a rather simple attempt at updating the name column with the second, unexpected and unspecified statement:

The statement runs without any problem!!! I guess that the most database developers will be rather surprised that a second statement executes

This means that any statements injected, that are not producing any result sets will run just fine, such as DROP TABLE, as presented below:

Yeap, your table is gone, baby! 🙂

Let’s recreate the table and I will show you how you will find this code in the wild:

Execute the basic test:

And now what will happen in the reality:

Trying to execute the same statement for the second time will lead to the following error message:

meaning that your table test_table is gone. 🙂

To finalise the experiment, let us make sure that the table is removed from the test database:

I always considered this WITH RESULT SETS enhancement to the EXECUTE statement as an important security improvement against the T-SQL injection attacks and seeing this entry open makes me a kind of surprised. I know that some of the very basic defenses for any interactive application is the creation of a special application user with limited permissions and almost always certainly without any access to the DDL, but there are times when it is simply impossible, when those commands are needed or the respective system administrators and application developers are simply now knowledgeable enough for it.

I do understand that the DDL commands are different enough and may not return any type of the result at all to create a lot of difficulties for their eventual implementation inside of the EXEC statement, but the reality is that a whole lot of DBAs and Developers are using them for some advanced scripts or dynamic statement execution.

I really hope that there will be some changes, and that maybe even in the next versions of SQL Server, Microsoft will implement a more complete support for eventual handling of DDL commands inside of the Execute statement.
This statement was written back in 2012, the release that had received a significant T-SQL improvements and my hopes were high. They are higher than 2 years ago, but still I do not see enough investment for the T-SQL in the past releases – window functions, and other improvements are still much needed. The developers need to get more love from the SQL Server & Azure SQL Database teams.

Oh, and please add more security features that will battle the default bad practices. A lot of them. 🙂

Leave a Reply

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