Prevent sql injection in python using cursor.execute correctly
Today I did some searching about how to prevent sql injection while using Python and MySQLdb. Then I found some links with tips and also did some experiment and found something interesting.
Usually I used to write mysql query in python this way:
But it can't prevent sql injection. Check this example: (It will be better if you create a table named user_info with the fields: id, name, email and populate the table with some data.)
It will show all records from user_info table! Because the query
Now if you run the following code this won't happen:
It displays "no item found". It is secured as it lets the MySQLdb library to handle the necessary checking. Here is the format:
Do you have any more suggestion to prevent sql injection while coding in Python thus improving security?
Usually I used to write mysql query in python this way:
query = ".."
cursor.execute(query)
But it can't prevent sql injection. Check this example: (It will be better if you create a table named user_info with the fields: id, name, email and populate the table with some data.)
import MySQLdb
try:
conn = MySQLdb.connect (host = "localhost",
user = "uname",
passwd = "pass",
db = "mydb")
cursor = conn.cursor()
email = "' OR '1'='1"
query = "SELECT * FROM user_info WHERE email = '" + email + "'"
print query
cursor.execute(query)
if cursor.rowcount > 0:
print cursor.fetchall()
else:
print "no item found"
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
It will show all records from user_info table! Because the query
SELECT * FROM user_info WHERE email = '' OR '1'='1'
gets executed.Now if you run the following code this won't happen:
import MySQLdb
try:
conn = MySQLdb.connect (host = "localhost",
user = "uname",
passwd = "pass",
db = "mydb")
cursor = conn.cursor()
email = "' OR '1'='1"
cursor.execute("SELECT * FROM user_info WHERE email = %s", email)
if cursor.rowcount > 0:
print cursor.fetchall()
else:
print "no item found"
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
It displays "no item found". It is secured as it lets the MySQLdb library to handle the necessary checking. Here is the format:
cursor.execute("... %s ...%s", (param1, param2))
Do you have any more suggestion to prevent sql injection while coding in Python thus improving security?
Comments