Parameterized SQL statements compared to a very simple method

Question

When I started writing the first SQL statements in my programs, I was very comfortable defending myself against SQL-Injection using the very simple method my colleague showed me. He replaced all single quotes with two single quotes.

So, for example, there is a search field in which you can enter a username to search in custom. If you enter

Peter Barber

The SELECT statement will look like

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

If now the attacker inserts this:

 ';DROP TABLE FOO; -- 

The operation will look like this:

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

He would not lose a single table, but did a search for a custom name for the user '; DROP TABLE FOO; - which, I believe, will not be found; -)

Now, after some time writing instructions and protecting against SQL-Injection using this method, I read that many developers use parameterized statements, but I never read an article that used our method. Therefore, there is definitely a good reason for this.

In what scenarios will the parameters be displayed, but our method does not work? What are the advantages of parameterized statements over our method?

thanks
Philipp

Answer 1

Parameterized queries have more proc than protection for sql-injection.

• Solves the problem with the formation of date and time and analysis.
• You can prepare an execution plan for a parameterized query.
• Protection against SQL injection.

I can’t remember now for other pros :).

However, the double-quote method has a problem with fields with a limited character length.

For example:

• On the page there is a field for "nickname", the length of which can be 10 characters.
• The user inserts "Do not care" - the exact 10 characters.

Now, if you double the quotation marks, the value will have 11 characters, and the database "truncates" it, and you will get a different value in db than the user one.

Therefore, I recommend options.

Answer 2

One of the big drawbacks is that your decision depends on the developer remembering to add the character, it is obvious that the compiler will not complain. This is dangerous.

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

Answer 3

One advantage is that the driver itself will determine what it needs to run away and what should not be avoided. Your method can be broken into an input as follows:

  \'; DROP TABLE foo;-- 

Which will lead to

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

The first quote is escaped, the second does not close and closes the line.

Answer 4

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

Long answer:
' not necessarily the only special character that needs to be escaped. These special characters differ from the database server from the database server. For example, MySQL uses \ as an escape character (unless sql_mode=NO_BACKSLASH_ESCAPES ). Therefore, '' and \' mean the same thing.

This does not apply, say, to Oracle.

Answer 5

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

The advantage is that it is harder to make a mistake; you cannot execute the parameterized method and do not forget to replace the quotation marks. Also, replacing quotes is vulnerable if you do this twice.

The disadvantage of parameterized queries (and the reason I never use them) is complexity. You can write ten times more special requests before you get RSI.