Comment (0)

Using dynamic SQL is unavoidable at times, but it is reckless to execute dynamic SQL that is created directly from strings that include values that change at execution time. It can allow SQL Injection and it is also inefficient.

SQL Prompt’s code analysis rule, BP013, will alert you to use of Execute(<string>) to execute a batch in a string, often assembled dynamically from user input. Use of this command is particularly suspect because it doesn’t allow you to parameterize values.

Even if you have full control and oversight over the values that can be submitted and are certain they could never come from an end user, it is still much better to parameterize the inputs using the sp_ExecuteSQL<string>stored procedure. This is not only more secure but also helps the query optimizer to realize that the SQL batch you want to execute is parameterized and, therefore, amenable to a reusable plan.

Both the EXECUTE command and the sp_ExecuteSQL procedure execute a batch, not just a query. So, even if you’re using sp_ExecuteSQL, it is easy to carelessly introduce a vulnerability to SQL Injection into stored procedures. If you injudiciously concatenate SQL with a parameter, it will still allow a malicious user to introduce input that adds extra statements into that batch.

SQL Injection Vulnerabilities

Imagine that an AdventureWorks developer sees this SQL and thinks, "Aha, I can provide any list as a parameter."

SELECT * FROM person.person WHERE lastname IN ( 'Goldberg', 'Erickson', 'Walters' );

Fine, so within a stored procedure, he assembles the code dynamically.

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

So now, he can supply any list of peoples’ last names and get a result. However, reality soon strikes when the code reviewer points out the horrid possibilities:

DECLARE @MyList NVARCHAR(50)='''factor'') Select * from sales.creditcard --'    EXECUTE ('SELECT * from person.person where lastname in ('+@myList+')')

Surely this is a bit academic, you might think, because SQL Injection is all about unparameterized queries in the application. Well, no; it isn’t just the application that can introduce the vulnerabilities. The application can parametrize a call to a stored procedure correctly, and if the vulnerability is in the stored procedure itself, an exploit can be successful. This is much easier to explain via a demonstration.

Hacking AdventureWorks

Let’s imagine that AdventureWorks creates a new website and the devs want a product search screen where the user types in a search term and all the products that are relevant appear in a list. Yes, it seems reasonable.

A developer who is new to databases is assigned the task. The website has a pooled connection in the name of a user called WebUser. The team is careful and makes sure WebUser has no access to any sensitive information on the website. It can access only the stored procedures on the website that deal specifically with WebUser’s requests. These stored procedures run in the context of the current owner of the module, to access a restricted portion of the data in a small number of base tables. This is done to allow a user such as WebUser to access the to the required data without being able to access to any table directly. This effectively prevents any SQL Injection from the application from accessing anything other than the procedures or functions in the interface. Some database designers prefer to use users without logins to provide this service.

So far, so good.

After creating a procedure and asking a database developer to install it into the database, the developer then, in the application, carefully parameterizes the call to the stored procedure to ensure that there won’t be any SQL Injection from the application.

Here is the procedure. The comments are mine, of course. I wouldn’t want anyone to think that this is good practice. All the sensible precautions described above will be undone by the use of EXECUTE(<string>).

/* we will now create a procedure that not only uses EXECUTE but also   fails to check the contents of the string parameter passed to it*/   IF Object_Id('dbo.SelectProductModel') IS NOT NULL       DROP procedure dbo.SelectProductModel;   GO   CREATE PROCEDURE dbo.SelectProductModel @queryString VARCHAR(255)   WITH EXECUTE AS OWNER --to execute as the login who created this procedure   AS--health warning!!! This is a demonstration of how not to do it   EXECUTE (   'SELECT name,summary,Wheel,Saddle,Pedal, RiderExperience      FROM Production.vProductModelCatalogDescription       WHERE ( name+summary  LIKE ''%'+ @queryString+'%'' )')   GO--health warning!!! This is a demonstration of how not to do it

There are occasionally good reasons for using dynamically-executed code, but these almost always involve parameters. In this case, the developer needed to use sp_ExecuteSql. On top of that, all parameters should be passed to it using the correct convention. In this example, the use of dynamic SQL was unnecessary, but if it had been, it should have been done like this.

IF Object_Id('dbo.SelectProductModel2') IS NOT NULL       DROP procedure dbo.SelectProductModel2;   GO   CREATE PROCEDURE dbo.SelectProductModel2 @queryString VARCHAR(255)   WITH EXECUTE AS owner   AS   EXECUTE sp_ExecuteSQL    N'SELECT name,summary,Wheel,Saddle,Pedal, RiderExperience      FROM Production.vProductModelCatalogDescription      WHERE (name+summary  LIKE ''%''+@search+''%'')',N'@search Varchar(20)',@search=@QueryString   GO

You can try this version in the following examples to prove it to yourself. I would add some parameter validation too because I like to sleep soundly at night. There are phrases that a customer would want to search about bikes, but that doesn’t include, ‘–‘, for example.

Now, in order to illustrate it, I should make you, the reader, set up a website with a search form and a grid to display the results. Instead, we will simulate this in SSMS so you can play along and try things out.

Having set up the evil procedure we create the website user with no permissions to do anything. Then we execute as that user and see how far we can get through a conspicuous data breach.

IF EXISTS (SELECT * FROM sys.sysusers AS S2 WHERE S2.name LIKE 'WebUser')        DROP USER Webuser;     -- We need to execute some of the following code with the restricted access rights of a     -- typical web user that has only access rights to the stored procedure that accesses     -- the table We then run part of the script as that user.     CREATE USER WebUser WITHOUT LOGIN;   /* we now assign it permission to call the stored procedure. It has no choice because   this is being done in middleware on the web server. Every attempt to break into the database has to be done merely by changing the search term for Adventureworks bicycles.   */    GRANT EXECUTE ON OBJECT::dbo.SelectProductModel       TO WebUser;      GRANT EXECUTE ON OBJECT::dbo.SelectProductModel2       TO WebUser;    execute as user = 'WebUser'   /* now we are working as WebUser. */ 

You have to imagine that I’m an external intruder, executing this from a website, rather than SSMS (if I have access to that, I’m already home and dry). I have to put a search term into a web form and study the results. I know nothing about this database. I am helped because errors are displayed, and I can see results on the website results grid. Even without the errors, I would only be slightly inhibited, as one can judge a lot from the response time.

My first objective is to find out the names of the tables and their schemas. If I don’t know them, there is a lot of trial and error that can take a long time and much patience. I then need to know the columns and datatypes. If I can find a way of doing all that, then I’m on the home straight.

Let’s start.

Execute dbo.SelectProductModel 'light'

It seems to work. Let’s check to see if this is properly parameterized by adding an expression. I, as ‘WebUser‘, wonder if the ‘always true’ trick works. I key into the Web page ‘light” or 1=1 –‘ for the search term:

EXECUTE dbo.SelectProductModel 'light'' or 1=1 --'     /* Msg 102, Level 15, State 1, Line 59 Incorrect syntax near '1'*/

The server-side error certainly suggests that the vulnerability exists. Do I need to add a bracket?

 EXECUTE dbo.SelectProductModel 'light'') or 1=1 --'

This listed out all the product models. If you use the second version of the procedure, SelectProductModel2, the query returns no products because it will search for the string 'light') or 1=1 –- which isn’t there at all.

/* Can I do the union all trick? */   --I don't know the names of any tables so I'll use functions   EXECUTE dbo.SelectProductModel 'ffff'') union all    SELECT db_name(), @@Servername,User_Name(), ORIGINAL_LOGIN( ) , session_user,''''     --'

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

0

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

1

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

2

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

3

Never mind. I can still get the payload out via the website. Now I’ve got the credit cards, I’ll go for that person.person table. Something is telling me that there is personal data there. Let’s study what is in that table.

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

4

I think, judging from this data, that I can extract that in one go if I join columns together.

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

5

To cut a long story short, the entire personal details and sales of AdventureWorks is now on its way to the dark web. Sure, it is laborious because I’d be getting the payload via the POST on the website, and probably having to reassemble the data from bits, but even a small breach is damaging!

Finally, tidy up:

DECLARE @MyList NVARCHAR(50) = '''Goldberg'',''Erickson'',''Walters''';   EXECUTE ('SELECT * from person.person where lastname in (' + @MyList + ')');

6

Conclusion

There is nothing essentially wrong with the technique of executing strings as SQL batches. However, it is a hard-hat area of programming. It can be dangerous if you don’t parametrize the values you put in that string. Sometimes, because of the vagaries of SQL Syntax, you just can’t do it, and there isn’t a way around it, in which case you need to take all necessary precautions to validate the inputs. For this reason, it is a good idea to discourage the use of Execute(<string>) because it has no means of parameterizing its inputs, and to check the use of sp_ExecuteSQL to make sure that all suitable precautions have been taking to prevent SQL Injection.

Comment (0)