Why doesn't MySQLdb provide a function like this:
def QuoteSQL(Str, DoWild) :
"""returns a MySQL string literal which evaluates to Str. Needed
for those times when MySQLdb's automatic quoting isn't good enough."""
Result = []
for Ch in str(Str) :
if Ch == "\0" :
Ch = "\\0"
elif Ch == "\010" :
Ch = "\\b"
elif Ch == "\011" :
Ch = "\\t"
elif Ch == "\012" :
Ch = "\\n"
elif Ch == "\015" :
Ch = "\\r"
elif Ch == "\032" :
Ch = "\\z"
elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch
elif DoWild and (Ch == "%" or Ch == "_") :
Ch = "\\" + Ch
#end if
Result.append(Ch)
#end for
return "\"" + "".join(Result) + "\""
#end QuoteSQL
67  2234 
Lawrence D'Oliveiro wrote:
Why doesn't MySQLdb provide a function like this:
Because the author has read PEP 8?
Lawrence D'Oliveiro wrote:
Why doesn't MySQLdb provide a function like this:
def QuoteSQL(Str, DoWild) :
"""returns a MySQL string literal which evaluates to Str. Needed
for those times when MySQLdb's automatic quoting isn't good enough."""
Presumably because you're expected to use placeholders. When is that
not good enough?
elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch
Always sad to see an SQL DBMS willfully violate the SQL standard.
- Anders
In message <45**********************@nntp02.dk.telia.net>, Anders J. Munch
wrote:
Lawrence D'Oliveiro wrote:
>Why doesn't MySQLdb provide a function like this:
def QuoteSQL(Str, DoWild) :
"""returns a MySQL string literal which evaluates to Str. Needed
for those times when MySQLdb's automatic quoting isn't good
enough."""
Presumably because you're expected to use placeholders. When is that
not good enough?
Here's an example:
def QuoteSQLList(TheList) :
"""returns a MySQL list containing the items of TheList, suitable
for use in an "in" clause."""
return \
"(" + ", ".join([QuoteSQL(Str, False) for Str in TheList]) + ")"
#end QuoteSQLList
> elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch
Always sad to see an SQL DBMS willfully violate the SQL standard.
Why is that a violation of SQL?
Anders J. Munch wrote:
Always sad to see an SQL DBMS willfully violate the SQL standard.
You must be a constantly depressed person, then. :-)
--
Robert Kern
"I have come to believe that the whole world is an enigma, a harmless enigma
that is made terrible by our own mad attempt to interpret it as though it had
an underlying truth."
-- Umberto Eco
Lawrence D'Oliveiro enlightened us with:
Why doesn't MySQLdb provide a function like this:
Because generally you're able to pass newlines and the like just fine.
You can even store binary data into a BLOB column.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
In message <sl*********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
>Why doesn't MySQLdb provide a function like this:
Because generally you're able to pass newlines and the like just fine.
You can even store binary data into a BLOB column.
Yes, I have done blobs. Still need a quoting function for the specials,
though.
Lawrence D'Oliveiro enlightened us with:
Yes, I have done blobs. Still need a quoting function for the
specials, though.
Why? What makes your data so different from mine? I can store newlines
and the likes just fine in a regular text field.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
Robert Kern wrote:
Anders J. Munch wrote:
>Always sad to see an SQL DBMS willfully violate the SQL standard.
You must be a constantly depressed person, then. :-)
Nah, I just look the other way most of the time *g*
- Anders
Lawrence D'Oliveiro wrote:
>> elif Ch == "'" or Ch == "\"" or Ch == "\\" :
Ch = "\\" + Ch
Always sad to see an SQL DBMS willfully violate the SQL standard.
Why is that a violation of SQL?
Taking another look, I might be wrong: Your code uses double quotes, and
since SQL uses single quotes for string literals, it just might be a
compatible extension.
Otherwise I would have taken note of the backslash escapes. E.g. '\\'
is a two-character SQL string literal.
- Anders
Lawrence D'Oliveiro wrote:
In message <sl*********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
>>Lawrence D'Oliveiro enlightened us with:
>>>Why doesn't MySQLdb provide a function like this:
Because generally you're able to pass newlines and the like just fine.
You can even store binary data into a BLOB column.
Yes, I have done blobs. Still need a quoting function for the specials,
though.
No, actually you need to use the DB API as it was intended to be used.
You think Booleans' invert behaviour is strange, you think that
cgi.escape is broken, and you think Python needs a SQLquote function.
It might be a good idea to lurk for a bit longer and get more idea of
how Python is used in practice before starting to suggest spurious
improvements.
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
>Yes, I have done blobs. Still need a quoting function for the
specials, though.
Why? What makes your data so different from mine?
"select * from details where person_name like"
" concat(\"%%\", %s, \"%%\")" \
% \
QuoteSQL(name, True)
Lawrence D'Oliveiro enlightened us with:
"select * from details where person_name like"
" concat(\"%%\", %s, \"%%\")" \
% \
QuoteSQL(name, True)
Wouldn't this be a whole lot better?
cursor.execute(
"select * from details where person_name like ?",
'%' + name + '%'
)
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
> "select * from details where person_name like"
" concat(\"%%\", %s, \"%%\")" \
% \
QuoteSQL(name, True)
Wouldn't this be a whole lot better?
cursor.execute(
"select * from details where person_name like ?",
'%' + name + '%'
)
No. Can you figure out why?
Lawrence D'Oliveiro enlightened us with:
>Wouldn't this be a whole lot better?
cursor.execute(
"select * from details where person_name like ?",
'%' + name + '%'
)
No. Can you figure out why?
Ok, should have tested it better. This works fine on my machine,
though:
curs.execute(
"select * from details where person_name like ?",
('%' + name + '%', )
)
Including all sorts of quotes, newlines, backslashes etc. in the name.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
Sybren Stuvel <sy*******@YOURthirdtower.com.imaginationwrote:
Ok, should have tested it better. This works fine on my machine,
though:
curs.execute(
"select * from details where person_name like ?",
('%' + name + '%', )
)
Including all sorts of quotes, newlines, backslashes etc. in the name.
I think his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for his
proposed QuoteSQL.
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
Sybren Stuvel <sy*******@YOURthirdtower.com.imaginationwrote:
>Ok, should have tested it better. This works fine on my machine,
though:
curs.execute(
"select * from details where person_name like ?",
('%' + name + '%', )
)
Including all sorts of quotes, newlines, backslashes etc. in the name.
I think his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for
his proposed QuoteSQL.
Why not? That is exactly one of the options my QuoteSQL offers.
Lawrence D'Oliveiro <ld*@geek-central.gen.new_zealandwrote:
>This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for
his proposed QuoteSQL.
Why not? That is exactly one of the options my QuoteSQL offers.
Yes, but your code also quotes non-wildcards, and that is precisely the
behaviour you don't want here as you should be using a parameterised
queries.
Duncan Booth enlightened us with:
I think his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
But of course.
This could be an argument for having a utility function to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
Lawrence D'Oliveiro <ld*@geek-central.gen.new_zealandwrote:
>>This could be an argument for having a utility function to escape the
wildcards for this sort of situation, but certainly not an argument for
his proposed QuoteSQL.
Why not? That is exactly one of the options my QuoteSQL offers.
Yes, but your code also quotes non-wildcards...
It quotes specials which can cause trouble with MySQL. The specials come in
two sets (wildcards and others), but they are both specials.
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Duncan Booth enlightened us with:
>I think his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
But of course.
>This could be an argument for having a utility function to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Even when the two situations are related?
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Duncan Booth enlightened us with:
>I think his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
But of course.
>This could be an argument for having a utility function to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Look at it this way: there is _no_ case where you need escaping of wildcards
without also escaping other specials.
Lawrence D'Oliveiro wrote:
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
>>Duncan Booth enlightened us with:
>>>I think his point was that any '%' characters inside name act like
wildcards whereas his version looked for literal percents.
But of course.
>>>This could be an argument for having a utility function to escape
the wildcards for this sort of situation, but certainly not an
argument for his proposed QuoteSQL.
Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Look at it this way: there is _no_ case where you need escaping of wildcards
without also escaping other specials.
Yes, there is, so please lose the bombast.
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are for on the first place, and they
have a portability advantage among other reasons why you should use them
(another's potential efficiency).
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
Lawrence D'Oliveiro <ld*@geek-central.gen.new_zealandwrote:
>Indeed. An escaping function should be small and not do all kinds of
escaping for different situations at once.
Look at it this way: there is _no_ case where you need escaping of
wildcards without also escaping other specials.
You need to engage brain before posting:
>>cursor.execute("select * from example"); pprint(cursor.fetchall())
3L
((1L, "o'neil"), (2L, "o'leary"), (3L, 'new\nline'))
>>cursor.execute("select * from example where name like concat('%%', %s,
'%%')", "'"); pprint(cursor.fetchall())
2L
((1L, "o'neil"), (2L, "o'leary"))
>>cursor.execute("select * from example where name like concat('%%', %s,
'%%')", "\\'"); pprint(cursor.fetchall())
2L
((1L, "o'neil"), (2L, "o'leary"))
>>cursor.execute("select * from example where name like concat('%%', %s,
'%%')", "\n"); pprint(cursor.fetchall())
1L
((3L, 'new\nline'),)
>>cursor.execute("select * from example where name like concat('%%', %s,
'%%')", "\\n"); pprint(cursor.fetchall())
2L
((1L, "o'neil"), (3L, 'new\nline'))
>>>
The spurious escaping of the apostrophe does no harm, but spuriously
escaping a newline makes the select match the letter 'n' insteal of
matching a newline.
Lawrence D'Oliveiro enlightened us with:
>An escaping function should be small and not do all kinds of
escaping for different situations at once.
Even when the two situations are related?
Yup, even then. Different situations need different escaping
functions.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
>>An escaping function should be small and not do all kinds of
escaping for different situations at once.
Even when the two situations are related?
Yup, even then. Different situations need different escaping
functions.
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
The trouble with this is that, instead of offering extra functionality, it
leaves the door open to making two stupid mistakes:
1) quoting of wildcards WITHOUT quoting of non-wildcards
2) quoting of wildcards BEFORE quoting of non-wildcards
There are only two non-stupid solutions to this problem that I can see.
Either:
* A pair of functions that perform
1) quoting of non-wildcard specials only
2) quoting of both non-wildcard and wildcard specials
Or:
* A single function that performs either of the above two operations,
depending on a boolean flag--which is what QuoteSQL does.
I guess I don't have a strong preference for which way we do it, but I would
suggest confining our discussion to non-stupid solutions.
In message <ma**************************************@python.o rg>, Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are for on the first place...
So you're suggesting I quote the wildcards, then rely on autoquoted
parameters to handle the rest? Unfortunately, that's stupid mistake number
2.
In article <ef**********@lust.ihug.co.nz>, Lawrence D'Oliveiro wrote:
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
Lawrence, you're wrong in this thread for the same reason you were
right in the cgi.escape thread. Escaping general characters for string
literals is a different operation from escaping wildcards from pattern
matches, and for a good reason.
In message <sl***********************@snowy.squish.net>, Jon Ribbens wrote:
In article <ef**********@lust.ihug.co.nz>, Lawrence D'Oliveiro wrote:
>You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
Lawrence, you're wrong...
Did I misunderstand what Sybren Stuvel was suggesting?
Lawrence D'Oliveiro wrote:
In message <ma**************************************@python.o rg>, Steve
Holden wrote:
>>When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are for on the first place...
So you're suggesting I quote the wildcards, then rely on autoquoted
parameters to handle the rest? Unfortunately, that's stupid mistake number
2.
Ah, so your quoting function will deduce the context in which arguments
intended for parameter substitution in the query will be used? Or are
you suggesting that it's unwise to rely on autoquoted parameters? That
could have a serious impact on the efficiency of some repeated queries.
I find it difficult to take your argument seriously. Are you doing this
just to be obnoxious, or do you have a genuine point to make?
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
Steve Holden wrote:
Ah, so your quoting function will deduce the context in which arguments
intended for parameter substitution in the query will be used? Or are
you suggesting that it's unwise to rely on autoquoted parameters? That
could have a serious impact on the efficiency of some repeated queries.
"but people wouldn't use Python if they cared about efficiency"
</F>
In message <ma**************************************@python.o rg>, Steve
Holden wrote:
Lawrence D'Oliveiro wrote:
>>
In message <ma**************************************@python.o rg>, Steve
Holden wrote:
>>>When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are for on the first place...
So you're suggesting I quote the wildcards, then rely on autoquoted
parameters to handle the rest? Unfortunately, that's stupid mistake
number 2.
Ah, so your quoting function will deduce the context in which arguments
intended for parameter substitution in the query will be used? Or are
you suggesting that it's unwise to rely on autoquoted parameters?
No, I'm saying it's _incorrect_ to use the existing autoquoting mechanism in
combination with a separate function that escapes the wildcards. I
previously described the two stupid mistakes that can arise from having a
separate function for doing just the wildcard quoting: this is the second
one.
That could have a serious impact on the efficiency of some repeated
queries.
Correctness comes before efficiency. It's no point doing it quickly if
you're doing it wrong.
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
The spurious escaping of the apostrophe does no harm, but spuriously
escaping a newline makes the select match the letter 'n' insteal of
matching a newline.
And how would you get my QuoteSQL routine, as written, to make the same
mistake you did?
Lawrence D'Oliveiro enlightened us with:
You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
I'm suggesting functions based on the role of the string they need to
escape, not the characters in that string.
1) Quoting of wildcard strings for a query using LIKE etc.
2) Quoting of values for putting into queries.
You only need the first one, since every database interface that
follows PEP 249.
I guess I don't have a strong preference for which way we do it, but
I would suggest confining our discussion to non-stupid solutions.
Good suggestion. I'd say, write a function that escapes for use in
LIKE queries, and leave the other quoting to the database interface.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
Lawrence D'Oliveiro enlightened us with:
The trouble with this is that, instead of offering extra functionality, it
leaves the door open to making two stupid mistakes:
2) quoting of wildcards BEFORE quoting of non-wildcards
Why is this a "stupid" mistake in your view? Please explain this in
detail, because I think it's a proper way of doing it.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
>The trouble with this is that, instead of offering extra functionality,
it leaves the door open to making two stupid mistakes:
2) quoting of wildcards BEFORE quoting of non-wildcards
Why is this a "stupid" mistake in your view? Please explain this in
detail, because I think it's a proper way of doing it.
Because quoting the wildcards introduces backslash specials before each
wildcard. Quoting non-wildcards then causes those backslashes to be
doubled, which means they escape themselves instead of the wildcards.
Sybren Stuvel wrote:
I'm suggesting functions based on the role of the string they need to
escape, not the characters in that string.
1) Quoting of wildcard strings for a query using LIKE etc.
2) Quoting of values for putting into queries.
it's actually quite amusing that some people have such a hard time
developing a conceptual model that actually matches the underlying
architecture and the information model. and it's usually the same
people that end up arguing for hypergeneralization.
it's about data, folks, not syntax artifacts.
</F>
Lawrence D'Oliveiro <ld*@geek-central.gen.new_zealandwrote:
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
>The spurious escaping of the apostrophe does no harm, but spuriously
escaping a newline makes the select match the letter 'n' insteal of
matching a newline.
And how would you get my QuoteSQL routine, as written, to make the same
mistake you did?
If you think I made a mistake I'm afraid you'll have to tell me what it
was. I'm unable to read your mind.
However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I think you'll find that the only characters you need to quote
are \, % and _. Quoting anything else would be a mistake.
In particular it currently turns newlines in backslash followed by n which
(since MySQL ignores the extra backslash escape) is equivalent to turning
newlines into the character n.
Lawrence D'Oliveiro enlightened us with:
Because quoting the wildcards introduces backslash specials before
each wildcard. Quoting non-wildcards then causes those backslashes
to be doubled, which means they escape themselves instead of the
wildcards.
I don't know about other DBMSes, but in PostgreSQL you can use any
escape character for the pattern, check out http://www.postgresql.org/docs/8.0/i...-matching.html
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I think you'll find that the only characters you need to quote
are \, % and _.
That won't work--that puts you into stupid mistake number 2.
I think autoquoting is fine as far as it goes. But it cannot cope with
wildcards, since it can't tell whether the string is being used in a LIKE
clause without doing its own parsing of the MySQL query. And there are
situations where you cannot rely on it, as in the QuoteSQLList example I
gave earlier. This is why my QuoteSQL function cannot be designed to work
together with autoquoting, but has to be used as a complete replacement for
it.
In particular it currently turns newlines in backslash followed by n which
(since MySQL ignores the extra backslash escape) is equivalent to turning
newlines into the character n.
But \n is valid MySQL syntax for a newline.
In message <sl**********************@schuimige.stuvel.eu>, Sybren Stuvel
wrote:
Lawrence D'Oliveiro enlightened us with:
>You're proposing two separate functions:
1) quoting of non-wildcard specials
2) quoting of wildcard specials
I'm suggesting functions based on the role of the string they need to
escape, not the characters in that string.
1) Quoting of wildcard strings for a query using LIKE etc.
2) Quoting of values for putting into queries.
You know what, it turns out there is no stupid mistake number 2. Quoted
wildcards can be put through a non-wildcard quoting routine and it will
work. In fact, that's how it should work--the LIKE clause does its own
parsing of the pattern string, I was just being misled by the fact that
MySQL's lexical analyzer was passing through the backslashes in the string
literals because they weren't escaping special characters.
So yes, there should be two separate functions, one for escaping
non-wildcard specials, and one for escaping wildcards.
You only need the first one, since every database interface that
follows PEP 249.
You still need the second one, in instances like the QuoteSQLList example I
gave earlier.
Lawrence D'Oliveiro wrote:
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
>>However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I think you'll find that the only characters you need to quote
are \, % and _.
That won't work--that puts you into stupid mistake number 2.
I think autoquoting is fine as far as it goes. But it cannot cope with
wildcards, since it can't tell whether the string is being used in a LIKE
clause without doing its own parsing of the MySQL query. And there are
situations where you cannot rely on it, as in the QuoteSQLList example I
gave earlier. This is why my QuoteSQL function cannot be designed to work
together with autoquoting, but has to be used as a complete replacement for
it.
>>In particular it currently turns newlines in backslash followed by n which
(since MySQL ignores the extra backslash escape) is equivalent to turning
newlines into the character n.
But \n is valid MySQL syntax for a newline.
Phew. It's a good hob that MySQL is the only database in the known
universe, then, isn't it.
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
Lawrence D'Oliveiro wrote:
In message <Xn*************************@127.0.0.1>, Duncan Booth wrote:
>>However, your QuoteSQL messes up every time because it wraps double
quotes round the whole string, so it isn't suitable for use with
parameterised queries at all. If you care to modify it to work in that
situation I think you'll find that the only characters you need to quote
are \, % and _.
That won't work--that puts you into stupid mistake number 2.
I think autoquoting is fine as far as it goes. But it cannot cope with
wildcards, since it can't tell whether the string is being used in a LIKE
clause without doing its own parsing of the MySQL query. And there are
situations where you cannot rely on it, as in the QuoteSQLList example I
gave earlier. This is why my QuoteSQL function cannot be designed to work
together with autoquoting, but has to be used as a complete replacement for
it.
>>In particular it currently turns newlines in backslash followed by n which
(since MySQL ignores the extra backslash escape) is equivalent to turning
newlines into the character n.
But \n is valid MySQL syntax for a newline.
Phew. It's a good hob that MySQL is the only database in the known
universe, then, isn't it.
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
Lawrence D'Oliveiro wrote:
In message <ma**************************************@python.o rg>, Steve
Holden wrote:
>>Lawrence D'Oliveiro wrote:
>>>In message <ma**************************************@python.o rg>, Steve
Holden wrote:
When you use the DB API correctly and paramterise your queries you still
need to quote wildcards in search arguments, but you absolutely
shouldn't quote the other SQL specials.
That's what parameterised queries are for on the first place...
So you're suggesting I quote the wildcards, then rely on autoquoted
parameters to handle the rest? Unfortunately, that's stupid mistake
number 2.
Ah, so your quoting function will deduce the context in which arguments
intended for parameter substitution in the query will be used? Or are
you suggesting that it's unwise to rely on autoquoted parameters?
No, I'm saying it's _incorrect_ to use the existing autoquoting mechanism in
combination with a separate function that escapes the wildcards. I
previously described the two stupid mistakes that can arise from having a
separate function for doing just the wildcard quoting: this is the second
one.
Sadly your assertions alone fail to convince. Perhaps you could provide
a concrete example?
>
>>That could have a serious impact on the efficiency of some repeated
queries.
Correctness comes before efficiency. It's no point doing it quickly if
you're doing it wrong.
Indeed not. But there's no point being right if you can't explain why.
regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden
On Tue, 2006-09-26 at 07:08, Lawrence D'Oliveiro wrote:
So yes, there should be two separate functions, one for escaping
non-wildcard specials, and one for escaping wildcards.
You only need the first one, since every database interface that
follows PEP 249.
You still need the second one, in instances like the QuoteSQLList example I
gave earlier.
"Need" is a strong word unless something like the following doesn't work
for some reason:
cur.execute("select * from people where last_name in (%s,%s,%s)",
(name1, name2, name3) )
-Carsten
"Need" is a strong word unless something like the
following doesn't work for some reason:
cur.execute("select * from people where last_name in
(%s,%s,%s)", (name1, name2, name3) )
Which could be nicely generalized to something like
>>t = (name1, name2, name3)
cur.execute("select * from people where last_name in (%s)" %
','.join('%s' for i in xrange(len(t))),
t)
which will create the number of items in the formatting-string on
the fly, and then map the tuple using standard DB escaping
methods. With older versions of Python, one might have to wrap
the contents of the join() call in [...]
-tkc
Tim Chase enlightened us with:
>cur.execute("select * from people where last_name in (%s)" %
','.join('%s' for i in xrange(len(t))),
t)
But since the value of 'i' isn't used at all, it can be abbreviated
to:
>>cur.execute("select * from people where last_name in (%s)" %
','.join('%s' for i in t),
t)
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
Sybren Stuvel wrote:
>cur.execute("select * from people where last_name in (%s)" % ','.join('%s' for i in t), t)
and now we're waiting for the "['%s']*len(t)" vs. "'%s' for i in t" benchmarks (and the
"consistency is more important than efficiency" and "creating extra objects is conceptually
wrong" followups, and the "it depends on what you mean by" followups to the followups)
</F>
Fredrik Lundh enlightened us with:
and now we're waiting for the "['%s']*len(t)" vs. "'%s' for i in t"
benchmarks (and the "consistency is more important than efficiency"
and "creating extra objects is conceptually wrong" followups, and
the "it depends on what you mean by" followups to the followups)
I didn't care anything about all that. I just found the way I wrote it
somewhat easier to read, that's all.
Sybren
--
Sybren Stüvel
Stüvel IT - http://www.stuvel.eu/
Sybren Stuvel wrote:
I didn't care anything about all that. I just found the way I wrote it
somewhat easier to read.
absolutely.
</F> This thread has been closed and replies have been disabled. Please start a new discussion. |
