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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
