fix: sqlite: string interpolation incorrectly uses backslash escapes #227
Description
Problem: SQLite string interpolation incorrectly uses backslash escapes
The current implementation of quoteStringValue in interpolate.go incorrectly applies MySQL-style backslash escaping to SQLite strings.
As specified in the SQLite Documentation: Literal Values (Constants), SQLite does not recognize the backslash (\) as an escape character within string literals. Currently, go-sqlbuilder is producing strings that contain literal backslashes instead of the intended control characters or escaped quotes.
Specific Issues:
1. Single Quotes: It uses ' instead of the standard SQL ''.
- Reference: SQLite Section 3: Literal Values states:
A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row.
- Impact: Using
\'results in a literal backslash followed by an accidental string termination, causing syntax errors.
2. Control Characters (\n, \r, \t, etc.): The builder currently appends sequences like \n or \t.
- Reference: SQLite's grammar does not include C-style escape sequences. To include a newline, one must include the literal byte or use the char() function.
- Impact: These are stored as two-character strings (backslash + 'n') rather than the intended control character.
3. NUL Characters (\x00): The builder appends \0.
- Reference: SQLite: NUL-containing Blobs/Strings explains that NUL bytes cannot be embedded in a string literal because SQLite uses C-style string functions internally in certain layers. The documentation suggests using
char(0)or hex literals. - Impact: In SQLite, this results in two literal backslashes being stored, as the first backslash does not act as an escape character.
Proposed Solution
Update quoteStringValue to handle the SQLite flavor by avoiding backslash escapes entirely and using concatenation for NUL bytes.
This change ensures that go-sqlbuilder generates SQLite-compliant SQL that correctly preserves data formatting and prevents syntax errors caused by invalid escape sequences.