CODEWITHSUNDEEP
pythonjsonpostgresqlsyntax-errorpsycopg2
Zeliax
Zeliax

Reputation: 5386

Inputting string into PostgreSQL results in syntax error at single quote

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.

Upvotes: 2

Views: 3462

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51406

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

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246073

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.

Upvotes: 3

Related Questions