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?

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.

@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. •

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

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.