Saving to database to prevent SQL injection in Python

Question

I have a script that pulls data from a weather API & save this info into a MySQL database on localhost. I want to have the UPDATE script prevent any SQL injection but the following doesn't seem to run the UPDATE at all. There isnt an error just the query doesn't seem to have been executed when I check the database.

Can anyone suggest the problem? I am using the mysql.connector import/plugin

def save_to_database(self, uid):
    sql = "UPDATE weather_data " \
          "SET temperature=%s, temperature_feels=%s, humidity=%s, precipitation=%s, weather_status=%s " \
          "WHERE UID =%s"

    temperature = self.weather_data['temperature']
    temperature_feels = self.weather_data['temperature_feels']
    humidity = self.weather_data['humidity']
    precipitation = self.weather_data['precipitation']
    weather_status = self.weather_data['type']

    print(sql)
    c = self._db.cursor()
    c.execute(sql, (temperature, temperature_feels, humidity, precipitation, weather_status, uid))

UPDATE

The following works fine - but isn't 'safe'

def save_weather_forecast(self, uid):
    print(self.weather_data);
    sql = "UPDATE weather_data SET temperature = "+ str(self.weather_data['temperature']) + ", " \
            +"temperature_feels = "+ str(self.weather_data['temperature_feels']) +", " \
            +"humidity = "+ str(self.weather_data['humidity']) +", " \
            +"weather_status = '"+ str(self.weather_data['type']) +"', " \
            +"precipitation = "+ str(self.weather_data['precipitation']) +"" \
            +" WHERE UID = '"+ str(uid) +"'"

    print(sql)
    c = self._db.cursor()
    c.execute(sql)
    c.close()

Seems like you're not commiting the data.

mata
– mata

2016-01-07 10:29:19 +00:00
Commented Jan 7, 2016 at 10:29 — mata
– mata, Commented Jan 7, 2016 at 10:29

Burhan Khalid · Accepted Answer · 2016-01-07 10:57:49Z

1

The Python DB API standard explicitly turns off auto commit which means you have to commit any transactions manually otherwise they are not effected at the database.

Committing is done at connection, so you need to add:

self._db.commit()

After the c.execute() line.

edited Jan 7, 2016 at 10:57

answered Jan 7, 2016 at 10:33

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

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

1 Comment

Zabs Over a year ago

Thanks although this just gives me an attributeError: 'MySQLConnection' object has no attribute 'connection' - good call though

Collectives™ on Stack Overflow

Saving to database to prevent SQL injection in Python

UPDATE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related