#137 _mysql.string_literal behaves funny

MySQLdb-1.2
closed
Andy Dustman
MySQLdb (285)
5
2012-09-19
2005-05-11
Anonymous
No

When you use MySQLdb.cursor.execute with a tuple of
string arguments, they are quoted in the resulting
query. That is not correct.

This behavior results from the method
_mysql.connection.string_literal, which is inherited by
MySQLdb.connection and seems to be identical with
_mysql.string_literal.

eg:

_mysql.string_literal("abcde")
... "'abcde'"

as you can see the string is quoted (inner single quotes)

I temporarily worked around the problem by
monkey-patching MySQLdb.connection.string_literal

Discussion

  • Andy Dustman
    Andy Dustman
    2005-05-11

    Logged In: YES
    user_id=71372

    Post your code, including query string. If you are putting
    quotes around the %s, save yourself some time and close this
    bug now: You shouldn't be doing that.

     
  • Logged In: NO

    Despite my tracking down the Problem, here the original
    Example (Sorry for the german names):

    cur.execute("SELECT %s, %s, %s from Patienten",
    ("Vorname", "Nachname", "PID"))
    ... 6L
    cur.fetchall()
    ... (('Vorname', 'Nachname', 'PID'), ('Vorname', 'Nachname',
    'PID'), ('Vorname', 'Nachname', 'PID'), ('Vorname',
    'Nachname', 'PID'), ('Vorname', 'Nachname', 'PID'),
    ('Vorname', 'Nachname', 'PID'))

    That seems like a correct call and an errorneous result to
    me. Hope that i didn't terribly misunderstand something here.

     
  • Andy Dustman
    Andy Dustman
    2005-05-12

    Logged In: YES
    user_id=71372

    You cannot use execute()'s parameter substitution to insert
    column or table names. It can only be used for values to be
    inserted or updated into a table, or for the query conditions.

    What you asked for (and got) is this:

    cur.execute("SELECT 'Vorname', 'Nachname', 'PID' from
    Patienten")

    What you really wanted was this:

    cur.execute("SELECT Vorname, Nachname, PID from Patienten")

    An example with parameter substittion would be:

    cur.execute("""SELECT Vorname, Nachname, PID from Patienten
    ... WHERE Vorname=%s AND Nachname=%s""", (vorname, nachname))

    Note that %s is not quoted; MySQLdb applies quotes as needed.

    If you really must have variable column or table names, then
    you must use the % operator, but then your parameter
    placeholders must be %%s (double the % to escape it).

     
  • Logged In: NO

    OK, I see your point now, but I still think "execute"
    behaves counter-intuitive this way. IMHO it would be much
    more convenient to have it behave like a mere stub for "foo
    %s" % "bar". This way the quotes could still be insertet by
    the User and you could have variable column and table names
    too (yes i really need them).
    Anyway, of course using the % operator crossed my mind, but
    doesn't this make parameter insertion by "execute" obsolete?

     
  • Andy Dustman
    Andy Dustman
    2005-05-12

    Logged In: YES
    user_id=71372

    Well, you're wrong. Read PEP-249:

    http://www.python.org/peps/pep-0249.html

    Not all databases use %s for parameters. Therefore using %
    is not portable, and it's not safe, either.

    If you look at the code, you'll see that execute() itself
    uses the % operator. It also uses connection.literal() (more
    general than string_literal()). Failure to use .literal() or
    something like that means your queries will randomly fail --
    or worse -- when some special character is in your
    parameter, the most obvious one being a single quote.

     
  • Logged In: NO

    I read that already. And I didn't say to make it a stub. I
    simply meant removing the automatic quoting. AFAIK that is
    not specified by DB-API. The user could still quote as needed.

    erm and when u say that some DBs don't use %s as
    placeholder, doesn't that mean, that using operator % is in
    fact more portable (because it's executed by Python and
    not the DB)? But of course the safety is a point. Wouldn't
    it be more safe to be able to use DB parameters for
    col/table names too? You could still write: execute("SELECT
    foo FROM bar WHERE a='%s'", ("bla",))

     
  • Andy Dustman
    Andy Dustman
    2005-05-12

    Logged In: YES
    user_id=71372

    This point is non-negotiable. This is a Python standard,
    it's been this way for more than six years, and it is how
    every other DB API module does it, i.e. they do not require
    the application to quote values passed to execute() .Do you
    have idea how much stuff would break if that were to change?

    One of the big reasons for this is that many databases
    (anything using ODBC, as an example) have a prepared
    statement interface where the server actually parses the
    query and recognizes placeholders, and the parameters are
    sent separately. MySQL-4.1 does this too, though the support
    has not been added yet. When it is added, you'll have to use
    ? for the placeholder because that's what the database
    requires. (Most likely, you'll be able to configure at
    run-time which you are using.)

    Now if you want to go ahead and use % to insert parameters
    in your query, go right ahead, but be sure to count your
    toes after each invocation, because sooner or later, you'll
    be shooting them off.

    The only valid reason to use the % operator with a query
    string is to change column and table names or insert other
    arbitrary bits of SQL, i.e.

    query = """SELECT %s FROM %s %s""" \ ... % ("Vorname, Nachname, PID", "Patienten", "WHERE
    PID=%s")
    cur.execute(query, (pid,))

     
  • Logged In: NO

    Yes the API breakage is of course the most important
    obstackle. And who knows whether my solution would be any
    better. Anyways thanks for enlightening me and sorry for the
    trouble. ;)