Theme

Error explainers · SQL

SQL error: unterminated quoted string

SQL string literals are enclosed in single quotes, and the database found an opening quote with no closing one before the end of the statement. Often the real problem is earlier: an apostrophe inside a value, like the one in O’Brien, closes the string too soon, and the quotes after it pair up wrongly until the last one is left open. The error is then reported at the end of the statement. PasteKit highlights the quote that is never closed.

Seen as:

  • ERROR: unterminated quoted string at or near "';"
  • ORA-01756: quoted string not properly terminated
  • Msg 105, Level 15, State 1, Line 4 Unclosed quotation mark after the character string ';'.
  • unrecognized token: "';"
  • ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Input

Settings

History

Load from URL

Common causes

1. An apostrophe inside a value

Names like O’Brien, words like don’t and possessives break a literal. In standard SQL an apostrophe inside a string is written as two single quotes.

Before
SELECT id FROM customers WHERE last_name = 'O'Brien';
After
SELECT id FROM customers WHERE last_name = 'O''Brien';

2. Values pasted into the SQL by string formatting

Building queries with string concatenation or f-strings breaks on the first apostrophe and is the root of SQL injection. Pass values as parameters and let the driver handle quoting.

Before
cur.execute(f"SELECT id FROM customers WHERE last_name = '{last_name}'")
After
cur.execute("SELECT id FROM customers WHERE last_name = %s", (last_name,))

3. A trailing backslash in MySQL

MySQL treats a backslash as an escape character by default, so 'C:\temp\' escapes the closing quote. Double the backslashes (or enable NO_BACKSLASH_ESCAPES). PostgreSQL, SQL Server and SQLite treat backslashes literally.

Before
SELECT * FROM files WHERE path = 'C:\temp\';
After
SELECT * FROM files WHERE path = 'C:\\temp\\';

4. Curly quotes from a document or chat

Queries copied from Word, Slack or a web page may contain ‘ ’ instead of straight quotes. Databases do not recognise them as string delimiters.

Before
SELECT * FROM users WHERE name = ‘Ada’;
After
SELECT * FROM users WHERE name = 'Ada';

Frequently asked questions

Why does the error point to the end of my query?

Each quote pairs with the next one, so one extra apostrophe shifts every pair after it. The database only notices at the end, where the last quote has no partner. Look for an apostrophe inside a value.

Should I escape quotes with a backslash?

Only in MySQL and MariaDB with default settings. Doubling the quote (‘’) is standard SQL and works in every database, and parameterised queries avoid the issue altogether.

What about PostgreSQL dollar quoting?

PostgreSQL also accepts $$text$$ or $tag$text$tag$, which needs no escaping inside. It is handy for function bodies and long text, but it is PostgreSQL-specific.

Related