Parameterized SQL statements vs. very simple method

Question

When I started to write the first SQL-Statements in my programs I felt quite comfortable with protecting myself against SQL-Injection with a very simple method that a colleague showed me. It replaced all single quotes with two single quotes.

So for example there is a searchfield in which you can enter a customername to search in the customertable. If you would enter

Peter's Barbershop

The SELECT Statement would look like

SELECT *
FROM Customers
WHERE Customername = 'Peter''s Barbershop'

If now an attacker would insert this:

';DROP TABLE FOO; --

The statement would look like:

SELECT *
FROM Customers
WHERE Customername = ''';DROP TABLE FOO;--'

It would not drop any table, but search the customertable for the customername ';DROP TABLE FOO;-- which, I suppose, won't be found ;-)

Now after a while of writing statements and protecting myself against SQL-Injection with this method, I read that many developers use parameterized statements, but I never read an article where "our" method was used. So definitely there is a good reason for it.

What scenarios would parameterized statements cover but our method doesn't? What are the advantages of parameterized statements compared to our method?

Thanks
Philipp

Polymeric

Answer 1

The parametrized queries has more proc than the defence to sql-injection.

1. It solves problem with date & time formating & parsing.
2. You can prepare execution plan for parametrized query.
3. The sql-injection protection.

I can't remember now for another pros :).

However the way "double every quotes" has problem with fields with limited character length.

For example:

• The page has box for "nickname" which can be 10 character long.
• The user insert "Don't care" - the exact 10 characters.

Now if you double the quotes, the value has 11 characters and the database will "cut" it, and you got another value in db than user typed.

So I recommend the parameters.

Enosis

The "Don't care" example doesn't work for MS SQL Server 2005 and 2008. They accept a longer string if there are doubled quotes in it. But +1 and accepted answer for the first 3 Points. – Polymeric

Answer 2

One big dis-advantage is that your solution relies on a developer remembering to add the character, obviously the compiler won't complain. That is dangerous.

Secondly, performance should be enhanced with parameterized SQL statements, as Jeff points out here (in 2005!!!).

Hutchison

No problem. What programming language are you using in the application layer? – Hutchison

In which case, have you looked at using Linq2Sql and getting rid of all that SQL? – Hutchison

I saw a few examples, but I didn't spend too much time to learn linq, yet. It looked kind of crazy if you want to join multible tables and use multiple conditions. But I will take a closer at it. – Polymeric

I know what you mean, but most people learn to love LINQ and don't want to return to SQL or stored procedures ever again. – Hutchison

Answer 3

One advantage is that the driver itself will determine what he has to escape and what doesn't need to be escaped. Your method could be broken with an input like this:

  \'; DROP TABLE foo;--

Which would result in

  SELECT *
  FROM Customers
  WHERE Customername = '\'';DROP TABLE FOO;--'

The first quote gets escaped, the second doesn't and closes the string.

Dashiell

Which database recognizess \ as an escape for ' ? – Fob

Oh, now I voted up to quickly, I tried this one out but it didn't work. The statement looks like the one you posted, but he whole DROP TABLE part is recoginized as a string. The \ didn't do anything, it's just the first character of the string. – Polymeric

And Bobby's scenario is, of course, the second thing an attacker would try! Defensive programming rule #1: Never roll your own security. – Muddy

@Andomar, @Philipp G: In MySQL `\` is the default escape character (and I bet not only in MySQL). And the statement works for me (MySQL 5.0). – Dashiell

Answer 4

Short answer:
You should use parameterized queries simply because the database server knows better than you do which characters need to be escaped.

Long answer:
' is not necessarily the only special character that needs escaping. These special characters differ from DB server to DB server. MySQL, for example, uses \ as an escape character as well (unless sql_mode=NO_BACKSLASH_ESCAPES is set). Therefore, '' and \' mean the same thing.

This is not true of, say, Oracle.

Leech

MySQL (or at least my installation of it) does not recognize \ as an escape character. – Fob

@Andomar: MySQL's behavior depends on the server mode, too. If sql_mode=NO_BACKSLASH_ESCAPES or another mode that includes it is set, \ is no longer allowed to escape characters: dev.mysql.com/doc/refman/5.0/en/… – Leech

+1. There might be binary octets in the input, or the DB connection might have a notion of character set support that your escaping code does not, etc. – Valval

Answer 5

What are the advantages of parameterized statements compared to our method?

The advantage is that it's harder to make a mistake; you can't do the parameterized method, and forget to replace the quotes. Also, replacing quotes is vulnerable if you do it twice.

The disadvantage of parameterized queries (and the reason I never use them) is complexity. You can write ten times as many ad-hoc queries before you get RSI.

Fob