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@webthatworks.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>