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",
"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()
---------------------------------------------
--
http://mail.python.org/mailman/listinfo/python-list
