(1) By curmudgeon on 2020-10-06 10:00:38 [link] [source]
Documentation is a bit vague about sqlite3_normalized_sql fct so I'm not sure if this is a bug. select 1 as x; select 1 as [x]; select 1 as "x"; select 1 as `x`; Normalized sql for above 4 is SELECT?AS x; but for select 1 as 'x'; it's SELECT?AS?;
This is a string literal containing a single character U+0078:
These forms are all different spellings for an identifier that consists of a single character U+0078:
The sqlite3_normalize_sql() interface should convert literals into "?" and convert identifiers into their simplest form. It appears to be doing the right thing in your example.
(3.1) By curmudgeon on 2020-10-06 12:04:43 edited from 3.0 in reply to 2 [link] [source]
It was the "converting identifiers to their simplest form" that I found useful Richard but this does make it less so. If I wanted to interpret user sql and they input select "tbl" . [col] from tbl; it will be normalized to SELECT tbl.col FROM tbl; -- useful but select "tbl" . 'col' from tbl; will be normalized to SELECT tbl.?FROM tbl; -- less useful although both the original sql's will return the exact same result. I'm guessing that I'm using sqlite3_normalized_sql for something it's not intended for. Wish I knew what it was intended for though.
SELECT "tbl".'col' FROM tbl;
Is not standard SQL. SQLite accepts it in a misguided effort to follow
Postel's Rule. But that does not make it right. The conversion
of the string literal
'col' into an identifier
"col" occurs during
parsing and requires a level of understanding of the SQL language far in
excess of what is available to sqlite3_normalize_sql().
I'm not sure the automatic conversion of string-literals into identifiers is even documented. Is it?
If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.
(6) By curmudgeon on 2020-10-06 13:07:19 in reply to 4 [link] [source]
OK Richard, thanks for the input.