python - sqlite3.OperationalError: near "": syntax error when trying to deliver a string for comparison from a variable using fstring

Question

I'm tryng to do something like this..

con = sqlite3.connect('tables.db')
cur = con.cursor()

max_tyme = '2022-06-22 17:14:01.048'

cur.execute(f'''SELECT A.ID, B, A, tyme
                    FROM A, BD
                    WHERE A.ID = BD.ID
                    AND tyme > {max_tyme}''')

Resulting in this error: Traceback (most recent call last): File "d:\MEGA\test\test.py", line 11, in cur.execute(f'''SELECT A.ID, B, A, tyme sqlite3.OperationalError: near "17": syntax error

When I try..

con = sqlite3.connect('tables.db')
cur = con.cursor()

max_tyme = '2022-06-22 17:14:01.048'

cur.execute(f'''SELECT A.ID, B, A, tyme
                    FROM A, BD
                    WHERE A.ID = BD.ID
                    AND tyme > '2022-06-22 17:14:01.048' ''')

It works as it's supposed to, but I really need to use a variable. As I can see, the problem is whitespace, but I can't change the format of datetime.

Please help! Thanks!

score 0 · Accepted Answer · answered Jun 22 '22 at 18:27

This is possible, but not in the way you have implemented right now.

The first thing to get out of the way is to never, ever, ever use fstrings for database queries. This opens you up to a form of attack called SQL Injection (read more here)

So what's happening here?

Your current code using fstrings takes the value of the variable and stitches it into the string that is sent to the cur.execute function. This means that the string being sent to the database is:

'''SELECT A.ID, B, A, tyme
   FROM A, BD
   WHERE A.ID = BD.ID
   AND tyme > 2022-06-22 17:14:01.048'''

(note the absence of quotes around the time representation). This is then not recognised by the database and leads to the error you are seeing.

How do you fix it? By using input sanitisation. sqlite3 uses the '?' symbol to represent it's sanitised variables, so the code you would need to use is:

con = sqlite3.connect('tables.db')
cur = con.cursor()

max_tyme = '2022-06-22 17:14:01.048'

cur.execute(f'''SELECT A.ID, B, A, tyme
                    FROM A, BD
                    WHERE A.ID = BD.ID
                    AND tyme > ?''', (max_tyme,))

This should give the same output as when you made the db call manually

It helped, I don't have anough rep to cast a vote, so thanks a lot! — Vav1r, Jun 22 '22 at 18:33
You can mark the answer as accepted (see how to [here](https://meta.stackexchange.com/a/86979)) — isaa_ctaylor, Jun 22 '22 at 18:39

python - sqlite3.OperationalError: near "": syntax error when trying to deliver a string for comparison from a variable using fstring

1 Answers1