Re: problem with pl/pgsql - Mailing list pgsql-general

Ben,

I can't comment on your particular problem, but if you look through the
posts on this list, you will find that it's a bad idea to execute
dynamic queries which include input you can't control.
In your example this could lead to problems if you don't escape properly
the artist name. Consider the following artist name (take everything
literally, including the quotes):
'); delete from artistSet; --
Your function will end up executing the truncated select AND the delete,
ignoring the rest.
I'm not sure if the above "artist name" is properly escaped itself to
produce this result, but you get the idea.
The general recommendation is to use prepared queries when including
output from exterior, or at least do paranoid escaping.

HTH,
Csaba.


On Thu, 2003-04-17 at 07:40, Ben wrote:
> I'm trying to build a table function using pl/pgsql. I've done this
> successfully many times in the past, but this is the first time I've tried
> to build up and execute a dynamic query, and either it or I (probably I)
> am getting confused.
>
> The error I get is:
> foo=# select * from fetch_artistset_by_artists('{1}');
> WARNING:Â  Error occurred while executing PL/pgSQL functionfetch_artistset_by_artists
> WARNING:Â  line 30 at return next
> ERROR:Â  Attribute "r" not found
>
> The stored proc is:
> CREATE or REPLACE FUNCTION Fetch_ArtistSet_By_Artists(integer[]) RETURNS setof integer AS
> 'DECLARE
>         artists alias for $1;
>         index   integer := 1;
>         total   integer := 0;
>         r       record;
>         q       text := ''select setID from (select setID,count(*) as c from artistSet where'';
>     BEGIN
>         WHILE artists[index] > 0
>         LOOP
>             if index > 1
>             then
>                 q := q || '' or'';
>             end if;
>
>             q := q || '' artistID = '' || artists[index];
>
>             total := total + 1;
>             index := index + 1;
>         END LOOP;
>
>         if total = 0
>         then
>             RETURN;
>         end if;
>
>         q := q || '' group by setID) as foo where c = '' || total;
>
>         for r in execute q
>         LOOP
>             RETURN next r;
>         END LOOP;
>         RETURN;
>     END;
> ' LANGUAGE 'plpgsql';
>
> The schema is:
> create table artistSet
> (
>     memberState integer not null,
>     setID integer not null references artistSetInfo(id),
>     artistID integer not null references artist(id),
>
>     primary key (setID, artistID)
> );
>
> Any thoughts? The error is on the "RETURN next r;" line, which has worked
> fine for me in the past, unless I'm making a typo I'm just not seeing. So
> that leads me to believe it has something to do with the dynamic query,
> but I don't know what it would be.....
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>