2

I need to insert this string, from the H2 console, with a two-levels deep nesting of quotes. Since H2 DB uses single quotes, I cannot use double quotes. I saw this SO fix for a string with a nested quote.

I tried to insert, but got this error

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 '\''Say to those with fearful hearts, '\''Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\'' '\'' (NIV).')

In the browser console output, I see the error message:

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \"Say to those with fearful hearts, \"Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\" '\" (NIV).');

Syntax error in SQL statement "INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \\""Say to those with fearful hearts, \\""Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'[*]\\"" '\\"" (NIV).')"; SQL statement:
INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \"Say to those with fearful hearts, \"Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\" '\" (NIV).') [42000-214] 42000/42000 (Help)

Here is the schema:

CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default')
CREATE TYPE Tone AS ENUM('Default', 'Uplifting', 'Urging', 'Warning', 'Soothing', 'Comforting', 'Inspiring', 'Centering', 'Balanced')
CREATE TYPE Topic AS ENUM('Default', 'AcceptedInChrist', 'SignificantInChrist', 'SecureInChrist', 'NoAnxietyInChrist');
CREATE TABLE Encouragement(ID INT PRIMARY KEY, CATEGORY Category, TOPIC Topic, TONE Tone, MESSAGE VARCHAR(255))
likejudo
  • 3,396
  • 6
  • 52
  • 107
  • 2
    Avoid concatenating strings to produce SQL statements. Use a parameterized "prepared statement" instead, and forget about problems like this one. – The Impaler Dec 30 '22 at 22:21
  • If you must, you can escape single quotes by duplicating them. For example, `INSERT INTO enc VALUES(31,'God is 35:4 ''Say'' Be strong.');` will insert the value `God is 35:4 'Say' Be strong.`. – The Impaler Dec 30 '22 at 22:21
  • @TheImpaler I am inserting rows into H2 from the console. I don't understand what you mean by using parameterized 'prepared statements' in the console. – likejudo Dec 30 '22 at 23:24
  • @TheImpaler Thank you - your second suggestion worked :) Can you please post it as an answer so I can accept it? – likejudo Dec 30 '22 at 23:53

1 Answers1

3

You can escape single quotes by duplicating them. For example:

INSERT INTO enc VALUES(31,'God is 35:4 ''Say'' Be strong.');

will insert the value God is 35:4 'Say' Be strong..

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • can you explain why it works? traditionally we escape the quote `\'` but that fails here – likejudo Dec 31 '22 at 15:35
  • 1
    Quoting the [H2 Manual - String Literal](https://www.h2database.com/html/grammar.html#string): "A character string literal starts and ends with a single quote. Two single quotes can be used to create a single quote inside a string.". It also explains that the backslash character "\" is used to escape 16-bit Unicode characters. – The Impaler Dec 31 '22 at 22:09