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

slevchenko

01/08/2022, 5:16 PM
@seph @fritz JFYI, I was wrong about line breaks is sql statements, real issue was
path
field, this field is kinda reserved or something, so ATC table can't contain such field in any form not
path
nor
Path
or
PATH
. If custom ATC table contains
path
osqueryd returns:
E0108 18:52:27.938575 33708 virtual_table.cpp:584] Error creating virtual table: trusted_binaries (1): SQLITE_ERROR
s

seph

01/08/2022, 5:18 PM
Hrm. IIRC defining path is a warning, not a fatal error
s

slevchenko

01/08/2022, 5:19 PM
Well above is an actual message which almost drove me to the hair pulling point 🙂
And more then that it actually breaks queries relying on it
s

seph

01/08/2022, 5:20 PM
Are you sure the underlying SQLite db has a path column?
s

slevchenko

01/08/2022, 5:21 PM
That's my table
Error creating virtual table: trusted_binaries (1): SQLITE_ERROR osqueryd[30983]: I0108 18:37:42.011741 30983 virtual_table.cpp:587] Cannot create virtual table using: CREATE TABLE trusted_binaries(`path` TEXT, `registeredAt` TEXT, `Path` TEXT, `Sha256` TEXT, `VThits` TEXT)
s

seph

01/08/2022, 5:22 PM
That lists path twice?
s

slevchenko

01/08/2022, 5:23 PM
yes, first
path
was injected by osquery, and second
Path
is mine
s

seph

01/08/2022, 5:23 PM
Anyhow, I'm not in a position to generate test cases today, or review code. I'm not sure it's the existence of path, vs something else in you sql -- we have tables with path in them.
s

slevchenko

01/08/2022, 5:23 PM
so once I've replaced my
Path
with
File
it works
Anyway that's JFYI, maybe just mentioning this in actual doc would be enough
f

fritz

01/08/2022, 6:50 PM
@seph , @slevchenko is not wrong, i have run into the same fatal error where defining a column in the atc config as the name:
path
causes issues because it is reserved
s

seph

01/10/2022, 6:44 PM
Okay. I made a test case. I spent a bit of time playing with this. I'm not totally sure, but... 1. You can absolutely have an ATC with a
path
column. You'll get a warning about it being not suported. 2. In osquery, this appears to be a case sensitive set of operations. we check for a
path
column, and if found, emit a warning and don't add the osquery defined one. 3. But if you create a
Path
column, osquery doesn't know. 4. sqlite appears to be case insensitive. 5. So you can end up with a conflicting
Path
and
path
. You could get this by definging both a
path
and a
Path
column.
Generally speaking, the ATC functionlity is a little fussy. It works, but and kind of skew between the configuration sql and actual table results in really hard to diagnose errors.
I think that we don't notice the
path
/
Path
conflict is a bug. https://github.com/osquery/osquery/issues/7441
s

slevchenko

01/18/2022, 5:29 PM
Thanks @seph, one bug less. I'm glad if my post helped even in slightest.
s

seph

01/18/2022, 6:02 PM
Thank you for reporting it!