A Backslash Is an Escape Character in MySQL Strings and a Plain Character in PostgreSQL

A Windows path written as 'C:\temp\new' reads back on MySQL and MariaDB with a tab and a line break in it. PostgreSQL and SQLite keep the backslashes as typed.

'C:\\temp' is C:\\temp, two backslashes, on SQLite and PostgreSQL and C:\temp on MySQL and MariaDB. '\t' is a tab and '\'' a quote there, while PostgreSQL needs its E'...' strings for escapes, which no other engine reads. MySQL’s NO_BACKSLASH_ESCAPES mode makes it read backslashes like the others.

What Each Engine Answered

The example

SELECT 'C:\\temp';

gave these answers:

Every Form BareSQL Checks

BareSQL Portable checks 3 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.

What to Write Instead

Keep backslashes out of SQL string literals: pass such values as query parameters, which every driver sends without escaping.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:8: warning: MySQL and MariaDB read a backslash in a string as an escape character; SQLite and PostgreSQL keep it as a backslash. [backslash-escapes]
    'C:\\temp'
    Keep backslashes out of SQL string literals: pass such values as query parameters, which every driver sends without escaping.

1 problem: 1 warning.

baresql rules backslash-escapes prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.