Как я должен параметризировать имена столбцов в pysqlite, чтобы избежать SQL-инъекций

Question

Я хочу, чтобы пользователь мог выбирать, какие результаты заказа отображаются, например, по возрасту), и я не хочу сортировать их после получения их из базы данных.

Очевидно, что если пользователь может указать ввод, который влияет на команды SQL, его необходимо очистить, и я обычно использую параметризацию, но pysqlite, похоже, игнорирует параметры для чего угодно, кроме значений.

Пример кода ниже показывает, что параметризация не работает для ORDER BYи также обходной путь, использующий форматирование строки, но это уязвимо для внедрения SQL.

Какое рекомендуемое решение позволяет пользователю вводить данные в порядок сортировки, не подвергая уязвимости SQL i? Нужно ли использовать форматирование строки и проверять каждый пользовательский ввод вручную?

#!/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()

Answer 1

Параметры SQL используются только для значений; все остальное может изменить смысл запроса. (Например, ORDER BY password мог оставить подсказки, как мог ORDER BY (SELECT ... FROM OtherTable ...).)

Чтобы убедиться, что имя столбца от клиента является действительным, вы можете использовать белый список:

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

Но все же плохая идея - интегрировать эту строку в запрос, потому что проверка и фактическая таблица могут не синхронизироваться или вы можете забыть проверку. Лучше вернуть индекс столбца от клиента, чтобы используемая вами строка всегда была вашей собственной, и любые ошибки можно было легко найти во время обычного тестирования:

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