PostgreSQL: dealing with variables and apostrophes in dblink query

Question

I am trying to execute dblink, and into it's query I need to put variables, but the problem is that dblink executes string and before it's execution "converts" variables into that string.

p_int int := 1;
p_text text;
p_apostroph text := '''';
p_sql text;

p_text := (select columnXY from table2 where id =1);

p_sql: =
'insert into table (column1,column2,column3,column4) 
 select 1
 ' || p_int  || ',
 ' || p_apostroph || p_text || p_apostroph || ',
 ''text''
 ';

dblink_exec('connection' , p_sql);

As seen in the code problem comes with text variables (and nightmare with arrays), because I already have to manually put my text variable p_text between apostrophes, and by nature I do not know what it contains (I fill it from another table or user interface or any other source I do not control). So every time this variable contains and apostrophe it ends up with error because string get's broken. So this means I would have to come up with some complicated string analyse to counter all possibilities.

Is there any other way how to put variables into dblink query without putting them into string?

PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

as suggested by @a_horse_with_no_name I tried Dollar-Quoted String

create table table1 (column1 int, column2 int, column3 text, column4 text);
create table table2 (column1 text, id int);
insert into table2 values ('unsafe ''',1);
insert into table2 values ('safe ',2);

create or replace function test (p_id int) returns integer as 
$body$
declare
p_int int := 1;
p_text text;
p_apostroph text := '''';
p_sql text;
begin

p_text := (select column1 from table2 where id = p_id);

p_sql:= 
$$
insert into table1(column1,column2,column3,column4) 
   select 
   1,
   $$ || p_int  || $$,
   $$ || p_apostroph || p_text || p_apostroph || $$,
   'textz'
$$ 
 ;
raise notice '%', p_sql;
perform dblink_exec('connection' , p_sql );
return 1;
end;
$body$ LANGUAGE plpgsql

select * from test (2); -- no problem
select * from test (1); -- error

[Code: 0, SQL State: 42601] ERROR: unterminated quoted string at or near "' " Kde: while executing command on unnamed dblink connection SQL statement "SELECT dblink_exec('connection' , p_sql )" PL/pgSQL function s5_text_apostrop(integer) line 22 at PERFORM

Answer 1

You forgot a comma in the INSERT statement.

Your code is also vulnerable to SQL injection. Use format to avoid that and make your code more readable:

p_sql: = format(
            'insert into atable (column1, column2, column3, column4)'
            'select 1, %s, %L, %L',
            p_int, p_text, 'text'
         );