Title
#general
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.
1:29 PM
Actually, I take that back, it was a misnamed
FROM
statement
1:30 PM
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
1:56 PM
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.
2:32 PM
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
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 🙂
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.
4:45 PM
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.
3:51 AM
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
3:51 AM
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
);
3:51 AM
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"
      ]
    }
  }
}
3:52 AM
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.