How to Properly Escape Single Quotes in SQLite INSERT Statements?

Escape single quote character in SQLite query

When inserting data into a SQLite database, it is crucial to properly escape special characters such as single quotes to prevent syntax errors. A common problem is that single quote characters cannot be escaped correctly.

For example, in your SQL query:

<code class="language-sql">INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there'\');</code>

Escape character '' does not work as expected. It does not escape the single quote, but acts as a special character in SQL.

The workaround is to double the single quotes. SQLite, like many other databases, requires the use of two consecutive single quotes ('') to represent a single quote in a string. Therefore, the correct query should be:

<code class="language-sql">INSERT INTO table_name (field1, field2) VALUES (123, 'Hello there''s');</code>

This will escape the single quote character and insert it into the database correctly.

According to the SQLite documentation, string constants are enclosed in single quotes, and single quotes in a string can be encoded using two consecutive single quotes. SQLite does not support C-style escaping using backslash characters.

The above is the detailed content of How to Properly Escape Single Quotes in SQLite INSERT Statements?. For more information, please follow other related articles on the PHP Chinese website!