https://github.com/osquery/osquery logo
Title
m

Magneto

10/09/2020, 6:00 PM
trying to limit returned users from the users table in macos, getting unexpected results -
SELECT * from users WHERE username NOT LIKE '_%';
returns nothing, but
SELECT * FROM users WHERE username NOT LIKE '_a%';
returns expected results (I get _d and _k and _n usernames, but no _a results). v4.2.0 am I crazy?
z

zwass

10/09/2020, 6:05 PM
Probably to do with
_
being a special character in sqlite: https://sqlite.org/lang_expr.html
An underscore ("_") in the LIKE pattern matches any single character in the string.
So
'_%'
will match any string with at least 1 character.
m

Magneto

10/09/2020, 6:10 PM
damn. any way to escape that?
z

zwass

10/09/2020, 6:10 PM
osquery> select 'zwass' LIKE '_%', '_zwass' LIKE '_%', 'zwass' LIKE '\_%' ESCAPE '\', '_zwass' LIKE '\_%' ESCAPE '\';
             'zwass' LIKE '_%' = 1
            '_zwass' LIKE '_%' = 1
 'zwass' LIKE '\_%' ESCAPE '\' = 0
'_zwass' LIKE '\_%' ESCAPE '\' = 1
m

Magneto

10/09/2020, 6:13 PM
got it, thanks!
s

seph

10/09/2020, 6:14 PM
Not sure if it’s better or worse, but you could use instr to see where the first character is, and then filter based on that
select username  from users where instr(username, "_") != 1;
m

Magneto

10/09/2020, 6:22 PM
SELECT * FROM users WHERE username NOT LIKE '\_%' ESCAPE '\';
was what I ended up with, gives me most everything I anticipate.
s

seph

10/09/2020, 6:28 PM
My gut sense is that instr has a slight performance win over like, but that it’s dwarfed by the underlying api calls.
z

zwass

10/09/2020, 6:30 PM
^ Agreed. I'd go with whatever you find most readable.
s

seph

10/09/2020, 6:30 PM
readable 💯 I found instr earlier today, so I’m enjoying it 🙂