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

Чтобы преобразовать любую строку в идентификатор 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's 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 57c971fc74

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

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

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

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

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

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

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

Если вы уверены, что вам нужно указывать имена столбцов динамически, вы должны использовать библиотеку, которая может сделать это безопасно (и жалуется на неправильные вещи). 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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Начиная с версии 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) 

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

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

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