#137 _mysql.string_literal behaves funny

[Anonymous]

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

[Andy Dustman]

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.

[Nobody/Anonymous]

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]

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).

[Nobody/Anonymous]

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]

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.

[Nobody/Anonymous]

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]

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,))

[Nobody/Anonymous]

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. ;)