Tuesday, June 3, 2008

EXECUTE USING feature in PostgreSQL 8.4

Hello,

My motivation for implementation USING feature in EXECUTE was simplifycation and securisation of dynamic SQL. I didn't thing about execution plans. I am speaking on plpgsql's lectures about risks related to dynamic SQL (mainly security risks). Prepared statements are safe, but has some gotcha http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/ , so there are some situations, where prepared statements are suboptimal. In plpgsql we have to use dynamic SQL
DECLARE
a integer;
r integer;
BEGIN
-- prepared statement
SELECT INTO r * FROM test WHERE c = a;
-- classic dynamic SQL witg risk of SQL injection
EXECUTE 'SELECT * FROM test WHERE c = ' || a INTO r;
-- dynamic SQL with USING (secure and with well execution plan)
EXECUTE 'SELECT * FROM test WHERE c = $1' INTO r USING a;
When we use USING clause, then dynamic SQL are secure (and readable too) . Every evalaution generate new execution plan with knowleage actual params. It's disadvantage (it's need some time - so don't use it in cycles), and advantage (generated plan will be propably better - not allways (sometimes we must play statistic alchemy game)).

I unlike SELECT composition. It's bad style:
CREATE OR REPLACE FUNCTION get_names(age integer, class integer)
RETURNS SETOF persons AS $$
DECLARE sql varchar = 'SELECT * FROM persons ';
BEGIN
IF age IS NOT NULL THEN
sql := sql || 'WHERE age = ' || age;
END IF;
IF class IS NOT NULL THEN
IF strpos(sql, 'WHERE') <> 0 THEN
sql := sql || ' AND ';
ELSE
sql := sql || 'WHERE ';
END IF;
sql := sql || ' class = ' || class;
END IF;
RETURN QUERY EXECUTE sql;
END $$ LANGUAGE plpgsql;
With USING clause I should to write:
..
BEGIN
RETURN QUERY EXECUTE
'SELECT * FROM persons
WHERE ($1 IS NULL or age = $1)
AND ($2 IS NULL or class = $2)
USING age, class;
END; $$ LANGUAGE plpgsql;
Optimaliser do predicat's reduction self.

Tom Lane demonstarated other example of EXECUTE USING:
create or replace function foo() returns trigger as $$
declare
t text;
begin
for i in 1 .. tg_argv[0] loop
execute 'select cast ( $1 . ' || tg_argv[i] || ' as text)'
into t using new;
raise notice '% = %', tg_argv[i], t;
end loop;
return new;
end $$ language plpgsql;
This code shows values of all field of NEW record in trigger's body. It wasn't possible before EXECUTE USING in plpgsql.

Any EXECUTE in plpgsql is door to you system for SQL injection. With EXECUTE USING your procedures can be 100% safe.
-- very unsafe function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
EXECUTE 'SELECT c FROM ' || _tablename || ' WHERE x=''' || _parameter || '''' INTO _r;
RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;

-- safe function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
EXECUTE 'SELECT c FROM ' || quote_ident(_tablename) || ' WHERE x='
|| quote_literal(_parameter) || INTO _r;
RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;

-- bullet proof function
CREATE OR REPLACE FUNCTION foo(_tablename varchar, _parameter varchar)
RETURNS int AS $$
DECLARE _r int;
BEGIN
EXECUTE 'SELECT c FROM ' || quote_ident(_tablename)::regclass || ' WHERE x=$1'
USING _parameter || INTO _r;
RETURN _r;
END;
$$ LANGUAGE plpgsql STRICT;

2 Comments:

At August 30, 2008 at 8:00 AM , Blogger Luca said...

SELECT composition can be necessary when the filter predicate requires additional joins which may not be necessary if the corresponding parameter is missing. I don't think the optimizer is smart enough to perform join elimination in these cases and sometimes, leaving the unnecessary joins may return incorrect or duplicate results or may simply be inefficient.

 
At August 31, 2008 at 4:46 AM , Blogger Pavel Stěhule said...

Just - test it.

 

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home