Escaping placeholders for db.executesql in MySQL.

[chris_g]

Here's an example of a problem that I have with a MySQL database and escaping of executesql placeholders.
I am using web2py 1.99.7 on Ubuntu with Python2.5

testDAL works, but testDB throws this error:

<class 'gluon.contrib.pymysql.err.ProgrammingError'> (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1\\'')' at line 1")

Is there some additional escaping that I need to do for testDB ?

Thanks,
Chris Guest


SQL:
CREATE TABLE `test1` (   `ID` int(11) NOT NULL auto_increment,   `Text` varchar(5000) default NULL,   PRIMARY KEY  (`ID`) ) ENGINE=InnoDB;

model:

db.define_table(
    'test1',
    Field('Text', 'string'),
    )


controller:

textStr = '1'

def testDB():
    reprTextStr = repr(textStr)
    test1 = db.executesql("insert into test1 (t1) VALUES (%s)", placeholders =(reprTextStr,))
    return dict(success=1)
   
def testDAL():
    reprTextStr = repr(textStr)
    test1 = db.test1.insert(Text=reprTextStr)
    test1_id = db._adapter.lastrowid('test1')
    return dict(test1_id=test1_id)


SELECT * FROM test1
    -> ;
+----+----------+
| ID | Text     |
+----+----------+
|  2 | '1'      |
|  3 | '1'      |
|  4 | '1'      |
+----+----------+

[Massimo Di Pierro]

Please open a ticket about this. I can fix it later tonight or tomorrow.

[chris_g]

I opened the ticket as you suggested and you've confirmed that in executesql the values in the placeholders argument are passed directly to the driver without escaping.

I've tried doing the same call with the MSSQL adaptor using the pyodbc driver.
db.executesql("insert into test1 (t1) VALUES (?)", placeholders =("'1'",))

This time the values are escaped and all is well.
So I am still unclear if this is a web2py bug in the implementation of executesql or a difference in how the underlying drivers perform.
I also note that there was a change in drivers from MySQLdb to pymysql in web2py 1.90 . I will also try this testDB method with 1.89.1 .

In the short term, as I want to use executesql with both MSSQL and MySQL I will probably write a wrapper function like this:

def executesql(db, query, placeholders=None, as_dict=False):

if db._name=='mssql':

query = query.replace('%s', '?')

elif db._name=='mysql':

if placeholders is not None:

placeholders = mysql_escape(placeholders)

return db.executesql(query, placeholders, as_dict)

Is there a single mysql_escape function that I should be using from the pymysql driver or should I be writing my own?

[Massimo Di Pierro]

Please use web2py 2.0.9+ executesql was re-written.

[Brian M]

I think that this may be a pymysql issue, with the latest trunk the executesql statement fails when using the included pymysql driver but works fine using the mysqldb driver.

[chris_g]

Thanks Massimo. That worked for me.