QuoteSQL

[Duncan Booth]

Lawrence D'Oliveiro <ldo at geek-central.gen.new_zealand> 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()
---------------------------------------------