design, plpgsql and sql injection in dynamically generated sql

Поиск
Список
Период
Сортировка
От Ivan Sergio Borgonovo
Тема design, plpgsql and sql injection in dynamically generated sql
Дата
Msg-id 20090817123649.034031a4@dawn.webthatworks.it
обсуждение исходный текст
Ответы Re: design, plpgsql and sql injection in dynamically generated sql
Re: design, plpgsql and sql injection in dynamically generated sql
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: "David De Maeyer"
Дата:
Сообщение: binary timestamp conversion
Следующее
От: "Daniel Verite"
Дата:
Сообщение: Re: Generating random unique alphanumeric IDs