Right. This explains why doing it yourself isn't the best strategy.
But I still feel like what is missing from this discussion is why parameterized queries are not vulnerable to injection. Are they just better implemented and better tested? Is there some technological reason why they are superior, something they are capable of doing that a concatenated string cannot?
I feel like having this discussion without telling the readers why the solution is the solution is analogous to just doing a bunch of hand-waving. "Don't do that, do this" doesn't really teach people why "this" is better.
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.
Part of the problem is the terrible name, "SQL Injection". I've heard it referred to by another term, something like "subverting the structure of the sql statement" (no, it's not as catchy). Even just calling it that gives a much more fundamental insight into the problem - it's not about always escaping apostrophes, it's about making sure the query parses in exactly the way you intend. Looking at the problem that way, you see that 1) there are obviously similar attacks possible against most query languages (LDAP, XPath, whatever) and 2) separating the query structure from the arguments (as tptacek describes) is key to avoiding the problem.
But I still feel like what is missing from this discussion is why parameterized queries are not vulnerable to injection. Are they just better implemented and better tested? Is there some technological reason why they are superior, something they are capable of doing that a concatenated string cannot?
I feel like having this discussion without telling the readers why the solution is the solution is analogous to just doing a bunch of hand-waving. "Don't do that, do this" doesn't really teach people why "this" is better.