Comment voulez-vous échapper à cordes pour SQLite/table des noms de colonne en Python?

Pour convertir une chaîne en un SQLite identifiant:

• S'assurer de la chaîne peut être codé en UTF-8.
• Assurer la chaîne de ne pas inclure tout NUL caractères.
• Remplacer tous les " avec "".
• Envelopper le tout dans des guillemets doubles.

Mise en œuvre

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

Donné une chaîne unique argument, il va s'échapper et de le citer correctement ou lever une exception. Le deuxième argument peut être utilisé pour spécifier n'importe quel gestionnaire d'erreur enregistrés dans le codecs module. Le haut-sont:

• 'strict': lever une exception en cas d'erreur de codage
• 'replace': remplacer des données malformées, avec un marqueur de remplacement, tels que '?' ou '\ufffd'
• 'ignore': ignorer des données malformées et continuer sans préavis
• 'xmlcharrefreplace': remplacer avec le caractère XML de référence (pour l'encodage uniquement)
• 'backslashreplace': remplacer avec backslashed des séquences d'échappement (pour l'encodage uniquement)

Cela ne veut pas vérifier réservés identifiants, donc si vous essayez de créer un nouveau SQLITE_MASTER table, il n'arrête pas de vous.

Exemple D'Utilisation

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

Observations et Références

• SQLite identificateurs sont TEXTE, pas binaire.
  • SQLITE_MASTER schéma dans la FAQ
  • Python 2 de l'API SQLite a crié à moi quand je lui ai donné octets qu'il ne pouvait pas décoder le texte.
  • Python 3 SQLite API nécessite des requêtes être strs, pas bytes.

• SQLite identifiants sont indiqués à l'aide de guillemets.
  • SQL telle que Comprise par SQLite

• Guillemets dans SQLite, les identificateurs sont échappés comme deux guillemets doubles.

• SQLite identificateurs de préserver la casse, mais ils sont insensibles à la casse vers ASCII des lettres. Il est possible d'activer unicode insensibilité à la casse.
  • SQLite Question dans la FAQ #18

• SQLite ne supporte pas la NUL personnage dans les chaînes ou les identifiants.
  • SQLite Billet 57c971fc74

• sqlite3 peut gérer n'importe quelle autre chaîne unicode aussi longtemps qu'il peut être correctement codées en UTF-8. Les chaînes non valides pourrait provoquer des collisions entre Python 3.0 et Python 3.1.2 ou à peu près. Python 2 accepté ces chaînes non valides, mais c'est considéré comme un bug.
  • Python Question N ° 12569
  • Modules/_sqlite/curseur.c
  • Je l'ai testé un tas.

Si vous êtes tout à fait certain que vous avez besoin de spécifier les noms de colonnes dynamiquement, vous devez utiliser une bibliothèque qui peut le faire en toute sécurité (et se plaint à propos de choses qui sont fausses). SQLAlchemy est très bon à cela.

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

foo_table maintenant représente la table avec la dynamique de schéma, mais vous ne pouvez l'utiliser que dans le contexte d'une connexion de base de données (de sorte que sqlalchemy sait le dialecte, et quoi faire avec le sql généré).

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

Vous pouvez ensuite le problème de la CREATE TABLE .... avec echo=True, sqlalchemy sera journal le sql généré, mais en général, sqlalchemy sort de sa façon de garder le sql généré hors de vos mains (de peur que vous envisagez d'utiliser à de mauvaises fins).

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

et oui, sqlalchemy va prendre soin de tous les noms de colonnes qui nécessitent un traitement spécial, comme lorsque le nom de la colonne est un mot réservé 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
>>> 

et peut vous sauver du possible, la méchanceté:

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

(apparemment des choses étranges se sont parfaitement légales identifiants sqlite)

De la sqlite faq, question 24 (la formulation de la question n'est évidemment pas donner un indice que la réponse peut être utile à votre question):

SQL utilise des guillemets doubles autour de identificateurs (colonne ou les noms de table) qui contient des caractères spéciaux ou qui sont des mots clés. Si les guillemets sont un moyen de s'échapper de l'identificateur de noms.

Si le nom lui-même contient des guillemets, échapper à ce double devis avec une autre.

De psycopg2 version 2.7 (sorti en Février 2017), les noms de colonnes et les noms de table (identifiants) peuvent être générées à la volée de façon sécuritaire à l'aide de psycopg2.sql. Voici un lien vers la documentation avec des exemples: http://initd.org/psycopg/docs/sql.html.

Donc, la façon d'écrire la requête dans votre question serait:

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

Si vous trouvez que vous avez besoin d'une variable le nom de l'entité (soit relvar ou champ), alors vous êtes probablement faire quelque chose mal. un autre modèle serait d'utiliser une carte de propriété, quelque chose comme:

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

Ensuite, il suffit juste de préciser le nom de façon dynamique lors d'un insert à la place d'une colonne.