0

I'm trying to generate a script to enter data into a database although I've come across an issue with names containing an apostrophe. For example:

INSERT INTO people(firstname, lastname) VALUES ('Mark', 'O'something');

The name containing the apostrophe is closing my single quotes and causing an error, I also can't use " as Postgres doesn't seem to accept them. Is there anything I can do to ensure names like this don't break my script.

SkinnyBetas
  • 461
  • 1
  • 5
  • 27
  • 1
    ISO Standard SQL uses **doubled-up-single-quotes** to escape single-quotes, so use `VALUES ( 'Mark', 'O''Rielly' )`. All major RDBMS implements ISO SQL (MS-SQL, PostgreSQL, MySQL, MariaDB, Oracle, even MS Access, etc) so this works for them. (ISO SQL also uses double-quotes to escape identifiers, hence why you can't use `"` in PostgreSQL). – Dai Aug 26 '21 at 01:37
  • Don’t use the same technique for data provided by users; use parameterized queries instead – Caius Jard Aug 26 '21 at 05:31
  • This is all explained in the chapter [String constants](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS) in the Postgres manual –  Aug 26 '21 at 06:17

0 Answers0