Re: Preventing SQL Injection in PL/pgSQL in psql

On 9 May 2006 17:04:31 -0700, Karen Hill <karen_hill22(at)yahoo(dot)com> wrote:
> Is my understanding correct that the following is vulnerable to SQL
> injection in psql:
>
> CREATE OR REPLACE FUNCTION fx ( my_var bchar)
> RETURNS void AS
> $$
> BEGIN
> INSERT INTO fx VALUES ( my_var ) ;
> END;
> $$
> LANGUAGE 'plpgsql' VOLATILE

no, IMO this is the safest and best option. Quoting, etc is handled
by the plpgsql processor (this is one of the things that make it so
great).

> Where this is NOT subject to SQL injection:
>
> CREATE OR REPLACE FUNCTION fx ( my_var bpchar)
> RETURNS void AS
> $$
> BEGIN
> EXECUTE ' INSERT INTO fx VALUES ( ' || quote_literal( my_var) || ' ); '
> END;
> $$ LANGUAGE 'plpgsql' VOLATILE

If you are making dynamic sql statements this (quote_literal) is the
preferred way to do quotations...otherwise there is potential for
malformed statement. My rule of thumb is to use static sql when you
can, dynamic when you have to.

Merlin