JFYI I was wrong about line breaks is sql s

Question

< 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```

seph · Answer

Hrm IIRC defining path is a warning not a fatal error

slevchenko · Answer

Well above is an actual message which almost drove me to the hair pulling point slightly smiling face

slevchenko · Answer

And more then that it actually breaks queries relying on it

seph · Answer

Are you sure the underlying SQLite db has a path column

slevchenko · Answer

That s my table

slevchenko · Answer

```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 ```

seph · Answer

That lists path twice

slevchenko · Answer

yes first `path` was injected by osquery and second `Path` is mine

seph · Answer

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

slevchenko · Answer

so once I ve replaced my `Path` with `File` it works

slevchenko · Answer

Anyway that s JFYI maybe just mentioning this in actual doc would be enough

fritz · Answer

< 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

seph · Answer

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

seph · Answer

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

seph · Answer

Specifically you re looking at the code <https github com directionless osquery blob c312e6ed3f18f4ba0c8ec0822adb3e1126bd1a1c osquery sql virtual sqlite table cpp L64 L69>

seph · Answer

I think that we don t notice the `path` `Path` conflict is a bug <https github com osquery osquery issues 7441>

slevchenko · Answer

Thanks < seph> one bug less I m glad if my post helped even in slightest

seph · Answer

Thank you for reporting it