Re: design, plpgsql and sql injection in dynamically generated sql

Hello

I am not sure, if it's possible for you. PostgreSQL 8.4 has EXECUTE
USING clause, it is 100% safe.

Pavel

2009/8/17 Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>:
> I've several list of items that have to be rendered on a web apps in
> the same way.
>
> The structure is:
>
> create table items (
>  itemid int primary key,
>  /* some fields */
> );
>
> create table headert1 (
>  ht1 int primary key,
>  /* several other fields that varies in nature */
> );
>
> create table itemlistt1 (
>  ht1 int references headert1 ht1,
>  itemid references items (itemid)
> );
>
> The query always match this pattern:
>
> select i.fieldA, i.fieldB, ..., from itemlistt1 il
>  join items i on i.itemid=il.itemid
>  where il.ht1=[somevalue];
>
> the nature of the lists and their usage pattern is very different.
> So unless someone come up with a better design I still would like to
> keep the item lists in different tables.
>
> I'd like to build up a function that takes the name of the table and
> the key to dynamically build up the query... but I don't know what
> should I use to sanitize them.
>
> create or replace function getitemlist(listtable text, listkey text,
> keyvalue int,
> , out ....) rerurns setof records as
> $$
> declare
>  statement text;
> begin
>  statement:='select i.fieldA, i.fieldB, ..., from ' ||
>   escapefunc1(listtable) ||
>   ' il join items i on i.itemid=il.itemid ' ||
>   ' where il.' || escapefunc2(listtable) || '=' || keyvalue;
>  return query execute statement; // can I?
>
> is it quote_ident the right candidate for escapefuncN?
>
> But this is still at risk of misuse... (eg. passing ('items',
> 'fieldA', 1) may return something that was not meant to be seen.
>
> One way would be to build up a table of permitted (table, key) and
> then just pass the table and the key value.
> What should be the equivalent of quote_ident in PHP?
>
> thanks
>
> --
> Ivan Sergio Borgonovo
> http://www.webthatworks.it
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>