I don't know if it changed in 0.9.2c2 or the release before that but it used to be possible to put unescaped %'s in quoted strings in queries. Now you get:
_mysql_exceptions.ProgrammingError: not enough arguments for format string
This seems like a big change, don't it?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
File it as a bug, include an example that works as expected in 0.9.1 or 0.9.0 but fails in 0.9.2c2. I'm holding the release until then, so be quick about it.... I'm pretty sure that no version of MySQLdb tries to quote % in strings, and that this behavior occurs in earlier versions as well.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
result = ''
instr = 0
for c in str:
if c in ("'", '"'):
if instr and c == strdelim:
instr = 0
else:
instr = 1
strdelim = c
elif c == '%':
if instr:
result += c
result += c
return result
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
(don't worry, the indentation shows up in the email)
It could probably be done a lot easier with a regex (from the re module), but still, that's a lot of work to do on every query to catch something that will only been seen rarely, if at all.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Just to clarify this further: There's no attempt to quote % in query strings, because %s is used as the placeholder. If % appears in a string literal in the query string, it needs to be escaped (%%). A % in a query *parameter* doesn't need to be escaped.
Examples:
c.execute("SELECT * FROM foo WHERE name LIKE 'Bob%'")
This actually will work as written because there are no parameters passed, and thus the % operator is not applied to the query string.
c.execute("SELECT * FROM foo WHERE name LIKE 'Bob%' AND salary>%s", (10000,))
This won't work because the % in the literal 'Bob %' needs to be escaped.
c.execute("SELECT * FROM foo WHERE name LIKE 'Bob%%' AND salary>%s", (10000,))
This will work, the actual query executed is:
SELECT * FROM foo WHERE name LIKE 'Bob%' AND salary>10000
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
c.execute("SELECT * FROM foo WHERE name LIKE %s AND salary>%s", ('Bob %',10000,))
This also works because % in query parameter doesn't need to be escaped. It executes the same query as the previous example, and is the most sensible real-world case.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm not going to have time to research this properly for a day or so but I can see at a glance that the cases where there is no parameter substitution don't work as expected:
c.execute('insert into foo values ("%Opps")')
More later,
Thanks
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The problem is, there is no consistent way for execute to know when to escape a % and when it is part of a parameter substitution.
Alternatives:
c.execute("insert into foo values ('%%Opps')")
c.execute("insert into foo values (%s)", ('%Opps',))
How common is it to put a literal % in a query anyway? So long as it is in the parameters, there is no problem. The most common use of % in a query (other than just character data) is wildcard matching using LIKE, and it seems you would almost always want to use parameter substitution in those cases.
Looking back at the recent changes, in 0.9.1, the execute parmeters had a default argument of None. This was changed to be (), to eliminate a test: query % args works even if there are no parameters and no placeholders.
I'm inclined to keep this behavior, since it is more consistent.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I still haven't had time to look closely but I think I might disagree. Doesn't it make sense that a query with no substitution involved shouldn't get munged?
Also, I'm not sure what you mean by "query % args works even if there are no parameters and no placeholders." Do you mean this is okay:
c.execute('show tables', ())
The standard really ought to specify things like this.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It doesn't get munged, and there's no easy way to tell if substitution is involved, just because a % is present. WIth the current method, you know you always have to escape all literal '%' (as %%) whether or not you have any parameters, whereas with the old way, you only do it if you have no parameters. I still need to think about it, but there will be a third release candidate.
Yes, your show tables example is fine.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The DATE_FORMAT function takes a string parameter that usually contains literal %s. Would it make sense to parse the query enough to detect quoted strings and leave them alone?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The first thing I did with the MySQLdb package was wrap it up into a little class to make it a little more convenient to use; here's the method it uses to deal with this issue:
def _quote_query(self, query):
"Replace %'s in query with %% (unless they're already %%, or are %s-placeholders)"
return re.sub('%(?!s)%?', '%%', query)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I don't know if it changed in 0.9.2c2 or the release before that but it used to be possible to put unescaped %'s in quoted strings in queries. Now you get:
_mysql_exceptions.ProgrammingError: not enough arguments for format string
This seems like a big change, don't it?
File it as a bug, include an example that works as expected in 0.9.1 or 0.9.0 but fails in 0.9.2c2. I'm holding the release until then, so be quick about it.... I'm pretty sure that no version of MySQLdb tries to quote % in strings, and that this behavior occurs in earlier versions as well.
Heh heh heh.
No, but something like the folloing might help:
def escapeLiteralPercents(str):
assert type(str) == type('')
result = ''
instr = 0
for c in str:
if c in ("'", '"'):
if instr and c == strdelim:
instr = 0
else:
instr = 1
strdelim = c
elif c == '%':
if instr:
result += c
result += c
return result
Doh!
Indentation's gone. I'll email it if you want.
(don't worry, the indentation shows up in the email)
It could probably be done a lot easier with a regex (from the re module), but still, that's a lot of work to do on every query to catch something that will only been seen rarely, if at all.
Just to clarify this further: There's no attempt to quote % in query strings, because %s is used as the placeholder. If % appears in a string literal in the query string, it needs to be escaped (%%). A % in a query *parameter* doesn't need to be escaped.
Examples:
c.execute("SELECT * FROM foo WHERE name LIKE 'Bob%'")
This actually will work as written because there are no parameters passed, and thus the % operator is not applied to the query string.
c.execute("SELECT * FROM foo WHERE name LIKE 'Bob%' AND salary>%s", (10000,))
This won't work because the % in the literal 'Bob %' needs to be escaped.
c.execute("SELECT * FROM foo WHERE name LIKE 'Bob%%' AND salary>%s", (10000,))
This will work, the actual query executed is:
SELECT * FROM foo WHERE name LIKE 'Bob%' AND salary>10000
I'm interested in doing this. Would you mind telling me the proper approach:
c.execute("select * from Program where name like '%%%s%%'", ('test',))
i.e., I want the query presented to MySQL as:
select * from Program where name like '%test%'
Thanks in advance for the help.
c.execute("SELECT * FROM foo WHERE name LIKE %s AND salary>%s", ('Bob %',10000,))
This also works because % in query parameter doesn't need to be escaped. It executes the same query as the previous example, and is the most sensible real-world case.
I'm not going to have time to research this properly for a day or so but I can see at a glance that the cases where there is no parameter substitution don't work as expected:
c.execute('insert into foo values ("%Opps")')
More later,
Thanks
The problem is, there is no consistent way for execute to know when to escape a % and when it is part of a parameter substitution.
Alternatives:
c.execute("insert into foo values ('%%Opps')")
c.execute("insert into foo values (%s)", ('%Opps',))
How common is it to put a literal % in a query anyway? So long as it is in the parameters, there is no problem. The most common use of % in a query (other than just character data) is wildcard matching using LIKE, and it seems you would almost always want to use parameter substitution in those cases.
Looking back at the recent changes, in 0.9.1, the execute parmeters had a default argument of None. This was changed to be (), to eliminate a test: query % args works even if there are no parameters and no placeholders.
I'm inclined to keep this behavior, since it is more consistent.
I still haven't had time to look closely but I think I might disagree. Doesn't it make sense that a query with no substitution involved shouldn't get munged?
Also, I'm not sure what you mean by "query % args works even if there are no parameters and no placeholders." Do you mean this is okay:
c.execute('show tables', ())
The standard really ought to specify things like this.
It doesn't get munged, and there's no easy way to tell if substitution is involved, just because a % is present. WIth the current method, you know you always have to escape all literal '%' (as %%) whether or not you have any parameters, whereas with the old way, you only do it if you have no parameters. I still need to think about it, but there will be a third release candidate.
Yes, your show tables example is fine.
The DATE_FORMAT function takes a string parameter that usually contains literal %s. Would it make sense to parse the query enough to detect quoted strings and leave them alone?
Got a working example?
The first thing I did with the MySQLdb package was wrap it up into a little class to make it a little more convenient to use; here's the method it uses to deal with this issue:
def _quote_query(self, query):
"Replace %'s in query with %% (unless they're already %%, or are %s-placeholders)"
return re.sub('%(?!s)%?', '%%', query)