> Using prepared statements or variable binding to fight SQL injections is not the best idea
What is the best idea then? If you bind variables to SQL statements, you are SQL injection safe 100% of the time. There is no crafty input sequence that can fool anything.
> In most cases where you want a prepared statement, you'd be better off using a stored proc, as you'll skip the expensive optimization every single time.
I am only qualified to speak about Oracle which is a DB I know extremely well. A query is a query, whether it comes from Java, Perl, Ruby or inside of a stored proc. If you prepare a statement once, and then cache that handle and execute it many times, you optimize the query one time. Also in Oracle, if you prepare-bind-execute one time only, the next time you do the same sequence of steps you Oracle doesn't have to optimize the query again - it can spot it is the same as a previous query and short circuit the process.
Mysql I think doesn't cache SQL statements for later reuse like Oracle, which is why binding isn't as important for performance (in Oracle, not bind queries is a pretty good way to bring the database to its knees) - but its still essential for security.
> SQL injections are 100% avoided by user input control in the application, and the simplest way is to escape all escape characters
What this bug has just proven, is that this escaping is not all that easy - crafty attackers can come up will all sorts of strings that seem to work around the escaping time and time again.
What is the best idea then? If you bind variables to SQL statements, you are SQL injection safe 100% of the time. There is no crafty input sequence that can fool anything.
> In most cases where you want a prepared statement, you'd be better off using a stored proc, as you'll skip the expensive optimization every single time.
I am only qualified to speak about Oracle which is a DB I know extremely well. A query is a query, whether it comes from Java, Perl, Ruby or inside of a stored proc. If you prepare a statement once, and then cache that handle and execute it many times, you optimize the query one time. Also in Oracle, if you prepare-bind-execute one time only, the next time you do the same sequence of steps you Oracle doesn't have to optimize the query again - it can spot it is the same as a previous query and short circuit the process.
Mysql I think doesn't cache SQL statements for later reuse like Oracle, which is why binding isn't as important for performance (in Oracle, not bind queries is a pretty good way to bring the database to its knees) - but its still essential for security.
> SQL injections are 100% avoided by user input control in the application, and the simplest way is to escape all escape characters
What this bug has just proven, is that this escaping is not all that easy - crafty attackers can come up will all sorts of strings that seem to work around the escaping time and time again.