我应该如何在 pysqlite 中参数化列名以避免 SQL 注入 [英] How should I parameterize column names in pysqlite to avoid SQL Injection

问题描述

我希望用户能够选择显示的订单结果，例如按年龄)，我不想在从数据库中获取它们后对它们进行排序.

I want the user to be able to choose what order results are displayed e.g. by age), and I don't want to sort them after getting them from the database.

显然，如果用户能够指定影响 SQL 命令的输入，则需要对其进行清理，我通常会使用参数化，但 pysqlite 似乎忽略了除值之外的任何参数.

Obviously if the user is able to specify input that affects SQL commands, it needs to be sanitised, and I would normally use parameterisation, but pysqlite seems to ignore parameters for anything except values.

下面的示例代码显示参数化不适用于 ORDER BY，以及使用字符串格式的解决方法，但容易受到 SQL 注入.

Example code is below showing parameterisation not working for ORDER BY, and also a workaround using string formatting, but that is vulnerable to SQL injection.

允许用户输入影响排序顺序而不暴露 SQLi 漏洞的推荐解决方案是什么?我是否必须使用字符串格式并手动检查每个用户输入?

What is the recommended solution to allow user input to affect sort order without exposing SQLi vulnerabilities? Do I have to use string formatting and check every user input manually?

#!/user/bin/env python3

import sqlite3

con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.execute('CREATE TABLE test (name, age)')
cur.execute('INSERT INTO test VALUES (:name, :age)', {'name': 'Aaron', 'age': 75})
cur.execute('INSERT INTO test VALUES (:name, :age)', {'name': 'Zebedee', 'age': 5})

cur.execute('SELECT * FROM test ORDER BY age ASC')
results = cur.fetchall()
print('\nGood, but hard coded:\n', results)
# Good, but hard coded:
#  [('Zebedee', 5), ('Aaron', 75)]

cur.execute('SELECT * FROM test ORDER BY :order_by ASC', {'order_by': 'age'})
results = cur.fetchall()
print('\norder_by parameter ignored:\n', results)
# order_by parameter ignored:
#  [('Aaron', 75), ('Zebedee', 5)]

cur.execute('SELECT * FROM test ORDER BY {order_by} ASC'.format(order_by='age'))
results = cur.fetchall()
print('\nRight order, but vulnerable to SQL injection:\n', results)
# Right order, but vulnerable to SQL injection:
#  [('Zebedee', 5), ('Aaron', 75)]

con.close()

推荐答案

SQL 参数仅用于值；其他任何事情都可能改变查询的含义.(例如，ORDER BY password 可以留下提示，ORDER BY (SELECT ... FROM OtherTable ...) 也可以.)

SQL parameters are used only for values; anything else could change the meaning of the query. (For example, ORDER BY password could leave hints, as could ORDER BY (SELECT ... FROM OtherTable ...).)

为确保来自客户端的列名有效，您可以使用白名单:

To ensure that the column name from the client is valid, you could use a whitelist:

if order_by not in ['name', 'age']:
    raise ...
execute('... ORDER BY {}'.format(order_by))

但是将该字符串集成到查询中仍然是一个坏主意，因为验证和实际表可能会不同步，或者您可能会忘记检查.最好从客户端返回一个列索引，以便您使用的实际字符串始终是您自己的，并且在正常测试期间可以轻松发现任何错误:

But it is still a bad idea to integrate that string into the query, because the validation and the actual table could go out of sync, or you could forget the check. Better return a column index from the client, so that the actual string you're using is always your own, and any errors can be easily found during normal testing:

order_by = ['name', 'age'][order_index]
execute('... ORDER BY {}'.format(order_by))

这篇关于我应该如何在 pysqlite 中参数化列名以避免 SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！