SQL UPDATE with multiple variables

Question

I'm working on a put request for a proof-of-concept at work. This project is my first time working with SQL since I got frustrated with it 10 years ago, so bear with me.

I want to update the POTS lines information for a particular store, so I'm having a request sent with data I'll parse and send an UPDATE query. I'm finding success when doing 1 individual entry at a time, but when adding multiple variables it gives me an error stating no column found by name: <name> where <name> is equal to the value I sent in the very next entry.

So take this statement:

cur.execute('UPDATE Pots SET Pots_ForwardOnFail={1}, Pots_FirePrimary={2}, Pots_FireSecondary={3}, Pots_BurglarPrimary={4}, Pots_DNR={5}, Pots_Passenger={6}, Pots_Freight={7} WHERE StoreNumber={0};'.format(args["Store"], args["HuntLine"], args["FirePrimary"], args["FireSecondary"], args["Burglar"], args["DNR"], args["PassengerElevator"], args["FreightElevator"]))

This sample post:

curl --request POST \
  --url http://127.0.0.1:9099/pots/42 \
  --form Store=42 \
  --form HuntLine=OnTheHunt \
  --form FirePrimary=FireHot \
  --form FireSecondary=FireNotSoHot \
  --form Burglar=Hamburglar \
  --form DNR=DNWut \
  --form PassengerElevator=GoingUp \
  --form FreightElevator=GoingDown

This would update the database column Pots_ForwardOnFail for store 42 with the value OnTheHunt, however I would be prompted with an error:

sqlite3.OperationalError: no such column: OnTheHunt

I've tried methods to no avail, and this just tells me that my syntax for handling multiple variables is not correct when working with UPDATE.

Answer 1

In this update query, string parameters are not enclosed in quotes. So, to DB these look like column names.

To validate , you can print the query before sending it to DB.

qry='UPDATE Pots SET Pots_ForwardOnFail={1}, Pots_FirePrimary={2}, Pots_FireSecondary={3}, Pots_BurglarPrimary={4}, Pots_DNR={5}, Pots_Passenger={6}, Pots_Freight={7} WHERE StoreNumber={0};'.format(args["Store"], args["HuntLine"], args["FirePrimary"], args["FireSecondary"], args["Burglar"], args["DNR"], args["PassengerElevator"], args["FreightElevator"])
print(qry)
cur.execute(qry)

This post has an example of how query should look like to DB. Escape single quote character for use in an SQLite query

Example of enclosing strings in query :

qry="UPDATE Pots SET Pots_ForwardOnFail='{1}', Pots_FirePrimary='{2}', WHERE StoreNumber={0};".format(args["Store"], args["HuntLine"])

Answer 2

Simple syntax error caused by a lack of delimiters around your test data, by the looks of it

UPDATE table SET HuntLine=OnTheHunt

Will try and set the value of column HuntLine equal to the value of column OnTheHunt. Your table doesn't have a column called OnTheHunt so query fails

UPDATE table SET HuntLine='OnTheHunt'

Will try and set the value of column HuntLine to the constant string 'OnTheHunt'

Ints would work, as they don't require a delimiter:

UPDATE table SET HuntLine=42

Most other data types tend to be passed into sql queries using strings

UPDATE table SET HuntDate='2018-01-01'

If huntdate is a date type then this string will usually be parsed and represented internally as a date. Some db require an additional keyword to trigger the parsing, like

UPDATE table SET HuntDate=DATE '2018-01-01'

Last tip, unrelated. Please have a good read through http://bobby-tables.com and absorb that advice, and then going forward never write python like this again. It's a massive security risk and I flat out wouldn't hire a developer that wrote code like this in an interview process test; I suspect many employers are the same

I'm hesitant to fix your query so it runs, because I wouldn't want to risk legitimising the way it's done, when it should be parameterised instead but..

Had you used double quotes for your string and single quotes around every curly bracket set where the column is a string:

qry="UPDATE Pots SET Pots_ForwardOnFail='{1}', Pots_FirePrimary='{2}'...

Then it would have worked out but it is still a massive security hole. Always parameterise your queries/never concatenate values supplied by the user into your query string.. in c# and sqlserver (what I know off by heart) parameterised queries look like this:

command.QueryText = "update pots set huntline=@hl, forwardonfail=@fof, fireprimary=@fp...";
command.Parameters.AddWithValue("@hl", request.Form["huntline"]);
command.Parameters.AddWithValue("@fp", request.Form["firepri"]);
command.Parameters.AddWithValue("@fof", request.Form["fwdonfail"]);

Not a million miles from what you have already but immune to users putting sql into the text boxes and wrecking your db/exposing its contents. Python and sqlite should have something similar