Title
#core
Stefano Bonicatti

Stefano Bonicatti

10/28/2019, 7:15 PM
So another interesting case, as far as I can understand tables with two or more
required
columns, indeed require all of them in a WHERE clause? Though testing
select * from extended_attributes where path = '/usr'
works fine, though
select * from extended_attributes directory = '/'
gives an empty result (but if we confront the result from the first query, directory is '/'). So either way I would expect both query to fail, due to the
required
, or both to succeed and return something.
packetzero

packetzero

10/28/2019, 7:23 PM
That's an unusual case, and I'm not sure how to handle it. Required can't be optional, and have to be specified. Perhaps they should all just be INDEX and let the tables return empty if one of the required constraints isn't present.
Stefano Bonicatti

Stefano Bonicatti

10/28/2019, 7:28 PM
On the documentation we say "This will create a warning if the table is used and the column does not appear in the predicate.", though it's not clear what happens with the results indeed. Currently if I give no required columns I get the error "Error: no query solution". I would expect to get that until I put both required columns.
7:52 PM
So very naively, I've found this https://github.com/osquery/osquery/blob/0c06658e45b493583d55ec2486456e3ce44dfa35/osquery/sql/virtual_table.cpp#L910-L914 Shouldn't be
required_satisfied = true
only if all the required constraint are present? Also below I see:https://github.com/osquery/osquery/blob/0c06658e45b493583d55ec2486456e3ce44dfa35/osquery/sql/virtual_table.cpp#L938-L941 So the warning that should come out but that it doesn't in any case. Time to open an issue I guess.
packetzero

packetzero

10/28/2019, 7:55 PM
I'll take a look
Stefano Bonicatti

Stefano Bonicatti

10/28/2019, 7:56 PM
ok! no sweat! I just noticed it because I was writing missing examples queries from the tables 🙂
packetzero

packetzero

10/28/2019, 8:23 PM
okay, so the code looks right. If any of required constraints are provided, it proceeds without error. A table like hash for example, file OR directory are required. use one or the other or both.
Stefano Bonicatti

Stefano Bonicatti

10/28/2019, 8:36 PM
ok, so they aren't all required. Next thing, most likely minor, is that when no constraint is put, you don't get that warning; probably because it's failing earlier in the logic?
packetzero

packetzero

10/28/2019, 8:38 PM
Does it give a SQL error ? something like constraints not satisfied ?
Stefano Bonicatti

Stefano Bonicatti

10/28/2019, 8:39 PM
it returns with
Error: no query solution
packetzero

packetzero

10/28/2019, 8:39 PM
it seems to vary depending on SQLite version
8:39 PM
right, so if a table has a required column, and it's not specified, you will get an error.
8:40 PM
the exact table of the error vary depending on sqlite version
8:40 PM
the exact text ...
Stefano Bonicatti

Stefano Bonicatti

10/28/2019, 8:41 PM
mmm... that might end up being a bit confusing.
8:42 PM
but obviously I'm speaking without knowing fully what's the reason. I'm just wondering if there's a way to support most cases (of sqlite versions) and give the same error
8:43 PM
but anyway, thanks for looking into it!
packetzero

packetzero

10/28/2019, 8:44 PM
np. I'll try to look into the error codes later in the week