On 2010-06-26 22:33:57 -0400, Lawrence D'Oliveiro said:
In message <2010062522560231540-angrybald...@gmailcom>, Owen Jacobson wrote:
It's not hard. It's just begging for a visit from the fuckup fairy.
That’s the same fallacious argument I pointed out earlier.
In the sense that "using correct manual escaping leads to SQL injection
vulnerabilities", yes, that's a fallacious argument on its own.
However, as sites like BUGTRAQ amply demonstrate, generating SQL
through string manipulation is a risky development practice[0]. You can
continue to justify your choice to do so however you want, and you may
even be the One True Developer capable of getting it absolutely right
under all circumstances, but I'd still reject patches that introduced a
SQLString-like function and ask that you resubmit them using the
database API's parameterization tools instead.
Assuming for the sake of discussion that your SQLString function
perfectly captures the transformation required to turn an arbitrary str
into a MySQL string literal. How do you address the following issues?
1. Other (possibly inexperienced) developers reading your source who
may not have the skills to correctly implement the same transform
correctly learn from your programs that writing your own query munger
is okay.
1a. Other (possibly inexperienced) developers decide to copy and paste
your function without fully understanding how it works, in tandem with
any of the other issues below. (If you think this is rare, I invite you
to visit stackoverflow or roseindia some time.)
2. MySQL changes the quoting and escaping rules to address a
bug/feature request/developer whim, introducing a new set of corner
cases into your function and forcing you to re-learn the escaping and
quoting rules. (For people using DB API parameters, this is a matter of
upgrading the DB adapter module to a version that supports the modified
rules.)
3. You decide to switch from MySQL to a more fully-featured RDBMS,
which may have different quoting and escaping rules around string
literals.
3a. *Someone else* decides to port your program to a different RDBMS,
and may not understand that SQLString implements MySQL's quoting and
escaping rules only.
4. MySQL AB finally get off their collective duffs and adds real
parameter separation to the MySQL wire protocol, and implements real
prepared statements to massive speed gains in scenarios that are
relevant to your interests; string-based query construction gets left
out in the cold.
4a. As with case 3, except that instead of the rules changing when you
move to a new RDBMS, it's the relative performance of submitting new
queries versus reusing a parameterized query that changes.
On top of the obvious issue of completely avoiding quoting bugs, using
query parameters rather than escaping and string manipulation neatly
saves you from having to address any of these problems (and a multitude
of others) -- the DB API implementation will handle things for you, and
you are propagating good practice in an easy-to-understand form.
I am honestly at a loss trying to understand your position. There is a
huge body of documentation out there about the weaknesses of
string-manipulation-based approaches to query construction, and the use
of query parameters is so compellingly the Right Thing that I have a
very hard time comprehending why anyone would opt not to use it except
out of pure ignorance of their existence. Generating executable code --
including SQL -- from untrusted user input introduces an large
vulnerability surface for very little benefit.
You don't handle function parameters by building up python-language
strs containing the values as literals and eval'ing them, do you?
-o
[0] If you want to be *really* pedantic, string-manipulation-based
query construction is strongly correlated with the occurrence of SQL
injection vulnerabilities and bugs, which is in turn not strongly
correlated with very many other practices. Happy?
--
http://mail.python.org/mailman/listinfo/python-list
