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

Answer 1

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.

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

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.

Answer 4

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.

Answer 5

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.