Escape single quote in the input parameter in a Function in postgresql

Question

I have a JSON which comes with single quote for one of the fields like below. A function with JSON as Input parameter is created and be called directly from the application. I would have used regexp_replace at least if it is read into the function.

for ex:

select '{
  "phrase": "foo",
  "phrase_1": "'bar'"
  }' :: json 

syntax error at or near "'"

  }'"
LINE 3:   "phrase_1": "'bar'"

The output of this is an error. so my actual problem is this json is directly read into my function.

create or replace function f_n(in json) -- it is failing to read here
returns text
---
---
end;
$$

so what is that I can do here to avoid such problem in postgresql?

score 3 · Accepted Answer · 2019-07-26 12:23:00Z

3

Single quotes need to be duplicate in SQL to escape them:

select '{
  "phrase": "foo",
  "phrase_1": "''bar''"
  }'::json;

alternatively use Postgres' dollar quoting to avoid that:

select $j${
  "phrase": "foo",
  "phrase_1": "'bar'"
  }$j$::json;

When passing that as an argument to a function, this works the same:

select f_n('{
  "phrase": "foo",
  "phrase_1": "''bar''"
  }'::json);

or

select f_n($j${
      "phrase": "foo",
      "phrase_1": "'bar'"
      }$j$::json);

Collectives™ on Stack Overflow

Escape single quote in the input parameter in a Function in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related