Обсуждение: PL/pgSQL EXECUTE quote_ident(), and SQL injection

Is there any known way to inject SQL into a function similar to this?

create function testinjection(text,integer)
 returns void as
$BODY$
declare
begin
execute 'update '||quote_ident($1)||' set c=null where id='||$2;
return;
end;
$BODY$
language 'plpgsql' volatile security definer;
grant execute on function testinjection(text,integer) to public;

Since this stripped down example function looks a bit meaningless, I'd like to rephrase the question to make it more clear: Does quote_ident() prevent all ways of trying to inject SQL into $1 so that the testinjection function cannot be used to do anything else than setting column c to null in an existing table?

----- Original Message -----
> Is there any known way to inject SQL into a function similar to this?
>
> create function testinjection(text,integer)
>  returns void as
> $BODY$
> declare
> begin
> execute 'update '||quote_ident($1)||' set c=null where id='||$2;
> return;
> end;
> $BODY$
> language 'plpgsql' volatile security definer;
> grant execute on function testinjection(text,integer) to public;
>

Hello

I thing, so quote_ident is secure.

you can add more security via explicit casting to regclass type -
check if value of variable is really relation identifier:

postgres=# select quote_ident('omega b')::regclass;
 quote_ident
-------------
 "omega b"
(1 row)

postgres=# select quote_ident('omega')::regclass;
ERROR:  relation "omega" does not exist
postgres=# select quote_ident('select * from some')::regclass;
ERROR:  relation "select * from some" does not exist
postgres=#

I hope so this method is 100%

regards
Pavel Stehule



2009/6/26 Knut P. Lehre <knutpl@broadpark.no>:
> Since this stripped down example function looks a bit meaningless, I'd like
> to rephrase the question to make it more clear: Does quote_ident() prevent
> all ways of trying to inject SQL into $1 so that the testinjection function
> cannot be used to do anything else than setting column c to null in an
> existing table?
>
> ----- Original Message -----
>> Is there any known way to inject SQL into a function similar to this?
>>
>> create function testinjection(text,integer)
>>  returns void as
>> $BODY$
>> declare
>> begin
>> execute 'update '||quote_ident($1)||' set c=null where id='||$2;
>> return;
>> end;
>> $BODY$
>> language 'plpgsql' volatile security definer;
>> grant execute on function testinjection(text,integer) to public;
>>
>
>