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?

Answer 1

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,))