SQLite3 и Python 3 - ошибка "нет такого столбца" со строками, но не с целыми числами

Question

Для этого проекта у меня есть файл базы данных с таблицей РЕЗУЛЬТАТЫ, который выглядит следующим образом:

conn.execute('''CREATE TABLE RESULTS(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age TEXT NOT NULL,
gender TEXT NOT NULL);''')

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

#setting up the user input variables
srcIn = input("what field do you want to search: id, name, age, or gender? ")
srcQuery = input("what result do you want to find from the "+ srcIn + " search? ")
if(srcIn == "age") or (srcIn == "id"):
    #by default input returns a string but it needs to be an int for age and id parameters
    srcQuery = int(srcQuery)
    print("converted to int")
srcOut = input("what field do you want to return: id, name, age, or gender? ")

print("making cursor")
cursor = conn.execute("SELECT %s FROM RESULTS WHERE %s = %s" % (srcOut, srcIn, srcQuery))
for row in cursor:
    print(srcOut + ": " + row[0])

conn.close()
print("closed database " + db)

Если бы я запустил это и передал ему age или id, число 45, и name, то я бы полностью возвратил имена, пол и другие характеристики из моей базы данных. Однако, когда я передаю его пол, женский или мужской и возвращаю имена, возраст или идентификатор, появляется сообщение об ошибке:

cursor = conn.execute("SELECT %s FROM RESULTS WHERE %s = %s" % (srcOut, srcIn, srcQuery))
sqlite3.OperationalError: no such column: male

Для этого конкретного примера:

srcOut = name
srcIn = gender
srcQuery = male

Я пытался заменить%s на? аргументы (я знаю, что он не будет работать с параметрами, которые я передаю в оператор SELECT), поэтому я в тупике.

Я думаю, что это как-то связано с тем, что в некоторых случаях я передаю ему целое число в поле srcQuery, а в других я передаю ему строку.

Любая помощь или предложения будут оценены.

Answer 1

Нет, вы не должны делать это так, как предлагал @Suever.

Представьте, какой запрос будет выполнен, если я предоставлю следующие значения:

srcOut = "name"
srcIn = "gender"
srcQuery = "' or 1 = 1 -- "

Ваш код будет фактически выполняться:

SELECT 
    name 
FROM 
    RESULTS 
WHERE  
    gender = '' or 1 = 1 -- '

Это будет соответствовать каждой строке в таблице.

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

Вместо форматирования строки вы должны параметризовать свой запрос. Параметризация запроса, тем не менее, не будет работать для имен таблиц и столбцов - их необходимо проверить перед вставкой в ​​запрос - поскольку количество возможных значений для scrIn а также srcOut очень ограничен - я бы просто выдавал ошибку, если вы видите неожиданное значение, например:

columns = {'id', 'name', 'age', 'gender'} 
srcIn = input("what field do you want to search: id, name, age, or gender? ")
if srcIn not in columns:
    raise ValueError("Invalid srcIn value")

Для srcQuery - этот вам нужно для параметризации:

query = "SELECT {column_out} FROM RESULTS WHERE {column_in} = ?".format(column_out=srcOut, column_in=scrIn)
cursor = conn.execute(query, (srcQuery, ))

Помимо того, что это намного безопаснее, это также устранит проблему размышлений о преобразованиях типов типа python-to-database и кавычках - драйвер базы данных будет обрабатывать это автоматически.