How to make regex_match patterns case insensitive in osquery queries?

Question

I'm currently working with osquery's ATC tables and utilizing regex_match() in my queries. However, I'm encountering a challenge with making the regex_match patterns case insensitive.

As per the osquery documentation osquery sql functions, the syntax for regex_match is regex_match(COLUMN, PATTERN, INDEX). However, it doesn't seem to provide an option for case insensitivity.

For instance, here's a sample query:

SELECT DISTINCT * FROM testregistry WHERE reg_key LIKE regex_match (reg_key, '\\\\REGISTRY\\\\USER\\\\(S-[0-9\\-]+|\\\\.DEFAULT)\\\\SOFTWARE\\\\Microsoft\\\\Windows NT\\\\CurrentVersion\\\\WinTrust',0)

I need to modify this query to make the regex_match pattern case insensitive.

Could someone provide insights or workaround on how to achieve case insensitivity with regex_match in osquery queries? Any help or suggestions would be greatly appreciated.

Answer 1

Convert the column to the same case as the regexp.

SELECT DISTINCT * FROM testregistry WHERE regex_match (UPPER(reg_key), '\\\\REGISTRY\\\\USER\\\\(S-[0-9\\-]+|\\\\.DEFAULT)\\\\SOFTWARE\\\\MICROSOFT\\\\WINDOWS NT\\\\CURRENTVERSION\\\\WINTRUST',0)

There's no need to use both LIKE and REGEX_MATCH(). If there's no match, regex_match() will return a falsey value.