Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

UnexpectedError: near "JOIN": syntax error #118

Closed
pcoccoli opened this issue Jan 17, 2024 · 0 comments · Fixed by #119
Closed

UnexpectedError: near "JOIN": syntax error #118

pcoccoli opened this issue Jan 17, 2024 · 0 comments · Fixed by #119
Assignees
@pcoccoli

Comments

@pcoccoli
Copy link
Collaborator

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@pcoccoli pcoccoli

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

stix20: fix SQL generation with ref lists opencybersecurityalliance/firepit
1 participant
@pcoccoli