Title
#general
a

Ahmed

08/17/2021, 8:54 AM
Hi All, I’m facing an issue, hopefully you can help me with. when running a query with case sensitive on OSquery version 4.9.0 it returns this error, but it worked on the version 4.5.1 previously.
osquery> PRAGMA case_sensitive_like=1; <Query>

E0817 04:50:29.635751  8504 sqlite_util.cpp:295] Authorizer denied action 19 case_sensitive_like 1 null null
Error: not authorized
s

seph

08/17/2021, 1:42 PM
At some point we started blocking PRAGMA commands that are not in an explicit allowlist. It seems reasonable to add it. Would you open a github issue for that?
1:43 PM
As a worker around. you can probably use `GLOB, but I’m not sure it’s a 1:1 replacement
a

Ahmed

08/17/2021, 1:44 PM
sure, thanks a lot for clarifying this to me. i’ll create an issue there. if you have an example of
GLOB
that would be great, i’ll try to research that.
1:45 PM
Unlike the 
LIKE
 operator, the 
GLOB
 operator is case sensitive and uses the UNIX wildcards. In addition, the 
GLOB
 patterns do not have escape characters.https://www.sqlitetutorial.net/sqlite-glob/
s

seph

08/17/2021, 1:46 PM
select 'a' GLOB 'A'
for example
a

Ahmed

08/17/2021, 1:47 PM
yes its case sensitive. which not what i was looking for
osquery> select 'a' GLOB 'A';
+--------------+
| 'a' GLOB 'A' |
+--------------+
| 0            |
+--------------+
osquery> select 'a' GLOB 'a';
+--------------+
| 'a' GLOB 'a' |
+--------------+
| 1            |
+--------------+
s

seph

08/17/2021, 1:49 PM
Er, wait…. You said
case_sensitive_like=1;
which is setting case sensitivity. LIKE defaults to insensitive
1:49 PM
I think it’s reasonable to add this to the allowlist, happy to review a PR, or issue for it.
1:50 PM
But tactically, sqlite looks kinda weird?
LIKE
defaults to case insensitive, and needs that pragma to become case sensitive. Most other comparators default to case sensitive, and you can use
COLLATE NOCASE
to make them insensitive. I’m not sure what you’re looking for though
a

Ahmed

08/17/2021, 1:50 PM
sorry, yes that what i was looking for it seems i lost my track of thoughts for a second. 😄 i will add an issue anyway and send it to you. and i will use GLOB in my case for now. thanks a lot Seph
s

seph

08/17/2021, 1:51 PM
Great! Glad there’s a workaround. (Yeah, I keep getting confused by them too)
a

Ahmed

08/17/2021, 2:05 PM