Title
#general
packetzero

packetzero

03/08/2019, 3:35 PM
Heads up ... you will likely need to change your queries to prepare for PR-5422 in production (currently in fb's experimental branch). Constraint checking added, so queries that don't satisfy 'required' column queries will error with 'constraint failed'. This is a good thing, as without this change, sqlite is doing things behind the scenes that are not expected. Things to look for:- you may need to fix ordering and qualifiers on JOIN to be LEFT JOIN - no more using multiple LIKE constraints on a required column, only multiple Equals or IN(). So you have to get creative with IN(select like or like) https://github.com/facebook/osquery/pull/5422
3:46 PM
One example is in hardware-monitoring.conf pack:
select file.path, uid, gid, mode, 0 as atime, mtime, ctime, md5, sha1, sha256 from (select * from file where path like '/System/Library/CoreServices/%.efi' union select * from file where path like '/System/Library/LaunchDaemons/com.apple%efi%') file join hash using (path);
3:47 PM
Needs a LEFT join on hash
3:53 PM
Second example is in osx-attacks.conf pack:
select * from file where
        path LIKE '/Users/%/Library/.kernel_%' OR
        path LIKE '/Users/%/Library/kernel_service';
3:54 PM
Needs to be a bit convoluted now:
SELECT * FROM file WHERE path IN (
        SELECT path FROM file WHERE path LIKE '/Users/%/Library/iMovie/%'
        UNION
        SELECT path FROM file WHERE path LIKE '/Users/%/Library/kernel_service')
zwass

zwass

03/08/2019, 4:36 PM
Thank you for the heads up on this. Are you saying that some queries that formerly returned the expected results will now error out?
packetzero

packetzero

03/08/2019, 5:19 PM
yes
5:22 PM
and likely the results returned now are not what would always be expected. For example, a table implementation may just return empty rows if constraint is not passed in
clong

clong

03/08/2019, 5:51 PM
can you open an issue for this?
packetzero

packetzero

03/08/2019, 5:56 PM
will do
5:57 PM
it's more of an education thing. not sure we can change the behavior
clong

clong

03/08/2019, 7:14 PM
lol this is going to break so much stuff for us
packetzero

packetzero

03/08/2019, 7:18 PM
well, the good news is you can start changing it now, and you will be ready when the time comes.