Setting PRAGMA case_sensitive_like causes unsafe error
(1.1) By Adrian (adrian.s) on 2023-08-15 08:42:34 edited from 1.0 [source]
While working on airsequel.com, I discovered following problem:
Once the case
PRAGMA case_sensitive_like is set to any value, it causes an
unsafe use of LIKE() error when
LIKE is used.
$ sqlite3 :memory: SQLite version 3.42.0 2023-05-16 12:36:15 Enter ".help" for usage hints. sqlite> PRAGMA case_sensitive_like = False; sqlite> CREATE VIEW test AS SELECT 'abc' LIKE 'a%'; sqlite> SELECT * FROM test; Parse error: unsafe use of LIKE()
- Why does
PRAGMA case_sensitive_like = Falsechange anything at all, if it's already the default?
PRAGMA case_sensitive_like = Trueapparently installs a new application-defined
LIKEfunction. Why is this function not trusted?
- Why is there an unsafe error at all? I didn't turn off
(2) By Stephan Beal (stephan) on 2023-08-15 08:21:46 in reply to 1.0 [link] [source]
CREATE VIEW test AS SELECT "abc" LIKE "a%";
Double-quotes are for identifiers and single-quotes are for strings:
sqlite> CREATE VIEW test AS SELECT 'abc' LIKE 'a%'; sqlite> SELECT * FROM test; 'abc' LIKE 'a%' 1
(3.1) By Adrian (adrian.s) on 2023-08-15 08:44:42 edited from 3.0 in reply to 2 [link] [source]
Oops, I fixed it, thanks! However, it doesn't change anything. The error still comes up. (You have to set the
PRAGMA case_sensitive_like to replicate it!)
(4) By anonymous on 2023-08-15 16:00:51 in reply to 1.1 [link] [source]
Fixed in this commit, which I find myself unable to resist nitpicking.
Surely, you can shave off a few bytes of code size and a few picoseconds of execution time by including the
SQLITE_INNOCUOUS flag when registering the function instead of clearing the
SQLITE_FUNC_UNSAFE flag afterwards?