Escape string within %

Question

I use PyMySQL to query from a MySQL database in python:

filter = "Pe"
connection = pymysql.connect(host="X", user="X", password="X", db="X", port=3306, cursorclass=pymysql.cursors.SSCursor)
cursor = connection.cursor()
sqlquery = "SELECT * FROM usertable WHERE name LIKE '%%s%'"
cursor.execute(sql, (filter))
response = cursor.fetchall()
connection.close()

This returns nothing. I could write:

sqlquery = "SELECT * FROM usertable WHERE name LIKE '%" + filter +"%'"

and execute: cursor.execute(sql), but then I lose the escaping, which makes the program vulnerable for injection attacks, right?

Is there way I could insert the value into the LIKE without losing the escape?

...WHERE name LIKE '%%%s%%'" does not work. I think %s adds ' on both sides of the replaced escaped string as a part of its function within PyMySQL.

You have to escape the special char %. Look here: http://stackoverflow.com/a/881208/6635287 — Radinator, Jul 28 '16 at 12:00

score 2 · Accepted Answer · answered Jul 28 '16 at 12:00

2

You need to pass the whole pattern as a query parameter, and use a tuple:

filter = "%Pe%"
sql = "SELECT * FROM usertable WHERE name LIKE %s"
cursor.execute(sql, (filter,))

answered Jul 28 '16 at 12:00

Eugene Yarmash

142,882
41
325
378

Thanks! Did not think of that. Solved it. – Brakebusk Jul 28 '16 at 12:10
What if there is another replacement in that line? "%(key)s id='%s'" % {'key': 'value'} – zalun Mar 03 '17 at 08:21

score 0 · Answer 2 · answered Jul 28 '16 at 11:56

0

Double the % you want to keep.

sqlquery = "SELECT * FROM usertable WHERE name LIKE '%%%s%%'"

answered Jul 28 '16 at 11:56

AdrienW

3,092
6
29
59

Does not work. I think %s adds ' on both sides of the replaced escaped string as a part of its function within PyMySQL. – Brakebusk Jul 28 '16 at 12:14

Escape string within %

2 Answers2