Can't update a value in sqlite3

Question

I have some data in an sqlite3 database, I made an input to be a command to update that data. for some reason when I run that command it does not work and gives me an sqlite3.OperationalError: no such column: "some lettres"

my function to update the data: (the generate_pwd() function works just fine and it has been tested)

    def update_query(self, wbname):
        """this function enables the user to update a query

        Args:
            wbname (str): the website name to change the pass
        """
        with conn:
            newpwd = go.generate_pwd()
            c.execute(f"UPDATE password_list SET password={newpwd} WHERE website_name={wbname}")
        pass

my elif statement to make the command:

elif command == "u":
    wbname = input("Enter the site name: ")
    co.update_query(wbname)

I hope it is clear enough, If you need any further data on my problem just type in the comments.

score 2 · Accepted Answer · answered Jan 01 '21 at 22:19

2

You can't use f-strings with SQL.

Use parametrized queries as explained in the docs instead:

c.execute(f"UPDATE password_list SET password=? WHERE website_name=?", (newpwd, wbname))

answered Jan 01 '21 at 22:19

AKX

152,115
15
115
172

Waiting for the seven minutes to pass so I can confirm your answer – eyadali05 Jan 01 '21 at 22:23
JFTR, "You can't use f-strings with SQL" is not quite right. You can do it. But "THOU SHALT NOT FORMAT PARAMETERS YOURSELVES" is true of course. – Matthias Jan 04 '21 at 21:32

Mike Scotty · Answer 2 · 2021-01-01T22:43:52.143

0

You need to wrap string values in the query within single quotes, otherwise they will be interpreted as column objects:

c.execute(f"UPDATE password_list SET password='{newpwd}' WHERE website_name='{wbname}'")

You might want to use a parameterized query instead of building a query string. That way your query is less vulnerable to SQL injections.

As parameters you can pass an interable object, e.g. a tuple or a list.

c.execute("UPDATE password_list SET password=? WHERE website_name=?", [newpwd, wbname])

You could also use named-style parameters:

c.execute("UPDATE password_list SET password=:pw WHERE website_name=:wbname", {"pw": newpwd, "wbname": wbname})

Another advantage of using parameterized queries is that you do not have to worry about wrapping strings in single quotes.

edited Jan 01 '21 at 22:43

answered Jan 01 '21 at 22:24

Mike Scotty

10,530
5
38
50

3

Never do this with SQL; it will be vulnerable to SQL injection attacks. – AKX Jan 01 '21 at 22:24
Not if you sanitize your inputs. This is nonethelesse a valid answer and it explains why OP gets the ``no such column`` exception. While "You can't use f-strings with SQL." is wrong. The f-string is evaluated before it's passed to the sql engine. – Mike Scotty Jan 01 '21 at 22:25
2

Obviously the input isn't sanizized and there is no reason to use something other than a parameterized version of the SQL statement here. Of course it's good to explain what was wrong, but the solution should show how it's done. Yes, I can show someone how to use a hammer to put a screw in a screw anchor, but it would be better if I teach them how to use a screwdriver. – Matthias Jan 01 '21 at 22:34
@MikeScotty Sanitization is not the right approach, correct quoting would be. But you can't generally know better how to quote a string than the SQL engine itself can. – AKX Jan 02 '21 at 17:52

Can't update a value in sqlite3

2 Answers2