How do I mitigate a SQL injection vuln?

re: How do I mitigate a SQL injection vuln?

Dave — Thu, 02 Nov 2006 00:17:50 GMT

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

re: How do I mitigate a SQL injection vuln?

Bob Barrows — Thu, 02 Nov 2006 00:19:39 GMT

<quote> use stored procedures </quote>

SQL Injection is still possible if stored procedures are used:

1. If the stored procedure includes code to create and execute a dynamic sql statement using user inputs

2. If the developer uses dynamic sql to execute the stored procedure, as in

sql="exec mysproc '" & userinput & "'"

SQL Injection depends on the use of dynamic sql. Therefore, the best way to defeat it is to not use dynamic sql: use parameters to pass data to either a stored procedure or to a sql string containing parameter markers.

This does not relieve one of the requirement to validate all user input, rejecting suspect data rather than forcing it into the database.

re: How do I mitigate a SQL injection vuln?

Eric Lippert — Thu, 02 Nov 2006 00:46:05 GMT

Dave: there is always a balance between security and convenience. Deciding where that balance is requires a cost analysis of each, and the cost of low security is often quite high!

Bob: Yes, I should have been more clear. Call stored procedures with parameters, otherwise you're just trading one injection path for another.

re: How do I mitigate a SQL injection vuln?

roberthahn — Thu, 02 Nov 2006 04:21:09 GMT

Actually, I'm not sure the advice regarding the string checker is at all that good - as proposed, anyone needing accented characters (or even Kanji) is going to find their content gets stripped out. It's not every day that someone wants to report that a møøse bit their webpage on your DB-backed web app, but it could happen, y'know?

It might be smarter to simply evaluate which non-alphanumerics are bad and strip those out. I wonder how Unicode-savvy SQL interpreters are -- perhaps you could replace 'bad' characters with some visual equivalent that's not in the ASCII range of characters.

re: How do I mitigate a SQL injection vuln?

roberthahn — Thu, 02 Nov 2006 04:22:37 GMT

whups -- i should mention that dispite that nit, the article overall is very well thought out. I hope that people reading this learn how to put these ideas to work, then actually do it.

re: How do I mitigate a SQL injection vuln?

bleroy — Thu, 02 Nov 2006 05:20:46 GMT

Robert: you're basically advocating for a blacklist. That's bad. You're right on the Unicode remark, but that doesn't apply to a numeric field, which was Eric's example. For a text field, you've got to rely on the other layers of protection, because it's a string and basically everything is valid in there, including ' delete * from foo.

re: How do I mitigate a SQL injection vuln?

mike — Thu, 02 Nov 2006 07:33:18 GMT

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

re: How do I mitigate a SQL injection vuln?

Binu Purayil — Thu, 02 Nov 2006 08:09:01 GMT

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

re: How do I mitigate a SQL injection vuln?

Alexey Romanov — Thu, 02 Nov 2006 13:58:29 GMT

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

re: How do I mitigate a SQL injection vuln?

Eric Lippert — Thu, 02 Nov 2006 18:38:13 GMT

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.

re: How do I mitigate a SQL injection vuln?

Eric Lippert — Thu, 02 Nov 2006 19:49:20 GMT

Rob: I said to ensure that the string contains only what you _expect_. If you expect kanji, then write a regular expression filter that allows kanji through!

A few questions do still arise though.

First, what if you WANT to allow " * - ;, etc? The classic approach is to run them through an escapement algorithm that encodes them into alphanumeric entities. Those entities then do not have semantic import when injected into SQL.

Second, what do you do when you get a string that doesn't meet your filter? You can 1) escape it, 2) strip the offending characters, or 3) reject it entirely. Which is the right thing to do depends on whether you need to maintain fidelity of the string and how paranoid you are about people attacking you. If you think that there's a high likelihood that unusual characters are characteristic of attacks, then the sensible thing to do is to put your shields up at the first sign of trouble.

re: How do I mitigate a SQL injection vuln?

O(1) — Fri, 03 Nov 2006 08:19:51 GMT

I first heard of these dangerous SQL injection attacks a few years ago and wondered why they were so important. Well, once I understood the nature of this problem it became obvious to me that such a thing would almost never happen to my code because I almost never incorporate user submitted strings in my code. I always compare user supplied data to an internal list and only if it matches my list do I execute some code that I wrote and the code only executes data that I provide. Usually nothing that the user provides ever gets embedded in a command. In the rare cases when you do have to accept some input from the user then it is of paramount importance to correctly use some form of carefully constructed regex that will only pass the data that you expect it to and no more. If people did this then they would almost never suffer from SQL injection attacks and it would become an obscure issue.

re: How do I mitigate a SQL injection vuln?

Eric Lippert — Fri, 03 Nov 2006 19:24:06 GMT

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!

Common vulnerability: SQL Injection

Steven Yong's Weblog — Mon, 06 Nov 2006 10:22:14 GMT

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

re: How do I mitigate a SQL injection vuln?

Mike — Mon, 06 Nov 2006 17:55:54 GMT

"Anything that gets passed to JScript's eval could be an injection attack"

Crikey! I'd say that any script using Eval was a problem in itself. I've yet to come up with a scenario where it was _ever_ necessary (apart from an "execute any script" page, which is bad enough in itself).

re: How do I mitigate a SQL injection vuln?

Eric Lippert — Mon, 06 Nov 2006 18:07:15 GMT

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

re: How do I mitigate a SQL injection vuln?

Gabe — Sat, 11 Nov 2006 09:51:17 GMT

I never understood why so many people write code that's so vulnerable. I just always write a function like this:

function SQLString(val)

....if val is null then

........return "NULL"

....else

........return "'" + str(val).replace("'", "''") + "'" 'replace single apostrophe with two apostrophes

end function

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?

And I should add that filtering out bad characters is generally a bad idea. There's nothing worse than not being able to use a secure password on a site because some fool that doesn't know how to prevent code injection decided that they need to make all non-alphanumeric characters illegal.

Figuring out what characters to allow is much trickier than preventing injection attacks every other place (SQLString function, stored procs, prepared statements), so it's probably more harmful to your product than beneficial. For example, a naive coder writing a regex to only accept numbers might write /[0-9]+/, only to realize he forgot about negative numbers and decimals (now he has [.0-9-]). That works fine until somebody tries to enter scientific notation, and now he has [.eE0-9+-]. And even then it would fail for somebody in Europe who uses commas for decimal places. I believe the correct regex is [.,eE0-9+-], and even then I could be forgetting about some valid possibilities I hadn't thought of.

Cross-site scripting (XSS) attacks are much harder to prevent, though. HTML-encoding every literal string is easy, but user input that can include HTML is much more of a problem. These really do require a very strict HTML white-list for tags and attributes. Otherwise you will have to write an HTML parser that keeps up with all the latest browser features to know what's code and what isn't. Besides, you probably want to be careful about what tags you allow anyway (you likely don't want <meta> or </body> tags, and unclosed tables can screw up your layout).

re: How do I mitigate a SQL injection vuln?

Jonas — Sat, 25 Nov 2006 02:23:18 GMT

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

re: How do I mitigate a SQL injection vuln?

Pete Doubleday — Sat, 25 Nov 2006 23:27:16 GMT

Well, OK, I'm a dinosaur.

I'm not proud of this, and I'm not going to boast. The only reason that the server-side systems I write (or more accurately co-write) are not vulnerable to SQL injection attacks, or indeed any other sort of insertion attack, is that they tend to have very constrained interfaces; basically yacc'n'lex parsing.

Now, as Hunter S Thompson once said, "I don't recommend drugs and violence for everybody; they just happen to work for me..."

I loathe stored procedures, and if you happen to use them across an unprotected interface that's open to dynamic SQL, then you are basically a moron and deserve to be kicked in the kidneys. Like many other database features, they have their place, but they are wildly overused.

Now, let's attack this problem from the other end of the telescope. Sure, you can layer one, two, three or more defences against SQL injection attacks and the like, but these things tend to be automated nowadays. There are a million idiots out there who will patch together vulnerable sites -- and the percentage vulnerability is quite frankly terrifying.

You could take the Darwinian approach and claim that these sites deserve to be attacked, and perhaps they do. I don't suppose it does Joe Public many favours to be exposed to identity fraud and the like, and it probably does none of us a favour in the long run, but what they heck? These people deserve it. Their sites should be hunted down and destroyed like the rats' nests they are.

On the other hand we could take a more aggressive approach to fraud (which is basically what an SQL injection attack is). Why not welcome it? Why not make it easy for an attacker to feed destructive guff into a bare SQL statement?

Think about it.

It's easier to parse an attack like this (hell, a simple regular expression could do it -- even PHP manages this) and throw an alert at the proper authority than it is to build in n-million layers of security into what is, fundamentally, an insecure and ill-thought-out mass-distribution network. Yup, that's WWW for you.

Seriously. Has anyone considered this sort of approach?

-- Pete

re: How do I mitigate a SQL injection vuln?

Shaun Kruger — Thu, 21 Dec 2006 00:41:35 GMT

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,"\\\"\'");

}

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 += "\\"+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

(#\' "''")

(#\\ "\\")

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

re: How do I mitigate a SQL injection vuln?

Matthew — Thu, 21 Dec 2006 02:57:18 GMT

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

re: How do I mitigate a SQL injection vuln?

Chad Okere — Sun, 31 Dec 2006 09:30:02 GMT

Stored procedures suck. They make version control a bitch and a half. Used <b>prepared</b> statements. Defined in your code, so they'll be version controlled, but not mailable. Also you can't use dynamic SQL with them.