python -sqlite3.erationalError：近距离“

发布于 2025-02-10 03:10:16 字数 795 浏览 3 评论 0原文

我正在尝试这样做。

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}''')

导致此错误： Trackback（最近的最新电话）：文件“ d：\ mega \ test \ test.py”，第11行，in cur.execute（f''select a.id，b，a，tyme sqlite3.erationalerror：接近“ 17”：

我尝试时的

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' ''')

语法错误。如我所见，问题是空格，但我无法更改日期时间的格式。

请帮忙！谢谢！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

葵雨 2025-02-17 03:10:16

这是可能的，但不是您现在实施的方式。

摆脱困境的第一件事是，永远不要将FStrings用于数据库查询。 This opens you up to a form of attack called SQL Injection (read more here)

So这里发生了什么？

您使用FSTRINGS的当前代码为变量的值，并将其缝合到发送到cur.execute函数的字符串中。 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).然后，数据库无法识别这并导致您看到的错误。

您如何修复它？通过使用输入疗法。 sqlite3使用“？” 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

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

回复收藏 0 原文

~没有更多了~