Title
#general
t

tokcum

05/09/2022, 1:09 PM
Hi, I'm just wondering about case sensitivity in osquery. Recently I've became aware of that string comparison might be case sensitive even if the underlying operating system is case insensitive. I was looking for a registry key with osquery and realized that there are different spellings:
RefusePassworChange
or
refusepassworchange
. I did some research on how to query with case insensitive and after some tests with lower(), I found this solution:
SELECT * FROM registry WHERE path = "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Netlogon\Parameters\RefusePassworChange" COLLATE NOCASE;
Without "COLLATE NOCASE" this statement will not match different spellings like "refusepasswordchange". Any other thoughts on this?
defensivedepth

defensivedepth

05/10/2022, 10:53 AM
I think this is a really great point, and something I will do some further testing on. I typically pull a bunch of data with osquery and then let the backend system (Elasticsearch in my case) parse & alert. We use a custom-built analyzer in Elasticsearch because it doesn't support wildcard, case-insensitive searches out of the box. Good find! 👍
s

seph

05/10/2022, 4:55 PM
This is expected, if confusing.
4:58 PM
The underlying datasource is case insensitive. The the registry will return in whatever case, and when you search it, you get data back, But sqlite is case sensitive. So when the data comes back and is compared by that
path =
filter, it has to match.
4:58 PM
This happens because the
path =
does double duty. First, it is passed to the underlying table’s generate routine to generate data. Second, it’s used by sqlite to filter.
defensivedepth

defensivedepth

05/10/2022, 5:53 PM
@seph Do you see any issues with using the solution @tokcum has outlined?
s

seph

05/10/2022, 6:47 PM
I’m not sure I understand? AFAIK forcing sqlite to do a case insensitive match is the only fix. (well, short of updating an implementation) There are several ways to get sqlite to compare case insensitively. •
COLLATE NOCASE
is a good one •
LIKE
is always case insensitive •
lower()
probably also good I don’t remember if there are performance implications. But probably not, since you’re hitting the registry so it’s not going to be that fast
defensivedepth

defensivedepth

05/11/2022, 10:04 AM
That was the context I was looking for, thanks!
t

tokcum

05/11/2022, 2:25 PM
Thanks for sharing your thoughts on this. 😃
s

seph

05/11/2022, 3:27 PM
This has confused me several times in the past 😉 A bunch of my tables take some input, and then do something with it. But need to track it to return it back so sqlite doesn’t filter out the rows.