Re: PL/pgSQL EXECUTE quote_ident(), and SQL injection

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(at)broadpark(dot)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;
>>
>
>