Save a string with special characters such as quotation marks or backslashes in the postgresql table

Question

I have a string with a value

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

But when pasting into a postgresql table, I get an error:

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

This is probably because my string contains single quotes. I do not know the meaning of the string. Every time it continues to change and may contain special characters, such as \ or something, because I read from a file and save it to the postgres database.

Please give a general solution to exit such characters.

Answer 1

In accordance with the SQL standard, quotation marks are separated by doubling them, that is:

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

If you replace each quote in the text with two single quotes, it will work.

Usually a backslash skips the next character, but literal backslashes are escaped in the same way with two backslashes "

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

Answer 2

You seem to be using Java and JDBC. Read the JDBC tutorial that describes how to use parameterized queries to safely insert data without risking SQL injection problems .

Read the JDBC tutorial's prepared instructions section and these simple examples in different languages, including Java .

Since you have backslashes and not just 'single quotes' problems, I would say that you use PostgreSQL 9.0 and older, the default is standard_conforming_strings = off . In newer versions, the backslash is only special if you use the PostgreSQL E'escape strings' extension. (That's why you always include your version of PostgreSQL in questions ).

You can also learn:

• Why you should use the prepared instructions .

• PostgreSQL documentation on the lexical structure of SQL queries .

Although you can explicitly specify values, this happens with an error that is slow and ineffective. You must use parameterized queries (prepared statements) to safely insert data.

In the future, please include the code snippet you came across and information about the language you use, version of PostgreSQL, etc.

If you really need to manually delete the lines, you need to make sure that standard_conforming_strings included and double quotes, for example don''t manually escape text ; or use PostgreSQL-specific E'escape strings where you \'backslash escape\' quotes' . But in fact, use prepared statements, it’s easier.

Answer 3

You can use double dollar quotation to avoid special characters in your string. The above query 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 does not mix with values, you can add any characters from $ 2, for example, insert into table (column) values ($aesc6$I'm OK$aesc6$) .

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

Answer 4

Some possible approaches:

• use prepared statements
• convert all special characters to equivalent html objects.
• use base64 encoding when saving a string and decoding base64 when reading a string from a db table.

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

Approach 3 (base64 encoding) converts all characters into hexadecimal characters without loss of information. But you will not be able to perform full-text searches using this approach.

Answer 5

The literals in SQLServer begin with N as follows:

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