How to Safely Insert Single Quotes into a PostgreSQL Database?

Insert text containing single quotes in PostgreSQL database

Inserting text containing single quotes into a PostgreSQL table requires special handling to avoid syntax errors.

Escape single quotes

Standard SQL escaping involves doubling single quotes, such as 'user''s log'. PostgreSQL also supports using backslash escapes, such as E'user's log'. However, it is generally recommended to use the standard double quote escaping method.

If you need to handle multiple levels of escaping or nested quotes, you can use dollar signs to quote the string. For example, $$escape ' with ''$$ or $token$escape ' with ''$token$.

Prepare value

When inserting values ​​containing single quotes into the database, they must be quoted correctly to prevent errors. PostgreSQL provides several functions for this:

• quote_literal(): Escape single quotes in a string and return the quoted value.
• quote_nullable(): Similar to quote_literal(), but outputs NULL for empty input.
• format(): Use the %L format specifier to apply quotes equivalent to quote_nullable().

Avoid using connections

Using functions such as concat() and concat_ws() to escape single quotes is not recommended as they do not escape nested quotes or backslashes.

Example

To insert the required values ​​into the test table:

<code class="language-sql">insert into test values (1, quote_literal('user''s log'));
insert into test values (2, quote_literal('my user''s'));
insert into test values (3, quote_literal('customer''s'));</code>

The above is the detailed content of How to Safely Insert Single Quotes into a PostgreSQL Database?. For more information, please follow other related articles on the PHP Chinese website!