Как вы избегаете строк для имен таблицы/столбцов SQLite в Python?

Стандартным подходом к использованию значений переменных в запросах SQLite является "стиль метки вопроса", например:

import sqlite3
with sqlite3.connect(":memory:") as connection:
    connection.execute("CREATE TABLE foo(bar)")
    connection.execute("INSERT INTO foo(bar) VALUES (?)", ("cow",))

    print(list(connection.execute("SELECT * from foo")))
    # prints [(u'cow',)]

Однако это работает только для подстановки значений в запросы. Он не работает при именах таблиц или столбцов:

import sqlite3
with sqlite3.connect(":memory:") as connection:
    connection.execute("CREATE TABLE foo(?)", ("bar",))
    # raises sqlite3.OperationalError: near "?": syntax error

Ни в модуле sqlite3, ни PEP 249 не упоминается функция для экранирования имен или значений. Предположительно, это препятствует пользователям собирать свои запросы со строками, но это оставляет меня в недоумении.

Какая функция или метод наиболее подходят для использования имен переменных для столбцов или таблиц в SQLite? Я бы предпочел сделать это без каких-либо других зависимостей, так как я буду использовать его в своей собственной оболочке.

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

SQLite использует " для цитирования идентификаторов, но я не уверен, что достаточно просто избежать их. Документация по функциям PHP sqlite_escape_string предполагает, что некоторые двоичные данные также могут быть экранированы, но это может быть причудой библиотеки PHP.

Ответ 1

Чтобы преобразовать любую строку в идентификатор SQLite:

Убедитесь, что строка может быть закодирована как UTF-8.
Убедитесь, что строка не содержит никаких символов NUL.
Замените все " на "".
Оберните все в двойные кавычки.

Реализация

import codecs

def quote_identifier(s, errors="strict"):
    encodable = s.encode("utf-8", errors).decode("utf-8")

    nul_index = encodable.find("\x00")

    if nul_index >= 0:
        error = UnicodeEncodeError("NUL-terminated utf-8", encodable,
                                   nul_index, nul_index + 1, "NUL not allowed")
        error_handler = codecs.lookup_error(errors)
        replacement, _ = error_handler(error)
        encodable = encodable.replace("\x00", replacement)

    return "\"" + encodable.replace("\"", "\"\"") + "\""

Учитывая строковый одиночный аргумент, он будет убегать и правильно указывать его или возбуждать исключение. Второй аргумент может использоваться для указания любого обработчика ошибок, зарегистрированного в модуле codecs. Встроенные:

'strict': создать исключение в случае ошибки кодирования

'replace': замените неверные данные подходящим маркером замещения, например '?' или '\ufffd'

'ignore': игнорировать неверные данные и продолжать без дальнейшего уведомления

'xmlcharrefreplace': замените соответствующую ссылку на символ XML (только для кодирования)

'backslashreplace': заменить исполняемые escape-последовательности (только для кодирования)

Это не проверяет зарезервированные идентификаторы, поэтому, если вы попытаетесь создать новую таблицу SQLITE_MASTER, это не остановит вас.

Пример использования

import sqlite3

def test_identifier(identifier):
    "Tests an identifier to ensure it handled properly."

    with sqlite3.connect(":memory:") as c:
        c.execute("CREATE TABLE " + quote_identifier(identifier) + " (foo)")
        assert identifier == c.execute("SELECT name FROM SQLITE_MASTER").fetchone()[0]

test_identifier("'Héllo?'\\\n\r\t\"Hello!\" -☃") # works
test_identifier("北方话") # works
test_identifier(chr(0x20000)) # works

print(quote_identifier("Fo\x00o!", "replace")) # prints "Fo?o!"
print(quote_identifier("Fo\x00o!", "ignore")) # prints "Foo!"
print(quote_identifier("Fo\x00o!")) # raises UnicodeEncodeError
print(quote_identifier(chr(0xD800))) # raises UnicodeEncodeError

Наблюдения и ссылки

Идентификаторы SQLite TEXT, а не бинарные.
- SQLITE_MASTER схема в FAQ
- Python 2 SQLite API кричал на меня, когда я дал ему байты, он не мог декодировать как текст.
- Python 3 SQLite API требует запросов str s, а не bytes.

Идентификаторы SQLite цитируются с использованием двойных кавычек.
- SQL как понимается SQLite

Двойные кавычки в идентификаторах SQLite экранируются как две двойные кавычки.

Идентификаторы SQLite сохраняют регистр, но они не учитывают регистр букв ASCII. Можно включить unicode-распознающую нечувствительность к регистру.
- SQLite FAQ Вопрос № 18

SQLite не поддерживает символ NUL в строках или идентификаторах.
- SQLite Ticket 57c971fc74

sqlite3 может обрабатывать любую другую строку юникода, если он может быть правильно закодирован в UTF-8. Неверные строки могут привести к сбоям между Python 3.0 и Python 3.1.2 или около того. Python 2 принял эти недопустимые строки, но это считается ошибкой.
- Проблема с Python # 12569
- Modules/_sqlite/cursor.c
- Я протестировал его в связке.

Ответ 2

Документация psycopg2 явно рекомендует использовать обычное форматирование python% или {} для замены в именах таблиц и столбцов (или других битов динамического синтаксиса), а затем используя механизм параметров для подстановки значений в запрос.

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

Это сводится к тому, что означает "безопасный". Обычная мудрость заключается в том, что использование обычной манипуляции с строкой python для ввода значений в ваши запросы не является "безопасным". Это связано с тем, что все, что может пойти не так, если вы это сделаете, и такие данные очень часто поступают от пользователя и не находятся под вашим контролем. Вам нужен 100% надежный способ избежать этих значений должным образом, чтобы пользователь не мог внедрить SQL в значение данных и выполнить его базу данных. Таким образом, авторы библиотек выполняют эту работу; вы никогда не должны.

Если, однако, вы пишете общий вспомогательный код для работы с вещами в базах данных, эти соображения не применяются так сильно. Вы неявно предоставляете любому, кто может называть такой код доступ ко всему в базе данных; , что точка хелперного кода. Поэтому сейчас проблема безопасности заключается в том, чтобы данные, созданные пользователем, никогда не использовались в таком коде. Это общая проблема безопасности при кодировании, и это та же проблема, что и слепое exec ввод строки ввода пользователем. Это отличная проблема от вставки значений в ваши запросы, потому что вы хотите, чтобы иметь возможность безопасно обрабатывать введенные пользователем данные.

Итак, моя рекомендация: делайте то, что хотите динамически собирать свои запросы. Используйте обычную строку шаблона python для добавления в имена таблиц и столбцов, приклейте туда, где клаузулы и соединения, все хорошее (и ужасное для отладки). Но убедитесь, что вы знаете, что любые значения, которые затрагивает такой код, должны исходить от вас, а не от ваших пользователей [1]. Затем вы используете функциональные возможности замены SQLite, чтобы безопасно вставлять введенные пользователем значения в ваши запросы как значения.

[1] Если (как и в случае с большим количеством кода, который я пишу), ваши пользователи - это люди, которые в любом случае имеют полный доступ к базам данных, а код - для упрощения их работы, тогда это соображение действительно не применяется; вы, вероятно, собираете запросы в пользовательских таблицах. Но вы все равно должны использовать замену параметров SQLite, чтобы спасти себя от неизбежной подлинной ценности, которая в конечном итоге содержит цитаты или знаки процента.

Ответ 3

Если вы совершенно уверены, что вам нужно указывать имена столбцов динамически, вы должны использовать библиотеку, которая может сделать это безопасно (и жалуется на то, что не так). SQLAlchemy очень хорош в этом.

>>> import sqlalchemy
>>> from sqlalchemy import *
>>> metadata = MetaData()
>>> dynamic_column = "cow"
>>> foo_table = Table('foo', metadata,
...     Column(dynamic_column, Integer))
>>>

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

>>> metadata.bind = create_engine('sqlite:///:memory:', echo=True)

Затем вы можете выдать CREATE TABLE .... с echo=True, sqlalchemy будет записывать сгенерированный sql, но в целом sqlalchemy делает все возможное, чтобы сгенерировать sql из ваших рук (чтобы вы не использовали его для злых целей).

>>> foo_table.create()
2011-06-28 21:54:54,040 INFO sqlalchemy.engine.base.Engine.0x...2f4c 
CREATE TABLE foo (
    cow INTEGER
)
2011-06-28 21:54:54,040 INFO sqlalchemy.engine.base.Engine.0x...2f4c ()
2011-06-28 21:54:54,041 INFO sqlalchemy.engine.base.Engine.0x...2f4c COMMIT
>>>

и да, sqlalchemy позаботится о любых именах столбцов, которые нуждаются в специальной обработке, например, когда имя столбца является зарезервированным словом sql

>>> dynamic_column = "order"
>>> metadata = MetaData()
>>> foo_table = Table('foo', metadata,
...     Column(dynamic_column, Integer))
>>> metadata.bind = create_engine('sqlite:///:memory:', echo=True)
>>> foo_table.create()
2011-06-28 22:00:56,267 INFO sqlalchemy.engine.base.Engine.0x...aa8c 
CREATE TABLE foo (
    "order" INTEGER
)
2011-06-28 22:00:56,267 INFO sqlalchemy.engine.base.Engine.0x...aa8c ()
2011-06-28 22:00:56,268 INFO sqlalchemy.engine.base.Engine.0x...aa8c COMMIT
>>>

и может спасти вас от возможной неудачи:

>>> dynamic_column = "); drop table users; -- the evil bobby tables!"
>>> metadata = MetaData()
>>> foo_table = Table('foo', metadata,
...     Column(dynamic_column, Integer))
>>> metadata.bind = create_engine('sqlite:///:memory:', echo=True)
>>> foo_table.create()
2011-06-28 22:04:22,051 INFO sqlalchemy.engine.base.Engine.0x...05ec 
CREATE TABLE foo (
    "); drop table users; -- the evil bobby tables!" INTEGER
)
2011-06-28 22:04:22,051 INFO sqlalchemy.engine.base.Engine.0x...05ec ()
2011-06-28 22:04:22,051 INFO sqlalchemy.engine.base.Engine.0x...05ec COMMIT
>>>

(по-видимому, некоторые странные вещи являются совершенно законными идентификаторами в sqlite)

Ответ 4

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

Причина в том, что вы либо должны:

принять/отклонить потенциальное имя таблицы/столбца, т.е. не гарантируется, что строка является допустимым именем столбца/таблицы, в отличие от строки, которая должна храниться в некоторой базе данных; или,
дезинформировать строку, которая будет иметь тот же эффект, что и создание дайджеста: используемая функция surjective, а не bijective (опять же, инверсия верна для строки, которая должна храниться в некоторой базе данных); так что вы не только не можете быть уверены в том, чтобы перейти от дезинфицированного имени к первоначальному имени, но вы рискуете непреднамеренно попытаться создать два столбца или таблицы с тем же именем.

Поняв, что вторая вещь, которую нужно понять, заключается в том, что то, как вы закончите "экранирование" имен таблиц/столбцов, зависит от вашего конкретного контекста, и поэтому существует более чем один способ сделать это, но каким бы то ни было способом, вам нужно будет выкопать, чтобы точно определить, что является или не является допустимым именем столбца/таблицы в sqlite.

Чтобы начать работу, вот одно условие:

Названия таблиц, начинающиеся с "sqlite_", зарезервированы для внутреннего использования. Ошибка создания таблицы с именем, начинающимся с "sqlite _".

Более того, использование определенных имен столбцов может иметь непреднамеренные побочные эффекты:

Каждая строка каждой таблицы SQLite имеет 64-разрядный целочисленный ключ со знаком, который уникально идентифицирует строку в своей таблице. Это целое число обычно называемый "rowid" . Значение rowid можно получить, используя один из специальные независимые от случая имена "rowid" , "oid" или "rowid" на месте имени столбца. Если таблица содержит определенный пользователем столбец с именем "rowid" , "oid" или "rowid" , тогда это имя всегда ссылается на явно объявленный столбец и не может использоваться для извлечения целого числа rowid value.

Оба цитированных текста взяты из http://www.sqlite.org/lang_createtable.html

Ответ 5

Из sqlite faq, вопрос 24 (формулировка вопроса, конечно, не дает понять, что ответ может быть полезен для вашего вопроса):

SQL использует двойные кавычки вокруг идентификаторов (имена столбцов или таблиц), которые содержат специальные символы или ключевые слова. Таким образом, двойные кавычки - это способ избежать имен идентификаторов.

Если само имя содержит двойные кавычки, убегайте, что двойная кавычка с другой.

Ответ 6

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

У вас есть три варианта:

Соответственно избегайте/цитируйте имя столбца везде, где вы его используете. Это хрупкий и опасный.
Используйте ORM, например SQLAlchemy, который позаботится об ускорении/цитировании для вас.
В идеале просто нет динамических имен столбцов. Таблицы и столбцы предназначены для структуры; все динамические данные и должны быть в таблице, а не в ней.

Ответ 7

Используйте функцию, определенную специально для этого:

цитата (X)

Функция quote (X) возвращает текст литерала SQL, который является значением его аргумента и подходит для включения в оператор SQL. Строки окружены одинарными кавычками с экранированием по внутренним кавычкам по мере необходимости. BLOB кодируются как шестнадцатеричные литералы. Строки со встроенными символами NUL не могут быть представлены как строковые литералы в SQL, и, следовательно, возвращаемый строковый литерал усекается до первого NUL.

Источник: https://www.sqlite.org/draft/lang_corefunc.html#quote

Пример использования:

db = sqlite3.connect('foo.sqlite3')
cur = db.cursor()
escaped_symbol = cur.execute('SELECT quote(?);', [unescaped_symbol]).fetchone()[0]

Ответ 8

Если вы обнаружите, что вам нужно имя переменной (либо relvar, либо поле), вы, вероятно, делаете что-то неправильно. альтернативным шаблоном будет использование карты свойств, например:

CREATE TABLE foo_properties(
    id INTEGER NOT NULL,
    name VARCHAR NOT NULL,
    value VARCHAR,
    PRIMARY KEY(id, name)
);

Затем вы просто указываете имя динамически при создании вставки вместо столбца.

Ответ 9

Начиная с версии psycopg2 версии 2.7 (выпущенной в феврале 2017 года) имена столбцов и имена таблиц (идентификаторы) могут быть созданы "на лету" безопасным способом, используя psycopg2.sql. Вот ссылка на документацию с примерами: http://initd.org/psycopg/docs/sql.html.

Таким образом, способ написать запрос в вашем вопросе будет следующим:

import sqlite3
from psycopg2 import sql
with sqlite3.connect(":memory:") as connection:
    query = sql.SQL("CREATE TABLE {}").format("bar")
    connection.execute(query)