"Datatype mismatch" on INSERT statements (serialization error)

[JulianCeaser]

I downloaded the official osquery 4.5.0.msi and installed on a Windows 10 system. When running osqueryi.exe using the following
osqueryi.exe --allow_unsafe --extension trailofbits_osquery_extensions.ext.exe

I am getting lots of errors when trying to use any INSERT or DELETE commands in the HostBlacklist or PortBlacklist tables. Any help would be much appreciated.

I have tried on two Windows 10 systems and both are showing the same issue.

osquery> select * from HostBlacklist;
+---------+---------------+-----------+----------------+-----------+
| address | domain        | sinkhole  | firewall_block | dns_block |
+---------+---------------+-----------+----------------+-----------+
|         | www.yahoo.com | 127.0.0.1 |                | UNMANAGED |
+---------+---------------+-----------+----------------+-----------+
osquery> DELETE FROM HostBlacklist WHERE domain="www.yahoo.com";
Error: SQL logic error
osquery> DELETE FROM HostBlacklist WHERE domain ="www.yahoo.com";
Error: SQL logic error
osquery> DELETE FROM HostBlacklist WHERE domain = "www.yahoo.com";
Error: SQL logic error
osquery> SELECT domain from HostBlacklist;
+---------------+
| domain        |
+---------------+
| www.yahoo.com |
+---------------+
osquery> INSERT INTO HostBlacklist(domain, sinkhole, address_type) VALUES ("www.google.com", "127.0.0.1", "ipv4");
Error: datatype mismatch
osquery>

[mike-myers-tob]

Confirmed that this happens on both Windows 10 and Ubuntu Linux 18, with osquery 4.5.1. It happens with not only this extension and table, but the windows_sync_objects table too. There must've been a breaking change in the osquery extensions SDK we will have to fix.

Error: datatype mismatch appears to be coming from the SQLite library.

With --verbose the output additionally includes I1106 17:34:01.640420 7848 virtual_table.cpp:403] Failed to serialize the INSERT request.

Ref: https://github.com/osquery/osquery/blob/8e504ff8143b411d1316d557e14e1d3a1dab48f7/osquery/sql/virtual_table.cpp#L403

@alessandrogario @Smjert do either of you know what changed in the SDK?

[GarretReece]

after some digging, the culprit is osquery/osquery#6006

The short answer is that running osquery with the command line option --extensions_default_index=false will fix this behavior. We need to investigate whether this is worth trying to add additional fixes or special handling for tables that provide write capabilities.

A slightly longer explanation: the sdk changed slightly with the referenced PR, and now all columns in extensions that are listed with the columnOption of DEFAULT are set to INDEX instead. INDEX columns are required for an update, so osquery was rejecting the insert statements that only specified a portion of the row's values.

[clong-msec]

Hi folks, this is seemingly affecting the santa extension as well and --extensions_default_index=false doesn't seem to be a viable workaround in this case. For example, let's say I have 20 whitelist rules and I run:

delete from santa_rules where state='whitelist';

I then end up with 19 whitelist rules, with only one of them having been removed from that query (when obviously all 20 should have).

Here's an example:

osquery> select count(*) from santa_rules;
+----------+
| count(*) |
+----------+
| 22       |
+----------+
osquery> delete from santa_rules where state='whitelist';
Error: SQL logic error
osquery> select count(*) from santa_rules;
+----------+
| count(*) |
+----------+
| 21       |
+----------+

Any idea why the default index flag doesn't seem to be fixing things here?