Comment échapper aux chaînes pour les noms de table / colonne SQLite en Python?

Question

L'approche standard pour l'utilisation de valeurs de variables dans SQLite requêtes est le "point d'interrogation style", comme ceci:

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

Toutefois, cela ne fonctionne que pour la substitution des valeurs dans les requêtes. Il échoue lorsque utilisé pour la table ou la colonne des noms:

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

Ni l' sqlite3 module ni PEP 249 mentionner une fonction pour échapper à des noms ou des valeurs. Sans doute c'est pour décourager les utilisateurs de l'assemblage de leurs requêtes avec des chaînes, mais il me laisse à une perte.

Quelle est la fonction ou de la technique la plus appropriée pour l'utilisation de noms de variables pour les colonnes ou de tables SQLite? J'avais la nette préférence de ne pouvoir le faire sans toutes les autres dépendances, car je vais l'utiliser dans mon propre wrapper.

J'ai cherché mais je ne pouvais pas trouver une description claire et complète de la partie pertinente de SQLite est la syntaxe à utiliser pour écrire ma propre fonction. Je veux être sûr que cela fonctionne pour n'importe quel identificateur permise par SQLite, donc un essai-et-erreur de la solution est trop aléatoire pour moi.

SQLite utilise " identificateurs de devis mais je ne suis pas sûr que juste échapper est suffisant. PHP sqlite_escape_string de la fonction de la documentation suggère que certaines des données binaires peuvent avoir besoin d'être échappé, mais qui peut être un caprice de la bibliothèque PHP.

Answer 1

Pour convertir une chaîne en un SQLite identifiant:

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

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

Étant 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é dans l' 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, comme l' '?' 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 tableau, 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 TEXT, 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

• Double-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 le caractère NUL 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.

Answer 2

L' psycopg2 documentation explicitement recommande l'utilisation normale python % ou {} mise en forme de substituer une table et les noms de colonne (ou d'autres bits de dynamique de la syntaxe), et ensuite, en utilisant le mécanisme de paramétrage de remplacer les valeurs dans la requête.

Je suis en désaccord avec tout le monde qui est en train de dire "ne jamais utiliser la dynamique de la table/les noms de colonne, vous êtes en train de faire quelque chose de mal si vous avez besoin d'". - Je écrire des programmes pour automatiser des trucs avec des bases de données tous les jours, et je le fais tout le temps. Nous avons beaucoup de bases de données avec beaucoup de tables, mais ils sont tous construits sur des motifs répétés, afin générique de code pour gérer leur est très utile. La main-écrire les requêtes de tous les temps seraient beaucoup plus enclins à faire des erreurs et dangereux.

Il s'agit de ce "coffre-fort" signifie. La sagesse conventionnelle est que l'utilisation normale de python de manipulation de chaîne de placer les valeurs dans vos requêtes n'est pas "safe". C'est parce qu'il y a toutes sortes de choses qui peuvent mal se passer si vous le faites, et ces données, très souvent, provient de l'utilisateur et n'est pas sous votre contrôle. Vous avez besoin d'un 100% fiable échapper à ces valeurs correctement de sorte qu'un utilisateur ne peut pas injecter du SQL dans une valeur de données et la base de données de l'exécuter. Ainsi la bibliothèque des écrivains de faire ce travail; vous ne doit jamais l'.

Si, toutefois, vous êtes en train de rédiger générique helper code pour fonctionner sur des choses de bases de données, ces considérations ne s'appliquent pas autant. Vous êtes implicitement de donner n'importe qui peut appeler un tel code d'accès à tout le contenu de la base de données; c'est le point de l'aide de code. Alors maintenant, le problème de sécurité est de s'assurer que les données générées par l'utilisateur ne peut jamais être utilisé dans un tel code. Il s'agit d'un problème de sécurité dans le codage, et est un peu le même problème que aveuglément execing un utilisateur de la chaîne d'entrée. C'est une question distincte de l'insertion de valeurs dans vos requêtes, parce que là, vous voulez être en mesure d' en toute sécurité poignée de l'utilisateur des données d'entrée.

Donc, ma recommandation est: "faites ce que vous voulez assembler dynamiquement vos requêtes. Normal d'utilisation de python de la chaîne de création de modèles de sous la table et les noms de colonne, de la colle sur les clauses where et des jointures, toutes les bonnes (et horrible à déboguer) trucs. Mais assurez-vous que vous êtes conscient que quelles que soient les valeurs de ces codes de touche doit venir de vous, pas vos utilisateurs[1]. Ensuite, vous utilisez SQLite du paramètre de substitution de la fonctionnalité en toute sécurité insérer l'utilisateur les valeurs d'entrée dans vos requêtes que valeurs.

[1] Si (comme c'est le cas pour une grande partie du code que j'ai écris) vos utilisateurs sont les personnes qui ont plein accès à des bases de données de toute façon, et le code est de simplifier leur travail, cette considération ne s'applique pas; vous êtes probablement l'assemblage des requêtes sur des tables spécifiées. Mais vous devriez toujours utiliser SQLite du paramètre de substitution pour vous sauver de l'inévitable véritable valeur que finalement, contient des apostrophes ou des signes de pourcentage.

Answer 3

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)

Answer 4

À partir de l' 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.