Closed pcoccoli closed 9 months ago
The extract call generates invalid SQL when the STIX object path contains a ref list:
extract
LOGLEVEL=DEBUG firepit extract msgs email-message email_qid "[email-message:to_refs[*].value = 'mary@example.com']" DEBUG:firepit.sqlitestorage:Connection to SQLite DB stix.db successful DEBUG:firepit.sqlitestorage:Executing query: SELECT value FROM "__metadata" WHERE name = 'dbversion' DEBUG:firepit.sqlstorage:Extract email-message as msgs from email_qid with [email-message:to_refs[*].value = 'mary@example.com'] DEBUG:firepit.sqlstorage:stix2sql produced " JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value" = 'mary@example.com')" DEBUG:firepit.sqlitestorage:Executing query: BEGIN; DEBUG:firepit.sqlitestorage:_create_view: "msgs" stmt "SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT "email-message".id FROM "email-message" INNER JOIN __queries ON "email-message".id = __queries.sco_id WHERE query_id = 'email_qid' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value" = 'mary@example.com')));" DEBUG:firepit.sqlitestorage:Executing query: SELECT sql from sqlite_master WHERE type='view' and name=? DEBUG:firepit.sqlitestorage:Executing query: CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT "email-message".id FROM "email-message" INNER JOIN __queries ON "email-message".id = __queries.sco_id WHERE query_id = 'email_qid' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value" = 'mary@example.com'))); ERROR:firepit.sqlitestorage:CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT "email-message".id FROM "email-message" INNER JOIN __queries ON "email-message".id = __queries.sco_id WHERE query_id = 'email_qid' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" = "r"."source_ref" WHERE "r"."target_ref" IN (SELECT "id" FROM "email-addr" WHERE "value" = 'mary@example.com')));: near "JOIN": syntax error ╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮ │ │ │ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:161 in _do_execute │ │ │ │ 158 │ │ try: │ │ 159 │ │ │ logger.debug('Executing query: %s', query) │ │ 160 │ │ │ if not values: │ │ ❱ 161 │ │ │ │ cursor.execute(query) │ │ 162 │ │ │ else: │ │ 163 │ │ │ │ cursor.execute(query, values) │ │ 164 │ │ except sqlite3.OperationalError as e: │ │ │ │ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │ │ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40> │ │ │ │ query = 'CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" │ │ │ │ I'+312 │ │ │ │ self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ values = None │ │ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────────╯ OperationalError: near "JOIN": syntax error The above exception was the direct cause of the following exception: ╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮ │ │ │ /home/pcoccoli/github/firepit/firepit/cli.py:82 in extract │ │ │ │ 79 ): │ │ 80 │ """Create a view of a subset of cached data""" │ │ 81 │ db = get_storage(state['dbname'], state['session']) │ │ ❱ 82 │ db.extract(name, sco_type, query_id, pattern) │ │ 83 │ │ 84 │ │ 85 @app.command() │ │ │ │ ╭───────────────────────────────── locals ──────────────────────────────────╮ │ │ │ db = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ name = 'msgs' │ │ │ │ pattern = "[email-message:to_refs[*].value = 'mary@example.com']" │ │ │ │ query_id = 'email_qid' │ │ │ │ sco_type = 'email-message' │ │ │ ╰───────────────────────────────────────────────────────────────────────────╯ │ │ /home/pcoccoli/github/firepit/firepit/sqlstorage.py:579 in extract │ │ │ │ 576 │ │ validate_name(viewname) │ │ 577 │ │ logger.debug('Extract %s as %s from %s with %s', │ │ 578 │ │ │ │ │ sco_type, viewname, query_id, pattern) │ │ ❱ 579 │ │ self._extract(viewname, sco_type, sco_type, pattern, query_id) │ │ 580 │ │ │ 581 │ def filter(self, viewname, sco_type, input_view, pattern): │ │ 582 │ │ """ │ │ │ │ ╭───────────────────────────────── locals ──────────────────────────────────╮ │ │ │ pattern = "[email-message:to_refs[*].value = 'mary@example.com']" │ │ │ │ query_id = 'email_qid' │ │ │ │ sco_type = 'email-message' │ │ │ │ self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ viewname = 'msgs' │ │ │ ╰───────────────────────────────────────────────────────────────────────────╯ │ │ │ │ /home/pcoccoli/github/firepit/firepit/sqlstorage.py:364 in _extract │ │ │ │ 361 │ │ │ │ f' INNER JOIN __queries ON "{sco_type}".id = __queries.sco_id' │ │ 362 │ │ │ │ f' WHERE {where});') │ │ 363 │ │ │ │ ❱ 364 │ │ cursor = self._create_view(viewname, select, sco_type, deps=[tablename], cursor= │ │ 365 │ │ self.connection.commit() │ │ 366 │ │ cursor.close() │ │ 367 │ │ │ │ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │ │ │ clause = "query_id = 'email_qid'" │ │ │ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40> │ │ │ │ pattern = "[email-message:to_refs[*].value = 'mary@example.com']" │ │ │ │ query_id = 'email_qid' │ │ │ │ sco_type = 'email-message' │ │ │ │ select = 'SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT │ │ │ │ "email-messa'+290 │ │ │ │ self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ tablename = 'email-message' │ │ │ │ viewname = 'msgs' │ │ │ │ where = 'query_id = \'email_qid\' AND ( JOIN "__reflist" AS "r" ON "email-message"."id" │ │ │ │ = "'+110 │ │ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │ │ │ │ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:216 in _create_view │ │ │ │ 213 │ │ if self._is_sql_view(viewname, cursor): │ │ 214 │ │ │ is_new = False │ │ 215 │ │ │ self._execute(f'DROP VIEW IF EXISTS "{viewname}"', cursor) │ │ ❱ 216 │ │ self._execute(f'CREATE VIEW "{viewname}" AS {select}', cursor) │ │ 217 │ │ if is_new: │ │ 218 │ │ │ self._new_name(cursor, viewname, sco_type) │ │ 219 │ │ return cursor │ │ │ │ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │ │ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40> │ │ │ │ deps = ['email-message'] │ │ │ │ is_new = True │ │ │ │ sco_type = 'email-message' │ │ │ │ select = 'SELECT "email-message".* FROM "email-message" WHERE "id" IN (SELECT │ │ │ │ "email-messa'+290 │ │ │ │ self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ viewname = 'msgs' │ │ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │ │ │ │ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:185 in _execute │ │ │ │ 182 │ │ return cursor │ │ 183 │ │ │ 184 │ def _execute(self, statement, cursor=None): │ │ ❱ 185 │ │ return self._do_execute(statement, cursor=cursor) │ │ 186 │ │ │ 187 │ def _query(self, query, values=None, cursor=None): │ │ 188 │ │ cursor = self._do_execute(query, values=values, cursor=cursor) │ │ │ │ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │ │ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40> │ │ │ │ self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ statement = 'CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" │ │ │ │ I'+312 │ │ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │ │ │ │ /home/pcoccoli/github/firepit/firepit/sqlitestorage.py:176 in _do_execute │ │ │ │ 173 │ │ │ │ raise UnknownViewname(e.args[0]) from e │ │ 174 │ │ │ elif e.args[0].endswith("syntax error"): │ │ 175 │ │ │ │ # We see this on SQL injection attempts │ │ ❱ 176 │ │ │ │ raise UnexpectedError(e.args[0]) from e │ │ 177 │ │ │ elif e.args[0].endswith("table") and e.args[0].endswith(" already exists"): │ │ 178 │ │ │ │ tablename = e.args[0].split('"')[1] │ │ 179 │ │ │ │ raise DuplicateTable(tablename) from e │ │ │ │ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │ │ │ cursor = <sqlite3.Cursor object at 0x7fe266db6a40> │ │ │ │ query = 'CREATE VIEW "msgs" AS SELECT "email-message".* FROM "email-message" WHERE "id" │ │ │ │ I'+312 │ │ │ │ self = <firepit.sqlitestorage.SQLiteStorage object at 0x7fe26963a1f0> │ │ │ │ values = None │ │ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │ ╰──────────────────────────────────────────────────────────────────────────────────────────────────╯ UnexpectedError: near "JOIN": syntax error
The
extractcall generates invalid SQL when the STIX object path contains a ref list: