On 11 Jun 2007, at 21:43, Carl Karsten wrote:
> This is kinda OT, so feel free to tell me where to go :)
>
> I just noticed that python's mysql bindings combine commands and
> parameters before they get sent to the server:
>
> def execute(self, query, args=None):
> ...
> if args is not None:
> query = query % db.literal(args)
>
> Unless I'm mistaken, this is in general: bad.
>
> I would like to bring it to someone's attention in hopes that it
> will be improved, but first I need to make a case for why.
I haven't seen this Python source code, and I am assuming what you
say is true, even though it sounds too awful to be true. I'll speak
strictly about what's bad from a Python/string point of view; there's
a big prepared-statement complaint to make also, and I'll let others
make it.
Strings are a necessary evil when expressing a SQL statement; they're
the best way we have to store a SQL statement. But, that doesn't
mean that they're easily operated-on as strings. C percent
conversion-specifications strings don't map at all to SQL bind
parameters. C-style Conversion specifications aren't stateful, but
bind parameters are.
Consider this query: SELECT 1, '2?', ? FROM t1;
and this string: SELECT 1, '2%s', %s FROM t1;
Python's string-modulus operator doesn't (and shouldn't) understand
SQL syntax at all. As it scans through the string looking for "%s",
it doesn't consider whether it's in a position that a bind parameter
is possible or legal. The first question-mark in the first query is
part of a literal string, but the second is a bind parameter because
it's not -- only one parameter should be given to be inserted by the
database. For the second, a stateless conversion-specification
interpreter will see two locations where parameters should be inserted.
The BIGGEST problem is that it mixes code and data before handing the
statement to the server. Return to the example above. Suppose the
third column I wish to insert is the string
3; DROP TABLE t2; SELECT 1, 2, 3
then think about what gets sent to the SQL server. That is NEVER
possible using bind parameters.
I love Python, and I'm all for being Pythonic, but the tools that
Python has are terrible for bind variables. In fact, it's a lie to
even call them that, given what's above. It's string substitution,
and that's all.
> What I am looking for is a write up on why keeping parameters
> separate is important. I think I know, but I am not a reliable
> source. so something on mysql.com or from a mysql dev would carry
> a bit more weight.
I suspect you can make this argument, Carl. Research what prepared
statements are for. I'd at least point out:
* (usually) doesn't incur parser for successive statements; prepare
once, execute many times with different parameters. Send SQL, then
send bind parameters for first execution, then send for second, et c.
* delegates understanding of SQL to the SQL-server or -library.
Taking on parsing of SQL locally is hugely presumptuous. Do not
meddle in the affairs of SQL servers, for they are subtle and quick
to anger.
* avoid security problems. Code is code, and data is data. They can
never mix.
- chad
--
Chad Miller, Software Developer chad@stripped
MySQL Inc., www.mysql.com
Orlando, Florida, USA 13-20z, UTC-0400
Office: +1 408 213 6740 sip:6740@stripped
Attachment: [application/pgp-signature] This is a digitally signed message part PGP.sig
