4

This is my script using MySQLdb with python3.6

import MySQLdb

# start connection
db = MySQLdb.connect("localhost", "root", "asdf", "projecten")

# create cursor
c = db.cursor()

# insert multiple records using a tuple
cities = [
    ('Boston', 'MA', 600000),
    ('Chicago', 'IL', 2700000),
    ('Houston', 'TX', 2100000),
    ('Phoenix', 'AZ', 1500000)
]

# sql statement
sql = "INSERT INTO projecten.population(city, state, population) VALUES(%s, %s, %s)"

# insert data into table with list cities
c.executemany(sql, cities)

# commit changes
db.commit()

# close connection
db.close()

Is this safe against sql injections because some people use ? instead of %s but on python3.6 that is not working

Karl Knechtel
  • 62,466
  • 11
  • 102
  • 153
Zawadi
  • 43
  • 8

1 Answers1

2

As Bruno says in an answer to a related question:

To avoid injections, use execute with %s in place of each variable, then pass the value via a list or tuple as the second parameter of execute.

Following this advice, you can create your SQL like this:

sql ="""INSERT INTO projecten.population (city, state, population)
      VALUES (%s, %s, %s)"""

c.executemany(sql, cities)

This is a much safer approach than what you have now.

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
nagendra547
  • 5,672
  • 3
  • 29
  • 43
  • So my script is safe – Zawadi Aug 10 '17 at 16:36
  • Not entirely. I have updated my reply. Check again – nagendra547 Aug 10 '17 at 16:53
  • Isn't that the same that I already have in my code – Zawadi Aug 11 '17 at 00:05
  • I am using 3 double quotes. 3 Double quotes allow us to specify strings which spawn over multiple lines and contain special characters without the need to escape them. Basically anything between a pair of them is considered a string. These come in very handy when dealing with large inputs and also invoking commands over sql/ssh via python etc.. – nagendra547 Aug 11 '17 at 05:45