Lawrence D'Oliveiro <ld*@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.
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",
"x\\\\nx")
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(
"select name from pythontest where name like %s",
EscapeSQLWild("x\\%x"))
expected = (('x\\%x',),)
self.assertEqual(expected, self.cursor.fetchall())
if __name__=='__main__':
unittest.main()
---------------------------------------------
