Correctly inserting literals in PL/PgSQL EXECUTE dynamic queries

Question

The following is part of a plpgsql function. The problem is that the result of source_geom and target_geom is a character varying data type, and therefore I need to surround the both source_geom and target_geom in quotes(' '). The thing is that in plpgsql language how I don't know I can do it.
Here's what I have at the moment:

 EXECUTE 'update ' || quote_ident(geom_table) || 
        ' SET source = ' || source_geom || 
        ', target = ' || target_geom ||
        ' WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;

The error that I am having is the following;

ERROR:  syntax error at or near "C03B9E3B66052D400DDEFC2BD0F24140"
LINE 1: ...pdate track_points SET source = 0101000020E6100000C03B9E3B66...
                                                             ^
QUERY:  update track_points SET source = 0101000020E6100000C03B9E3B66052D400DDEFC2BD0F24140, target = 0101000020E610000075690DEF83052D40F88E75CCD4F24140 WHERE ogc_fid =  2
CONTEXT:  PL/pgSQL function "create_network" line 26 at EXECUTE statement

Please any suggestions how I can solve this problem.?

Impenitent

Answer 1

Use extra quotes:

EXECUTE 'update ' || quote_ident(geom_table) || 
        ' SET source = ''' || source_geom || ''' 
        , target = ''' || target_geom || '''
        WHERE ' || quote_ident(gid_cname) || ' =  ' || _r.id;

Ligurian

quote_literal to avoid SQL injection? – Patisserie

Answer 2

Using EXECUTE ... USING with the format() function and its format specifiers will make your code much safer, simpler, easier to read and probably faster.

---

SQL INJECTION WARNING: If you ever accept source_geom or target_geom from the end user, your code is potentially vulnerable to SQL injection. It is important to use parameterized statements (like EXECUTE ... USING) or failing that, paranoid quoting to prevent SQL injection attacks. Even if you don't think your function takes user input you should still harden it against SQL injection, because you don't know how your app will evolve.

---

If you're on a newer PostgreSQL with the format function your code can be significantly simplified into:

EXECUTE format('update %I SET source = %L, target = %L WHERE %I = %L',
    geom_table, source_geom, target_geom, gid_cname, _r.id);

... which handles identifier (%I) and literal (%L) quoting for you using format specifiers so you don't have to write all that awful || concatenation and quote_literal/quote_ident stuff.

Then, as per the documentation on EXECUTE ... USING you can further refine the query into:

EXECUTE format(
    'update %I SET source = $1, target = $2 WHERE %I =  $3',
    geom_table, gid_cname
) USING source_geom, target_geom, _r.id;

which turns the query into a parameterised statement, clearly separating parameters from identifiers and reducing string processing costs for a more efficient query.

Chavers

EXECUTE format('...') USING is probably best. A parameters in format function should be used when cannot be used in USING clause - column, table names. – Reeve

Nice explanation and lifesaver. I have just had a situation where I have had to update something based on a passed in table name and a dynamically created geometry and I think I finally understand how to use format and using together like this. – Shroud

@Craig Ringer awesome explanation and a lifesaver. – Spinel