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

mhawke · Accepted Answer · 2017-11-29 05:33:11Z

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().

edited Nov 29, 2017 at 5:33

answered Nov 29, 2017 at 5:27

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Michael Presman Over a year ago

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 Over a year ago

@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 Over a year ago

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 Over a year ago

@MichaelPresman: then the paramstyle is not ?, it's %s. Can you post the exact error that you see when you use %s?

Michael Presman · Accepted Answer · 2017-11-29 06:24:03Z

0

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

answered Nov 29, 2017 at 6:24

Michael Presman

258 bronze badges

1 Comment

mhawke Over a year ago

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

Collectives™ on Stack Overflow

mySQL Error from Python Code

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related