Product articles SQL Prompt SQL Code Analysis
The risks of using EXECUTE (‘SQL…

11 January 2019

3 Comments

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

11 January 2019

3 Comments

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.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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.

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

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:

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

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.

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.

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.

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:

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

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.

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.

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

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

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.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter
  • Dillon

    Thanks for the article Phil. I’m still a novice when it comes to SQL Server and this really help me see what SQL injection is and why it’s so dangerous.

    I was hoping you could answer me this though, how exactly is sp_ExecuteSQL more secure? Is it primarily because we can assign a datatype to our parameters and that will block some types of attacks? Like if we set the type to an int but the attacker tried injecting a string? Or is there some sort of sanitizing going on behind the scenes? Your example seems to point out that there is no difference in the end and we have to do our own SQL sanitation to truly be secure.

    I understand that sp_ExecuteSQL has the caching benefit, but what other benefits does it really give us?

  • Dillon

    Thanks for the article Phil. I’m still a novice when it comes to SQL Server and this really help me see what SQL injection is and why it’s so dangerous.

    I was hoping you could answer me this though, how exactly is sp_ExecuteSQL more secure? Is it primarily because we can assign a datatype to our parameters and that will block some types of attacks? Like if we set the type to an int but the attacker tried injecting a string? Or is there some sort of sanitizing going on behind the scenes? Your example seems to point out that there is no difference in the end and we have to do our own SQL sanitation to truly be secure.

    I understand that sp_ExecuteSQL has the caching benefit, but what other benefits does it really give us?

    • Phil Factor

      Thanks, Dillon.
      It is the parameterisation that is the most important added feature of Sp_ExecuteSQL over SP_Execute, but it must be used. Parameterisation makes SQL Injection almost impossible. The reason that I give the caution is that parameterisation at the application level can still cause injection if you fail to use parameterisation at the database level. Putting it simply, use parameterisation with Sp_ExecuteSQL and you can feel more secure. The reason that parameterisation prevents SQL Injection is because the parameters are filled in after the query is parsed, not before, and so it is impossible to tag on extra statements, however hard you try. Does that help?

      • Dillon

        Yes, that perfectly answers my question. Thank you!

You may also like