Hi, I'm just wondering about case sensitivity in ...
# general
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:
. 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?
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! 👍
This is expected, if confusing.
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.
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.
@seph Do you see any issues with using the solution @tokcum has outlined?
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. •
is a good one •
is always case insensitive •
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
That was the context I was looking for, thanks!
Thanks for sharing your thoughts on this. :)
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.