Can I protect against SQL Injection by escaping si

Question

I realize that parameterized SQL queries is the optimal way to sanitize user input when building queries that contain user input, but I'm wondering what is wrong with taking user input and escaping any single quotes and surrounding the whole string with single quotes. Here's the code:

sSanitizedInput = "'" & Replace(sInput, "'", "''") & "'"

Any single-quote the user enters is replaced with double single-quotes, which eliminates the users ability to end the string, so anything else they may type, such as semicolons, percent signs, etc, will all be part of the string and not actually executed as part of the command. We are using Microsoft SQL Server 2000, for which I believe the single-quote is the only string delimiter and the only way to escape the string delimiter, so there is no way to execute anything the user types in.

I don't see any way to launch an SQL injection attack against this, but I realize that if this were as bulletproof as it seems to me someone else would have thought of it already and it would be common practice. My question is this: what's wrong with this code? Does anybody know a way to get an SQL injection attack past this sanitization technique? Sample user input that exploits this technique would be very helpful.

UPDATE:

Thanks to everyone for their answers; pretty much all the information I came across in my research showed up on this page somewhere, which is a sign of the intelligence and skill of the people who have taken time out of their busy days to help me out with this question.

The reason I have not yet accepted any of the answers is that I still don't know of any way to effectively launch a SQL injection attack against this code. A few people suggested that a backslash would escape one single-quote and leave the other to end the string so that the rest of the string would be executed as part of the SQL command, and I realize that this method would work to inject SQL into a mySQL database, but in MS SQL 2000 the only way (that I've been able to find) to escape a single-quote is with another single-qoute; backslashes won't do it. And unless there is a way to stop the escaping of the single-quote, none of the rest of the user input will be executed because it will all be taken as one contiguous string.

I understand that there are better ways to sanitize input but I'm really more interested in learning why the method I provided above won't work. If anyone knows of any specific way to mount a SQL injection attack against this sanitization method I would love to see it.

Answer 1

I realize this is a long time after the question was asked, but ..

One way to launch an attack on the 'quote the argument' procedure is with string truncation. According to MSDN, in SQL Server 2000 SP4 (and SQL Server 2005 SP1), a too long string will be quietly truncated.

When you quote a string, the string increases in size. Every apostrophe is repeated. This can then be used to push parts of the SQL outside the buffer. So you could effectively trim away parts of a where clause.

This would probably be mostly useful in a 'user admin' page scenario where you could abuse the 'update' statement to not do all the checks it was supposed to do.

So if you decide to quote all the arguments, make sure you know what goes on with the string sizes and see to it that you don't run into truncation.

I would recommend going with parameters. Always. Just wish I could enforce that in the database. And as a side effect, you are more likely to get better cache hits because more of the statements look the same. (This was certainly true on Oracle 8)

Answer 2

I've used this technique when dealing with 'advanced search' functionality, where building a query from scratch was the only viable answer. (Example: allow the user to search for products based on an unlimited set of constraints on product attributes, displaying columns and their permitted values as GUI controls to reduce the learning threshold for users.)

In itself it is safe AFAIK. As another answerer pointed out, however, you may also need to deal with backspace escaping (albeit not when passing the query to SQL Server using ADO or ADO.NET, at least -- can't vouch for all databases or technologies).

The snag is that you really have to be certain which strings contain user input (always potentially malicious), and which strings are valid SQL queries. One of the traps is if you use values from the database -- were those values originally user-supplied? If so, they must also be escaped. My answer is to try to sanitize as late as possible (but no later!), when constructing the SQL query.

However, in most cases, parameter binding is the way to go -- it's just simpler.

Answer 3

Patrick, are you adding single quotes around ALL input, even numeric input? If you have numeric input, but are not putting the single quotes around it, then you have an exposure.

Answer 4

Simple answer: It will work sometimes, but not all the time. You want to use white-list validation on everything you do, but I realize that's not always possible, so you're forced to go with the best guess blacklist. Likewise, you want to use parametrized stored procs in everything, but once again, that's not always possible, so you're forced to use sp_execute with parameters.

There are ways around any usable blacklist you can come up with (and some whitelists too).

A decent writeup is here: http://www.owasp.org/index.php/Top_10_2007-A2

If you need to do this as a quick fix to give you time to get a real one in place, do it. But don't think you're safe.

Answer 5

Yes, you can, if...

After studying the topic, I think input sanitized as you suggested is safe, but only under these rules:

1. you never allow string values coming from users to become anything else than string literals (i.e. avoid giving configuration option: "Enter additional SQL column names/expressions here:"). Value types other than strings (numbers, dates, ...): convert them to their native data types and provide a routine for SQL literal from each data type.

   • SQL statements are problematic to validate

2. you either use nvarchar/nchar columns (and prefix string literals with N) OR limit values going into varchar/char columns to ASCII characters only (e.g. throw exception when creating SQL statement)

   • this way you will be avoiding automatic apostrophe conversion from CHAR(700) to CHAR(39) (and maybe other similar Unicode hacks)

3. you always validate value length to fit actual column length (throw exception if longer)

   • there was a known defect in SQL Server allowing to bypass SQL error thrown on truncation (leading to silent truncation)

4. you ensure that SET QUOTED_IDENTIFIER is always ON

   • beware, it is taken into effect in parse-time, i.e. even in inaccessible sections of code

Complying with these 4 points, you should be safe. If you violate any of them, a way for SQL injection opens.

Answer 6

Input sanitation is not something you want to half-ass. Use your whole ass. Use regular expressions on text fields. TryCast your numerics to the proper numeric type, and report a validation error if it doesn't work. It is very easy to search for attack patterns in your input, such as ' --. Assume all input from the user is hostile.