Title
#macos
Magneto

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?
zwass

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.
6:06 PM
So
'_%'
will match any string with at least 1 character.
Magneto

Magneto

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

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
Magneto

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
6:15 PM
select username  from users where instr(username, "_") != 1;
Magneto

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.
zwass

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 🙂