Inputting string into PostgreSQL results in syntax error at single quote

Question

I am running through a very large data set with a json object for each row. And after passing through almost 1 billion rows I am suddenly getting an error with my transaction.

I am using Python 3.x and psycopg2 to perform my transactions.

The json object I am trying to write is as follows:

{"message": "ConfigManager: Config if valid JSON but doesn't seem to have the correct schema. Adopting new config aborted."}

and psycopg2 is reporting the following:

syntax error at or near "t"
LINE 3: ...": "ConfigManager: Config if valid JSON but doesn't seem to ...

I am aware that the problem is the single quote, however I have no idea of how to escape this problem so that the json object can be passed to my database.

My json column is of type jsonb and is named first_row

So if I try:

INSERT INTO "356002062376054" (first_row) VALUES {"message": "ConfigManager: Config if valid JSON but doesn't seem to have the correct schema. Adopting new config aborted."}

I still get an error. And nothing I seem to do will fix this problem. I have tried escaping the t with a \ and changing the double quotes to single qoutes, and removing the curly brackets as well. Nothing works.

I believe it's quite important that I am remembering to do

`json.dumps({"message": "ConfigManager: Config if valid JSON but doesn't seem to have the correct schema. Adopting new config aborted."})` in my python code and yet I am getting this problem.

you have to escape ' and put values with quotes. or use different quotes, like $not_sgl_quote$ or better prepare statement and then pass value — Vao Tsun
– Vao Tsun, Commented Jan 31, 2017 at 9:51
Eg: INSERT INTO "356002062376054" (first_row) VALUES e'{"message": "ConfigManager: Config if valid JSON but doesn\'t seem to have the correct schema. Adopting new config aborted."}' — Vao Tsun
– Vao Tsun, Commented Jan 31, 2017 at 10:23
my bad: INSERT INTO "356002062376054" (first_row) SELECT e'{"message": "ConfigManager: Config if valid JSON but doesn\'t seem to have the correct schema. Adopting new config aborted."}' — Vao Tsun
– Vao Tsun, Commented Jan 31, 2017 at 10:42
or: INSERT INTO "356002062376054" (first_row) values (E'{"message": "ConfigManager: Config if valid JSON but doesn\'t seem to have the correct schema. Adopting new config aborted."}'); — Vao Tsun
– Vao Tsun, Commented Jan 31, 2017 at 10:43

Laurenz Albe · Accepted Answer · 2017-01-31 10:26:45Z

3

You have to double a single quote to escape it in SQL.

But the fact that you have this problem means that your code is vulnerable to SQL injection. You should use parameterized statements and feed the string as parameter when executing the statement. If you do this, the problem will go away.

The psycopg2 documentation has this dire, but well warranted warning:

Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

edited Jan 31, 2017 at 10:26

answered Jan 31, 2017 at 9:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zeliax Over a year ago

What is a prepared statement and how exactly should I escape it with double single quotes. Some examples would be preferred :P

Laurenz Albe Over a year ago

I have added a link to the psycopg2 documentation. That should make it clear.

Zeliax Over a year ago

Thanks. I am using "{}".format(some_str) to do it.

Laurenz Albe Over a year ago

If you get a problem with single quotes, the solution is not good. If you really don't want to use the best solution, you'll have to turn all ' to '' yourself and pray that you don't forget any.

Vao Tsun · Accepted Answer · 2017-01-31 10:53:21Z

2

You can use dollar sign constraints or C style escape for this. Eg per docs:

An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'

So then:

INSERT INTO "356002062376054" (first_row) SELECT e'{"message": "ConfigManager: Config if valid JSON but doesn\'t seem to have the correct schema. Adopting new config aborted."}'

or

INSERT INTO "356002062376054" (first_row) values (E'{"message": "ConfigManager: Config if valid JSON but doesn\'t seem to have the correct schema. Adopting new config aborted."}');

should work for you

answered Jan 31, 2017 at 10:53

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Inputting string into PostgreSQL results in syntax error at single quote

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related