How to escape quotes from a json key in Postgresql?

Asked

Viewed 302 times

0

I have a function that receives JSON’s, it turns out that it is possible for the user to put as follows:

Normal:

'[{"nome":"João Carlos"}, {"nome":"Maria Silva"}]'

Problem:

'[{"nome":"João - "Carlos" - "}, {"nome":"Maria Silva"}]'

Note that it is possible for the user to place double or single quotes in the field name, how could I escape these quotes?

I use the function JSON_ARRAY_ELEMENTS to separate each JSON into rows.

SELECT 
    * 
FROM 
    json_array_elements('[{"nome":"João - "Carlos" - "}, {"nome":"Maria Silva"}]')
sql postgresql

  • have you tried using scape or repeated double quotes? type json_array_elements('[{"nome":"João - \"Carlos\" - "}, {"nome":"Maria Silva"}]') or json_array_elements('[{"nome":"João - ""Carlos"" - "}, {"nome":"Maria Silva"}]')

  • @Adrianogomes Until I thought, but I as I could put these bars inverted?

  • vc is using some programming language to run sql script?

  • Node.JS to run the postgresql function that receives JSON and inserts into table.

  • Instead of using the json_array_elements function and using only Node to create this list is an option?

  • The problem is that the json format is invalid, you need to inform the backslash when there are double quotes in a string, as @Adrianogomes suggested. If you don’t do this you won’t be able to parse. An alternative would be to process the data informed by the user by adding the bars.

  • In Node I could only do it using instead of a backslash, two, thus staying: var str = '[{"nome":"João - \\"Carlos\\" - "}, {"nome":"Maria Silva"}]';
var obj = JSON.parse(str);
console.log(obj);

Show 2 more comments

1 answer

1


There’s nothing wrong with your job!

This data in format JSON that you’re trying to process is malformed, see well:

'[{"nome":"João - "Carlos" - "}, {"nome":"Maria Silva"}]'

Note that there is no way to differentiate the quotation marks that should be escaped from the ones that should not be!

The problem is outside the scope of its function.

The caller (Caller) of its function needs to be careful to pass a JSON valid as argument when calling it.

Maybe this can help: https://www.postgresql.org/docs/9.5/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.