Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> In message <[EMAIL PROTECTED]>, LI wrote:
>
> execfile("QuoteSQL.py")
> EscapeSQLWild(r"\%")
>> '%'
> SQLString("%" + EscapeSQLWild(r"\%") + "%")
>> '"%%%"'
> EscapeSQLWild(r"\%") == r"\\%"
>> True
> SQLString("%" + EscapeSQLWild(r"\%") + "%") == r'"%%%"'
>> True
>
> With the correction to EscapeSQLWild, this becomes:
>
execfile("QuoteSQL.py")
EscapeSQLWild(r"\%")
> '\\%'
SQLString("%" + EscapeSQLWild(r"\%") + "%")
> '"%%%"'
EscapeSQLWild(r"\%") == r"\\\%"
> True
SQLString("%" + EscapeSQLWild(r"\%") + "%") == r'"%\\%%"'
> True
>
True but irrelevant. The point is that it isn't relevant whether you are
seeing 4, 6, 8, or 12 backslashes, because you wrote the code to produce
the number you thought you wanted and you had misunderstood how MySQL
works. That's why it is important in a situation like this to test against
the code that actually uses the string. I had no idea how MySQL would
handle escapes in this situation, but I didn't need to know, I just wrote
some tests and figured out which strings would make them pass or fail.
Anyway, congratulations on finally getting the message.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, LI wrote:
execfile("QuoteSQL.py")
EscapeSQLWild(r"\%")
> '%'
SQLString("%" + EscapeSQLWild(r"\%") + "%")
> '"%%%"'
EscapeSQLWild(r"\%") == r"\\%"
> True
SQLString("%" + EscapeSQLWild(r"\%") + "%") == r'"%%%"'
> True
With the correction to EscapeSQLWild, this becomes:
>>> execfile("QuoteSQL.py")
>>> EscapeSQLWild(r"\%")
'\\%'
>>> SQLString("%" + EscapeSQLWild(r"\%") + "%")
'"%%%"'
>>> EscapeSQLWild(r"\%") == r"\\\%"
True
>>> SQLString("%" + EscapeSQLWild(r"\%") + "%") == r'"%\\%%"'
True
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, I wrote:
> def EscapeSQLWild(Str) :
> """escapes MySQL pattern wildcards in Str."""
> Result = []
> for Ch in str(Str) :
> if Ch == "%" or Ch == "_" :
> Result.append("\\")
> #end if
> Result.append(Ch)
> #end for
> return "".join(Result)
> #end EscapeSQLWild
Correction, backslashes need to be escaped at this level as well. So that
should become
def EscapeSQLWild(Str) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "\\" or Ch == "%" or Ch == "_" :
Result.append("\\")
#end if
Result.append(Ch)
#end for
return "".join(Result)
#end EscapeSQLWild
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
>> Deary me. Did you actually test out that bit of code before you
>> posted it?
>
execfile("QuoteSQL.py")
EscapeSQLWild(r"\%")
> '%'
SQLString("%" + EscapeSQLWild(r"\%") + "%")
> '"%%%"'
EscapeSQLWild(r"\%") == r"\\%"
> True
SQLString("%" + EscapeSQLWild(r"\%") + "%") == r'"%%%"'
> True
>
Ah, so that's a 'no' then. I can't see any tests there. How do you know
that those strings work correctly MySQL queries?
Please, open your mind to what I'm saying. I'm not trying to criticise your
aims, just trying to point out the simple fact that your EscapeSQLWild
function has a bug. If nothing else, the fact that you are finding this so
hard to understand shows that there is a need for a correctly written
function to do this.
The fix to EscapeSQLWild to get test_escapebackslashwild2 to work is a
trivial change, and not suprisingly also makes the other failing test in my
script (the one using parameterised queries and EscapeSQLWild) pass.
Again, please, try running the script I posted, and in particular
test_escapebackslashwild2. It uses the SQL query you yourself created, and
it fails because it matches something it shouldn't.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>
>> I'm assuming you mean, how would you get from a Python expression to a
>> MySQL clause that looks like
>>
>> name like "%%%"
>>
>> (wildcard % followed by literal backslash \\ followed by literal
>> percent \% followed by wildcard %.) That's easy:
>>
>> EscapeSQLWild(r"\%") => r"\\%"
>> SQLString(r"\\%") => r'"%"'
>>
>> So the Python expression
>>
>> "name like %s" % SQLString("%" + EscapeSQLWild(r"\%") + "%")
>>
>> gives you what you want.
>>
> Deary me. Did you actually test out that bit of code before you posted it?
>>> execfile("QuoteSQL.py")
>>> EscapeSQLWild(r"\%")
'%'
>>> SQLString("%" + EscapeSQLWild(r"\%") + "%")
'"%%%"'
>>> EscapeSQLWild(r"\%") == r"\\%"
True
>>> SQLString("%" + EscapeSQLWild(r"\%") + "%") == r'"%%%"'
True
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> I'm assuming you mean, how would you get from a Python expression to a
> MySQL clause that looks like
>
> name like "%%%"
>
> (wildcard % followed by literal backslash \\ followed by literal
> percent \% followed by wildcard %.) That's easy:
>
> EscapeSQLWild(r"\%") => r"\\%"
> SQLString(r"\\%") => r'"%"'
>
> So the Python expression
>
> "name like %s" % SQLString("%" + EscapeSQLWild(r"\%") + "%")
>
> gives you what you want.
>
Deary me. Did you actually test out that bit of code before you posted it?
No, I thought not. I even gave you a test harness to make it easy for you
to check the quality of your code before posting.
All you had to do was to add another test:
def test_escapebackslashwild2(self):
self.cursor.execute(
("select name from pythontest where name like %s" %
SQLString("%" + EscapeSQLWild(r"\%") + "%")))
expected = (('x\\%x',),)
self.assertEqual(expected, self.cursor.fetchall())
and the output is:
==
FAIL: test_escapebackslashwild2 (__main__.Tests)
--
Traceback (most recent call last):
File "mysqltest.py", line 111, in test_escapebackslashwild2
self.assertEqual(expected, self.cursor.fetchall())
AssertionError: (('x\\%x',),) != (('x\\nx',), ('x\\%x',))
--
as I said before, your escaping is too late and not enough. You've got a
search for a literal backslash in there sure enough, but you haven't
managed to escape the percent character.
Try again.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>
>>> You are still missing the point. I'm not talking about generating a
>>> MySQL string literal, I'm talking about preventing wildcards
>>> characters having their special meaning when using the string as a
>>> parameter in cursor.execute.
>>
>> But that's what cursor.execute will do if you use its
>> parameter-substitution mechanism--generate a string literal.
>
> The current implementation of the MySQL database adapter will do that.
> Other database adaptors may handle parameters without generating string
> literals.
Doesn't matter what other implementations of parametrization might or might
not do. The syntax I generate is valid for MySQL, therefore it will work
with the MySQL database adapter regardless of what else the adaptor might
do.
>>> You still have to escape the escape character...
>>
>> Which will be done by cursor.execute if you use its
>> parameter-substitution mechanism.
>
> Too late and not enough. Too late, because if you want to search for the
> literal "\\%" (single backslash percent) you need to escape the backslash
> before you escape the percent. Not enough because at the point MySQLdb
> finally converts it to a string literal a literal backslash to be used in
> a context where wildcards are allowed needs to be spelled with 4
> backslashes. i.e. it needs to be escaped twice, once for the string
> literal and once to stop it being interpreted as an escape within the
> wildcard string.
I'm assuming you mean, how would you get from a Python expression to a MySQL
clause that looks like
name like "%%%"
(wildcard % followed by literal backslash \\ followed by literal percent \%
followed by wildcard %.) That's easy:
EscapeSQLWild(r"\%") => r"\\%"
SQLString(r"\\%") => r'"%"'
So the Python expression
"name like %s" % SQLString("%" + EscapeSQLWild(r"\%") + "%")
gives you what you want.
> Correct: they both do the same thing. So you have to use either SQLString
> or the parameter substitution. You cannot use both. Calling SQLString on a
> string to be passed in to the parameter substitution mechanism will not
> work correctly.
I thought I had made that clear already.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>> You are still missing the point. I'm not talking about generating a
>> MySQL string literal, I'm talking about preventing wildcards
>> characters having their special meaning when using the string as a
>> parameter in cursor.execute.
>
> But that's what cursor.execute will do if you use its
> parameter-substitution mechanism--generate a string literal.
The current implementation of the MySQL database adapter will do that.
Other database adaptors may handle parameters without generating string
literals.
>
>> You still have to escape the escape character...
>
> Which will be done by cursor.execute if you use its
> parameter-substitution mechanism.
Too late and not enough. Too late, because if you want to search for the
literal "\\%" (single backslash percent) you need to escape the backslash
before you escape the percent. Not enough because at the point MySQLdb
finally converts it to a string literal a literal backslash to be used in a
context where wildcards are allowed needs to be spelled with 4 backslashes.
i.e. it needs to be escaped twice, once for the string literal and once to
stop it being interpreted as an escape within the wildcard string.
>
>> Calling the SQLString routine in this situation would be wrong
>> because it would escape characters such as newline which must not be
>> escaped.
>
> SQLString will convert newlines into the \n sequence in the generated
> string literal, which MySQL will interpret as a newline.
> cursor.execute's parameter-substitution mechanism would do exactly the
> same thing.
>
Correct: they both do the same thing. So you have to use either SQLString
or the parameter substitution. You cannot use both. Calling SQLString on a
string to be passed in to the parameter substitution mechanism will not
work correctly.
May I suggest that the way for you to progress would be if you wrote some
unit tests? So, create a simple table containing a few strings with special
characters and do a few wildcard searches looking for %, newline etc. That
way you can post not just a function, but some runnable code which either
demonstrates that your function does what you say, or lets people suggest a
new test which demonstrates that it fails to handle some particular edge
case.
Here, I'll even give you a start. Run the code below (you might need to
create a database called 'test' if you don't already have one), and then
explain why test_escapebackslashwild fails, and either why you think the
test is broken or how you would fix your code? All the other tests should
pass.
mysqltest.py ---
import unittest
import MySQLdb
def EscapeSQLWild(Str) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "%" or Ch == "_" :
Result.append("\\")
#end if
Result.append(Ch)
#end for
return "".join(Result)
#end EscapeSQLWild
class Tests(unittest.TestCase):
values = "x%x", "xnx", "x\nx", "x\\nx", "x\\%x"
def setUp(self):
db = self.db = MySQLdb.connect("", "", "", "test")
cursor = self.cursor = db.cursor()
cursor.execute('''create temporary table pythontest
(id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(30))''')
cursor.executemany(
"insert into pythontest(name) values(%s)",
self.values)
def tearDown(self):
self.cursor.execute("drop table pythontest")
def test_wildcard(self):
n = self.cursor.execute(
"select name from pythontest where name like %s",
"x%x")
self.assertEqual(n, 5)
def test_nonwildcard(self):
self.cursor.execute(
"select name from pythontest where name like %s",
"x\\%x")
expected = (('x%x',),)
self.assertEqual(expected, self.cursor.fetchall())
def test_newline(self):
self.cursor.execute(
"select name from pythontest where name like %s",
"x\nx")
expected = (('x\nx',),)
self.assertEqual(expected, self.cursor.fetchall())
def test_backslashn(self):
self.cursor.execute(
"select name from pythontest where name like %s",
"xnx")
expected = (('x\\nx',),)
self.assertEqual(expected, self.cursor.fetchall())
def test_backslashpercent(self):
self.cursor.execute(
"select name from pythontest where name like %s",
"x\\%x")
expected = (('x\\%x',),)
self.assertEqual(expected, self.cursor.fetchall())
def test_escapewild(self):
self.cursor.execute(
"select name from pythontest where name like %s",
EscapeSQLWild("x%x"))
expected = (('x%x',),)
self.assertEqual(expected, self.cursor.fetchall())
def test_escapebackslashwild(self):
self.cursor.execute(
"se
Re: QuoteSQL
Lawrence D'Oliveiro wrote:
> In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
>
> > Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> >
> >> In message <[EMAIL PROTECTED]>, Duncan Booth
> >> wrote:
> >>
> >>> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> >>>
> def EscapeSQLWild(Str) :
> """escapes MySQL pattern wildcards in Str."""
> Result = []
> for Ch in str(Str) :
> if Ch == "%" or Ch == "_" :
> Result.append("\\")
> #end if
> Result.append(Ch)
> #end for
> return "".join(Result)
> #end EscapeSQLWild
> >>>
> >>> That doesn't quite work. If you want to stop wildcards being
> >>> interpreted as such in a string used as a parameter to a query, then
> >>> you have to escape the escape character as well.
> >>
> >> That's part of the separation of function. Note that the above
> >> function does not generate a MySQL string literal: you must still put
> >> it through the previously-defined SQLString routine, which will
> >> automatically escape all the specials added by EscapeSQLWild.
> >>
> > You are still missing the point. I'm not talking about generating a MySQL
> > string literal, I'm talking about preventing wildcards characters having
> > their special meaning when using the string as a parameter in
> > cursor.execute.
>
> But that's what cursor.execute will do if you use its parameter-substitution
> mechanism--generate a string literal.
>
> > You still have to escape the escape character...
>
> Which will be done by cursor.execute if you use its parameter-substitution
> mechanism.
>
> > Calling the SQLString routine in this situation would be wrong because it
> > would escape characters such as newline which must not be escaped.
>
> SQLString will convert newlines into the \n sequence in the generated string
> literal, which MySQL will interpret as a newline. cursor.execute's
> parameter-substitution mechanism would do exactly the same thing.
But cursor.execute does not necessarily do parameter-substitution. It
can send the data directly to the database with no escaping. In this
case, doing it yourself is a massive pessimization, and you're more
likely to get it wrong than the driver writers
Ben
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>
>> In message <[EMAIL PROTECTED]>, Duncan Booth
>> wrote:
>>
>>> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>>>
def EscapeSQLWild(Str) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "%" or Ch == "_" :
Result.append("\\")
#end if
Result.append(Ch)
#end for
return "".join(Result)
#end EscapeSQLWild
>>>
>>> That doesn't quite work. If you want to stop wildcards being
>>> interpreted as such in a string used as a parameter to a query, then
>>> you have to escape the escape character as well.
>>
>> That's part of the separation of function. Note that the above
>> function does not generate a MySQL string literal: you must still put
>> it through the previously-defined SQLString routine, which will
>> automatically escape all the specials added by EscapeSQLWild.
>>
> You are still missing the point. I'm not talking about generating a MySQL
> string literal, I'm talking about preventing wildcards characters having
> their special meaning when using the string as a parameter in
> cursor.execute.
But that's what cursor.execute will do if you use its parameter-substitution
mechanism--generate a string literal.
> You still have to escape the escape character...
Which will be done by cursor.execute if you use its parameter-substitution
mechanism.
> Calling the SQLString routine in this situation would be wrong because it
> would escape characters such as newline which must not be escaped.
SQLString will convert newlines into the \n sequence in the generated string
literal, which MySQL will interpret as a newline. cursor.execute's
parameter-substitution mechanism would do exactly the same thing.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> In message <[EMAIL PROTECTED]>, Duncan Booth
> wrote:
>
>> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>>
>>> def EscapeSQLWild(Str) :
>>> """escapes MySQL pattern wildcards in Str."""
>>> Result = []
>>> for Ch in str(Str) :
>>> if Ch == "%" or Ch == "_" :
>>> Result.append("\\")
>>> #end if
>>> Result.append(Ch)
>>> #end for
>>> return "".join(Result)
>>> #end EscapeSQLWild
>>
>> That doesn't quite work. If you want to stop wildcards being
>> interpreted as such in a string used as a parameter to a query, then
>> you have to escape the escape character as well.
>
> That's part of the separation of function. Note that the above
> function does not generate a MySQL string literal: you must still put
> it through the previously-defined SQLString routine, which will
> automatically escape all the specials added by EscapeSQLWild.
>
You are still missing the point. I'm not talking about generating a MySQL
string literal, I'm talking about preventing wildcards characters having
their special meaning when using the string as a parameter in
cursor.execute. You still have to escape the escape character, and you have
to do that before or at the same time as you escape the wildcards. No
string literals are involved anywhere.
Calling the SQLString routine in this situation would be wrong because it
would escape characters such as newline which must not be escaped.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
> Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>
>> def EscapeSQLWild(Str) :
>> """escapes MySQL pattern wildcards in Str."""
>> Result = []
>> for Ch in str(Str) :
>> if Ch == "%" or Ch == "_" :
>> Result.append("\\")
>> #end if
>> Result.append(Ch)
>> #end for
>> return "".join(Result)
>> #end EscapeSQLWild
>
> That doesn't quite work. If you want to stop wildcards being interpreted
> as such in a string used as a parameter to a query, then you have to
> escape the escape character as well.
That's part of the separation of function. Note that the above function does
not generate a MySQL string literal: you must still put it through the
previously-defined SQLString routine, which will automatically escape all
the specials added by EscapeSQLWild.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
> def EscapeSQLWild(Str) :
> """escapes MySQL pattern wildcards in Str."""
> Result = []
> for Ch in str(Str) :
> if Ch == "%" or Ch == "_" :
> Result.append("\\")
> #end if
> Result.append(Ch)
> #end for
> return "".join(Result)
> #end EscapeSQLWild
That doesn't quite work. If you want to stop wildcards being interpreted as
such in a string used as a parameter to a query, then you have to escape
the escape character as well. In a LIKE clause, backslash percent matches a
percent character, but double backslash matches a single backslash and
double backslash percent matches a backslash followed by anything.
I think this version should work, (or rewrite it as a 'for' loop if you
prefer, though I think the replace version is clearer as well as being
between 3 and 222 times faster on the inputs I tried):
def EscapeSQLWild(s):
s = s.replace('\\', '')
s = s.replace('%', '\\%')
s = s.replace('_', '\\_')
return s
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, Steve > Holden wrote: [...] >>Sadly your assertions alone fail to convince. Perhaps you could provide >>a concrete example? > > > Sorry, that turned out to be wrong. You do in fact need to escape the > escapes on wildcards. Thank you. -- 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Steve Holden wrote: > Lawrence D'Oliveiro wrote: > >> In message <[EMAIL PROTECTED]>, Duncan Booth wrote: >> >>>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. It's the one I was talking about in this thread. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Steve Holden wrote: > Lawrence D'Oliveiro wrote: >> In message <[EMAIL PROTECTED]>, Steve >> Holden wrote: >> >> >>>Lawrence D'Oliveiro wrote: >>> In message <[EMAIL PROTECTED]>, 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? Sorry, that turned out to be wrong. You do in fact need to escape the escapes on wildcards. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Sybren Stuvel
wrote:
> 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)
Stuff this. Here are some new functions, separating the escaping of specials
from that of wildcards:
def SQLString(Str) :
"""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
#end if
Result.append(Ch)
#end for
return "\"" + "".join(Result) + "\""
#end SQLString
def EscapeSQLWild(Str) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "%" or Ch == "_" :
Result.append("\\")
#end if
Result.append(Ch)
#end for
return "".join(Result)
#end EscapeSQLWild
So to turn the result from EscapeSQLWild into a string, you still need to
wrap it in SQLString.
And here is the corresponding replacement for my previous QuoteSQLList
routine:
def SQLStringList(TheList) :
"""returns a MySQL list containing the items of TheList, suitable
for use in an "in" clause."""
return \
"(" + ", ".join([SQLString(Str) for Str in TheList]) + ")"
#end SQLStringList
So Tim Chase's example above now becomes:
cur.execute("select * from people where last_name in %s" %
SQLStringList(t))
Much simpler, isn't it?
And there are other, more dynamic cases where explicit quoting using
SQLString is easier than trying to make do with autoquoting.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Dennis Lee Bieber wrote: > So.. In the case of MySQLdb, one can modify the dictionary of quote > converters... Seems like one could create a "class" for "LIKE" wildcard > strings, rather than just pass plain strings, and add that "type" to the > list of quote converters. That way, one's custom quote function CAN work > through the regular auto-quote method: > > cr.execute(SQL, (LikeType(some_string_with_wildcards),) ) > > http://mail.python.org/pipermail/python-list/2003-October/189583.html > > I believe SQLite has a similar capability -- possibly other DB > adapters also have a means of attaching converters (in both directions). using the term "auto-quoting" for bound parameters isn't very portable, though. intelligent database engines don't even attempt to pass such data through the SQL parser. (does MySQL still do that, btw? or has that always been a PHP issue?) -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Sybren Stuvel wrote: > I didn't care anything about all that. I just found the way I wrote it > somewhat easier to read. absolutely. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
> "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
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, Steve > Holden wrote: > > >>Lawrence D'Oliveiro wrote: >> >>>In message <[EMAIL PROTECTED]>, 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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)
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/interactive/functions-matching.html Sybren -- Sybren Stüvel Stüvel IT - http://www.stuvel.eu/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote: > In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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? -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Steve Holden wrote: > Lawrence D'Oliveiro wrote: >> >> In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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" -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Jon Ribbens wrote: > In article <[EMAIL PROTECTED]>, 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? -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In article <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote:
>> 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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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? -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Duncan Booth wrote: > Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote: > >>> 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro <[EMAIL PROTECTED]> wrote: >> 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, Duncan Booth wrote:
> Sybren Stuvel <[EMAIL PROTECTED]> wrote:
>
>> 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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Sybren Stuvel <[EMAIL PROTECTED]> wrote:
> 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.
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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? -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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) -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > In message <[EMAIL PROTECTED]>, 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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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. -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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/ -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
In message <[EMAIL PROTECTED]>, 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?
--
http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
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 -- http://mail.python.org/mailman/listinfo/python-list
Re: QuoteSQL
Lawrence D'Oliveiro wrote: > Why doesn't MySQLdb provide a function like this: Because the author has read PEP 8? -- http://mail.python.org/mailman/listinfo/python-list
QuoteSQL
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
--
http://mail.python.org/mailman/listinfo/python-list
