mySQL Error from Python Code

Question

class Database(object):
 def __init__(self,ip_address,datetime_now):
 self.db_connec = mysql.connector.connect(user = DATABASE_USER, password = DATABASE_PASS, host = DATABASE_HOST, database = DATABASE)
 self.ip = ip_address
 self.datetime_now = datetime_now
def run_query(self, query):
 if db_connec == None:
 raise mysql.connector.DatabaseError
 return None
def log_threat(self):
 lol = "ass"
 self.cursor = self.db_connec.cursor()
 self.cursor.execute("INSERT INTO unauthorized_clients (ip_address, time) VALUES ({}, {})".format(self.ip, lol))
#INSERT INTO unauthorized_clients (ip_address, time) VALUES ("trtr", "test")

I'm calling the log_threat function and getting this error. When i run the query non-blind in a mysql terminal (navicat) it works fine but here i get this error .

check the manual corresponds to your MySQL server version for the right syntax to use near '0.1, ass)' at line 1

why is the ip being stripped?

when i log print(self.ip), i get 127.0.0.1

Answer 1

Presumably self.ip would be a string. When it is used to construct the query string the quotes are not inserted, so the resulting query would be:

>>> "INSERT INTO unauthorized_clients (ip_address, time) VALUES ({}, {})".format('127.0.0.1', 'ass')
'INSERT INTO unauthorized_clients (ip_address, time) VALUES (127.0.0.1, ass)'

notice that the string values have not been quoted.

Don't use string functions when creating queries as it can lead to SQL injection vulnerabilities, as well as the sort of error that you have here. Instead, use parameterised queries which will protect against SQL injection and properly quote the string values:

lol = 'ass'
self.ip = '127.0.0.1'
self.cursor.execute("INSERT INTO unauthorized_clients (ip_address, time) VALUES (%s, %s)", (self.ip, lol))

Here that the vaules are supplied in a tuple which is passed as the second argument to execute().

• Michael Presman: thank you :) I've got now to learn about parameterised queries :p as I want the proper and recommended solution. and the second solution with %s gave me a weird .decode socket error
• mhawke: @MichaelPresman: it's possible that I guessed the wrong placeholder. It could be ? instead of %s. You should be able to check with mysql.connector.paramstyle.
• Michael Presman: self.cursor.execute("INSERT INTO unauthorized_clients (ip_address, time) VALUES (?, ?)", (self.ip, lol)) -> not all parameters were used in the SQL statement. thanks anyway
• mhawke: @MichaelPresman: then the paramstyle is not ?, it's %s. Can you post the exact error that you see when you use %s?

Answer 2

Solution to my code, this fixed it. add quotes around {} -> '{}' as well as add self.db_connec.commit()

class Database(object):
 def __init__(self,ip_address,datetime_now, ):
 self.db_connec = mysql.connector.connect(user = DATABASE_USER, password = DATABASE_PASS, host = DATABASE_HOST, database = DATABASE)
 self.ip = ip_address
 self.datetime_now = datetime_now
 def run_query(self, query):
 if self.db_connec == None:
 raise mysql.connector.DatabaseError
 return None
 def log_threat(self):
 self.cursor = self.db_connec.cursor()
 print("Logging Threat... ")
 self.cursor.execute("INSERT INTO unauthorized_clients (ip_address, time) VALUES ('{}', '{}')".format(self.ip, self.datetime_now))
 self.db_connec.commit()
if __name__ == "__main__":
 client_connect()

• mhawke: I really encourage you to get the parameterised query working for the benefit of mitigating SQL injection vulnerabilities.