How Do I Safely Insert Text with Single Quotes into a PostgreSQL Database?

Handling Single Quotes in PostgreSQL Text Insertion

Inserting text containing single quotes into a PostgreSQL database requires careful handling to avoid syntax errors and security vulnerabilities like SQL injection. This guide outlines safe and effective methods.

Escaping Single Quotes: The Double-Quote Method

The simplest approach is to double up single quotes within the string. For instance, to insert "user's log", use this query:

<code class="language-sql">INSERT INTO test VALUES (1, 'user''s log');</code>

While escaping with a backslash () is possible, it's less preferred and should be reserved for situations where doubling quotes isn't feasible.

Dollar-Quoted Strings: A Robust Solution

For complex strings or situations with nested quotes, PostgreSQL's dollar-quoted strings provide a superior solution. Enclosed in $$, these strings allow for easier handling of special characters, including the dollar sign itself (which needs escaping if present).

<code class="language-sql">$$escape ' with ''$$</code>

Using PostgreSQL's Built-in Quoting Functions

PostgreSQL offers functions like quote_literal(), quote_nullable(), and format() (with the %L specifier) to automatically generate properly quoted strings for SQL queries, eliminating manual escaping and reducing the risk of errors.

<code class="language-sql">format('%L', string_var)</code>

Prioritize Prepared Statements for Security

Crucially, these string escaping techniques are not a replacement for prepared statements or parameterized queries. These methods are the most effective way to prevent SQL injection attacks by separating data from the SQL command itself. Always prioritize prepared statements for secure database interactions.

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