Correct literal insertion into dynamic PL / PgSQL EXECUTE queries

Question

The following is part of the plpgsql function. The problem is that the result of source_geom and target_geom is a character varying data type, so I need to surround both source_geom and target_geom in quotation marks (''). The fact is that in the language plpgsql, as I do not know, I can do this.
Here is 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 I encountered 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.?

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; 

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 WARNING 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 (e.g. EXECUTE ... USING ) or, if not, paranoid quotation to prevent SQL injection attacks. Even if you don’t think your function requires user input, you should still strengthen it against SQL injection because you don’t know how your application will develop.

---

If you use the new PostgreSQL with the format function , your code can be greatly simplified:

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

... which processes the identifier ( %I ) and the literal ( %L ) for you using format specifiers, so you don’t have to write all these terrible || concatenation and quote_literal / quote_ident things.

Then, according to the documentation for EXECUTE ... USING , you can further refine the request:

 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 a query into a parameterized statement, clearly separating parameters from identifiers and reducing the cost of processing strings for a more efficient query.