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

slevchenko

12/07/2021, 11:52 AM
Hi everyone. Trying to use ATC feature, and hitting following error: 
I1207 13:47:10.155495 48644 virtual_sqlite_table.cpp:111] ATC table: Could not prepare database at path: "/etc/osquery/quarantine.db"
I1207 13:47:10.155580 48644 auto_constructed_tables.cpp:38] ATC Table: Unable to detect journal mode, applying default locking policy for path /etc/osquery/quarantine.db
I1207 13:47:10.155865 48644 virtual_sqlite_table.cpp:111] ATC table: Could not prepare database at path: "/etc/osquery/quarantine.db"
W1207 13:47:10.155910 48644 auto_constructed_tables.cpp:47] ATC Table: Error Code: 26 Could not generate data: Could not prepare database for path /etc/osquery/quarantine.db
I suspect that that's due to: https://github.com/osquery/osquery/issues/5225 since error message does mention 
journal mode
detection problem, but I'm not sure how to fix this from my side. Does anyone know how to fix\prevent such issues ?
f

fritz

12/07/2021, 1:28 PM
@slevchenko in the past I have seen that error due to a malformed ATC config file, iirc it was a misnamed column the last time I saw it.
Actually, I take that back, it was a misnamed 
FROM
statement
Here's the last time someone raised this: https://osquery.slack.com/archives/C08V7KTJB/p1631826420144600
s

slevchenko

12/07/2021, 1:55 PM
Thanks @fritz. I've resolved this issue. In my case following requirements had to be met: 1. db file had to be sqlite3 on some distros 3-rd version is not default 2. journal_mode had to be set 3. sqlite table had to be created with a single line statement, no linebreaks, tabs or any fancy formatting
in my case journal_mode = 'delete' works
f

fritz

12/07/2021, 1:57 PM
ah yes, #3 is super important
s

slevchenko

12/07/2021, 1:59 PM
That almost drove me nuts, since other tools, browsers and python libs worked fine even with that formatting. So it's super important to be mentioned somewhere in docs
s

seph

12/07/2021, 2:32 PM
Hrm. That feels like a bug.
Would you file it? I can't promise to fix it, but it should be fixable...
s

slevchenko

12/07/2021, 2:49 PM
Will try to do it. I'll need some time to reproduce this behavior by removing changes I made to make it work
f

fritz

12/07/2021, 3:01 PM
I doubt it is a bug, json does not like line-breaks generally speaking iirc? I thought you had to use 
\n
newlines
@slevchenko https://blog.kolide.com/build-custom-osquery-tables-using-atc-ab112a30674c#e2e0 I added a section on newlines
s

slevchenko

12/07/2021, 3:17 PM
@fritz Thank you very much. As you said this feature is awesome and I'm already using it 🙂
❤️ 1
s

seph

12/07/2021, 3:55 PM
json? I thought we were talking about the underlying sql databases
s

slevchenko

12/07/2021, 4:41 PM
@seph Yes, we are. In a blogpost above json syntax was used,my case it was python library which was relying on (pseudo code follows): 
CREATE table quarantine(
  ...
  ...
);
In my case statements were formatted by IDE, and worked in any python, sqlite client and IDE itself. That's why it was very hard to notice what was actually wrong.
So regardless of initial source, be it json or just text sql if statements end up containing newline chars, these statements are not recognized by osquery ATC.
s

seph

12/09/2021, 3:29 AM
Seems like either a bug, or some subtle error in what you're doing.
Testing this quickly, I cannot replicate you're issue: 
read -r -d '' SQL <<'EOF'
CREATE TABLE testatc (
  astring TEXT,
  anum INT
);

INSERT INTO testatc(astring, anum)
VALUES
('a', 1),
('b', 2);
EOF

rm -f newlines.db
printf "$SQL" | sqlite3 newlines.db

rm -f nolines.db
printf "$SQL" | tr -d "\n" | sqlite3 nolines.db
will make two sqlite DBs
You can see their schemas as: 
pemberton:osquery-atc-bug seph$ sqlite3 nolines.db .schema
CREATE TABLE testatc (  astring TEXT,  anum INT);
pemberton:osquery-atc-bug seph$ sqlite3 newlines.db .schema
CREATE TABLE testatc (
  astring TEXT,
  anum INT
);
Using: 
pemberton:osquery-atc-bug seph$ cat osq.conf 
{
  "auto_table_construction": {
    "newlines": {
      "query": "select astring, anum from testatc",
      "path": "newlines.db",
      "columns": [
        "astring",
        "anum"
      ]
    },
    "nolines": {
      "query": "select astring, anum from testatc",
      "path": "nolines.db",
      "columns": [
        "astring",
        "anum"
      ]
    }
  }
}
ATC tables work fine: 
osquery> select * from nolines;
+----------------------------------------+---------+------+
| path                                   | astring | anum |
+----------------------------------------+---------+------+
| /Users/seph/osquery-atc-bug/nolines.db | a       | 1    |
| /Users/seph/osquery-atc-bug/nolines.db | b       | 2    |
+----------------------------------------+---------+------+
osquery> select * from newlines;
+-----------------------------------------+---------+------+
| path                                    | astring | anum |
+-----------------------------------------+---------+------+
| /Users/seph/osquery-atc-bug/newlines.db | a       | 1    |
| /Users/seph/osquery-atc-bug/newlines.db | b       | 2    |
+-----------------------------------------+---------+------+
s

slevchenko

12/12/2021, 8:18 AM
@seph I can't reproduce this either on version 5.0.1. Well I hope this bug is gone for good.
7 Views
#generalJoin Slack
Powered by