When Not To Use Prepared Statements?

[masterlayouts]

"The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."

 

If this is true, why would anybody ever use something else than prepared statements to handle the SQL queries?

 

1. Can I have an example when prepared statements are used and SQL injection will occur?

2. Can I have a reasonable technical reason why NOT to use prepared statements every time? When and why not to use prepared statements?

[Larry]

If this is true, why would anybody ever use something else than prepared statements to handle the SQL queries?

 

Three reasons off the top of my head:

1. Personal preference

2. Performance (prepared statements may not perform as well)

3. Prepared statements are not supported

 

1. Can I have an example when prepared statements are used and SQL injection will occur?

 

If you do this--SELECT * FROM $table WHERE col=?, the $table value could be a potential security hole. The query itself is mostly a prepared statement, but part of it isn't.

 

2. Can I have a reasonable technical reason why NOT to use prepared statements every time? When and why not to use prepared statements?

 

Technical reason NOT to use prepared statements: prepared statements aren't supported. Non-technical reason: you're not comfortable with them and they can be much harder to debug.

 

To me, it's not a question of when and why not to use prepared statements. In other words, I would never advocate that you DON'T use them. But I also don't advocate that you ALWAYS use them. In situations where the same query is being executed multiple times in the same single execution of a script, then prepared statements make sense. Other than than, it's largely a matter of personal preference. If you take the right security steps, non-prepared statements can be just as secure as prepared statements.