主页 / coding / 问题 / 79599188
Accepted
hugo
Asked: 2025-04-30 04:50:34 +0800 CST

Python pg8000 查询参数...在“$2”处或附近出现语法错误

  • 772

你好...我正在尝试向查询添加一个参数，但出现此错误：

"errorMessage": "{'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near \”$2\"', 'P': '3793', 'F': 'scan.l', 'L': '1146', 'R': 'scanner_yyerror'}"

这有效：

import pg8000

account_id = 1234

sql = “”"
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    ORDER BY date DESC
“”"

cursor.execute(sql, (account_id,))

但事实并非如此：

import pg8000

account_id = 1234

start_date = query_string_params['start-date'] if 'start-date' in query_string_params else None

// start_date format is: '2025-02-04'

filters = “"
if start_date is not None:
   filters = filters + f" AND DATE(sample_date) >= '{start_date}'"

sql = “”"
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    %s
    ORDER BY date DESC
“”"

cursor.execute(sql, (account_id, filters))

知道我做错了什么吗？

python

1 个回答

  1. Best Answer

    SQL 参数必须是值，而不是 SQL 代码本身。过滤器是动态查询的一部分，而不是参数值。

    这是可行的，但可能会增加 SQL 注入攻击的风险，因为start_date输入不会被 pg 驱动程序转义。

    filters = “"
if start_date is not None:
   filters = filters + f" AND DATE(sample_date) >= '{start_date}'"

sql = f“”"
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    {filters}
    ORDER BY date DESC
“”"

    转换start_date为相同的格式并将sample_date过滤器写为

    params = (accountid,)
if start_date is not None:
   filters = filters + f" AND sample_date >= %s"
   params = (accountid, start_date)

sql = f"""
    SELECT *
    FROM samples
    WHERE account_id = %s
    AND delete_date IS NULL
    {filters}
    ORDER BY date DESC
"""

cursor.execute(sql, params)

    filters = filters + f" AND DATE(sample_date) >= %s"也可以工作，但DATE(sample_date)可能会根据检查的记录数量引入性能损失。

    • 2

相关问题