Improve SQL INSERT query to avoid sql injections

Question

I am using pymyql/mysql-connector to write the messages to mysql database. The messages are processed on callback (paho.mqtt callback) from mqtt broker.I have 4 different tables and based on the message type, I am inserting messages into database. I have written the insert queries as below. this way of writing leads to sql injections it seems.Any suggestions how can I improve the insert query statements?

# callback attached to paho.mqtt.client    
def on_message(self, client, userdata, msg):

    if  msg.topic.startswith("topic1/"):
        self.bulkpayload += "(" + msg.payload.decode("utf-8") + "," + datetime + "),"
    elif msg.topic.startswith("topic2/"):
        self.insertStatement += "INSERT INTO mydatabase.table1 VALUES (" + msg.payload.decode("utf-8") + "," + datetime + ");"
    elif msg.topic.startswith("topic3/")   
        self.insertStatement += "INSERT INTO mydatabase.table2 VALUES (" +msg.payload.decode("utf-8") + "," + datetime + ");"
    elif msg.topic.startswith("messages"):
        self.insertStatement += "INSERT INTO mydatabase.table3 VALUES ('" + msg.topic + "',"  + msg.payload.decode("utf-8") + "," + datetime + ");"
    else:
    return  # do not store in DB

    cursor.execute(self.insertStatement)
    cursor.commit()

Validate your user input before you send the query and use query parameters. — Dan Bracuk
– Dan Bracuk, Commented Mar 9, 2018 at 12:19
I'm not overly familiar with Python syntax but can you make your insert statement parameterised? That will help — scgough
– scgough, Commented Mar 9, 2018 at 12:20

scgough · Accepted Answer · 2018-03-09 12:23:25Z

4

Make your query use parameters. Much less chance of injection:

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

credit (and more info) here: How to use variables in SQL statement in Python?

Also, Dan Bracuk is correct - make sure you validate your params before executing the SQL if you aren't already

answered Mar 9, 2018 at 12:23

scgough

5,2723 gold badges33 silver badges56 bronze badges

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

5 Comments

Bindu Manjunath Over a year ago

Can I use like this cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (msg.payload.decode("utf-8"), var3))

scgough Over a year ago

I think so yes...I'm not an expert on Python syntax...give it a try anyway! You will be able to work out the best solution for you then. Note the example has three %s placeholders but you are only accounting for 2 of them.

Bindu Manjunath Over a year ago

msg.payload.decode("utf-8") holds the value for first two (%s,%s) placeholders. I am not sure if this will work. I will try. otherwise, I have to split the values contained in payload and map to placeholders. Thank you.

scgough Over a year ago

Ah ok so is it an array? Either way I'm pretty certain you will need to take the value/values and work them so that your SQL statement has a value for placeholder 1 and 2 separately.

ali reza Over a year ago

dev.mysql.com/doc/connector-python/en/… athenic.net/posts/2017/Jan/21/…

Collectives™ on Stack Overflow

Improve SQL INSERT query to avoid sql injections

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related