April 2nd, 2008 by depesz | Tags: , , , , | 5 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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.

  1. 5 comments

  2. Apr 2, 2008

    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!

  3. # Chris
    Apr 2, 2008

    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!

  4. # jdbo
    Apr 3, 2008

    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.

  1. 2 Trackback(s)

  2. Apr 2, 2008: darh|blog
  3. Mar 8, 2010: dynamic SQL parameters in PL/PgSQL functions « imitatio creatio

Leave a comment