Nom de table comme paramètre de fonction PostgreSQL

Question

Je veux passer un nom de table comme paramètre dans une fonction Postgres. J'ai essayé ce code :

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

Et j'ai eu ça :

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

Et voici l'erreur que j'ai obtenue lorsque j'ai changé pour ceci select * from quote_ident($1) tab where tab.id=1 :

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

Probablement, quote_ident($1) fonctionne, car sans le where quote_ident($1).id=1 partie que j'obtiens 1 ce qui signifie que quelque chose est sélectionné. Pourquoi le premier quote_ident($1) travail et le second pas en même temps ? Et comment résoudre ce problème ?

Answer 1

Ce système peut encore être simplifié et amélioré :

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

Appel avec un nom qualifié de schéma (voir ci-dessous) :

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

Ou :

SELECT some_f('"my very uncommon table name"');

Principaux points

Utilisez un OUT paramètre pour simplifier la fonction. Vous pouvez y sélectionner directement le résultat du SQL dynamique et le tour est joué. Pas besoin de variables et de code supplémentaires.

EXISTS fait exactement ce que vous voulez. Vous obtenez true si la ligne existe ou false autrement. Il existe plusieurs façons de procéder, EXISTS est généralement la plus efficace.

Vous semblez vouloir un entier de retour, alors j'ai lancé le boolean résultat de EXISTS a integer ce qui donne exactement ce que vous aviez. Je retournerais boolean à la place.

J'utilise le type d'identifiant d'objet regclass comme type d'entrée pour _tbl . Cela fait tout quote_ident(_tbl) o format('%I', _tbl) ferait l'affaire, mais en mieux, car :

• il empêche Injection SQL tout aussi bien.

• il échoue immédiatement et plus gracieusement si le nom de la table est invalide / n'existe pas / est invisible pour l'utilisateur actuel. (A regclass est uniquement applicable pour existant tables.)

• il fonctionne avec des noms de table qualifiés de schéma, où un simple quote_ident(_tbl) o format(%I) échoueraient parce qu'ils ne peuvent pas résoudre l'ambiguïté. Vous devez transmettre et échapper les noms de schémas et de tables séparément.

Il ne fonctionne que pour existant des tables, évidemment.

J'utilise toujours format() parce que cela simplifie la syntaxe (et pour montrer comment l'utiliser), mais avec %s au lieu de %I . En général, les requêtes sont plus complexes, donc format() aide plus. Pour l'exemple simple, nous pourrions tout aussi bien concaténer :

EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'

Il n'est pas nécessaire de qualifier la table id alors qu'il n'y a qu'une seule table dans le fichier FROM liste. Aucune ambiguïté possible dans cet exemple. Commandes SQL (dynamiques) à l'intérieur EXECUTE ont un champ d'application distinct Les variables ou les paramètres de la fonction ne sont pas visibles à cet endroit, contrairement aux commandes SQL simples dans le corps de la fonction.

Voici pourquoi vous toujours échapper correctement à l'entrée utilisateur pour le SQL dynamique :

_db<>fidèle aquí_ Démonstration de l'injection SQL
_{Vieux <a href="http://sqlfiddle.com/#!17/39ef7/15" rel="noreferrer">sqlfiddle</a>}

Answer 2

Dans la mesure du possible, ne faites pas ça.

C'est la réponse - c'est un anti-modèle. Si le client connaît la table dont il veut les données, alors SELECT FROM ThatTable . Si une base de données est conçue de manière à ce que cela soit nécessaire, elle semble être conçue de manière sous-optimale. Si une couche d'accès aux données doit savoir si une valeur existe dans une table, il est facile de composer du SQL dans ce code, et pousser ce code dans la base de données n'est pas bon.

Pour moi, cela revient à installer un dispositif à l'intérieur d'un ascenseur où l'on peut taper le numéro de l'étage souhaité. Une fois que l'on a appuyé sur le bouton "Go", une main mécanique se déplace vers le bouton correspondant à l'étage souhaité et l'appuie. Cela pose de nombreux problèmes potentiels.

Veuillez noter qu'il n'y a aucune intention de se moquer, ici. Mon exemple d'ascenseur ridicule était *le meilleur moyen que j'ai pu imaginer* pour souligner succinctement les problèmes de cette technique. Elle ajoute une couche inutile d'indirection, déplaçant le choix du nom de la table d'un espace d'appel (utilisant un DSL robuste et bien compris, SQL) à un hybride utilisant un code SQL obscur/bizarre côté serveur.

Un tel partage des responsabilités par le déplacement de la logique de construction des requêtes dans le SQL dynamique rend le code plus difficile à comprendre. Il viole une convention standard et fiable (la façon dont une requête SQL choisit ce qu'elle doit sélectionner) au nom d'un code personnalisé qui présente un potentiel d'erreur élevé.

Voici des points détaillés sur certains des problèmes potentiels de cette approche :

• Le SQL dynamique offre la possibilité d'une injection SQL qu'il est difficile de reconnaître dans le code frontal ou le code dorsal seul (il faut les inspecter ensemble pour s'en rendre compte).

• Les procédures et fonctions stockées peuvent accéder à des ressources sur lesquelles le propriétaire de la SP/fonction a des droits mais pas l'appelant. D'après ce que j'ai compris, sans précautions particulières, par défaut, lorsque vous utilisez du code qui produit du SQL dynamique et que vous l'exécutez, la base de données exécute le SQL dynamique sous les droits de l'appelant. Cela signifie que soit vous ne pourrez pas du tout utiliser les objets privilégiés, soit vous devrez les ouvrir à tous les clients, ce qui augmente la surface d'attaque potentielle des données privilégiées. Définir le SP/fonction au moment de la création pour qu'il soit toujours exécuté en tant qu'utilisateur particulier (dans SQL Server, EXECUTE AS ) peut résoudre ce problème, mais rend les choses plus compliquées. Cela exacerbe le risque d'injection SQL mentionné au point précédent, en faisant du SQL dynamique un vecteur d'attaque très séduisant.

• Lorsqu'un développeur doit comprendre ce que fait le code de l'application afin de le modifier ou de corriger un bogue, il lui sera très difficile d'obtenir la requête SQL exacte qui est exécutée. Il est possible d'utiliser un profileur SQL, mais cela nécessite des privilèges spéciaux et peut avoir des effets négatifs sur les performances des systèmes de production. La requête exécutée peut être enregistrée par le SP, mais cela accroît la complexité pour un bénéfice discutable (nécessitant d'accueillir de nouvelles tables, de purger les anciennes données, etc. ) et n'est pas évidente. En fait, certaines applications sont conçues de telle sorte que le développeur ne dispose pas d'informations d'identification de la base de données et qu'il lui est pratiquement impossible de voir la requête qui est soumise.

• Lorsqu'une erreur se produit, par exemple lorsque vous essayez de sélectionner une table qui n'existe pas, vous recevez un message du type "nom d'objet non valide" de la base de données. Cela se produira exactement de la même manière, que vous composiez le SQL dans le back-end ou dans la base de données, mais la différence est que le pauvre développeur qui essaie de dépanner le système doit s'enfoncer un peu plus dans une autre grotte, en dessous de celle où se trouve le problème, pour creuser dans la procédure miracle qui fait tout pour essayer de trouver la cause du problème. Les journaux ne montreront pas "Error in GetWidget", mais "Error in OneProcedureToRuleThemAllRunner". Cette abstraction rendra généralement un système pire .

Un exemple en pseudo-C# de changement de nom de table en fonction d'un paramètre :

string sql = $"SELECT * FROM {EscapeSqlIdentifier(tableName)};"
results = connection.Execute(sql);

Bien que cela n'élimine pas tous les problèmes possibles et imaginables, les défauts que j'ai soulignés avec l'autre technique sont absents de cet exemple.

Answer 3

Dans le code plpgsql, le EXECUTE doit être utilisée pour les requêtes dans lesquelles les noms de tables ou les colonnes proviennent de variables. De même, l'instruction IF EXISTS (<query>) n'est pas autorisée lorsque query est généré dynamiquement.

Voici votre fonction avec les deux problèmes résolus :

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

Answer 4

Je sais que c'est un vieux fil, mais je suis tombé dessus récemment en essayant de résoudre le même problème - dans mon cas, pour des scripts assez complexes.

Transformer l'ensemble du script en SQL dynamique n'est pas idéal. C'est un travail fastidieux et sujet aux erreurs, et vous perdez la possibilité de paramétrer : les paramètres doivent être interpolés en constantes dans le SQL, avec de mauvaises conséquences pour les performances et la sécurité.

Voici une astuce simple qui vous permet de conserver le SQL intact si vous n'avez besoin que de sélectionner dans votre table : utilisez le SQL dynamique pour créer une vue temporaire :

CREATE OR REPLACE FUNCTION some_f(_tbl varchar) returns integer
AS $$
BEGIN
    drop view if exists myview;
    execute format('create temporary view myview as select * from %s', _tbl);
    -- now you can reference myview in the SQL
    IF EXISTS (select * from myview where myview.id=1) THEN
     return 1;
    END IF;
    return 0;
END;
$$ language plpgsql;

Answer 5

La première ne "fonctionne" pas vraiment dans le sens où vous l'entendez, elle ne fonctionne que dans la mesure où elle ne génère pas d'erreur.

Essayez SELECT * FROM quote_ident('table_that_does_not_exist'); et vous verrez pourquoi votre fonction renvoie 1 : le select renvoie un tableau avec une colonne (nommée quote_ident ) avec une ligne (la variable $1 ou dans ce cas particulier table_that_does_not_exist ).

Ce que vous voulez faire va nécessiter du SQL dynamique, qui est en fait l'endroit où le quote_* Les fonctions sont destinées à être utilisées.