How can I escape the input to a MySQL db in Python3?
I'm using PyMySQL and works fine, but when I try to do something like:
cursor.execute("SELECT * FROM `Codes` WHERE `ShortCode` = '{}'".format(request[1]))
it won't work if the string has ' or ". I also tried:
cursor.execute("SELECT * FROM `Codes` WHERE `ShortCode` = %s",request[1])
The problem with this is that the library (PyMySQL) uses the formatting syntax for Python2.x, %, that doesn't work anymore.
I also found this possible solution
conn.escape_string()
in here, but I don't know where to add this code.
This is all I got:
import pymysql
import sys
conn = pymysql.connect( host = "localhost",
user = "test",
passwd = "",
db = "test")
cursor = conn.cursor()
cursor.execute("SELECT * FROM `Codes` WHERE `ShortCode` = {}".format(request[1]))
result = cursor.fetchall()
cursor.close()
conn.close()
Edit: I solved it! In PyMySQL the right way is like this:
import pymysql
import sys
conn = pymysql.connect(host="localhost",
user="test",
passwd="",
db="test")
cursor = conn.cursor()
text = conn.escape(request[1])
cursor.execute("SELECT * FROM `Codes` WHERE `ShortCode` = {}".format(text))
cursor.close()
conn.close()
Where the text = conn.escape(request[1]) line is what escapes the code. Found it inside PyMySQL code. There, request[1] is the input.
解决方案
Solved. In PyMySQL the right way is like this:
import pymysql
import sys
conn = pymysql.connect(host="localhost",
user="test",
passwd="",
db="test")
cursor = conn.cursor()
text = conn.escape(request[1])
cursor.execute("SELECT * FROM `Codes` WHERE `ShortCode` = {}".format(text))
cursor.close()
conn.close()
Where the text = conn.escape(request[1]) line is what escapes the code. Found it inside PyMySQL code. There, request[1] is the input.