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:

-- Drop if test_table exists
if( exists( select 1 from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME = 'test_table') )
	drop table test_table;
GO

-- create a new test_table
create table test_table(
	i int identity(1,1) primary key,
	name varchar(50) unique );
GO

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

-- inserting some values into the test_table 
 insert into test_table
	(name) 
	values ('Test 1'),('Test 2'),('Test 3');

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:

-- Works fine
exec ('select name from test_table') with result sets ( (name varchar(50)) );

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:

exec ('select name from test_table; update test_table set name = ''niko'' + cast(i as VARCHAR(5));') with result sets ( (name varchar(50)) );

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:

-- Does not fail, but as for me it should produce an error message
exec ('select name from test_table; drop table test_table;') with result sets ( (name varchar(50)) );

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:

 
create table test_table(
	i int identity(1,1) primary key,
	name varchar(50) unique );
GO
-- inserting some values into the test_table 
 insert into test_table
	(name) 
	values ('Test 1'),('Test 2'),('Test 3');

Now let’s create a typical stored procedure that will read the data from the test_table by the specified id and to feel safe we can add the WITH RESULT SETS ( (name varchar(50)) ) so that nothing will happen to us:

create procedure dbo.ExecSql( @id VARCHAR(50)) AS
BEGIN
	EXECUTE (N'SELECT * FROM dbo.test_table WHERE i = ' + @id) with result sets ( (name varchar(50)) );
END

Execute the basic test:

EXECUTE dbo.ExecSql @id = 1;

And now what will happen in the reality:

EXEC dbo.ExecSql @id = '1; DROP TABLE dbo.test_table;';

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

Msg 208, Level 16, State 1, Line 8
Invalid object name 'dbo.test_table'.
Msg 11536, Level 16, State 1, Procedure ExecSql, Line 3 [Batch Start Line 7]
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

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:

-- Drop it
if( exists( select 1 from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME = 'test_table') )
	drop table test_table;
GO

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