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