What Is SQL Injection?

Say we have a stored procedure that queries the Stack Overflow database. We have two separate parameters, @DisplayName and @Location, so folks can search for people by name, location, or both.

For performance reasons, we decide to build dynamic SQL:

When we run it, it works, and we can see the query on the Messages tab in SSMS:

Finding Bobby Tables

But look what happens when the bad guy runs it:

Well, that’s not good

The bad guy can:

  • Close the DisplayName search string by adding a couple of apostrophes
  • Add a semicolon to end the first query
  • Add his own evil query, like drop database or create login or whatever
  • Finish the batch and ignore any subsequent syntax errors by throwing in a couple of minus signs

That’s a really simplistic example, but it can get way worse, as Bert Wagner explains in this GroupBy session on SQL injection.

The first step in avoiding this problem is to avoid using user inputs as-is. Pass the parameters in to sp_executesql instead, like this:

sp_executesql lets you pass in a list of parameter definitions, and then pass the parameters in safely. Now, when the bad guy calls it, here’s what the query looks like:

Whew – safer.

The bad guy doesn’t get a list of your databases.

Avoid EXEC, and use sp_executesql instead. Then, for more learning, check out:

Previous Post
Updated First Responder Kit for November 2018: Get Into Sports Dummy
Next Post
[Video] Office Hours 2018/11/28 (With Transcriptions)

4 Comments. Leave new

  • I think this is not the best example since you start with “For performance reasons, we decide to build dynamic SQL:” and then you use sp_executesql that is going to parameterize the query. Probably we are going to end up with the same performance issues of using the sp directly in the first time, Or am I missing something?

    • I’m not demonstrating performance tuning in this post. The example SI strictly to show SQL injection. We have lots of other cool posts about dynamic SQL if you’re interested in performance tuning. Thanks!

      • I am (and every reader is) interested in both.. It will be cool to have an article that shows how to use secure dynamic SQL while preserving the benefits of using dynamic SQL to improve performance. Showing a way to secure dynamic SQL that voids the advantages of using dynamic SQL seems like a loophole. Anyway it was just a suggestion.

Menu
{"cart_token":"","hash":"","cart_data":""}