Re:A guide to preventing SQL injection
by tptacek on Tuesday September 15, 2009 @03:52PM
(#824594)
Parameterized queries effectively pre-compile the syntax of the query, and pass the parameters out of band (you can see that on the wire in the TSQL or MySQL protocols --- the parameters are actually seperate messages).
The net effect is, there's no opportunity to mix query structure and parameter, because by the time the database looks at user input, it's already fixed the query structure in place.
You do not get the same benefit by aggressively quoting; among other things:
* Quoting can fall to character set attacks
* You still may need to handle truncation
* Different quoting regimes are required for different parameter types
It's possible to safely do concatenated sanitized SQL (ActiveRecord does it), it's just hard. It's also possible to have injectable parameterized queries, or even injectable stored procedures (for instance, if the procedure uses dynamic SQL). There's no panacea.
The net effect is, there's no opportunity to mix query structure and parameter, because by the time the database looks at user input, it's already fixed the query structure in place.
You do not get the same benefit by aggressively quoting; among other things:
* Quoting can fall to character set attacks
* You still may need to handle truncation
* Different quoting regimes are required for different parameter types
It's possible to safely do concatenated sanitized SQL (ActiveRecord does it), it's just hard. It's also possible to have injectable parameterized queries, or even injectable stored procedures (for instance, if the procedure uses dynamic SQL). There's no panacea.