在SQLAlchemy自定义查询中使用order by作为参数失败

问题根源 #

SQLAlchemy的参数绑定机制是为数据值设计的，会自动给字符串类型的参数添加单引号。但ORDER BY后的列名、排序方向（ASC/DESC）属于SQL语法标识符，不是数据值，用普通绑定会被转义成带引号的字符串，导致生成order by 'column1' 'asc'这种非法SQL。

解决方案1：安全拼接SQL（推荐，需做输入校验） #

先对排序字段和方向做合法性校验，再直接拼接进SQL语句，既能避免语法错误，又能防止SQL注入：

from sqlalchemy import text

# 定义允许的排序字段和方向，限制输入范围
ALLOWED_COLUMNS = {"column1", "column2", "column3"}
ALLOWED_SORTS = {"ASC", "DESC"}

# 校验并设置默认值
target_column = variable2 if variable2 in ALLOWED_COLUMNS else "column1"
sort_direction = variable3.upper() if variable3.upper() in ALLOWED_SORTS else "ASC"

# 构建带排序的SQL
sql = text(f"""
    select t.column1, t.column2
    from table1 t
    where t.column3 = :which_column
    order by {target_column} {sort_direction}
""")

sql_params = {"which_column": variable}
sql_response = session.execute(sql, sql_params)

解决方案2：使用literal_column和text处理语法标识符 #

如果不想拼接SQL，可以通过bindparam的expression参数，用literal_column处理列名，text处理排序方向，让SQLAlchemy把它们解析为SQL语法元素而非字符串值：

from sqlalchemy import text, literal_column, bindparam

# 同样要做输入校验，避免注入风险
ALLOWED_COLUMNS = {"column1", "column2", "column3"}
ALLOWED_SORTS = {"ASC", "DESC"}

target_column = variable2 if variable2 in ALLOWED_COLUMNS else "column1"
sort_direction = variable3.upper() if variable3.upper() in ALLOWED_SORTS else "ASC"

sql = text("""
    select t.column1, t.column2
    from table1 t
    where t.column3 = :which_column
    order by :order_by :sort
""")

# 绑定参数时指定表达式类型
sql_query = sql.bindparams(
    bindparam("which_column"),
    bindparam("order_by", expression=literal_column(target_column)),
    bindparam("sort", expression=text(sort_direction))
)

sql_params = {"which_column": variable}
sql_response = session.execute(sql_query, sql_params)

关键注意事项 #

• 必须做输入校验：无论是拼接还是用表达式绑定，都要限制排序字段和方向的可选范围，防止恶意输入引发SQL注入。
• 不要直接使用未校验的用户输入作为排序参数，即便用literal_column也可能存在注入风险。

内容的提问来源于stack exchange，提问作者Web-GIS entrepreneur