Nom de la table en tant que paramètre de la fonction PostgreSQL

Je veux passer un nom de table en tant que 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 ceci:

 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 select * from quote_ident($1) tab where tab.id=1 changeant cet 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, je reçois 1 , ce qui signifie que quelque chose est sélectionné. Pourquoi le premier quote_ident($1) fonctionner et le second pas en même temps? Et comment cela pourrait-il être résolu?

Cela peut être encore simplifié et amélioré:

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

Appel (exemple 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"') 

Points majeurs

  • Utilisez un paramètre OUT pour simplifier la fonction. Vous pouvez directement sélectionner le résultat du SQL dynamic et être fait. 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 sinon. Il y a plusieurs façons de le faire, EXISTS est généralement le plus efficace.

  • Vous semblez vouloir un nombre entier en arrière, alors je lance le résultat boolean de EXISTS en integer , ce qui donne exactement ce que vous aviez. Je retournerais plutôt booléen .

  • J’utilise le type d’identifiant d’object regclass comme type d’entrée pour _tbl . Cela fait tout ce que quote_ident(_tbl) ou format('%I', _tbl) ferait, mais mieux, car:

    • .. il empêche aussi bien l’ injection SQL .

    • .. il échoue immédiatement et plus facilement si le nom de la table est invalide / n’existe pas / est invisible pour l’utilisateur actuel. (Un paramètre regclass ne s’applique qu’aux tables existantes .)

    • .. il fonctionne avec les noms de table qualifiés par un schéma, où un simple quote_ident(_tbl) ou un format(%I) échouerait car ils ne peuvent pas résoudre l’ambiguïté. Vous devez passer et échapper les noms de schéma et de table séparément.

  • J’utilise toujours format() , car il simplifie la syntaxe (et montre comment il est utilisé), mais avec %s au lieu de %I En règle générale, les requêtes sont plus complexes, donc le format() aide davantage. Pour l’exemple simple, nous pourrions tout simplement concaténer:

     EXECUTE 'SELECT (EXISTS (SELECT 1 FROM ' || _tbl || ' WHERE id = 1))::int' 
  • Il n’est pas nécessaire de qualifier la colonne id alors qu’il n’y a qu’une seule table dans la liste FROM . Aucune ambiguïté possible dans cet exemple. Les commandes SQL (dynamics) dans EXECUTE ont une scope distincte , les variables de fonction ou les parameters n’y sont pas visibles – contrairement aux commandes SQL simples dans le corps de la fonction.

Testé avec PostgreSQL 9.1. format() nécessite au moins cette version.

Voici pourquoi vous échappez toujours correctement à l’entrée utilisateur pour SQL dynamic:

SQL Fiddle démontrant l’injection SQL

Ne fais pas ça.

C’est la réponse. C’est un anti-modèle terrible. A quoi cela sert-il? Si le client connaît la table, il veut des données, puis SELECT FROM ThatTable ! Si vous avez conçu votre firebase database de manière à ce que cela soit nécessaire, vous l’avez probablement mal conçue. Si votre couche d’access aux données doit savoir si une valeur existe dans une table, il est sortingvial de faire la partie SQL dynamic dans ce code. Le pousser dans la firebase database n’est pas bon.

J’ai une idée: installons un appareil à l’intérieur des ascenseurs où vous pourrez saisir le numéro de l’étage souhaité. Ensuite, lorsque vous appuyez sur “Go”, vous déplacez une main mécanique sur le bouton correspondant à l’étage souhaité et vous appuyez dessus. Révolutionnaire!

Apparemment, ma réponse était trop courte pour que je puisse expliquer ce problème avec plus de détails.

Je n’avais aucune intention de moquerie. Mon exemple idiot d’ascenseur était le meilleur appareil que je pouvais imaginer pour souligner succinctement les défauts de la technique suggérée dans la question. Cette technique ajoute une couche d’indirection complètement inutile et déplace inutilement le choix du nom de la table d’un espace appelant en utilisant un DSL (SQL) robuste et bien compris dans un hybride utilisant un code SQL côté serveur obscur / bizarre.

Une telle division de la responsabilité par le mouvement de la logique de construction des requêtes en SQL dynamic rend le code plus difficile à comprendre. Cela détruit une convention parfaitement raisonnable (comment une requête SQL choisit quoi sélectionner) au nom d’un code personnalisé présentant un potentiel d’erreur.

  • Le SQL dynamic offre la possibilité d’une injection SQL difficile à reconnaître dans le code frontal ou dans le code principal (il faut les examiner ensemble pour voir cela).

  • Les procédures et fonctions stockées peuvent accéder aux ressources sur lesquelles le propriétaire du SP / de la fonction a des droits, mais pas l’appelant. Si je comprends bien, lorsque vous utilisez du code qui produit du SQL dynamic et l’exécute, la firebase database exécute le SQL dynamic sous les droits de l’appelant. Cela signifie que vous ne serez pas en mesure d’utiliser des objects privilégiés ou que vous devrez les ouvrir à tous les clients, augmentant ainsi la surface des attaques potentielles sur les données privilégiées. Définir le SP / la fonction au moment de la création pour toujours exécuter 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 dynamic un vecteur d’attaque très alléchant.

  • Lorsqu’un développeur doit comprendre ce que fait le code de l’application pour le modifier ou corriger un bogue, il aura beaucoup de mal à exécuter la requête SQL exacte. Le profileur SQL peut être utilisé, 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 consignée par le fournisseur de services mais cela augmente la complexité sans aucune raison (maintenir de nouvelles tables, purger les anciennes données, etc.) et est totalement non évidente. En fait, certaines applications sont conçues de manière à ce que le développeur ne dispose pas d’informations d’identification de la firebase database. Il lui est donc pratiquement impossible de voir la requête soumise.

  • Lorsqu’une erreur se produit, par exemple lorsque vous essayez de sélectionner une table qui n’existe pas, vous obtenez un message du type “nom d’object non valide” dans la firebase database. Cela se passera exactement de la même manière que vous composiez le SQL dans le backend ou la firebase database, mais la différence est que certains développeurs pauvres qui essaient de dépanner le système doivent pénétrer un niveau plus profond dans une autre cave sous celle où le problème existe réellement, pour creuser la procédure de merveille qui fait tout et essayer de comprendre quel est le problème. Les journaux n’indiqueront pas “Erreur dans GetWidget”, il affichera “Erreur dans OneProcedureToRuleThemAllRunner”. Cette abstraction ne fera qu’empirer votre système.

Voici un exemple bien meilleur en pseudo-C # de changement de nom de table basé sur un paramètre:

 ssortingng sql = ssortingng.Format("SELECT * FROM {0};", escapeSqlIdentifier(tableName)); results = connection.Execute(sql); 

Chaque défaut que j’ai mentionné avec l’autre technique est complètement absent de cet exemple.

Il n’y a tout simplement aucun but, aucun avantage, aucune amélioration possible dans la soumission d’un nom de table à une procédure stockée.

Dans le code plpgsql, l’instruction EXECUTE doit être utilisée pour les requêtes dans lesquelles les noms de tables ou les colonnes proviennent de variables. De plus, la construction IF EXISTS () n’est pas autorisée lorsque la query est générée dynamicment.

Voici votre fonction avec les deux problèmes corrigés:

 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; 

Le premier ne fonctionne pas réellement dans le sens où vous voulez dire, il ne fonctionne que dans la mesure où il ne génère pas d’erreur.

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

Ce que vous voulez faire nécessitera un SQL dynamic, qui est en fait l’endroit où les fonctions quote_* doivent être utilisées.

Si la question était de tester si la table est vide ou pas (id = 1), voici une version simplifiée de la procédure stockée d’Erwin:

 CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS $func$ BEGIN EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName) INTO zeroIfEmpty; END $func$ LANGUAGE plpgsql; 

Si vous souhaitez que le nom de la table, le nom de la colonne et la valeur soient transmis dynamicment pour fonctionner en tant que paramètre

utiliser ce code

 create or replace function total_rows(tbl_name text, column_name text, value int) returns integer as $total$ declare total integer; begin EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total; return total; end; $total$ language plpgsql; postgres=# select total_rows('tbl_name','column_name',2); --2 is the value 

J’ai la version 9.4 de PostgreSQL et j’utilise toujours ce code:

 CREATE FUNCTION add_new_table(text) RETURNS void AS $BODY$ begin execute 'CREATE TABLE ' || $1 || '( item_1 type, item_2 type )'; end; $BODY$ LANGUAGE plpgsql 

Et alors:

 SELECT add_new_table('my_table_name'); 

Ça marche bien pour moi.

Attention! L’exemple ci-dessus est l’un de ceux qui montre “Comment faire si on veut garder la sécurité pendant l’interrogation de la firebase database”: P