Re: QuoteSQL
Lawrence D'Oliveiro <ldo@geek-central.gen.new _zealandwrote:
>
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.
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.
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_escapeback slashwild 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(S tr) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "%" or Ch == "_" :
Result.append(" \\")
#end if
Result.append(C h)
#end for
return "".join(Res ult)
#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.executem any(
"insert into pythontest(name ) values(%s)",
self.values)
def tearDown(self):
self.cursor.exe cute("drop table pythontest")
def test_wildcard(s elf):
n = self.cursor.exe cute(
"select name from pythontest where name like %s",
"x%x")
self.assertEqua l(n, 5)
def test_nonwildcar d(self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\\%x")
expected = (('x%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_newline(se lf):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\nx")
expected = (('x\nx',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_backslashn (self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\\\\nx")
expected = (('x\\nx',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_backslashp ercent(self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\\\\\\%x" )
expected = (('x\\%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_escapewild (self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
EscapeSQLWild(" x%x"))
expected = (('x%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_escapeback slashwild(self) :
self.cursor.exe cute(
"select name from pythontest where name like %s",
EscapeSQLWild(" x\\%x"))
expected = (('x\\%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
if __name__=='__ma in__':
unittest.main()
---------------------------------------------
Lawrence D'Oliveiro <ldo@geek-central.gen.new _zealandwrote:
>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.
>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.
Other database adaptors may handle parameters without generating string
literals.
>
>
Which will be done by cursor.execute if you use its
parameter-substitution mechanism.
>You still have to escape the escape character...
Which will be done by cursor.execute if you use its
parameter-substitution mechanism.
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.
>
>
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.
>
>Calling the SQLString routine in this situation would be wrong
>because it would escape characters such as newline which must not be
>escaped.
>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.
>
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_escapeback slashwild 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(S tr) :
"""escapes MySQL pattern wildcards in Str."""
Result = []
for Ch in str(Str) :
if Ch == "%" or Ch == "_" :
Result.append(" \\")
#end if
Result.append(C h)
#end for
return "".join(Res ult)
#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.executem any(
"insert into pythontest(name ) values(%s)",
self.values)
def tearDown(self):
self.cursor.exe cute("drop table pythontest")
def test_wildcard(s elf):
n = self.cursor.exe cute(
"select name from pythontest where name like %s",
"x%x")
self.assertEqua l(n, 5)
def test_nonwildcar d(self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\\%x")
expected = (('x%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_newline(se lf):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\nx")
expected = (('x\nx',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_backslashn (self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\\\\nx")
expected = (('x\\nx',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_backslashp ercent(self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
"x\\\\\\%x" )
expected = (('x\\%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_escapewild (self):
self.cursor.exe cute(
"select name from pythontest where name like %s",
EscapeSQLWild(" x%x"))
expected = (('x%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
def test_escapeback slashwild(self) :
self.cursor.exe cute(
"select name from pythontest where name like %s",
EscapeSQLWild(" x\\%x"))
expected = (('x\\%x',),)
self.assertEqua l(expected, self.cursor.fet chall())
if __name__=='__ma in__':
unittest.main()
---------------------------------------------
Comment