trying to limit returned users from the users tabl...
# macosm
Magneto
10/09/2020, 6:00 PMtrying to limit returned users from the users table in macos, getting unexpected results -
SELECT * from users WHERE username NOT LIKE '_%';SELECT * FROM users WHERE username NOT LIKE '_a%';z
zwass
10/09/2020, 6:05 PM
Probably to do with
_An underscore ("_") in the LIKE pattern matches any single character in the string.
zwass
10/09/2020, 6:06 PM
So
'_%'m
Magneto
10/09/2020, 6:10 PMdamn. any way to escape that?
z
zwass
10/09/2020, 6:10 PM
Copy code
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 '\' = 1m
Magneto
10/09/2020, 6:13 PMgot 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
seph
10/09/2020, 6:15 PM
select username from users where instr(username, "_") != 1;m
Magneto
10/09/2020, 6:22 PMSELECT * FROM users WHERE username NOT LIKE '\_%' ESCAPE '\';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 🙂