[FIXED] sqlite error message when inserting users input into .db

Issue

When I run flask, I get the following error meassage.

sqlite3.OptionalError: near "years": syntax error

the error mesasge is placed in "cursor.execute(query1)"

However, I do not use the term "years" and I have searched online but I can not find any advice. Therefore, I do not know what I am doing wrong. Could please anyone give me an hint?

I tried to insert users unput into a table of my database.

my code is:

if request.method == "POST":
        b = request.form.get("bathtube")
        bath = request.form["bath"]
        di = request.form.get("dish")
        d = request.form["dishwasher"]
        wm = request.form.get("washing_machine")
        was = request.form["was"]
        wc = request.form["wc"]
        s = request.form["shower"]
        wa = request.form["water_use"]
        connection = sqlite3.connect('project.db')
        cursor = connection.cursor()



        query1 = "INSERT INTO water_use (user_id, bathtube, wc, washing_maschine, dishwasher,  wash_time, shower_time) VALUES ({user_id}, {bath}, {wc}, {wm}, {d}, {was}, {s}) WHERE user_id = :user_id".format(
            user_id=session["user_id"],
            bath=bath,
            wc=wc,
            wm=wm,
            d=d,
            was=was,
            s=s)

        cursor.execute(query1)
        connection.commit()

my table is:

CREATE TABLE IF NOT EXISTS "water_use" (
    "user_id"   int,
    "bathtube"  int,
    "shower"    int,
    "wc"    int,
    "washing_maschine"  int,
    "dishwasher"    int,
    "wash"  int,
    "timestamp" timestamp,
    "wash_time" int,
    "shower_time"   int,
    "total" int,
    CONSTRAINT "id" FOREIGN KEY("user_id") REFERENCES "users"("id"),
    PRIMARY KEY("user_id")
);

Solution

You should always use placeholders instead of string formatting to bind Python values to SQL statements, to avoid SQL injection attacks.

insert_statement = """
INSERT INTO water_use 
    (user_id, bathtube, wc, washing_maschine, dishwasher,  wash_time, shower_time)
VALUES 
    (?, ?, ?, ?, ?, ?, ?)
"""

values = (session["user_id"], bath, wc, wm, d, was, s)

cursor.execute(query1, values)

Answered By - Dmitry