¿Cómo se escapan las cadenas para los nombres de tabla / columna SQLite en Python?

El enfoque estándar para el uso de valores variables en consultas SQLite es el “estilo de signo de interrogación”, como este:

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',)] 

Sin embargo, esto solo funciona para sustituir valores en consultas. Falla cuando se usa para nombres de tablas o columnas:

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

Ni el módulo sqlite3 ni PEP 249 mencionan una función para escapar nombres o valores. Presumiblemente, esto es para desalentar a los usuarios de armar sus consultas con cadenas, pero me deja con una pérdida.

¿Qué función o técnica es la más adecuada para usar nombres de variables para columnas o tablas en SQLite? Preferiría encarecidamente hacer esto sin otras dependencias, ya que lo utilizaré en mi propio envoltorio.

Busqué, pero no pude encontrar una descripción clara y completa de la parte relevante de la syntax de SQLite, para usarla para escribir mi propia función. Quiero estar seguro de que esto funcionará con cualquier identificador permitido por SQLite, por lo que una solución de prueba y error es demasiado incierta para mí.

SQLite utiliza " para citar identificadores, pero no estoy seguro de que simplemente escaparlos sea suficiente. La documentación de la función sqlite_escape_string de PHP sugiere que algunos datos binarios también deben escaparse, pero eso puede ser un capricho de la biblioteca PHP.

Para convertir cualquier cadena en un identificador de SQLite:

  • Asegúrese de que la cadena se pueda codificar como UTF-8.
  • Asegúrate de que la cadena no incluya ningún carácter NUL.
  • Reemplace todos los " con "" .
  • Envuelva todo el asunto entre comillas dobles.

Implementación

 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("\"", "\"\"") + "\"" 

Dado un único argumento de cadena, se escapará y lo citará correctamente o generará una excepción. El segundo argumento se puede usar para especificar cualquier controlador de errores registrado en el módulo de codecs . Los incorporados son:

  • 'strict' : genera una excepción en caso de un error de encoding
  • 'replace' : reemplace los datos mal formados con un marcador de reemplazo adecuado, como '?' o '\ufffd'
  • 'ignore' : ignorar datos mal formados y continuar sin previo aviso
  • 'xmlcharrefreplace' : sustitúyalo por la referencia de caracteres XML adecuada (solo para encoding)
  • 'backslashreplace' : reemplace con secuencias de escape con barra invertida (solo para encoding)

Esto no comprueba los identificadores reservados, por lo que si intenta crear una nueva tabla SQLITE_MASTER no lo detendrá.

Ejemplo de uso

 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 

Observaciones y referencias

  • Los identificadores de SQLite son TEXT , no binarios.
    • Esquema SQLITE_MASTER en el FAQ
    • Python 2 SQLite API me gritó cuando le di bytes que no podía decodificar como texto.
    • La API SQLite de Python 3 requiere que las consultas sean str , no bytes .
  • Los identificadores de SQLite se citan utilizando comillas dobles.
    • SQL como se entiende por SQLite
  • Las comillas dobles en los identificadores de SQLite se escapan como dos comillas dobles.
  • Los identificadores de SQLite conservan el caso, pero no distinguen entre mayúsculas y minúsculas a las letras ASCII. Es posible habilitar la insensibilidad de mayúsculas y minúsculas en unicode.
    • Preguntas frecuentes de SQLite Pregunta # 18
  • SQLite no admite el carácter NUL en cadenas o identificadores.
    • SQLite Ticket 57c971fc74
  • sqlite3 puede manejar cualquier otra cadena Unicode siempre que pueda codificarse adecuadamente a UTF-8. Las cadenas no válidas podrían provocar fallos entre Python 3.0 y Python 3.1.2 o más. Python 2 aceptó estas cadenas no válidas, pero esto se considera un error.
    • Edición de Python # 12569
    • Módulos / _sqlite / cursor.c
    • Lo probé un montón.

La documentación de psycopg2 recomienda explícitamente usar el formato normal de python o {} para sustituir en nombres de tablas y columnas (u otros bits de syntax dinámica), y luego usar el mecanismo de parámetros para sustituir valores en la consulta.

No estoy de acuerdo con todos los que dicen “no uses nombres de tabla / columna dynamics, estás haciendo algo mal si es necesario”. Escribo progtwigs para automatizar cosas con bases de datos todos los días, y lo hago todo el tiempo. Tenemos muchas bases de datos con muchas tablas, pero todas están basadas en patrones repetidos, por lo que el código genérico para manejarlas es extremadamente útil. Escribir las consultas cada vez sería mucho más propenso a errores y peligroso.

Todo se reduce a lo que significa “seguro”. La sabiduría convencional es que el uso de la manipulación normal de cadenas de python para poner valores en sus consultas no es “seguro”. Esto se debe a que hay todo tipo de cosas que pueden salir mal si lo hace, y esos datos a menudo provienen del usuario y no están bajo su control. Necesita una forma 100% confiable de escapar de estos valores correctamente para que un usuario no pueda inyectar SQL en un valor de datos y hacer que la base de datos lo ejecute. Así que los escritores de la biblioteca hacen este trabajo; nunca deberías

Sin embargo, si está escribiendo un código de ayuda genérico para operar con cosas en bases de datos, estas consideraciones no se aplican tanto. Usted está dando implícitamente a cualquier persona que pueda llamar a dicho código acceso a todo en la base de datos; Ese es el punto del código de ayuda . Así que ahora la preocupación de seguridad es asegurarse de que los datos generados por el usuario nunca puedan usarse en dicho código. Este es un problema de seguridad general en la encoding, y es el mismo problema que exec una cadena de entrada de usuario a ciegas. Es un problema distinto de insertar valores en sus consultas, ya que allí desea poder manejar de forma segura los datos de entrada de usuario.

Así que mi recomendación es: haga lo que quiera para ensamblar dinámicamente sus consultas. Use la creación de plantillas de cadenas de python para sub en los nombres de tablas y columnas, pegue las cláusulas y uniones, todas las cosas buenas (y horribles de depurar). Pero asegúrese de estar consciente de que cualquier valor que tenga ese código debe provenir de usted , no de sus usuarios [1]. Luego, utiliza la funcionalidad de sustitución de parámetros de SQLite para insertar de forma segura los valores de entrada del usuario en sus consultas como valores.

[1] Si (como es el caso de gran parte del código que escribo) sus usuarios son las personas que tienen acceso total a las bases de datos de todos modos y el código es para simplificar su trabajo, entonces esta consideración realmente no se aplica; probablemente esté reuniendo consultas en tablas especificadas por el usuario. Pero aún debe usar la sustitución de parámetros de SQLite para salvarse del inevitable valor genuino que eventualmente contiene comillas o signos de porcentaje.

Si está bastante seguro de que necesita especificar los nombres de las columnas dinámicamente, debe usar una biblioteca que pueda hacerlo de manera segura (y quejarse de las cosas que están mal). SQLAlchemy es muy bueno en eso.

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

foo_table ahora representa la tabla con el esquema dynamic, pero solo puede usarla en el contexto de una conexión de base de datos real (para que sqlalchemy sepa el dialecto y qué hacer con el sql generado).

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

A continuación, puede emitir la CREATE TABLE ... con echo=True , sqlalchemy registrará el sql generado, pero en general, sqlalchemy hace todo lo posible para mantener el sql generado fuera de sus manos (para que no considere usarlo con propósitos malvados).

 >>> 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 >>> 

y sí, sqlalchemy se hará cargo de cualquier nombre de columna que necesite un manejo especial, como cuando el nombre de la columna es una palabra reservada de 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 >>> 

y puede salvarte de una posible maldad:

 >>> 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 >>> 

(Al parecer, algunas cosas extrañas son identificadores perfectamente legales en sqlite)

Lo primero que hay que entender es que los nombres de tablas / columnas no se pueden escapar en el mismo sentido que los de las cadenas almacenadas como valores de la base de datos.

La razón es que tienes que:

  • acepte / rechace el nombre de la tabla / columna potencial, es decir, no se garantiza que una cadena sea un nombre de columna / tabla aceptable, al contrario de una cadena que se almacenará en alguna base de datos; o,
  • desinfecte la cadena que tendrá el mismo efecto que crear un compendio: la función utilizada es suprayectiva , no biyectiva (una vez más, lo inverso es cierto para una cadena que se almacenará en alguna base de datos); así que no solo no puede estar seguro de volver del nombre sanitizado al nombre original, sino que también corre el riesgo de intentar crear dos columnas o tablas con el mismo nombre sin querer.

Habiendo entendido eso, lo segundo que hay que entender es que la forma en que terminará “escapando” de los nombres de tabla / columna depende de su contexto específico, y por lo tanto hay más de una forma de hacer esto, pero cualquiera sea la forma, necesitará desenterrar para averiguar exactamente qué es o no es un nombre de columna / tabla aceptable en sqlite.

Para empezar, aquí hay una condición:

Los nombres de tablas que comienzan con “sqlite_” están reservados para uso interno. Es un error intentar crear una tabla con un nombre que comience con “sqlite_”.

Aún mejor, el uso de ciertos nombres de columna puede tener efectos secundarios no deseados:

Cada fila de cada tabla SQLite tiene una clave de entero con signo de 64 bits que identifica de forma única la fila dentro de su tabla. Este entero se suele llamar “rowid”. Se puede acceder al valor de rowid usando uno de los nombres especiales independientes de mayúsculas y minúsculas “rowid”, “oid” o ” rowid ” en lugar de un nombre de columna. Si una tabla contiene una columna definida por el usuario llamada “rowid”, “oid” o ” rowid “, ese nombre siempre hace referencia a la columna declarada explícitamente y no se puede utilizar para recuperar el valor de rowid entero.

Ambos textos citados son de http://www.sqlite.org/lang_createtable.html

De las preguntas frecuentes de sqlite, pregunta 24 (la formulación de la pregunta, por supuesto, no da una pista de que la respuesta puede ser útil para su pregunta):

SQL utiliza comillas dobles alrededor de los identificadores (nombres de columnas o tablas) que contienen caracteres especiales o que son palabras clave. Así que las comillas dobles son una forma de escapar de los nombres de identificadores.

Si el nombre en sí contiene comillas dobles, evite esa comilla doble con otra.

Los marcadores de posición son sólo para valores. Los nombres de columnas y tablas son estructurales, y son similares a los nombres de variables; No puedes usar marcadores de posición para rellenarlos.

Tienes tres opciones:

  1. Escape adecuadamente / cite el nombre de la columna en todos los lugares donde lo use. Esto es frágil y peligroso.
  2. Use un ORM como SQLAlchemy , que se encargará de escapar / cotizar por usted.
  3. Idealmente, simplemente no tienen nombres de columna dynamics. Las tablas y columnas son para estructura ; Todo lo dynamic es información y debería estar en la tabla en lugar de en parte.

A partir de la versión 2.7 de psycopg2 (lanzada en febrero de 2017), los nombres de las columnas y los nombres de las tablas (identificadores) se pueden generar sobre la marcha de forma segura utilizando psycopg2.sql . Aquí hay un enlace a la documentación con ejemplos: http://initd.org/psycopg/docs/sql.html .

Entonces, la forma de escribir la consulta en tu pregunta sería:

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

Si encuentra que necesita un nombre de entidad variable (relvar o campo), probablemente esté haciendo algo mal . un patrón alternativo sería utilizar un mapa de propiedades, algo como:

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

Luego, simplemente especifique el nombre dinámicamente cuando haga una inserción en lugar de una columna.