UnexpectedError: near "JOIN": syntax error

[pcoccoli]

The extract call generates invalid SQL when the STIX object path contains a ref list:

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