PostgreSQL使用psycopg实现动态ORDER BY失效问题求助

问题描述 #

我正在测试PostgreSQL与psycopg的组合使用，现有代码操作名为cars的表（包含brand、model、year、price、id字段），尝试通过输入参数k动态指定ORDER BY的排序字段，但输入price时，查询结果并未按price降序排列，手动替换k为'price'也无效。

代码示例 #

import psycopg

k = input("")

conn = psycopg.connect(
    dbname="Testing",
    user="postgres",
    password="my_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

cur.execute("SELECT * FROM cars ORDER BY %s DESC;", (k,))  # Corrected the query here

rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

输入price后的输出结果 #

price
('bmw', 'X5', 2000, 1000, 1)
('lada', 'granta', 2020, 1000, 2)
('Audi', 'A8', 2030, 200000, 3)
('audi', 'A6', 2008, 200000, 4)
('a', 'b', 2000, 2000, 5)
('mercedes', 'c-klasse', 2019, 20000, 6)
('mercedes', 'c-klasse', 2019, 20000, 7)
('Audi', 'Q7', 2020, 300000, 8)

原因分析 #

问题核心在于psycopg参数化查询的作用对象：参数化占位符%s会把传入的k当作字符串字面量处理，而非SQL标识符（字段名）。实际执行的SQL是按字符串常量'price'排序，所有行的这个“值”都相同，自然不会按price字段的数值排序。

解决方案 #

方法1：使用psycopg.sql模块构造合法标识符（推荐） #

这是psycopg官方推荐的方式，既能正确识别字段名，又能避免SQL注入风险：

import psycopg
from psycopg import sql

k = input("")

conn = psycopg.connect(
    dbname="Testing",
    user="postgres",
    password="my_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

# 用sql.Identifier包装字段名，再拼接SQL语句
cur.execute(sql.SQL("SELECT * FROM cars ORDER BY {} DESC;").format(sql.Identifier(k)))

rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

方法2：白名单校验+字符串拼接（需谨慎） #

如果不想引入psycopg.sql，可以先对输入字段做白名单校验，确保仅合法字段被使用，再拼接SQL：

import psycopg

k = input("")
# 定义允许排序的字段白名单
allowed_fields = {'brand', 'model', 'year', 'price', 'id'}

if k not in allowed_fields:
    print("非法的排序字段")
    exit()

conn = psycopg.connect(
    dbname="Testing",
    user="postgres",
    password="my_password",
    host="localhost",
    port="5432"
)

cur = conn.cursor()

# 校验通过后拼接SQL
cur.execute(f"SELECT * FROM cars ORDER BY {k} DESC;")

rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

注意事项 #

• 禁止直接将未校验的用户输入拼接到SQL语句中，会引发严重的SQL注入漏洞。
• 优先选择方法1，这是最安全、符合psycopg设计规范的实现方式。

内容的提问来源于stack exchange，提问作者MrAdvocat2019