Do Stored Procedures Protect Against SQL Injection?

re: Do Stored Procedures Protect Against SQL Injection?

Andy P — Tue, 22 Feb 2011 10:05:09 GMT

Bob, I was reluctant to make my (rather long) comment even longer by discussing secondary SQL injection. My view is that the same rule still applies, because anything you're getting from a table is EXTERNAL to your stored procedure (or function, or query, or whatever). Just to clarify, when I talk about EXTERNAL input, I don't just mean somthing that comes in from the internet, or that comes from the user: I mean anything that comes from outside the scope of the query, function or procedure you're writing.

Perhaps we can refine the rule to:

-- NEVER concatenate dynamic SQL out of EXTERNAL input.

-- EXTERNAL input can ONLY be used as a PARAMETER.

re: Do Stored Procedures Protect Against SQL Injection?

Bob — Mon, 21 Feb 2011 19:38:26 GMT

Never building sql by concatenating EXTERNAL input is fine advice ... as far as it goes. The problem is: sometimes developers forget the source of their inputs. Secondary sql injection is a technique whereby malicious code is inserted into a database table where it lies in wait for a naive developer to forget that the source of the data in that table was EXTERNAL.

The proper answer to the question of how to prevent sql injection is: always use parameters instead of concatenation.

Attempting to sanitize/cleanse input is not a technique for preventing sql injection: it should be more properly thought of as a technique for attempting to _detect_ injection attempts, and should never be the only technique used to secure your application. It is the first layer in a multi-layer defense.

re: Do Stored Procedures Protect Against SQL Injection?

Brian Swan - MSFT — Mon, 21 Feb 2011 16:19:45 GMT

Thanks for the questions and comments. I think Andy P summed things up nicely. I tried to point out what he is saying in the post, but I didn't quite say it as eloquently. :-) As Andy P points out, in my example the SQL injection vulnerability is just moved from the PHP script to the stored procedure. His NEVER build dynamic SQL out of EXTERNAL input is a nice, simple summary.

re: Do Stored Procedures Protect Against SQL Injection?

Andy P — Mon, 21 Feb 2011 15:00:38 GMT

I would suggest that there is a very simple rule that protects agains SQL injection:

NEVER build dynamic SQL out of EXTERNAL input

Dynamic SQL is fine, as long as all the building blocks are inside your procedure/function/query. For example, you might have to select from a different combination of tables depending on some user-input parameter. That's perfectly safe. Trouble comes when you allow the user input to be pasted into your query.

In the example, the SQL injection vulnerability has simply been moved out of the PHP script and into the stored procedure. The stored procedure is guilty of building dynamic SQL out of something (the input parameters) whose value is set OUTSIDE the stored procedure.

If you're the developer working on that stored procedure, you can't be sure who or what will end up calling your code. For this reason, SQL injection is not just for web developers - it's every developer's responsibility. System variables, input parameters, results from other functions - all these could contain some horrible SQL that you don't want in your query.

Sure, you can check the external input for suspect characters, try to strip out punctuation and so on. But string parsing is notoriously inefficient, and how can you be sure you've covered every possibility? What happens if the input you've been given - like the password in the example - is allowed to contain strings like ' or -- ?

Follow the simple rule and you should be completely safe.

re: Do Stored Procedures Protect Against SQL Injection?

Dooby Duck — Mon, 21 Feb 2011 10:38:44 GMT

Won't passing parameters into dynamic SQL also protect against SQL injection? I would have expected this to form one of the 'right way' examples

re: Do Stored Procedures Protect Against SQL Injection?

Geezer — Mon, 21 Feb 2011 00:31:03 GMT

The author did not comment about input validations because it is irrelevant to the specific topic being discussed, i.e. whether stored procedures can protect against SQL injection. Input validation is a necessary thing, but it's beside the point in the context of this article.

re: Do Stored Procedures Protect Against SQL Injection?

Brian Swan - MSFT — Fri, 18 Feb 2011 17:29:28 GMT

@David - Thanks. I probably should not have been so subtle about the "plan". I was referring to query plans (en.wikipedia.org/.../Query_plan) which can improve performance as well as protect against injection.

@Owolabi - IMHO, validation should be done on both the client and the server. Obviously, if speed is the most important factor in your application, you favor one over the other, but that's going to depend on the application. Just my 2 cents.

re: Do Stored Procedures Protect Against SQL Injection?

Owolabi Charles — Thu, 17 Feb 2011 16:02:37 GMT

What about input validations and strictly disallowing some injectable characters? Although, a Client side validation will only work for the honest user, A combination of serverside validation will help. Again, Trading off speed or performance for security!

re: Do Stored Procedures Protect Against SQL Injection?

Evan — Wed, 16 Feb 2011 17:23:32 GMT

Using a decent O/RM will prevent the SQL injection attack (by sanitizing input) and remove the burden of writing all those sprocs..

re: Do Stored Procedures Protect Against SQL Injection?

David Malouf — Wed, 16 Feb 2011 17:01:31 GMT

I know it's subtle, but I really like the way you call a stored procedure a 'plan.' It puts me in the right mindset when creating SPs to think of them as plans and not queries.

As always, so well worded, Brian!