Обсуждение: Preventing SQL Injection in PL/pgSQL in psql

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

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


Is this understanding correct?

On 9 May 2006 17:04:31 -0700, Karen Hill <karen_hill22@yahoo.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

"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 9 May 2006 17:04:31 -0700, Karen Hill <karen_hill22@yahoo.com> wrote:
>> Is my understanding correct that the following is vulnerable to SQL
>> injection in psql:
> ...
> no, IMO this is the safest and best option.

Neither of the options that Karen shows are dangerous.  What would be
dangerous is building a SQL command string and feeding it to EXECUTE
*without* using quote_literal.

I agree with Merlin that you shouldn't use EXECUTE unless you have to
--- it's both much slower than a precompiled statement, and much more
vulnerable to security mistakes.

            regards, tom lane