Yikes -- don't forget escaping quotes, etc.
> -----Original Message-----
> From: Chad MILLER [mailto:cmiller@stripped]
> Sent: Tuesday, June 12, 2007 6:44 AM
> To: Carl Karsten
> Cc: MySQL Internals
> Subject: Re: python's MySQLdb
>
>
> 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
>
>
>
