Search code examples
postgresqlplpgsql

How to use $$ as string value inside pl/pgsql

Im building parameterised sql using pgp and wrapping the generated sql inside DO $$ END $$. Problem is when I try saving password (varchar) some of the password hashes contain $$ which causes issues since the psql is inside DO $$ END $$. My question, Is it possible to somehow escape $$ or save password hash with $$ inside it but without plpgsql erroring out.

sample generated plpgsql

DO $$ DECLARE parent_user int; 
BEGIN  CREATE TEMP TABLE IF NOT EXISTS tmp_f6a8e872_b346_4184_889d_52ffeded463f (id int); 
INSERT INTO form_user(firstname,lastname,displayname,job_title,username,authentication_type,password,password2,password_reset,role,comm_portal,issue_ticket,email,rec_sms,created_by,created_on,auto_name) 
VALUES ('delete','delete','delete','admin','delete','password','$p5k2$$9kenbgnr$XuoSekYV4qQlDE0NkNEs2UGJEEIWtjG/','$p5k2$$7cxgxz4n$JSkgV8oGg72S1O6i2xYZSLEU2Yd6bdCO','0','admin','No','No','delete@delete.com','No',22,'11/5/2023 19:59:45','delete delete (admin)') RETURNING id INTO parent_user; 
INSERT INTO tmp_f6a8e872_b346_4184_889d_52ffeded463f (id) VALUES (parent_user);
END; $$;

Error error: syntax error at or near "9"

Solution

  • While dealing with a single known string constant, just add a token to your dollar-quotes that is not repeated in the string:

    DO
$do$  -- $do$ instead of just $$
BEGIN
   CREATE TEMP TABLE tmp_tbl AS 
   WITH ins1 AS (
      INSERT INTO form_user(firstname,lastname,displayname,job_title,username,authentication_type,password,password2,password_reset,role,comm_portal,issue_ticket,email,rec_sms,created_by,created_on,auto_name) 
      VALUES ('delete','delete','delete','admin','delete','password','$p5k2$$9kenbgnr$XuoSekYV4qQlDE0NkNEs2UGJEEIWtjG/','$p5k2$$7cxgxz4n$JSkgV8oGg72S1O6i2xYZSLEU2Yd6bdCO','0','admin','No','No','delete@delete.com','No',22,'11/5/2023 19:59:45','delete delete (admin)')
      RETURNING id
      )
   TABLE ins;
END
$do$;

    Also, you don't need the intermediate step with a variable. I would just use the SQL without the wrapping DO command, which doesn't add anything useful in the example.

    When dealing with unknown string input, and you need a quoted string literal (like for dynamic SQL, unlike the given example!) use the function quote_literal() or the equivalent format('%s', $input) to build a string with a nested string literal. Each produces a valid string literal automatically, escaping all special characters as needed. (There must be an input variable or parameter in such a case for initial input.

    See: