python - sqlite3.OperationalError: near "": syntax error when trying to deliver a string f-CodePudding

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

By the way, 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 like supposed, but I really need to use a variable. As I can see, the problem is a whitespace, but I can't change the format of datetime.

Please help! Thanks!

CodePudding user response：

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