Inserting to Mariadb from multiple scripts

Question

So currently I have 1 script inserting sensor data into my weather database every hour using python.

I have now added a second script to add rainfall data into the same table also every hour.

Now the problem: When the 2nd script inserts, all other values get 'zeroed'. As displayed in grafana.

am I overwriting somewhere or, if someone could point me in the right direction

Weather sensors insert statement

 sql=("INSERT INTO WEATHER_MEASUREMENT (AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY) VALUES ({},{},{})".format(temperature,pressure,humidity))
         mycursor.execute(sql)
         weatherdb.commit()

Rainfall sensors insert

sql=("INSERT INTO WEATHER_MEASUREMENT (RAINFALL) VALUES ({})".format(rainfall))
mycursor.execute(sql)
weatherdb.commit()

This seems to feature a SQL injection vulnerability.

Victor Stafusa
– Victor Stafusa

2023-04-20 04:49:29 +00:00
Commented Apr 20, 2023 at 4:49 — Victor Stafusa
– Victor Stafusa, Commented Apr 20, 2023 at 4:49

Mikhail Ilin · Accepted Answer · 2023-04-20 21:10:31Z

1

Tell me if I understand it right: Your table “WEATHER_MEASUREMENT” has 4 columns (apart ID): AMBIENT_TEMPERATURE, AIR_PRESSURE, HUMIDITY and RAINFALL. When you add RAINFALL value it creates a new row in your table with other column values at “NULL” and this is the problem? If this is the case, you probably want to update existing row with a query like:

sql = ("""
          UPDATE _ WEATHER_MEASUREMENT
          SET RAINFALL = "{}"
          WHERE id_of_the_row = {}
          """.format(rainfall, id)
mycursor.execute(sql)

You will need to find a way to figure out the ID of the row you just created with your Weather sensor insert statement (maybe search for last inserted row if you are sure of timings).

/!\NOTE/!\ As mentioned in comments by victor-stafusa-bozonacadeia, sql request made with formated string (as provided in my example) is vulnerable to SQL injection. You can safely use it only if variables used for formating are coming from your code and are known to be safe. Do not use string formating if variable content comes from outside, like user input.

If there is a chance that query content comes from unsure source, one should "sanitize query". The closest way to a raw SQL request that allows to sanitize request that i found is using sqlalchemy module with "text" query and "bind parameters". You can learn more about here : https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.text

edited Apr 20, 2023 at 21:10

answered Oct 23, 2020 at 10:36

Mikhail Ilin

2052 silver badges9 bronze badges

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

5 Comments

DanF Over a year ago

yes you are correct. So upon inserting rainfall after the other script, other values are 'zero' or 'null' not sure on the ID setup, however what you've said looks like what i need to do

Mikhail Ilin Over a year ago

If you are sure that your Rainfall sensors query comes right after Weather sensors query, you can execute following request to get the id: sql = ("""SELECT id FROM WEATHER_MEASUREMENT ORDER BY id DESC """) mycursor.execute(sql) last_id = mycursor.fetchall()[0] You just need to check what is the exact label of “id” column and you will be able to get the id of last inserted row like this. After that you update the row with that ID with your Rainfall data.

DanF Over a year ago

Ah, perfect thankyou. See i wouldn't know to do this, still relatively new to mariadb. I will give this a go, thanks!

Mikhail Ilin Over a year ago

@DanF with pleasure : )

Victor Stafusa Over a year ago

This seems to feature a SQL injection vulnerability.

Collectives™ on Stack Overflow

Inserting to Mariadb from multiple scripts

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related