another very interesting addon. pavel stehule wrote, and tom lane commited “execute using" in plpgsql.
if you ever wrote in plpgsql you know that in some cases you had to use
EXECUTE 'insert into ...
usually it was neccessary when you wanted dynamic table or column names or you were writing code like this:
SELECT * FROM TABLE WHERE FIELD LIKE some_param || '%';
the problem with execute was that you had to build your sql to plain text version, together with all parameters.
can you spell “sql-injection"? ;-P
of course you could always use quote_literal (or lately quote_nullable), but it made the code unnecessarily long.
solution was well known – placeholders.
and finally it arrived.
now you can write a code like this:
CREATE OR REPLACE FUNCTION test(use_param_name TEXT, use_param_value TEXT) RETURNS void AS $BODY$ DECLARE use_sql_update TEXT; use_sql_insert TEXT; tempint INT4; BEGIN use_sql_update := 'UPDATE setup SET value = $2 WHERE param = $1'; use_sql_insert := 'INSERT INTO setup (param, value) VALUES ($1, $2)'; EXECUTE use_sql_update USING use_param_name, use_param_value; GET DIAGNOSTICS tempint = ROW_COUNT; IF tempint = 0 THEN EXECUTE use_sql_insert USING use_param_name, use_param_value; END IF; RETURN; END; $BODY$ LANGUAGE plpgsql;
please note that this code is perfectly safe against sql injection despite the fact that i didn't use any quote_* functions.
additional benefit is that it might be a bit faster in some cases, as “USING" avoids conversion from internal representation to text, and then from text to internal representation.
Just wanted to thank you for this line of blogging: I’m very much enjoying it. Apart from beginning able to see the new features (even the small ones) with examples, it’s nice knowing that progress is being made towards 8.4 on such a regular basis (and so soon).
Thanks!
This is long overdue. I’m glad to see a lot of progress already into the 8.4 branch with 8.3 having been released so recently. Kudos!
That’s a wonderful addition – but is there any possibility of this being made even more flexible in regards to the assigned parameters?
For example, I can imagine a function that would create (and then execute) a few different variations on a query – with each variation using different parameters.
Right now, each query variation would require a distinct EXECUTE USING statement that explicitly notes the parameters being passed in.
However, if there was some way to pass the parameters as an array (or other complex variable, perhaps a record), then EXECUTE using becomes a much more general-purpose tool, further encouraging its use.
While this treads near the “dangerous waters” territory of SQL queries and parameters being passed around between functions, I think that everything that can be done to encourage safe avodiance of SQL injection (which EXECUTE functionality is oh-so-vulnerable to) as an improvement.