Store string with special characters like quotes or backslash in postgresql table

Question

I have a string with value

'MAX DATE QUERY: SELECT iso_timestamp(MAX(time_stamp)) AS MAXTIME FROM observation WHERE offering_id = 'HOBART''

But on inserting into postgresql table i am getting error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "HOBART".

This is probably because my string contains single quotes. I don't know my string value. Every time it keeps changing and may contain special characters like \ or something since I am reading from a file and saving into postgres database.

Please give a general solution to escape such characters.

Answer 1

As per the SQL standard, quotes are delimited by doubling them, ie:

insert into table (column) values ('I''m OK')

If you replace every single quote in your text with two single quotes, it will work.

Normally, a backslash escapes the following character, but literal backslashes are similarly escaped by using two backslashes"

insert into table (column) values ('Look in C:\\Temp')

Answer 2

You can use double dollar quotation to escape the special characters in your string. The above query as mentioned insert into table (column) values ('I'm OK')

changes to insert into table (column) values ($$I'm OK$$).

To make the identifier unique so that it doesn't mix with the values, you can add any characters between 2 dollars such as
insert into table (column) values ($aesc6$I'm OK$aesc6$).

here $aesc6$ is the unique string identifier so that even if $$ is part of the value, it will be treated as a value and not a identifier.

Answer 3

You appear to be using Java and JDBC. Please read the JDBC tutorial, which describes how to use paramaterized queries to safely insert data without risking SQL injection problems.

Please read the prepared statements section of the JDBC tutorial and these simple examples in various languages including Java.

Since you're having issues with backslashes, not just 'single quotes', I'd say you're running PostgreSQL 9.0 or older, which default to standard_conforming_strings = off. In newer versions backslashes are only special if you use the PostgreSQL extension E'escape strings'. (This is why you always include your PostgreSQL version in questions).

You might also want to examine:

• Why you should use prepared statements.

• The PostgreSQL documentation on the lexical structure of SQL queries.

While it is possible to explicitly quote values, doing so is error-prone, slow and inefficient. You should use parameterized queries (prepared statements) to safely insert data.

In future, please include a code snippet that you're having a problem with and details of the language you're using, the PostgreSQL version, etc.

If you really must manually escape strings, you'll need to make sure that standard_conforming_strings is on and double quotes, eg don''t manually escape text; or use PostgreSQL-specific E'escape strings where you \'backslash escape\' quotes'. But really, use prepared statements, it's way easier.

Answer 4

Some possible approaches are:

1. use prepared statements
2. convert all special characters to their equivalent html entities.
3. use base64 encoding while storing the string, and base64 decoding while reading the string from the db table.

Approach 1 (prepared statements) can be combined with approaches 2 and 3.

Approach 3 (base64 encoding) converts all characters to hexadecimal characters without loosing any info. But you may not be able to do full-text search using this approach.

Answer 5

Literals in SQLServer start with N like this:

update table set stringField = N'/;l;sldl;'''mess'