How do I mitigate a SQL injection vuln?

Joel points out today that SQL injection vulnerabilities are common and bad, bad, bad. He does a good job of describing the attack but doesn't really talk about how to mitigate it.

When I advise people on how to close security holes like this I always tell them that closing the original hole is probably not enough. You don't want to make the attackers do one "impossible" thing because you just might be mistaken about what is actually impossible. Make them do three or four impossible things, and odds are good that it really will be impossible to cause harm.

To start with, run all strings that come from users through a string checker looking for anything out of the ordinary. If you expect a string to contain only letters, numbers and spaces, then write a regular expression that verifies that and get in the habit of rejecting all input that doesn't conform. That should make it impossible for attackers to put special characters like quotation marks in there.

Assume that a clever attacker will subvert that. Assume that you've made a mistake and forgotten to put a check in somewhere. Look for every place in the code that uses that user-supplied string. Don't stop at SQL construction. Anything that gets passed to JScript's eval could be an injection attack. Anything that gets echoed back to the user could be a cross-site scripting attack. Anything that gets written to disk could be an attempt to write a script onto the server's disk to trick an admin into running it. Eliminate as many of these as you can.

But how do you eliminate them? A great way to mitigate the risk of a SQL injection attack is to use stored procedures. Stored procedures ensure that only the query that you want to run actually runs. But they have nice properties in addition to being more secure against injection. They can be updated in the database, so that when the database structure changes, you change the stored procedure rather than searching through your code for SQL statement construction. And stored procedures often run faster because the database can optimize itself for them.

How else can we make it impossible for an attacker to run Joel's proposed attack? The attack Joel describes deletes stuff from the database, which is pretty unsubtle. A more subtle attack would consist of an update query which ups the user's available credit, or changes the prices of products, or some such thing. The database code should make use of the principle of least privilege. If you only expect it to look up results from particular database tables, then create a database account that only has those permissions, and then use that account from the server code. Don't connect to the database as admin if you don't need admin privileges; that's just asking for someone to abuse those privileges.

Furthermore, don't keep secrets in source code. For instance, put the name of the database server, the name of the database account, and the password in a registry key or an ACL'd file. Assume that attackers will obtain your source code. Keep machine names, employee names, keep anything sensitive that an attacker could use out of the source code.

How did Joel find out that the server was vulnerable at all? When the server helpfully told him that there was malformed SQL, and furthermore, what part of it was. Not only is this potentially a cross-site scripting attack (because user-supplied data is echoed back) but it is like waving a big sign explaining exactly how to construct an attack! Detailed error messages that describe the internal state of the server should only be given to users who have been authenticated by the server and are known to be server developers. Ordinary users should get a generic "something bad happened" error that explains nothing.

For Joel's proposed attack to succeed, everything has to go wrong. The server has to fail to validate input, then use it in an insecure way, then connect to the database as an administrator. Regrettably, many server-side web apps leave themselves wide open to these sorts of attacks. Eliminate all of these problems, not just the string concatenation. Remember, think like an evil person, assume the worst, and make evildoers jump through as many hoops as you possibly can. Defense in depth!

Comments

• Anonymous
  November 01, 2006
  " A great way to mitigate the risk of a SQL injection attack is to use stored procedures. " Absolutely! Then call the stored procedures this way: query="exec InsertRecord @value1=" & request.querystring("value1") & ... :-) Seriously though, versioning on stored procedures is a pain. Ad-hoc queries aren't that slow and you don't have to deal with the DBAs to get work done. For inserts I tend to stick with SPs though.

• Anonymous
  November 01, 2006
  The comment has been removed

• Anonymous
  November 01, 2006
  The comment has been removed

• Anonymous
  November 01, 2006
  The comment has been removed

• Anonymous
  November 01, 2006
  The comment has been removed

• Anonymous
  November 01, 2006
  The comment has been removed

• Anonymous
  November 01, 2006
  Steve Friedl posted a step-by-step walkthrough of how he cracked a SQL Server once. In that case, the door was apparently wide open, but still, it's interesting reading: http://www.unixwiz.net/techtips/sql-injection.html

• Anonymous
  November 01, 2006
  my approach to minimize sql injection..

1. Use stored procedure
2. Avoid using dynamic sql in stored procedure
3. Grant  only minumum required permission to the account executing the proc
4. validate all user input.  use XMLSchema for input validation Please add more...

• Anonymous
  November 01, 2006
  What about simply using type system to do this (a la http://blog.moertel.com/articles/2006/10/18/a-type-based-solution-to-the-strings-problem)?

• Anonymous
  November 02, 2006
  Using the type system to track semantic information about string contents is an excellent idea and a step in the right direction.  No doubt about that.  But based on the word "simply" in your suggestion, I suspect that you have missed the point of my posting.  The point is that good defense requires more than one layer of protection.  If an attack requires three things to be broken to work, FIX ALL OF THEM. Don't just fix the strings problem and hope for the best.  Take a multi-layered approach to security.

• Anonymous
  November 02, 2006
  The comment has been removed

• Anonymous
  November 02, 2006
  The comment has been removed

• Anonymous
  November 03, 2006
  Yes, again, you are correct. And again, people seem to be missing my point.   The problem could be solved by allowing the injection attack but limiting the database permissions to only allow reading from certain tables.  If everyone did that, it would become an "obscure issue".  And if everyone used stored procedures it would be a non-issue. My point is that there are any number of ways to fix this problem.  DO ALL OF THEM.  Don't just do enough to fix the problem and then hope for the best.  Defense in depth!

• Anonymous
  November 05, 2006
  Have you ever get any error message telling something's wrong with the SQL statement? If yes, you might consider attack the application in order to, say for example, increase your bank account balance.While SQL Injection is a well-known security vulnerab

• Anonymous
  November 06, 2006
  The comment has been removed

• Anonymous
  November 06, 2006
  Indeed!  See http://blogs.msdn.com/ericlippert/archive/2003/11/01/53329.aspx and http://blogs.msdn.com/ericlippert/archive/2003/11/04/53335.aspx

• Anonymous
  November 10, 2006
  The comment has been removed

• Anonymous
  November 24, 2006
  is no one else bothered by the fact that using stored procedures for simple queries is blaringly bad design? You move business logic (what do I want to know) into the storage layer of your application where it doesn't belong and is hard to manage and maintain. Use prepared statements, preferably with named parameters! And avoid the problem of having to change a query that is used multiple times in the application by moving it into a constant. and @Gabe: I know you only talk about SQL Server, but your supposed fix breaks on MySQL if you input " blah' ", because MySQL allows different escaping characters. Rule-of-thumb: only vendor-supplied character escape functions are safe (and even those have bugs sometimes, prepared statements are a much better solution).

• Anonymous
  November 25, 2006
  The comment has been removed

• Anonymous
  December 20, 2006
  One of the first things I do is find a function that will escape my untrusted input strings. Basicly I look for backslash, quote, and double quote characters. For my PostgreSQL database projects I always throw these functions into an appropriate class (usually class DBEscape). public static string Escape(string str) { return Escape(str,"&quot;'"); } public static string Escape(string str,string chrs) { string ret = ""; int i; int o; int oct = chrs.Length; int ct = str.Length; bool found; for(i=0;i<ct;i++) { found = false; for(o=0;o<oct;o++) { if(str[i] == chrs[o]) { ret += "&quot;+str[i].ToString(); found = true; } } if(found == false) ret += str[i].ToString(); } return ret; } For my lisp projects I do this which even has support for adding the encasing single quotes, but not for passing a list of characters to escape. (defun db-escape (str &optional addquote)  (format nil (if addquote "'~a'" "~a")  (format nil "~{~a~}"  (loop for c across str collect (case c  (#' "''")  (#\ "&quot;)  (t c)))))) If it's an input string escape it.  If it's an input number (in string form) make sure it passes a Convert.Int32(). I got tired of errors from users wanting to type names to things into the database like "Bart's call list".  It wasn't until I wrote my Escape() function that these became a thing of the past. I quickly learned that if you escape and validate types you will throw less errors and you'll go a long way to avoiding SQL injection attacks.  This is a habit that will do me good the rest of my days.

• Anonymous
  December 20, 2006
  >> Just so long as all user inputs (even dates and numbers) are passed through this function, no injection attack is possible with SQL Server. Why doesn't everybody do this? Because most people are smart enough to use parameterised queries, which avoid the problem altogether.

• Anonymous
  December 30, 2006
  The comment has been removed