Thread: design, plpgsql and sql injection in dynamically generated sql
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
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
>
On Mon, Aug 17, 2009 at 12:36:49PM +0200, Ivan Sergio Borgonovo wrote: > I've several list of items that have to be rendered on a web apps in > the same way. [..] > 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. As you've explained it, I'd be tempted to have a function for each table. You're going to have special code outside the database for each one, so why not a (small) amount for each one inside the database. > 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. I'd stay away from this; they may be doing similar things at the moment but if they really are as different as you seem to suggest then having them as separate functions would make this easier. If they really are that similar then you should have all the data in one table anyway! -- Sam http://samason.me.uk/
On Mon, 17 Aug 2009 12:48:21 +0200
Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> I am not sure, if it's possible for you. PostgreSQL 8.4 has EXECUTE
> USING clause, it is 100% safe.
Sorry I don't get it.
How can I use USING safely when the substitution involves a table
name?
The examples I've seen just involve column values.
Where is the corresponding fine manual page?
Still I don't get how USING could make safer plpgsql functions...
well... I'm going to check some prejudices I have on pg functions
firts...
I thought that if you passed eg. text to
create or replace function typetest(a int) returns text as
$$
begin
raise notice 'is this an int? %', a;
-- don't do anything else with a
and calling
select * from typetest('tonno');
was going to raise an error anyway.
So somehow I find the example here
http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html
not really helpful in understanding what's going on.
Maybe an example with text comparing a version using quote_literal
and one using USING could be clearer...
or am I completely missing the point?
far from an optimal solution I've built a "client side" array of
permitted table, key to dynamically build the query on the client
side.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
2009/8/18 Ivan Sergio Borgonovo <mail@webthatworks.it>:
> On Mon, 17 Aug 2009 12:48:21 +0200
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>> Hello
>>
>> I am not sure, if it's possible for you. PostgreSQL 8.4 has EXECUTE
>> USING clause, it is 100% safe.
>
> Sorry I don't get it.
>
> How can I use USING safely when the substitution involves a table
> name?
>
> The examples I've seen just involve column values.
>
> Where is the corresponding fine manual page?
>
> Still I don't get how USING could make safer plpgsql functions...
> well... I'm going to check some prejudices I have on pg functions
> firts...
some unsafe function:
create or replace function foo(tablename varchar, parameter varchar)
returns int as $$
declare _result integer;
begin
execute 'select i from ' || table_name || ' where x = \'' ||
parameter || '\'' into _result;
return result;
end;
$$ language plpgsql strict;
I thing, so there are two safe variants
create or replace function foo(tablename varchar, parameter varchar)
returns int as $$
declare _result integer;
begin
execute 'select i from ' || quote_ident(table_name) || ' where x = '
|| quote_literal(parameter) into _result;
return _result;
end;
$$ language plpgsql strict;
or
create or replace function foo(tablename varchar, parameter varchar)
returns int as $$
declare _result integer;
begin
execute 'select i from ' || table_name::regclass || ' where x = $1'
using parameter into _result;
return _result;
end;
$$ language plpgsql strict;
"USING" works like prepared statements.
regards
Pavel Stehule
>
> I thought that if you passed eg. text to
> create or replace function typetest(a int) returns text as
> $$
> begin
> raise notice 'is this an int? %', a;
> -- don't do anything else with a
>
> and calling
>
> select * from typetest('tonno');
>
> was going to raise an error anyway.
>
> So somehow I find the example here
> http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html
> not really helpful in understanding what's going on.
>
> Maybe an example with text comparing a version using quote_literal
> and one using USING could be clearer...
>
> or am I completely missing the point?
>
> far from an optimal solution I've built a "client side" array of
> permitted table, key to dynamically build the query on the client
> side.
>
> --
> 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
>
On Tue, 18 Aug 2009 12:38:49 +0200 Pavel Stehule <pavel.stehule@gmail.com> wrote: > some unsafe function: I suspected something similar. I think many would appreciate if you put these examples here http://www.okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html and substitute the int example there with the text one. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
2009/8/18 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Tue, 18 Aug 2009 12:38:49 +0200 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > >> some unsafe function: > > I suspected something similar. > > I think many would appreciate if you put these examples here > http://www.okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html > and substitute the int example there with the text one. actualized http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html regards Pavel > > thanks > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > >