data flatten table should handle binary data passed as raw_data

[James-Pickett]

using an ATC config such as this

{
"auto_table_construction": {
    "kolide_apple_accounts_db": {
      "query": "SELECT ZUSERNAME AS username, ZACCOUNTDESCRIPTION AS account_description, ZACCOUNTTYPEDESCRIPTION AS account_type_description, ZACCOUNT.ZOWNINGBUNDLEID AS owning_bundleid, ZACCOUNT.ZDATACLASSPROPERTIES AS dataclass_properties FROM ZACCOUNT LEFT JOIN ZACCOUNTTYPE ON ZACCOUNT.ZACCOUNTTYPE = ZACCOUNTTYPE.Z_PK",
      "path": "/Users/%/Library/Accounts/Accounts4.sqlite",
      "columns": [
        "username",
        "dataclass_properties",
        "account_description",
        "account_type_description",
        "owning_bundleid",
        "account_type"
      ],
      "platform": "darwin"
  }
 }
}

will result in an error for a query such as

select * from kolide_plist where raw_data = (select dataclass_properties from kolide_apple_accounts_db);

because the dataclass_properties column is binary data

one possible solution is to adjust the ATC config to pass the binary column as hex like:

{
"auto_table_construction": {
    "kolide_apple_accounts_db": {
      "query": "SELECT ZUSERNAME AS username, ZACCOUNTDESCRIPTION AS account_description, ZACCOUNTTYPEDESCRIPTION AS account_type_description, ZACCOUNT.ZOWNINGBUNDLEID AS owning_bundleid, hex(ZACCOUNT.ZDATACLASSPROPERTIES) AS dataclass_properties FROM ZACCOUNT LEFT JOIN ZACCOUNTTYPE ON ZACCOUNT.ZACCOUNTTYPE = ZACCOUNTTYPE.Z_PK",
      "path": "/Users/%/Library/Accounts/Accounts4.sqlite",
      "columns": [
        "username",
        "dataclass_properties",
        "account_description",
        "account_type_description",
        "owning_bundleid",
        "account_type"
      ],
      "platform": "darwin"
  }
 }
}

and then decode the hex with inside launcher before parsing the plist

[directionless]

I suspect we'll need to encode it. Can we could use base64 and not hex?

[James-Pickett]

I suspect we'll need to encode it. Can we could use base64 and not hex?

@directionless , is there a function for base64 encoding in sqlite? I did some googling, but could find anything outside of sqlite extensions.

[directionless]

Osquery ships a to_base64 and from_base64

[James-Pickett]

@directionless , looks like these the to_base64 is not available to use in ATC creation. So far the only way I've found to get binary columns from ATCs is the hex func

[directionless]

Huh. So I guess ATC doesn't expose the osquery sql extensions.

Naive question, when you did this without the hex, I assume it was a straight join. I'm wondering if it would work if the ATC was still bare, but our raw_data allowed a base64 in, and we joined there. I don't have the right words, but I think there are 2 places this is getting passed as a string, and I'm trying to isolate where the issue is.

Though saying that.... There's high odds the ATC implementation doesn't handle binary data correctly. I wonder if we can fix that.