Repository: sql-formatter-org/sql-formatter (ts)

Issue:
More than one string with trailing backslash
**Describe the bug**

I tried formatting

```sql
select
  *
from
  mytable
where
  mycol_a like 'prefix\_%' escape '\'
  and mycol_b like 'prefix\_%' escape '\'
```

**Expected behavior**

Output exactly the same as input.

**Actual behavior**

```
Error: Parse error: Unexpected "\_%'"
    at E.value (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:34317)
    at E.value (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:41347)
    at I.value (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:30283)
    at Module.SS (https://unpkg.com/sql-formatter@latest/dist/sql-formatter.min.js:1:210009)
    at HTMLTextAreaElement.format (https://sql-formatter-org.github.io/sql-formatter/index.js:50:31)
```

If I add a space after the first `'\'` to `'\ '` it works. If I remove the last `and [etc.]` line then it also works.

**Usage**

- How are you calling / using the library? https://sql-formatter-org.github.io/sql-formatter/
- What SQL language(s) does this apply to? I tried every formatter. This is definitely [valid syntax in Trino](https://github.com/trinodb/trino/blob/91ce0ec5c98a5019f5fb66683bae0ee6c18201b0/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L1147) but also other SQL dialects I think
- Which SQL Formatter version are you using? `latest`. v4.0.0 (with some modifications which shouldn't affect this case) adds a bunch of spaces to the last line `and mycol_b like ' prefix \ _ % ' escape ' \ '`

Pull request:
Overhaul of string/identifier/variable escaping
instead of quote types `""` and `''`, there are now four versions of each to pick the escaping style:

- `""-qq` - with repeated-quote escape
- `""-bs` - with backslash escape
- `""-qq-bs` - with both repeated-quote and backslash escape
- `""-raw` - with out any escaping

The old plain `""` and `''` have been dropped, to force one to explicitly make a choice of escaping style.

Added bunch of additional tests to check for escaping (and also to check for particular styles of escaping not working).

Also dropped support for incomplete strings (like `"...`), just like I previously dropped support for incomplete block-comments.

Fixes #308

Patch:
diff --git a/src/languages/bigquery/bigquery.formatter.ts b/src/languages/bigquery/bigquery.formatter.ts
index a1054329..71d0ff60 100644
--- a/src/languages/bigquery/bigquery.formatter.ts
+++ b/src/languages/bigquery/bigquery.formatter.ts
@@ -159,8 +159,10 @@ export default class BigQueryFormatter extends Formatter {
         // Otherwise the first two quotes of """ will get matched as an empty "" string.
         { quote: '""".."""', prefixes: ['R', 'B', 'RB', 'BR'] },
         { quote: "'''..'''", prefixes: ['R', 'B', 'RB', 'BR'] },
-        { quote: '""', prefixes: ['R', 'B', 'RB', 'BR'] },
-        { quote: "''", prefixes: ['R', 'B', 'RB', 'BR'] },
+        '""-bs',
+        "''-bs",
+        { quote: '""-raw', prefixes: ['R', 'B', 'RB', 'BR'], requirePrefix: true },
+        { quote: "''-raw", prefixes: ['R', 'B', 'RB', 'BR'], requirePrefix: true },
       ],
       identTypes: ['``'],
       identChars: { dashes: true },
diff --git a/src/languages/db2/db2.formatter.ts b/src/languages/db2/db2.formatter.ts
index 524020bb..b19b9126 100644
--- a/src/languages/db2/db2.formatter.ts
+++ b/src/languages/db2/db2.formatter.ts
@@ -182,8 +182,11 @@ export default class Db2Formatter extends Formatter {
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
-      stringTypes: [{ quote: "''", prefixes: ['G', 'N', 'X', 'BX', 'GX', 'UX', 'U&'] }],
-      identTypes: [`""`],
+      stringTypes: [
+        { quote: "''-qq", prefixes: ['G', 'N', 'U&'] },
+        { quote: "''-raw", prefixes: ['X', 'BX', 'GX', 'UX'], requirePrefix: true },
+      ],
+      identTypes: [`""-qq`],
       paramTypes: { positional: true, named: [':'] },
       paramChars: { first: '@#$', rest: '@#$' },
       operators: Db2Formatter.operators,
diff --git a/src/languages/hive/hive.formatter.ts b/src/languages/hive/hive.formatter.ts
index 2f61bb45..47efc4c8 100644
--- a/src/languages/hive/hive.formatter.ts
+++ b/src/languages/hive/hive.formatter.ts
@@ -95,7 +95,7 @@ export default class HiveFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       extraParens: ['[]'],
-      stringTypes: ['""', "''"],
+      stringTypes: ['""-bs', "''-bs"],
       identTypes: ['``'],
       variableTypes: [{ quote: '{}', prefixes: ['$'], requirePrefix: true }],
       operators: HiveFormatter.operators,
diff --git a/src/languages/mariadb/mariadb.formatter.ts b/src/languages/mariadb/mariadb.formatter.ts
index 78e283f3..238d0edf 100644
--- a/src/languages/mariadb/mariadb.formatter.ts
+++ b/src/languages/mariadb/mariadb.formatter.ts
@@ -276,13 +276,17 @@ export default class MariaDbFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       // TODO: support _ char set prefixes such as _utf8, _latin1, _binary, _utf8mb4, etc.
-      stringTypes: [{ quote: "''", prefixes: ['B', 'X'] }, '""'],
+      stringTypes: [
+        '""-qq-bs',
+        "''-qq-bs",
+        { quote: "''-raw", prefixes: ['B', 'X'], requirePrefix: true },
+      ],
       identTypes: ['``'],
       identChars: { first: '$', rest: '$', allowFirstCharNumber: true },
       variableTypes: [
         { regex: '@@?[A-Za-z0-9_.$]+' },
-        { quote: '""', prefixes: ['@'], requirePrefix: true },
-        { quote: "''", prefixes: ['@'], requirePrefix: true },
+        { quote: '""-qq-bs', prefixes: ['@'], requirePrefix: true },
+        { quote: "''-qq-bs", prefixes: ['@'], requirePrefix: true },
         { quote: '``', prefixes: ['@'], requirePrefix: true },
       ],
       paramTypes: { positional: true },
diff --git a/src/languages/mysql/mysql.formatter.ts b/src/languages/mysql/mysql.formatter.ts
index a7364c78..3b2ea41c 100644
--- a/src/languages/mysql/mysql.formatter.ts
+++ b/src/languages/mysql/mysql.formatter.ts
@@ -244,13 +244,17 @@ export default class MySqlFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       // TODO: support _ char set prefixes such as _utf8, _latin1, _binary, _utf8mb4, etc.
-      stringTypes: [{ quote: "''", prefixes: ['B', 'N', 'X'] }, '""'],
+      stringTypes: [
+        '""-qq-bs',
+        { quote: "''-qq-bs", prefixes: ['N'] },
+        { quote: "''-raw", prefixes: ['B', 'X'], requirePrefix: true },
+      ],
       identTypes: ['``'],
       identChars: { first: '$', rest: '$', allowFirstCharNumber: true },
       variableTypes: [
         { regex: '@@?[A-Za-z0-9_.$]+' },
-        { quote: '""', prefixes: ['@'], requirePrefix: true },
-        { quote: "''", prefixes: ['@'], requirePrefix: true },
+        { quote: '""-qq-bs', prefixes: ['@'], requirePrefix: true },
+        { quote: "''-qq-bs", prefixes: ['@'], requirePrefix: true },
         { quote: '``', prefixes: ['@'], requirePrefix: true },
       ],
       paramTypes: { positional: true },
diff --git a/src/languages/n1ql/n1ql.formatter.ts b/src/languages/n1ql/n1ql.formatter.ts
index e6c0e0da..8de35613 100644
--- a/src/languages/n1ql/n1ql.formatter.ts
+++ b/src/languages/n1ql/n1ql.formatter.ts
@@ -95,7 +95,7 @@ export default class N1qlFormatter extends Formatter {
       // NOTE: single quotes are actually not supported in N1QL,
       // but we support them anyway as all other SQL dialects do,
       // which simplifies writing tests that are shared between all dialects.
-      stringTypes: ['""', "''"],
+      stringTypes: ['""-bs', "''-bs"],
       identTypes: ['``'],
       extraParens: ['[]', '{}'],
       paramTypes: { positional: true, numbered: ['$'], named: ['$'] },
diff --git a/src/languages/plsql/plsql.formatter.ts b/src/languages/plsql/plsql.formatter.ts
index aeb06484..62bb902a 100644
--- a/src/languages/plsql/plsql.formatter.ts
+++ b/src/languages/plsql/plsql.formatter.ts
@@ -103,10 +103,13 @@ export default class PlSqlFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       stringTypes: [
-        { quote: "''", prefixes: ['N'] },
+        { quote: "''-qq", prefixes: ['N'] },
         { quote: "q''", prefixes: ['N'] },
       ],
-      identTypes: [`""`],
+      // PL/SQL doesn't actually support escaping of quotes in identifiers,
+      // but for the sake of simpler testing we'll support this anyway
+      // as all other SQL dialects with "identifiers" do.
+      identTypes: [`""-qq`],
       identChars: { rest: '$#' },
       variableTypes: [{ regex: '&{1,2}[A-Za-z][A-Za-z0-9_$#]*' }],
       paramTypes: { numbered: [':'], named: [':'] },
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index 74fae657..8756b25c 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -334,8 +334,13 @@ export default class PostgreSqlFormatter extends Formatter {
       reservedFunctionNames: functions,
       nestedBlockComments: true,
       extraParens: ['[]'],
-      stringTypes: ['$$', { quote: "''", prefixes: ['B', 'E', 'X', 'U&'] }],
-      identTypes: [{ quote: '""', prefixes: ['U&'] }],
+      stringTypes: [
+        '$$',
+        { quote: "''-qq", prefixes: ['U&'] },
+        { quote: "''-bs", prefixes: ['E'], requirePrefix: true },
+        { quote: "''-raw", prefixes: ['B', 'X'], requirePrefix: true },
+      ],
+      identTypes: [{ quote: '""-qq', prefixes: ['U&'] }],
       identChars: { rest: '$' },
       paramTypes: { numbered: ['$'] },
       operators: PostgreSqlFormatter.operators,
diff --git a/src/languages/redshift/redshift.formatter.ts b/src/languages/redshift/redshift.formatter.ts
index 120f7aa8..b8d0358e 100644
--- a/src/languages/redshift/redshift.formatter.ts
+++ b/src/languages/redshift/redshift.formatter.ts
@@ -152,8 +152,8 @@ export default class RedshiftFormatter extends Formatter {
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
-      stringTypes: ["''"],
-      identTypes: [`""`],
+      stringTypes: ["''-qq"],
+      identTypes: [`""-qq`],
       identChars: { first: '#' },
       paramTypes: { numbered: ['$'] },
       operators: RedshiftFormatter.operators,
diff --git a/src/languages/singlestoredb/singlestoredb.formatter.ts b/src/languages/singlestoredb/singlestoredb.formatter.ts
index 20a6479b..76286d5c 100644
--- a/src/languages/singlestoredb/singlestoredb.formatter.ts
+++ b/src/languages/singlestoredb/singlestoredb.formatter.ts
@@ -244,7 +244,11 @@ export default class SingleStoreDbFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       // TODO: support _binary"some string" prefix
-      stringTypes: [{ quote: "''", prefixes: ['B', 'X'] }, '""'],
+      stringTypes: [
+        '""-qq-bs',
+        "''-qq-bs",
+        { quote: "''-raw", prefixes: ['B', 'X'], requirePrefix: true },
+      ],
       identTypes: ['``'],
       identChars: { first: '$', rest: '$', allowFirstCharNumber: true },
       variableTypes: [
diff --git a/src/languages/spark/spark.formatter.ts b/src/languages/spark/spark.formatter.ts
index ae815dd1..d8940310 100644
--- a/src/languages/spark/spark.formatter.ts
+++ b/src/languages/spark/spark.formatter.ts
@@ -132,8 +132,10 @@ export default class SparkFormatter extends Formatter {
       reservedFunctionNames: functions,
       extraParens: ['[]'],
       stringTypes: [
-        { quote: "''", prefixes: ['R', 'X'] },
-        { quote: '""', prefixes: ['R', 'X'] },
+        "''-bs",
+        '""-bs',
+        { quote: "''-raw", prefixes: ['R', 'X'], requirePrefix: true },
+        { quote: '""-raw', prefixes: ['R', 'X'], requirePrefix: true },
       ],
       identTypes: ['``'],
       variableTypes: [{ quote: '{}', prefixes: ['$'], requirePrefix: true }],
diff --git a/src/languages/sql/sql.formatter.ts b/src/languages/sql/sql.formatter.ts
index c7bc9b83..7560961d 100644
--- a/src/languages/sql/sql.formatter.ts
+++ b/src/languages/sql/sql.formatter.ts
@@ -80,8 +80,11 @@ export default class SqlFormatter extends Formatter {
       reservedPhrases,
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
-      stringTypes: [{ quote: "''", prefixes: ['N', 'X', 'U&'] }],
-      identTypes: [`""`, '``'],
+      stringTypes: [
+        { quote: "''-qq-bs", prefixes: ['N', 'U&'] },
+        { quote: "''-raw", prefixes: ['X'], requirePrefix: true },
+      ],
+      identTypes: [`""-qq`, '``'],
       paramTypes: { positional: true },
     });
   }
diff --git a/src/languages/sqlite/sqlite.formatter.ts b/src/languages/sqlite/sqlite.formatter.ts
index d06d4d90..abc7e100 100644
--- a/src/languages/sqlite/sqlite.formatter.ts
+++ b/src/languages/sqlite/sqlite.formatter.ts
@@ -75,10 +75,12 @@ export default class SqliteFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       stringTypes: [
-        { quote: "''", prefixes: ['X'] },
-        // { quote: '""', prefixes: ['X'] }, // currently conflict with "" identifiers
+        "''-qq",
+        { quote: "''-raw", prefixes: ['X'], requirePrefix: true },
+        // Depending on context SQLite also supports double-quotes for strings,
+        // and single-quotes for identifiers.
       ],
-      identTypes: [`""`, '``', '[]'],
+      identTypes: [`""-qq`, '``', '[]'],
       // https://www.sqlite.org/lang_expr.html#parameters
       paramTypes: { positional: true, numbered: ['?'], named: [':', '@', '$'] },
       operators: SqliteFormatter.operators,
diff --git a/src/languages/trino/trino.formatter.ts b/src/languages/trino/trino.formatter.ts
index 2b6c2691..172659db 100644
--- a/src/languages/trino/trino.formatter.ts
+++ b/src/languages/trino/trino.formatter.ts
@@ -141,9 +141,12 @@ export default class TrinoFormatter extends Formatter {
       extraParens: ['[]', '{}'],
       // https://trino.io/docs/current/language/types.html#string
       // https://trino.io/docs/current/language/types.html#varbinary
-      stringTypes: [{ quote: "''", prefixes: ['X', 'U&'] }],
+      stringTypes: [
+        { quote: "''-qq", prefixes: ['U&'] },
+        { quote: "''-raw", prefixes: ['X'], requirePrefix: true },
+      ],
       // https://trino.io/docs/current/language/reserved.html
-      identTypes: ['""'],
+      identTypes: ['""-qq'],
       paramTypes: { positional: true },
       operators: TrinoFormatter.operators,
     });
diff --git a/src/languages/tsql/tsql.formatter.ts b/src/languages/tsql/tsql.formatter.ts
index 1534969d..5333785d 100644
--- a/src/languages/tsql/tsql.formatter.ts
+++ b/src/languages/tsql/tsql.formatter.ts
@@ -227,8 +227,8 @@ export default class TSqlFormatter extends Formatter {
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
       nestedBlockComments: true,
-      stringTypes: [{ quote: "''", prefixes: ['N'] }],
-      identTypes: [`""`, '[]'],
+      stringTypes: [{ quote: "''-qq", prefixes: ['N'] }],
+      identTypes: [`""-qq`, '[]'],
       identChars: { first: '#@', rest: '#@$' },
       paramTypes: { named: ['@'], quoted: ['@'] },
       operators: TSqlFormatter.operators,
diff --git a/src/lexer/regexFactory.ts b/src/lexer/regexFactory.ts
index 988bd00b..7f7bb508 100644
--- a/src/lexer/regexFactory.ts
+++ b/src/lexer/regexFactory.ts
@@ -96,25 +96,35 @@ const buildQStringPatterns = () => {
   return qStringPattern;
 };
 
-// This enables the following quote styles:
-// 1. backtick quoted using `` to escape
-// 2. square bracket quoted (SQL Server) using ]] to escape
-// 3. double quoted using "" or \" to escape
-// 4. single quoted using '' or \' to escape
-// 5. PostgreSQL dollar-quoted
-// 6. BigQuery '''triple-quoted'''
-// 7. BigQuery """triple-quoted"""
-// 8. Hive and Spark variables: ${name}
-// 9. Oracle q'' strings: q'<text>' q'|text|' ...
+// Regex patterns for all supported quote styles.
+//
+// Most of them have a single escaping-style built in,
+// but "" and '' support multiple versions of escapes,
+// which must be selected with suffixes: -qq, -bs, -qq-bs, -raw
 export const quotePatterns = {
-  '``': '(?:`[^`]*(?:$|`))+',
-  '[]': String.raw`(?:\[[^\]]*(?:$|\]))(?:\][^\]]*(?:$|\]))*`,
-  '""': String.raw`(?:"[^"\\]*(?:\\.[^"\\]*)*(?:"|$))+`,
-  "''": String.raw`(?:'[^'\\]*(?:\\.[^'\\]*)*(?:'|$))+`,
-  '$$': String.raw`(?<tag>\$\w*\$)[\s\S]*?(?:\k<tag>|$)`,
-  "'''..'''": String.raw`'''[^\\]*?(?:\\.[^\\]*?)*?(?:'''|$)`,
-  '""".."""': String.raw`"""[^\\]*?(?:\\.[^\\]*?)*?(?:"""|$)`,
-  '{}': String.raw`(?:\{[^\}]*(?:$|\}))`,
+  // - backtick quoted (using `` to escape)
+  '``': '(?:`[^`]*`)+',
+  // - Transact-SQL square bracket quoted (using ]] to escape)
+  '[]': String.raw`(?:\[[^\]]*\])(?:\][^\]]*\])*`,
+  // double-quoted
+  '""-qq': String.raw`(?:"[^"]*")+`, // with repeated quote escapes
+  '""-bs': String.raw`(?:"[^"\\]*(?:\\.[^"\\]*)*")`, // with backslash escapes
+  '""-qq-bs': String.raw`(?:"[^"\\]*(?:\\.[^"\\]*)*")+`, // with repeated quote or backslash escapes
+  '""-raw': String.raw`(?:"[^"]*")`, // no escaping
+  // single-quoted
+  "''-qq": String.raw`(?:'[^']*')+`, // with repeated quote escapes
+  "''-bs": String.raw`(?:'[^'\\]*(?:\\.[^'\\]*)*')`, // with backslash escapes
+  "''-qq-bs": String.raw`(?:'[^'\\]*(?:\\.[^'\\]*)*')+`, // with repeated quote or backslash escapes
+  "''-raw": String.raw`(?:'[^']*')`, // no escaping
+  // PostgreSQL dollar-quoted
+  '$$': String.raw`(?<tag>\$\w*\$)[\s\S]*?\k<tag>`,
+  // BigQuery '''triple-quoted''' (using \' to escape)
+  "'''..'''": String.raw`'''[^\\]*?(?:\\.[^\\]*?)*?'''`,
+  // BigQuery """triple-quoted""" (using \" to escape)
+  '""".."""': String.raw`"""[^\\]*?(?:\\.[^\\]*?)*?"""`,
+  // Hive and Spark variables: ${name}
+  '{}': String.raw`(?:\{[^\}]*\})`,
+  // Oracle q'' strings: q'<text>' q'|text|' ...
   "q''": buildQStringPatterns(),
 };
 


Test patch:
diff --git a/test/behavesLikeMariaDbFormatter.ts b/test/behavesLikeMariaDbFormatter.ts
index 6affd9d9..f9c8ce70 100644
--- a/test/behavesLikeMariaDbFormatter.ts
+++ b/test/behavesLikeMariaDbFormatter.ts
@@ -20,7 +20,7 @@ import supportsTruncateTable from './features/truncateTable';
 export default function behavesLikeMariaDbFormatter(format: FormatFn) {
   behavesLikeSqlFormatter(format);
   supportsComments(format, { hashComments: true });
-  supportsStrings(format, ["''", '""', "X''"]);
+  supportsStrings(format, ["''-qq", "''-bs", '""-qq', '""-bs', "X''"]);
   supportsIdentifiers(format, ['``']);
   supportsDropTable(format, { ifExists: true });
   supportsConstraints(format);
diff --git a/test/bigquery.test.ts b/test/bigquery.test.ts
index c16e1731..9c8d699b 100644
--- a/test/bigquery.test.ts
+++ b/test/bigquery.test.ts
@@ -45,7 +45,7 @@ describe('BigQueryFormatter', () => {
   supportsUpdate(format);
   supportsTruncateTable(format);
   supportsMergeInto(format);
-  supportsStrings(format, ['""', "''", "R''", 'R""', "B''", 'B""']);
+  supportsStrings(format, ['""-bs', "''-bs", "R''", 'R""', "B''", 'B""']);
   supportsIdentifiers(format, ['``']);
   supportsArrayLiterals(format);
   supportsBetween(format);
diff --git a/test/db2.test.ts b/test/db2.test.ts
index 110b9b12..dfe41a63 100644
--- a/test/db2.test.ts
+++ b/test/db2.test.ts
@@ -45,8 +45,8 @@ describe('Db2Formatter', () => {
   supportsUpdate(format, { whereCurrentOf: true });
   supportsTruncateTable(format, { withoutTable: true });
   supportsMergeInto(format);
-  supportsStrings(format, ["''", "X''", "U&''", "N''"]);
-  supportsIdentifiers(format, [`""`]);
+  supportsStrings(format, ["''-qq", "X''", "U&''", "N''"]);
+  supportsIdentifiers(format, [`""-qq`]);
   supportsBetween(format);
   supportsSchema(format);
   supportsOperators(format, Db2Formatter.operators);
diff --git a/test/features/identifiers.ts b/test/features/identifiers.ts
index f0f666e9..a701b970 100644
--- a/test/features/identifiers.ts
+++ b/test/features/identifiers.ts
@@ -2,13 +2,16 @@ import { expect } from '@jest/globals';
 import dedent from 'dedent-js';
 import { FormatFn } from 'src/sqlFormatter';
 
-type IdentType = '""' | '``' | '[]' | 'U&""';
+type IdentType =
+  | '""-qq' // with repeated-quote escaping
+  | '``' // with repeated-quote escaping
+  | '[]' // with ]] escaping
+  | 'U&""'; // with repeated-quote escaping
 
 export default function supportsIdentifiers(format: FormatFn, identifierTypes: IdentType[]) {
-  if (identifierTypes.includes('""')) {
+  if (identifierTypes.includes('""-qq')) {
     it('supports double-quoted identifiers', () => {
       expect(format('"foo JOIN bar"')).toBe('"foo JOIN bar"');
-      expect(format('"foo \\" JOIN bar"')).toBe('"foo \\" JOIN bar"');
       expect(format('SELECT "where" FROM "update"')).toBe(dedent`
         SELECT
           "where"
@@ -28,12 +31,15 @@ export default function supportsIdentifiers(format: FormatFn, identifierTypes: I
     it('supports escaping double-quote by doubling it', () => {
       expect(format('"foo""bar"')).toBe('"foo""bar"');
     });
+
+    it('does not support escaping double-quote with a backslash', () => {
+      expect(() => format('"foo \\" JOIN bar"')).toThrowError('Parse error: Unexpected "');
+    });
   }
 
   if (identifierTypes.includes('``')) {
     it('supports backtick-quoted identifiers', () => {
       expect(format('`foo JOIN bar`')).toBe('`foo JOIN bar`');
-      expect(format('`foo `` JOIN bar`')).toBe('`foo `` JOIN bar`');
       expect(format('SELECT `where` FROM `update`')).toBe(dedent`
         SELECT
           \`where\`
@@ -42,6 +48,10 @@ export default function supportsIdentifiers(format: FormatFn, identifierTypes: I
       `);
     });
 
+    it('supports escaping backtick by doubling it', () => {
+      expect(format('`foo `` JOIN bar`')).toBe('`foo `` JOIN bar`');
+    });
+
     it('no space around dot between two backtick-quoted identifiers', () => {
       const result = format(`SELECT \`my table\`.\`col name\`;`);
       expect(result).toBe(dedent`
@@ -54,7 +64,6 @@ export default function supportsIdentifiers(format: FormatFn, identifierTypes: I
   if (identifierTypes.includes('U&""')) {
     it('supports unicode double-quoted identifiers', () => {
       expect(format('U&"foo JOIN bar"')).toBe('U&"foo JOIN bar"');
-      expect(format('U&"foo \\" JOIN bar"')).toBe('U&"foo \\" JOIN bar"');
       expect(format('SELECT U&"where" FROM U&"update"')).toBe(dedent`
         SELECT
           U&"where"
@@ -71,15 +80,22 @@ export default function supportsIdentifiers(format: FormatFn, identifierTypes: I
       `);
     });
 
-    it('detects consecuitive U&"" identifiers as separate ones', () => {
+    it('supports escaping in U&"" strings by repeated quote', () => {
+      expect(format('U&"foo "" JOIN bar"')).toBe('U&"foo "" JOIN bar"');
+    });
+
+    it('detects consecutive U&"" identifiers as separate ones', () => {
       expect(format('U&"foo"U&"bar"')).toBe('U&"foo" U&"bar"');
     });
+
+    it('does not supports escaping in U&"" strings with a backslash', () => {
+      expect(() => format('U&"foo \\" JOIN bar"')).toThrowError('Parse error: Unexpected "');
+    });
   }
 
   if (identifierTypes.includes('[]')) {
     it('supports [bracket-quoted identifiers]', () => {
       expect(format('[foo JOIN bar]')).toBe('[foo JOIN bar]');
-      expect(format('[foo ]] JOIN bar]')).toBe('[foo ]] JOIN bar]');
       expect(format('SELECT [where] FROM [update]')).toBe(dedent`
         SELECT
           [where]
@@ -88,6 +104,10 @@ export default function supportsIdentifiers(format: FormatFn, identifierTypes: I
       `);
     });
 
+    it('supports escaping close-bracket by doubling it', () => {
+      expect(format('[foo ]] JOIN bar]')).toBe('[foo ]] JOIN bar]');
+    });
+
     it('no space around dot between two [bracket-quoted identifiers]', () => {
       const result = format(`SELECT [my table].[col name];`);
       expect(result).toBe(dedent`
diff --git a/test/features/strings.ts b/test/features/strings.ts
index 0d6d7820..286f118b 100644
--- a/test/features/strings.ts
+++ b/test/features/strings.ts
@@ -4,23 +4,25 @@ import dedent from 'dedent-js';
 import { FormatFn } from 'src/sqlFormatter';
 
 type StringType =
-  | '""'
-  | "''"
-  | 'U&""'
-  | "U&''"
-  | "N''"
-  | "X''"
-  | 'X""'
-  | "B''"
-  | 'B""'
-  | "R''"
-  | 'R""';
+  // Note: ""-qq and ""-bs can be combined to allow for both types of escaping
+  | '""-qq' // with repeated-quote escaping
+  | '""-bs' // with backslash escaping
+  // Note: ''-qq and ''-bs can be combined to allow for both types of escaping
+  | "''-qq" // with repeated-quote escaping
+  | "''-bs" // with backslash escaping
+  | "U&''" // with repeated-quote escaping
+  | "N''" // with escaping style depending on whether also ''-qq or ''-bs was specified
+  | "X''" // no escaping
+  | 'X""' // no escaping
+  | "B''" // no escaping
+  | 'B""' // no escaping
+  | "R''" // no escaping
+  | 'R""'; // no escaping
 
 export default function supportsStrings(format: FormatFn, stringTypes: StringType[]) {
-  if (stringTypes.includes('""')) {
+  if (stringTypes.includes('""-qq') || stringTypes.includes('""-bs')) {
     it('supports double-quoted strings', () => {
       expect(format('"foo JOIN bar"')).toBe('"foo JOIN bar"');
-      expect(format('"foo \\" JOIN bar"')).toBe('"foo \\" JOIN bar"');
       expect(format('SELECT "where" FROM "update"')).toBe(dedent`
         SELECT
           "where"
@@ -28,16 +30,35 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
           "update"
       `);
     });
+  }
 
+  if (stringTypes.includes('""-qq')) {
     it('supports escaping double-quote by doubling it', () => {
       expect(format('"foo""bar"')).toBe('"foo""bar"');
     });
+
+    if (!stringTypes.includes('""-bs')) {
+      it('does not support escaping double-quote with a backslash', () => {
+        expect(() => format('"foo \\" JOIN bar"')).toThrowError('Parse error: Unexpected """');
+      });
+    }
   }
 
-  if (stringTypes.includes("''")) {
+  if (stringTypes.includes('""-bs')) {
+    it('supports escaping double-quote with a backslash', () => {
+      expect(format('"foo \\" JOIN bar"')).toBe('"foo \\" JOIN bar"');
+    });
+
+    if (!stringTypes.includes('""-qq')) {
+      it('does not support escaping double-quote by doubling it', () => {
+        expect(format('"foo "" JOIN bar"')).toBe('"foo " " JOIN bar"');
+      });
+    }
+  }
+
+  if (stringTypes.includes("''-qq") || stringTypes.includes("''-bs")) {
     it('supports single-quoted strings', () => {
       expect(format("'foo JOIN bar'")).toBe("'foo JOIN bar'");
-      expect(format("'foo \\' JOIN bar'")).toBe("'foo \\' JOIN bar'");
       expect(format("SELECT 'where' FROM 'update'")).toBe(dedent`
         SELECT
           'where'
@@ -45,33 +66,35 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
           'update'
       `);
     });
+  }
 
+  if (stringTypes.includes("''-qq")) {
     it('supports escaping single-quote by doubling it', () => {
       expect(format("'foo''bar'")).toBe("'foo''bar'");
     });
+
+    if (!stringTypes.includes("''-bs")) {
+      it('does not support escaping single-quote with a backslash', () => {
+        expect(() => format("'foo \\' JOIN bar'")).toThrowError(`Parse error: Unexpected "'"`);
+      });
+    }
   }
 
-  if (stringTypes.includes('U&""')) {
-    it('supports unicode double-quoted strings', () => {
-      expect(format('U&"foo JOIN bar"')).toBe('U&"foo JOIN bar"');
-      expect(format('U&"foo \\" JOIN bar"')).toBe('U&"foo \\" JOIN bar"');
-      expect(format('SELECT U&"where" FROM U&"update"')).toBe(dedent`
-        SELECT
-          U&"where"
-        FROM
-          U&"update"
-      `);
+  if (stringTypes.includes("''-bs")) {
+    it('supports escaping single-quote with a backslash', () => {
+      expect(format("'foo \\' JOIN bar'")).toBe("'foo \\' JOIN bar'");
     });
 
-    it("detects consecutive U&'' strings as separate ones", () => {
-      expect(format("U&'foo'U&'bar'")).toBe("U&'foo' U&'bar'");
-    });
+    if (!stringTypes.includes("''-qq")) {
+      it('does not support escaping single-quote by doubling it', () => {
+        expect(format("'foo '' JOIN bar'")).toBe("'foo ' ' JOIN bar'");
+      });
+    }
   }
 
   if (stringTypes.includes("U&''")) {
-    it('supports single-quoted strings', () => {
+    it('supports unicode single-quoted strings', () => {
       expect(format("U&'foo JOIN bar'")).toBe("U&'foo JOIN bar'");
-      expect(format("U&'foo \\' JOIN bar'")).toBe("U&'foo \\' JOIN bar'");
       expect(format("SELECT U&'where' FROM U&'update'")).toBe(dedent`
         SELECT
           U&'where'
@@ -79,12 +102,19 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
           U&'update'
       `);
     });
+
+    it("supports escaping in U&'' strings with repeated quote", () => {
+      expect(format("U&'foo '' JOIN bar'")).toBe("U&'foo '' JOIN bar'");
+    });
+
+    it("detects consecutive U&'' strings as separate ones", () => {
+      expect(format("U&'foo'U&'bar'")).toBe("U&'foo' U&'bar'");
+    });
   }
 
   if (stringTypes.includes("N''")) {
     it('supports T-SQL unicode strings', () => {
       expect(format("N'foo JOIN bar'")).toBe("N'foo JOIN bar'");
-      expect(format("N'foo \\' JOIN bar'")).toBe("N'foo \\' JOIN bar'");
       expect(format("SELECT N'where' FROM N'update'")).toBe(dedent`
         SELECT
           N'where'
@@ -93,6 +123,17 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
       `);
     });
 
+    if (stringTypes.includes("''-qq")) {
+      it("supports escaping in N'' strings with repeated quote", () => {
+        expect(format("N'foo '' JOIN bar'")).toBe("N'foo '' JOIN bar'");
+      });
+    }
+    if (stringTypes.includes("''-bs")) {
+      it("supports escaping in N'' strings with a backslash", () => {
+        expect(format("N'foo \\' JOIN bar'")).toBe("N'foo \\' JOIN bar'");
+      });
+    }
+
     it("detects consecutive N'' strings as separate ones", () => {
       expect(format("N'foo'N'bar'")).toBe("N'foo' N'bar'");
     });
@@ -167,12 +208,11 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
   }
 
   if (stringTypes.includes("R''")) {
-    it('supports raw strings', () => {
-      expect(format("r'abc'")).toBe("r'abc'");
-      expect(format("R'ha ha'")).toBe("R'ha ha'");
-      expect(format("SELECT r'some text' FROM foo")).toBe(dedent`
+    it('supports no escaping in raw strings', () => {
+      expect(format("SELECT r'some \\',R'text' FROM foo")).toBe(dedent`
         SELECT
-          r'some text'
+          r'some \\',
+          R'text'
         FROM
           foo
       `);
@@ -184,12 +224,11 @@ export default function supportsStrings(format: FormatFn, stringTypes: StringTyp
   }
 
   if (stringTypes.includes('R""')) {
-    it('supports raw strings (with double-quotes)', () => {
-      expect(format(`r"abc"`)).toBe(`r"abc"`);
-      expect(format(`R"ha ha"`)).toBe(`R"ha ha"`);
-      expect(format(`SELECT r"some text" FROM foo`)).toBe(dedent`
+    it('supports no escaping in raw strings (with double-quotes)', () => {
+      expect(format(`SELECT r"some \\", R"text" FROM foo`)).toBe(dedent`
         SELECT
-          r"some text"
+          r"some \\",
+          R"text"
         FROM
           foo
       `);
diff --git a/test/hive.test.ts b/test/hive.test.ts
index 51f7a298..caf99f47 100644
--- a/test/hive.test.ts
+++ b/test/hive.test.ts
@@ -39,7 +39,7 @@ describe('HiveFormatter', () => {
   supportsDeleteFrom(format);
   supportsTruncateTable(format, { withoutTable: true });
   supportsMergeInto(format);
-  supportsStrings(format, ['""', "''"]);
+  supportsStrings(format, ['""-bs', "''-bs"]);
   supportsIdentifiers(format, ['``']);
   supportsBetween(format);
   supportsSchema(format);
diff --git a/test/mariadb.test.ts b/test/mariadb.test.ts
index 3e25224b..b94d8448 100644
--- a/test/mariadb.test.ts
+++ b/test/mariadb.test.ts
@@ -43,11 +43,23 @@ describe('MariaDbFormatter', () => {
     renameColumn: true,
   });
 
-  it(`supports @"name", @'name' variables`, () => {
-    expect(format(`SELECT @"foo fo", @'bar ar' FROM tbl;`)).toBe(dedent`
+  it(`supports @"name" variables`, () => {
+    expect(format(`SELECT @"foo fo", @"foo\\"x", @"foo""y" FROM tbl;`)).toBe(dedent`
       SELECT
         @"foo fo",
-        @'bar ar'
+        @"foo\\"x",
+        @"foo""y"
+      FROM
+        tbl;
+    `);
+  });
+
+  it(`supports @'name' variables`, () => {
+    expect(format(`SELECT @'bar ar', @'bar\\'x', @'bar''y' FROM tbl;`)).toBe(dedent`
+      SELECT
+        @'bar ar',
+        @'bar\\'x',
+        @'bar''y'
       FROM
         tbl;
     `);
diff --git a/test/mysql.test.ts b/test/mysql.test.ts
index 308d33a0..3d144ae9 100644
--- a/test/mysql.test.ts
+++ b/test/mysql.test.ts
@@ -43,11 +43,23 @@ describe('MySqlFormatter', () => {
     renameColumn: true,
   });
 
-  it(`supports @"name", @'name' variables`, () => {
-    expect(format(`SELECT @"foo fo", @'bar ar' FROM tbl;`)).toBe(dedent`
+  it(`supports @"name" variables`, () => {
+    expect(format(`SELECT @"foo fo", @"foo\\"x", @"foo""y" FROM tbl;`)).toBe(dedent`
       SELECT
         @"foo fo",
-        @'bar ar'
+        @"foo\\"x",
+        @"foo""y"
+      FROM
+        tbl;
+    `);
+  });
+
+  it(`supports @'name' variables`, () => {
+    expect(format(`SELECT @'bar ar', @'bar\\'x', @'bar''y' FROM tbl;`)).toBe(dedent`
+      SELECT
+        @'bar ar',
+        @'bar\\'x',
+        @'bar''y'
       FROM
         tbl;
     `);
diff --git a/test/n1ql.test.ts b/test/n1ql.test.ts
index 6e7709b0..5365b06a 100644
--- a/test/n1ql.test.ts
+++ b/test/n1ql.test.ts
@@ -30,7 +30,7 @@ describe('N1qlFormatter', () => {
   behavesLikeSqlFormatter(format);
   supportsComments(format, { hashComments: true });
   supportsDeleteFrom(format);
-  supportsStrings(format, [`""`]);
+  supportsStrings(format, [`""-bs`]);
   supportsIdentifiers(format, ['``']);
   supportsBetween(format);
   supportsSchema(format);
diff --git a/test/plsql.test.ts b/test/plsql.test.ts
index 5ea29995..bb943ed6 100644
--- a/test/plsql.test.ts
+++ b/test/plsql.test.ts
@@ -47,8 +47,8 @@ describe('PlSqlFormatter', () => {
   supportsUpdate(format);
   supportsTruncateTable(format);
   supportsMergeInto(format);
-  supportsStrings(format, ["''", "N''"]);
-  supportsIdentifiers(format, [`""`]);
+  supportsStrings(format, ["''-qq", "N''"]);
+  supportsIdentifiers(format, [`""-qq`]);
   supportsBetween(format);
   supportsSchema(format);
   supportsOperators(format, PlSqlFormatter.operators, ['AND', 'OR', 'XOR']);
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index efbb9290..e87d6ca8 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -48,8 +48,8 @@ describe('PostgreSqlFormatter', () => {
   supportsInsertInto(format);
   supportsUpdate(format, { whereCurrentOf: true });
   supportsTruncateTable(format, { withoutTable: true });
-  supportsStrings(format, ["''", "U&''", "X''", "B''"]);
-  supportsIdentifiers(format, [`""`, 'U&""']);
+  supportsStrings(format, ["''-qq", "U&''", "X''", "B''"]);
+  supportsIdentifiers(format, [`""-qq`, 'U&""']);
   supportsBetween(format);
   supportsSchema(format);
   supportsOperators(
diff --git a/test/redshift.test.ts b/test/redshift.test.ts
index 0fe3491c..0c114223 100644
--- a/test/redshift.test.ts
+++ b/test/redshift.test.ts
@@ -40,8 +40,8 @@ describe('RedshiftFormatter', () => {
   supportsInsertInto(format);
   supportsUpdate(format);
   supportsTruncateTable(format, { withoutTable: true });
-  supportsStrings(format, ["''"]);
-  supportsIdentifiers(format, [`""`]);
+  supportsStrings(format, ["''-qq"]);
+  supportsIdentifiers(format, [`""-qq`]);
   supportsOperators(format, RedshiftFormatter.operators);
   supportsJoin(format);
   supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT', 'MINUS']);
diff --git a/test/spark.test.ts b/test/spark.test.ts
index 48484c49..3ab78876 100644
--- a/test/spark.test.ts
+++ b/test/spark.test.ts
@@ -36,7 +36,7 @@ describe('SparkFormatter', () => {
   });
   supportsInsertInto(format, { withoutInto: true });
   supportsTruncateTable(format);
-  supportsStrings(format, ["''", '""', "X''", 'X""', "R''", 'R""']);
+  supportsStrings(format, ["''-bs", '""-bs', "X''", 'X""', "R''", 'R""']);
   supportsIdentifiers(format, ['``']);
   supportsBetween(format);
   supportsOperators(format, SparkFormatter.operators, ['AND', 'OR', 'XOR']);
diff --git a/test/sql.test.ts b/test/sql.test.ts
index 9033a0e4..e7ca60c4 100644
--- a/test/sql.test.ts
+++ b/test/sql.test.ts
@@ -45,8 +45,8 @@ describe('SqlFormatter', () => {
   supportsInsertInto(format);
   supportsUpdate(format, { whereCurrentOf: true });
   supportsTruncateTable(format);
-  supportsStrings(format, ["''", "X''", "N''", "U&''"]);
-  supportsIdentifiers(format, [`""`, '``']);
+  supportsStrings(format, ["''-qq", "''-bs", "X''", "N''", "U&''"]);
+  supportsIdentifiers(format, [`""-qq`, '``']);
   supportsBetween(format);
   supportsSchema(format);
   supportsJoin(format);
diff --git a/test/sqlite.test.ts b/test/sqlite.test.ts
index a90869ff..1c52c46e 100644
--- a/test/sqlite.test.ts
+++ b/test/sqlite.test.ts
@@ -42,8 +42,8 @@ describe('SqliteFormatter', () => {
   supportsDeleteFrom(format);
   supportsInsertInto(format);
   supportsUpdate(format);
-  supportsStrings(format, ["''", "X''"]);
-  supportsIdentifiers(format, [`""`, '``', '[]']);
+  supportsStrings(format, ["''-qq", "X''"]);
+  supportsIdentifiers(format, [`""-qq`, '``', '[]']);
   supportsBetween(format);
   supportsSchema(format);
   supportsJoin(format);
diff --git a/test/trino.test.ts b/test/trino.test.ts
index 120321e3..1665e438 100644
--- a/test/trino.test.ts
+++ b/test/trino.test.ts
@@ -44,8 +44,8 @@ describe('TrinoFormatter', () => {
   supportsInsertInto(format);
   supportsUpdate(format);
   supportsTruncateTable(format);
-  supportsStrings(format, ["''", "X''", "U&''"]);
-  supportsIdentifiers(format, ['""']);
+  supportsStrings(format, ["''-qq", "X''", "U&''"]);
+  supportsIdentifiers(format, [`""-qq`]);
   supportsBetween(format);
   supportsOperators(format, TrinoFormatter.operators, ['AND', 'OR']);
   supportsArrayLiterals(format);
diff --git a/test/tsql.test.ts b/test/tsql.test.ts
index 9dc7c5d5..ae93c2aa 100644
--- a/test/tsql.test.ts
+++ b/test/tsql.test.ts
@@ -43,8 +43,8 @@ describe('TSqlFormatter', () => {
   supportsUpdate(format, { whereCurrentOf: true });
   supportsTruncateTable(format);
   supportsMergeInto(format);
-  supportsStrings(format, ["N''", "''"]);
-  supportsIdentifiers(format, [`""`, '[]']);
+  supportsStrings(format, ["N''", "''-qq"]);
+  supportsIdentifiers(format, [`""-qq`, '[]']);
   supportsBetween(format);
   supportsOperators(
     format,
diff --git a/test/unit/Parser.test.ts b/test/unit/Parser.test.ts
index 262b36a4..43d89db8 100644
--- a/test/unit/Parser.test.ts
+++ b/test/unit/Parser.test.ts
@@ -12,8 +12,8 @@ describe('Parser', () => {
       reservedFunctionNames: ['SQRT', 'CURRENT_TIME'],
       reservedKeywords: ['BETWEEN', 'LIKE', 'ON', 'USING'],
       extraParens: ['[]', '{}'],
-      stringTypes: ["''"],
-      identTypes: ['""'],
+      stringTypes: ["''-qq"],
+      identTypes: ['""-qq'],
     });
 
     return createParser(tokenizer).parse(sql, {});