Re: How can I refer to an ANYELEMENT variable in postgresql dynamic SQL?

Christopher Currie wrote
> Cross-posting from stackoverflow in the hope of getting some additional
> eyes on the question.
>
> http://stackoverflow.com/questions/28505782/how-can-i-refer-to-an-anyelement-variable-in-postgresql-dynamic-sql
>
>   update_stmt := format(
>     'UPDATE %s SET %s WHERE %s',
>     pg_typeof(target),
>     array_to_string(setters, ', '),
>     array_to_string(selectors, ' AND ')
>   );
>
> [...]
>
> EXECUTE update_stmt USING target;
>
> ERROR: there is no parameter $2: SELECT * FROM upsert(ROW(1,'{}')::doodad)
>
> EXECUTE update_stmt USING target.*;
>
> ERROR: query "SELECT target.*" returned 2 columns: SELECT * FROM
> upsert(ROW(1,'{}')::doodad)

Haven't tried to determine or explain where you are exposing yourself to SQL
injection; but I'm pretty sure you are.

I suggest you learn the difference between a "simple string", an "SQL
identifier", and a "SQL literal" as described in the format function
documentation.  Choosing the correct one will offer some protection that you
are forgoing in your current code.  It will also help you better understand
where you can place parameters and where you have to inject data into the
source SQL string.

With dynamic SQL putting the word "target" into the SQL string causes it to
look within that string for a source relation named "target".  It will not
look to the calling environment (i.e., pl/pgsql) for a variable of that
name.

Your update_stmt above doesn't have any parameter placeholders so adding a
USING clause to the EXECUTE command is going to fail.

I have no clue why you are making use of "pg_typeof(...)".

Consider that (I think...): "UPDATE %s SET", pg_typeof(1.00) => "UPDATE
numeric SET"

The function itself also has no protection from race conditions...

Hopefully between the above observations and the documentation you will be
able to at least build up an executable dynamic sql statement - whether it
is safe is another matter entirely.

I would suggest you try building up simpler statements first.

Lastly, I'm not sure how or whether your issues have anything to do with
ANYELEMENT; but I am doubtful that is the case.

David J.





--
View this message in context:
http://postgresql.nabble.com/How-can-I-refer-to-an-ANYELEMENT-variable-in-postgresql-dynamic-SQL-tp5837899p5837927.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.