How to escape single quotes in SQL
An apostrophe in a name is enough to break a hand-written INSERT. SQL solves this with one simple rule, and it is easy to apply automatically.
The rule: double the single quote
Inside a single-quoted string literal, write a single quote as two single quotes. So O'Connor is written as 'O''Connor'.
This is part of standard SQL and works the same in PostgreSQL, MySQL, and SQLite. It is also why backslash escaping is not needed for normal strings.
Why backslashes are a red herring
MySQL historically allowed \' as an escape. That works only if specific modes are enabled, and it is not portable to other databases.
The portable answer is always to double the quote, which every major database accepts.
Escaping a whole file at once
Doing this by hand does not scale. Instead, generate the INSERT statements from the file and the escaping is applied to every value in one pass.
You can read the output before you copy it, so you can confirm the escaping is correct.
Do it in your browser
Frequently asked questions
- Do double quotes need escaping too?
- Not inside a single-quoted string. Double quotes only matter when they quote identifiers.
- What about a string that contains both quote types?
- Only single quotes are doubled; a double quote inside the value is stored as-is.
- Can a tool do this for my whole dataset?
- Yes. Drop the file into the SQL generator and every single quote is escaped automatically.