Forums PostgreSQL.fr

Jiff

Membre

Petit problème dans une fonction

Salut forumers,
.
J'ai une query qui fonctionne très bien en ligne de Cde, mais qui me me renvoit aucun résultat quand elle est placée dans une fonction.
Je suspecte fortement le quoting automatique de PG, mais je ne trouve pas comment la réécrire pour qu'elle fonctionne correctement.
.
La ligne qui marche est:

WHERE n.nspname ~ '^(common)$'

En l'occurence, common est le nom d'un schéma.
.
La ligne qui me plante est sa "traduction" dans ma fonction qui prend ce nom de schéma comme parm:

WHERE n.nspname ~ '^($1)$'

Bon, je me doute que la regexp permet d'éviter une éventuelle injection (et c'est Tbien), mais en attendant çamorchpôéjtroufpôpourkoa.
.
J'ai d'ailleurs le même résultat que ci-dessus (une réponse vide de lignes) avec:

WHERE n.nspname ~ '^("common")$'

.
Comment dois-je réécrire cela pour que ça fonctionne comme attendu? (et que la protection reste activée).

rjuju

Administrateur

Re : Petit problème dans une fonction

Bonjour,

À partir du moment ou vous mettez votre paramètre entre guillemet simple, cela devient une simple chaîne. Il y a 2 contournements possibles :

- soit vous remplacez « n.nspname ~ '^($1)$' » par « n.nspname = $1 »
- soit vous utilisez effectivement la regexp, et vous pouvez faire ceci :

DECLARE
regex text;
BEGIN
regex := '^(' || $1 || ')$';
[...]
WHERE n.nspname ~ regex
[...]

---

Julien.
https://rjuju.github.io/

Jiff

Membre

Re : Petit problème dans une fonction

Ahhh, merci, ça fonctionne Tbien.
Mon erreur était de persister à vouloir rester en SQL (plPgSQL, céplukomplikékesicétéplusimp;-).
J'avais utilisé temporairement "n.nspname = $1" pour pouvoir tester mon client, mais ça n'était pas satisfaisant puisque, d'après ce que je comprend, la regexp permet d'éviter une injection.

rjuju

Administrateur

Re : Petit problème dans une fonction

La regexp ne vous permet pas d'éviter une injection. L'utilisation de requêtes préparées ou l'appels à des fonctions comme quote_literal sont à utiliser pour éviter les injectons.

---

Julien.
https://rjuju.github.io/

Jiff

Membre

Re : Petit problème dans une fonction

? J'ai eu une discussion il y a un certain temps avec Tom Lane, et il me confirmait qu'utiliser: '$n' à la place de: nom_du_parm permettait justement d'éviter ça (d'après ce que j'avais compris, c'est équivalent à: quote_literal(nom_du_parm)).
.
Maintenant, j'ai ptêt ben compris de travers; auquel cas, d'après votre réponse, il suffit que je prépare une requête paramétrée et que je fasse l'appel par la proc?
.
Par ailleurs, j'ai un bis repetita (plpgsql, ça n'est _vraiment_ pas simple), je dois passer… j'sais pas trop quoi en fait… à une proc.
.
La proc d'origine utilise:

…
WHERE c.relkind IN ('r','s','')
…

et là encore, je sèche
.
Après m'être battu un bon bout de temps avec un: IN parm CHAR[], qui n'a pas plus fonctionné, je suis revenu à une string (avec laquelle j'ai aussi bataillé un bout de temps):

-- SELECT * FROM admin.tst( '''r'',''s'',''''' );

CREATE FUNCTION	admin.tst(my TEXT)
	
RETURNS BOOLEAN AS $$	
DECLARE	
    az TEXT := '(' || $1 || ')';	
    ch CHAR := 'r';	
BEGIN	
--    RETURN az; --AVEC "RETURNS TEXT AS": Fonctionne et renvoie bien: ('r','s','')	
--    RETURN ch ~ az;  --Fonctionne mais renvoie FALSE:(	
--    RETURN ch IN az;  --Plante à la création: syntax error at or near “az”	
    RETURN ch IN ($1);  --Fonctionne mais renvoie FALSE:(	
END	
$$ LANGUAGE plpgsql STRICT;	

Et là, je n'y comprend plus rien; je pensais qu'en appliquant la même recette que la précédente, ça irait, meuh nan (

Jiff

Membre

Re : Petit problème dans une fonction

Bon, je me réponds à moi-même, personnellement, tout seul (pu**in, j'en ai chibavé quand même:(
.
Après un nombre respectable de tests, dont j'aurais pu éviter une bonne partie en utilisant RAISE pour afficher ma string finale de l'intérieur de la proc (@!#©<), et lu un nombre impressionnant de pages sur le web, voila comment j'ai résolu le PB.
.
En début de proc, j'ai ajouté un IF et modifié le WHERE d'origine:

…
IF    $2 = 'tables'    THEN rel_type = '^(r|s|'''')';
ELSIF $2 = 'views'     THEN rel_type = '(''v'',''s'','''')';
ELSIF $2 = 'matviews'  THEN rel_type = '(''m'',''s'','''')';
ELSIF $2 = 'indexes'   THEN rel_type = '(''i'',''s'','''')';
ELSIF $2 = 'sequences' THEN rel_type = '(''S'',''s'','''')';
ELSE
    RAISE EXCEPTION 'Function ILLEGAL CALL: admin.tabviematvieindseq(TEXT, TEXT) - Wrong 2nd Parm - By User #%', common.whoami_id();
END IF;
RAISE NOTICE 'VOICI rel_type APRÈS AFFECTATION DANS LE IF: %', rel_type;
RETURN QUERY
…
WHERE c.relkind ~ rel_type
…

Quand le 2nd parm de ma proc est: 'tables', ça fonctionne nickel; par contre, ce que je m'explique absolument pas, c'est que le RAISE renvoie également "une bonne string" pour 'views': ('v','s',''), ce qui devrait normalement fonctionner aussi, non? (Pas sûr que j'ai correctement appréhendé toutes les subtilités des regexp…)

gleu

Administrateur

Re : Petit problème dans une fonction

Ce serait tellement plus simple si vous fournissiez un exemple, avec la fonction qui ne marche pas et comment vous l'appelez.

Là, personnellement, je suis incapable de fournir la moindre réponse.

---

Guillaume.

Jiff

Membre

Re : Petit problème dans une fonction

Effectivement, ça parle mieux; voici donc la query originale et ma fonction.
.
Elle sert à récupérer les tables|views|matviews|indexes|sequences d'un schéma par son ptit nom.
.
Il y a deux choses que j'aimerais savoir:
0- Pourquoi ça ne marche pas (enfin, si, mais ça renvoie *tous* les types d'un seul coup) dans le cas précédemment évoqué (où 'rel_type' était une string conforme à l'original)?
1- Existe-t-il une façon plus simple de faire que la mienne?
2- Est-ce que cette function est injection-free ou non? (et si non, évidemment le pourquoi du comment;)
3- S'il est important de déclarer son type? (et pourquoi)
4- Si 3, quel est son type? (j'ai du mal à vraiment saisir le distingo entre STABLE et IMMUTABLE)
5- À tout hasard, si vous savez ce que peuvent bien être les tables|views|etc de types 'special' et ''?
6- Pourquoi ça plante si j'utilise '$BODY$' à la place de '$$'?
.
Query originale:

SELECT n.nspname AS "schema", c.relname AS "name", c.oid AS "oid",
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'matview'
        WHEN 'i' THEN 'index'
        WHEN 'S' THEN 'sequence'
        WHEN 's' THEN 'special'
        WHEN 'f' THEN 'foreign table'
    END AS "type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "owner",
    pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) AS "size",
    pg_catalog.obj_description(c.oid, 'pg_class') AS "description"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
    AND n.nspname !~ '^pg_toast'
    AND n.nspname ~ '^(mon_schema_a_moi_qu_il_est_beau)$'
ORDER BY 1,2;

La fonction que j'ai fini par trouver (celle qui ne fonctionnait pas était identique à l'exception des 'rel_type' tels que précédemment décrit):

CREATE FUNCTION	admin.schtabviematvieindseq(IN schema_name TEXT, IN relation_type TEXT, OUT schema NAME, OUT name NAME, OUT oid OID, OUT type TEXT, OUT owner NAME, OUT size TEXT, OUT description TEXT)
RETURNS SETOF record AS $$	
DECLARE	
    sch_name TEXT := '^(' || $1 || ')$';	
    rel_type TEXT;	
BEGIN	
IF    $2 = 'tables'    THEN rel_type = '^(r|s|'''')';	
ELSIF $2 = 'views'     THEN rel_type = '^(v|s|'''')';	
ELSIF $2 = 'matviews'  THEN rel_type = '^(m|s|'''')';	
ELSIF $2 = 'indexes'   THEN rel_type = '^(i|s|'''')';	
ELSIF $2 = 'sequences' THEN rel_type = '^(S|s|'''')';	
ELSE	
    RAISE EXCEPTION 'Function ILLEGAL CALL: admin.tabviematvieindseq(TEXT, TEXT) - wrong 2nd parameter – By User #%', common.whoami_id();	
------    HINT 'Depending on his mood, the DBA might cut your balls off with a rusty chainsaw or have you fired.';  --Breaks @ creation
END IF;	
------RAISE NOTICE 'HERE IS rel_type AFTER IF: %', rel_type;	
RETURN QUERY	
SELECT n.nspname AS "schema", c.relname AS "name", c.oid AS "oid",	
    CASE c.relkind	
        WHEN 'r' THEN 'table'	
        WHEN 'v' THEN 'view'	
        WHEN 'm' THEN 'matview'	
        WHEN 'i' THEN 'index'	
        WHEN 'S' THEN 'sequence'	
        WHEN 's' THEN 'special'	
        WHEN 'f' THEN 'foreign table'	
    END AS "type",	
    pg_catalog.pg_get_userbyid(c.relowner) AS "owner",	
    pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) AS "size",	
    pg_catalog.obj_description(c.oid, 'pg_class') AS "description"	
FROM pg_catalog.pg_class c	
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace	
WHERE c.relkind ~ rel_type	
    AND n.nspname !~ '^pg_toast'	
    AND n.nspname ~ sch_name	
ORDER BY 1,2;	
END	
$$ LANGUAGE plpgsql STRICT;	

Dernière modification par Jiff (13/10/2014 00:11:33)