CODEWITHSUNDEEP
pythonmysqlpython-3.xsql-injection
Zabs
Zabs

Reputation: 14142

Saving to database to prevent SQL injection in Python

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

Upvotes: 0

Views: 564

Answers (1)

Burhan Khalid
Burhan Khalid

Reputation: 174624

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.

Upvotes: 1

Related Questions