From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: design, plpgsql and sql injection in dynamically generated sql |
Date: | 2009-08-18 10:38:49 |
Message-ID: | 162867790908180338x76d43cb5x4961c325cc97a2e0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2009/8/18 Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>:
> On Mon, 17 Aug 2009 12:48:21 +0200
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Mason | 2009-08-18 10:42:20 | Re: comparing NEW and OLD (any good this way?) |
Previous Message | Terry Lee Tucker | 2009-08-18 10:32:25 | Configuration Question |