opencybersecurityalliance / firepit Goto Github PK
View Code? Open in Web Editor NEWFirepit - STIX Columnar Storage
License: Apache License 2.0
firepit's People
Contributors
Stargazers
Watchers
Forkers
raymundl rongwutwc aidbutlr imolloy l1kw1d hecg119 charliewutw1 a110605 leila-rashidi cmadam priamaifirepit's Issues
support variable number of sub-second digits in `to_datetime()`
The current to_datetime() function in firepit.timestamp only supports 0,3,6 sub-second digits, which is the limitation of datetime.datetime.fromisoformat().
>>> import datetime
>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00")
datetime.datetime(2022, 2, 1, 0, 0)
>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00.123")
datetime.datetime(2022, 2, 1, 0, 0, 0, 123000)
>>> datetime.datetime.fromisoformat("2020-06-30T19:29:59.986123")
datetime.datetime(2020, 6, 30, 19, 29, 59, 986123)
>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00.1")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2022-02-01T00:00:00.1'
>>> datetime.datetime.fromisoformat("2022-02-01T00:00:00.12")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2022-02-01T00:00:00.12'
>>> datetime.datetime.fromisoformat("2020-06-30T19:29:59.96346")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ValueError: Invalid isoformat string: '2020-06-30T19:29:59.96346'
Help needed to make it work on variable number of sub-second digits input.
DependentObjectsStillExist execption with postgresql when trying to reuse a view name
One way to recreate:
firepit cache my_data ~/tmp/stix_bundle.json
firepit extract addrs1 ipv4-addr my_data "[ipv4-addr:value ISSUBSET '192.168.0.0/16']"
firepit assign addrs2 --op sort --by value addrs1
firepit extract addrs1 ipv4-addr my_data "[ipv4-addr:value ISSUBSET '192.168.1.0/24']"
The second time we extract and try to reuse the name addrs1 an exception is raised:
Traceback (most recent call last):
File "/home/pcoccoli/.pyenv/versions/gh36/bin/firepit", line 33, in <module>
sys.exit(load_entry_point('firepit', 'console_scripts', 'firepit')())
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/typer/main.py", line 214, in __call__
return get_command(self)(*args, **kwargs)
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 829, in __call__
return self.main(*args, **kwargs)
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/typer/main.py", line 497, in wrapper
return callback(**use_params) # type: ignore
File "/home/pcoccoli/github/firepit/firepit/cli.py", line 60, in extract
db.extract(name, sco_type, query_id, pattern)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 370, in extract
self._extract(viewname, sco_type, sco_type, pattern, query_id)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 187, in _extract
cursor = self._create_view(viewname, select, deps=[tablename], cursor=cursor)
File "/home/pcoccoli/github/firepit/firepit/pgstorage.py", line 116, in _create_view
self._execute(f'DROP VIEW IF EXISTS "{viewname}";', cursor)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 80, in _execute
cursor.execute(statement)
File "/home/pcoccoli/.pyenv/versions/3.6.8/envs/gh36/lib/python3.6/site-packages/psycopg2/extras.py", line 251, in execute
return super(RealDictCursor, self).execute(query, vars)
psycopg2.errors.DependentObjectsStillExist: cannot drop view addrs1 because other objects depend on it
DETAIL: view addrs2 depends on view addrs1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
CardinalityViolation: ON CONFLICT DO UPDATE command cannot affect row a second time
With PostgreSQL, duplicate identity objects (which you would naturally have if your data comes from stix-shifter and is broken up into pages where each page has an identity object for the stix-shifter connector configuration hat produced it) will raise the above exception.
For identity objects, we should just ignore duplicates.
STIX MATCHES operator is translated to SQL incorrectly
firepit just passes MATCHES through, but I think we need to use MATCH for SQLite3 (https://www.sqlite.org/lang_expr.html) and ~ for PostgreSQL (https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "__columns_otype_path_key"
There's a unique constraint on the __columns table but no ON CONFLICT in the INSERT statements. This can produce the above exception when using multiple threads.
NameError: name 'InvalidAttr' is not defined
Introduced in 1.0.7
File "/opt/modules/lib/python3.9/site-packages/firepit/sqlstorage.py", line 518, in run_query
return self._query(query_text, query_values)
File "/opt/modules/lib/python3.9/site-packages/firepit/pgstorage.py", line 108, in _query
raise InvalidAttr(str(e)) from e
NameError: name 'InvalidAttr' is not defined
fast translation error
Looks like I get an error with fast translation enabled (executed successfully with fast translation disabled).
proc = GET process FROM stixshifter://host101
WHERE name = 'cmd.exe'
START 2021-09-29T00:00:00Z STOP 2021-09-30T00:00:00Z
Where host101 points to our rainbow instance with indexes winlogbeat-* and beats dialects enabled (dialect is not an issue since it works with fast translation disabled).
Error
Log
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel/codegen/commands.py", line 622, in _prefetch
resp = ds_manager.query(data_source, stix_pattern, session_id, store)
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel/datasource/manager.py", line 33, in query
rs = i.query(uri, pattern, session_id, c, store)
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel_datasource_stixshifter/interface.py", line 288, in query
fast_translate(
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/kestrel_datasource_stixshifter/interface.py", line 358, in fast_translate
loop.run_until_complete(
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/nest_asyncio.py", line 90, in run_until_complete
return f.result()
File "/Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/futures.py", line 201, in result
raise self._exception
File "/Applications/Xcode.app/Contents/Developer/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/asyncio/tasks.py", line 256, in __step
result = coro.send(None)
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/firepit/aio/ingest.py", line 620, in ingest
odf.columns = [c.rpartition(':')[2] for c in cols]
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/generic.py", line 6002, in __setattr__
return object.__setattr__(self, name, value)
File "pandas/_libs/properties.pyx", line 69, in pandas._libs.properties.AxisProperty.__set__
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/generic.py", line 730, in _set_axis
self._mgr.set_axis(axis, labels)
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/internals/managers.py", line 225, in set_axis
self._validate_set_axis(axis, new_labels)
File "/Users/subx/venv/kestrel-release/lib/python3.9/site-packages/pandas/core/internals/base.py", line 70, in _validate_set_axis
raise ValueError(
ValueError: Length mismatch: Expected axis has 7 elements, new values have 5 elements
exception on Windows: NotImplementedError: c
Reported by user on Windows.
File "C:\Users\redacted\pyenv\redacted\lib\site-packages\firepit\__init__.py", line 30, in get_storage
raise NotImplementedError(url.scheme)
NotImplementedError: c
I think what happened here is that a Windows path was passed in as the url to get_storage. The urlparse looks at it and thinks c is the "scheme" but we only support "" or "postgresql". We could add some code to try and detect a Windows path and then skip urlparse.
missing dependency specification
requests used at raft.py seems missing in requirement specification.
UndefinedFunction for ifnull on postgresql
The join operation uses ifnull, but that function only exists on sqlite3 and not postgresql.
psycopg2.errors.SyntaxError: each UNION query must have the same number of columns
Traceback (most recent call last):
File "/home/pcoccoli/.pyenv/versions/gh392/bin/kestrel", line 7, in <module>
exec(compile(f.read(), __file__, 'exec'))
File "/home/pcoccoli/github/kestrel-lang/bin/kestrel", line 8, in <module>
runpy.run_module('kestrel', run_name='__main__')
File "/home/pcoccoli/.pyenv/versions/3.9.2/lib/python3.9/runpy.py", line 213, in run_module
return _run_code(code, {}, init_globals, run_name, mod_spec)
File "/home/pcoccoli/.pyenv/versions/3.9.2/lib/python3.9/runpy.py", line 87, in _run_code
exec(code, run_globals)
File "/home/pcoccoli/github/kestrel-lang/src/kestrel/__main__.py", line 54, in <module>
outputs = session.execute(huntflow)
File "/home/pcoccoli/github/kestrel-lang/src/kestrel/session.py", line 262, in execute
return self._execute_ast(ast)
File "/home/pcoccoli/github/kestrel-lang/src/kestrel/session.py", line 437, in _execute_ast
output_var_struct, display = execute_cmd(stmt, self)
File "/home/pcoccoli/github/kestrel-lang/src/kestrel/codegen/commands.py", line 92, in wrapper
return func(stmt, session)
File "/home/pcoccoli/github/kestrel-lang/src/kestrel/codegen/commands.py", line 60, in wrapper
ret = func(stmt, session)
File "/home/pcoccoli/github/kestrel-lang/src/kestrel/codegen/commands.py", line 262, in get
session.store.merge(
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 532, in merge
cursor = self._create_view(viewname, stmt, sco_type, deps=input_views)
File "/home/pcoccoli/github/firepit/firepit/pgstorage.py", line 141, in _create_view
self._execute(f'CREATE OR REPLACE VIEW "{viewname}" AS {select}', cursor)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 82, in _execute
cursor.execute(statement)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/psycopg2/extras.py", line 236, in execute
return super().execute(query, vars)
psycopg2.errors.SyntaxError: each UNION query must have the same number of columns
LINE 43: ...mbership.var = 'cmd_local'::text))) UNION SELECT process.ty...
^
unexpected exception from store.columns()
While doing a small refactoring for commands.py in Kestrel, I find an existing unit test does not behave as I thought.
The unit test: https://github.com/opencybersecurityalliance/kestrel-lang/blob/develop/tests/test_timestamped.py#L85
The huntflow to reproduce the exception and the stack:
conns = GET network-traffic
FROM https://raw.githubusercontent.com/opencybersecurityalliance/kestrel-lang/develop/tests/test_bundle.json
WHERE dst_ref.value NOT ISSUBSET '192.168.0.0/16'
grp_conns = GROUP conns BY dst_ref.value WITH COUNT(dst_ref) AS count
ts_grp_conns = TIMESTAMPED(grp_conns)
The error when running the huntflow:
Traceback (most recent call last):
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 161, in _do_execute
cursor.execute(query)
sqlite3.OperationalError: no such column: grp_conns.id
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/summary.py", line 97, in get_variable_entity_count
columns = variable.store.columns(variable.entity_table)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 290, in columns
cursor = self._execute(stmt)
^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 185, in _execute
return self._do_execute(statement, cursor=cursor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/firepit/sqlitestorage.py", line 168, in _do_execute
raise InvalidAttr(m) from e
firepit.exceptions.InvalidAttr: invalid attribute: grp_conns.id
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/subx/venv/kestrel-dev/bin/kestrel", line 9, in <module>
runpy.run_module("kestrel", run_name="__main__")
File "<frozen runpy>", line 229, in run_module
File "<frozen runpy>", line 88, in _run_code
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/__main__.py", line 32, in <module>
outputs = session.execute(huntflow)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/session.py", line 274, in execute
return self._execute_ast(ast)
^^^^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/session.py", line 427, in _execute_ast
output_var_struct, display = execute_cmd(stmt, self)
^^^^^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/commands.py", line 102, in wrapper
return func(stmt, session)
^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/commands.py", line 66, in wrapper
var_struct = new_var(
^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/symboltable/variable.py", line 128, in new_var
return VarStruct(
^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/symboltable/variable.py", line 38, in __init__
self.length = get_variable_entity_count(self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/subx/venv/kestrel-dev/lib/python3.11/site-packages/kestrel/codegen/summary.py", line 102, in get_variable_entity_count
raise MissingEntityAttribute(table_name, attr) from e
kestrel.exceptions.MissingEntityAttribute: [ERROR] MissingEntityAttribute: variable "grp_conns" does not have required attribute "id"
remove transform or specify different variable in the Kestrel command.
The strange: it is the function store.columns() that hit the InvalidAttr exception in firepit when running ts_grp_conns = TIMESTAMPED(grp_conns). Should it just return all columns?
NumericValueOutOfRange with large src_byte_count
With file https://github.com/opencybersecurityalliance/stix-shifter/blob/develop/data/cybox/20000.json:
firepit cache --session test-20k 20k /home/pcoccoli/github/stix-shifter/data/cybox/20000.json
Traceback (most recent call last):
File "/home/pcoccoli/.pyenv/versions/gh392/bin/firepit", line 33, in <module>
sys.exit(load_entry_point('firepit', 'console_scripts', 'firepit')())
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/typer/main.py", line 214, in __call__
return get_command(self)(*args, **kwargs)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 829, in __call__
return self.main(*args, **kwargs)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 782, in main
rv = self.invoke(ctx)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 1259, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 1066, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/click/core.py", line 610, in invoke
return callback(*args, **kwargs)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/typer/main.py", line 497, in wrapper
return callback(**use_params) # type: ignore
File "/home/pcoccoli/github/firepit/firepit/cli.py", line 48, in cache
db.cache(query_id, filenames)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 246, in cache
splitter.write(obj)
File "/home/pcoccoli/github/firepit/firepit/splitter.py", line 220, in write
self.writer.write_records(obj_type, self.records[obj_type], schema, self.replace, self.query_id)
File "/home/pcoccoli/github/firepit/firepit/splitter.py", line 153, in write_records
self.store.upsert(cursor, tablename, obj, query_id)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 224, in upsert
cursor.execute(stmt, values)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/gh392/lib/python3.9/site-packages/psycopg2/extras.py", line 236, in execute
return super().execute(query, vars)
psycopg2.errors.NumericValueOutOfRange: integer out of range
We use type INTEGER but it looks like maybe the value "4400235700" on line 303 is too big.
Missing tables with "fast translation"
When a stix-shifter connector's "to_stix_map" doesn't use an object name in a mapping, those objects could be silently dropped by async translate/ingest. This happens e.g. with qradar's software:name mapping.
You can tell by inspecting the DB (in this case PostgreSQL but also happens with sqlite3):
otype | path | shortname | dtype
----------+------+-----------+-------
software | name | name | str
(1 row)
# \d
List of relations
Schema | Name | Type | Owner
---------+-----------------+-------+----------
flat-id | __columns | table | postgres
flat-id | __contains | table | postgres
flat-id | __metadata | table | postgres
flat-id | __queries | table | postgres
flat-id | __symtable | table | postgres
flat-id | artifact | table | postgres
flat-id | domain-name | table | postgres
flat-id | email-message | table | postgres
flat-id | file | table | postgres
flat-id | identity | table | postgres
flat-id | ipv4-addr | table | postgres
flat-id | ipv6-addr | table | postgres
flat-id | network-traffic | table | postgres
flat-id | observed-data | table | postgres
flat-id | url | table | postgres
flat-id | x-ibm-finding | table | postgres
flat-id | x-oca-event | table | postgres
flat-id | x-qradar | table | postgres
(18 rows)
From this example you can see that firepit recorded a software:name column (meaning it was in the native qradar data passed into the translate function) which means there should be a software table with id and name columns, but listing the tables in the database shows that software is missing.
ValueError: invalid literal for int() with base 10: '0.0'
From this line:
File \"/usr/local/lib/python3.9/site-packages/firepit/aio/ingest.py\", line 308, in translate
df[txf_col] = df[txf_col].dropna().astype('int')
Can't convert the string "0.0" to int. E.g. in ipython:
In [26]: int(0.0)
Out[26]: 0
In [27]: int("0.0")
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
Input In [27], in <module>
----> 1 int("0.0")
ValueError: invalid literal for int() with base 10: '0.0'
DependentObjectsStillExist: cannot drop view because other objects depend on it
Found while testing Kestrel with PostgreSQL:
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 419, in assign
self.assign_query(viewname, query)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 799, in assign_query
cursor = self._create_view(viewname, stmt, sco_type, deps=deps)
File "/home/pcoccoli/github/firepit/firepit/pgstorage.py", line 333, in _create_view
self._execute(f'DROP VIEW IF EXISTS "{viewname}"', cursor)
File "/home/pcoccoli/github/firepit/firepit/sqlstorage.py", line 152, in _execute
cursor.execute(statement)
File "/home/pcoccoli/.pyenv/versions/3.9.2/envs/p392/lib/python3.9/site-packages/psycopg2_binary-2.8.6-py3.9-linux-x86_64.egg/psycopg2/extras.py", line 251, in execute
return super(RealDictCursor, self).execute(query, vars)
psycopg2.errors.DependentObjectsStillExist: cannot drop view conns because other objects depend on it
DETAIL: view ts_conns depends on view conns
HINT: Use DROP ... CASCADE to drop the dependent objects too.
Does not happen on sqlite3.
Can reproduce with this Kestrel script:
# Create var
conns = GET network-traffic
FROM file:///home/pcoccoli/Data/THL/dns_tunneling.json
WHERE [network-traffic:dst_port = 53 AND network-traffic:dst_ref.value NOT ISSUBSET '192.168.1.0/24']
# Create dependent var
ts_conns = TIMESTAMPED(conns)
# Now try to recreate original var
conns = SORT conns BY src_port
setup.cfg update
When running pytest with Python 3.9.5, get the following warning:
========================================================================================================== warnings summary ===========================================================================================================
../../venv/kestrel39/lib/python3.9/site-packages/_pytest/config/__init__.py:1233
/workspace/venv/kestrel39/lib/python3.9/site-packages/_pytest/config/__init__.py:1233: PytestConfigWarning: Unknown config option: collect_ignore
self._warn_or_fail_if_strict(f"Unknown config option: {key}\n")
-- Docs: https://docs.pytest.org/en/stable/warnings.html
=================================================================================================== 106 passed, 1 warning in 1.88s ====================================================================================================
This seems to be a cookiecutter template issue:
audreyfeldroy/cookiecutter-pypackage#608
Consider removing the following lines in setup.cfg:
[tool:pytest]
collect_ignore = ['setup.py']
Multiple unit test failures with postgresql
FAILED tests/test_errors.py::test_empty_results - firepit.exceptions.UnknownViewname: relation "my_findings" does not exist
FAILED tests/test_storage.py::test_ops[url-value-MATCHES-^.example.com/page/1[0-9]$-http://www26.example.com/page/176-http://www67.example.com/page/264] - AssertionErro...
FAILED tests/test_storage.py::test_grouping - firepit.exceptions.IncompatibleType
FAILED tests/test_storage.py::test_extract - firepit.exceptions.IncompatibleType
FAILED tests/test_storage.py::test_reassign - KeyError: 'x_enrich'
FAILED tests/test_storage.py::test_appdata - TypeError: the JSON object must be str, bytes or bytearray, not NoneType
FAILED tests/test_storage.py::test_sort_same_name - psycopg2.errors.DependentObjectsStillExist: cannot drop view gmirloov because other objects depend on it
A new method for sqlstorage Module
I am adding a new transformer to the Kestrel, which lists all objects after appending the id of the SCO to which the object belongs. To this end, I think a new method, similar to the timestamped but simpler, should be added to the SQLStorage module. I am planning to contribute to firepit by adding this method. If any one think that the problem can be solved without extending firepit, I would be grateful to hear (@subbyte).
Plugin mechanism
Currently, the psycopg2 package is required for postgresql support. However, if you're using sqlite3, you shouldn't need to install psycopg2. Using a plugin mechanism to separate out different "back ends" would solve this.
AttributeError: 'dict' object has no attribute 'split'
With azure_sentinel data and 2.3.26:
File "/home/pcoccoli/github/firepit/firepit/aio/ingest.py", line 258, in translate
new_col = _make_colname(col_mapping)
File "/home/pcoccoli/github/firepit/firepit/aio/ingest.py", line 63, in _make_colname
parts = shifter_name.split('.')
AttributeError: 'dict' object has no attribute 'split'
TypeError: 'float' object is not iterable
With aws_guardduty results and 2.3.26:
df[txf_col] = df[txf_col].apply(_to_protocols)
File "/home/pcoccoli/.pyenv/versions/gh392/lib/python3.9/site-packages/pandas/core/series.py", line 4771, in apply
return SeriesApply(self, func, convert_dtype, args, kwargs).apply()
File "/home/pcoccoli/.pyenv/versions/gh392/lib/python3.9/site-packages/pandas/core/apply.py", line 1105, in apply
return self.apply_standard()
File "/home/pcoccoli/.pyenv/versions/gh392/lib/python3.9/site-packages/pandas/core/apply.py", line 1156, in apply_standard
mapped = lib.map_infer(
File "pandas/_libs/lib.pyx", line 2918, in pandas._libs.lib.map_infer
File "/home/pcoccoli/github/firepit/firepit/aio/ingest.py", line 135, in _to_protocols
value = [str(i).lower() for i in value if i != '']
TypeError: 'float' object is not iterable```
macOS installation error
pip install firepit failed with macOS 10.15 and Python 3.9. Lower Python version works.
Testing script: https://github.com/subbyte/github-action-test/blob/main/.github/workflows/firepit-install.yml
Error with Python 3.9: https://github.com/subbyte/github-action-test/runs/3078492170?check_suite_focus=true
Run python -m pip install --upgrade pip
Requirement already satisfied: pip in /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages (21.1.3)
Requirement already satisfied: setuptools in /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages (56.0.0)
Collecting setuptools
Downloading setuptools-57.2.0-py3-none-any.whl (818 kB)
Installing collected packages: setuptools
Attempting uninstall: setuptools
Found existing installation: setuptools 56.0.0
Uninstalling setuptools-56.0.0:
Successfully uninstalled setuptools-56.0.0
Successfully installed setuptools-57.2.0
Collecting firepit
Downloading firepit-1.0.14-py2.py3-none-any.whl (33 kB)
Collecting lark-parser
Downloading lark-parser-0.11.3.tar.gz (229 kB)
Collecting ijson
Downloading ijson-3.1.4-cp39-cp39-macosx_10_9_x86_64.whl (52 kB)
Collecting orjson==3.3.1
Downloading orjson-3.3.1.tar.gz (655 kB)
Installing build dependencies: started
Installing build dependencies: finished with status 'done'
Getting requirements to build wheel: started
Getting requirements to build wheel: finished with status 'done'
Preparing wheel metadata: started
Preparing wheel metadata: finished with status 'error'
ERROR: Command errored out with exit status 1:
command: /Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages/pip/_vendor/pep517/in_process/_in_process.py prepare_metadata_for_build_wheel /var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/tmpoj1meudm
cwd: /private/var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/pip-install-b17aksbl/orjson_8be9df0c073140d4bd3b219ebd07017a
Complete output (13 lines):
💥 maturin failed
Caused by: Cargo metadata failed. Do you have cargo in your PATH?
Caused by: Error during execution of `cargo metadata`: error: failed to run `rustc` to learn about target-specific information
Caused by:
process didn't exit successfully: `rustc - --crate-name ___ --print=file-names -Z mutable-noalias --crate-type bin --crate-type rlib --crate-type dylib --crate-type cdylib --crate-type staticlib --crate-type proc-macro --print=sysroot --print=cfg` (exit status: 1)
--- stderr
error: the option `Z` is only accepted on the nightly compiler
Checking for Rust toolchain....
Running `maturin pep517 write-dist-info --metadata-directory /private/var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/pip-modern-metadata-3baxp3mn --interpreter /Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python --manylinux=off --strip=on`
Error: Command '['maturin', 'pep517', 'write-dist-info', '--metadata-directory', '/private/var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/pip-modern-metadata-3baxp3mn', '--interpreter', '/Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python', '--manylinux=off', '--strip=on']' returned non-zero exit status 1.
----------------------------------------
WARNING: Discarding https://files.pythonhosted.org/packages/23/c9/d4db467d5b5c91f5ff3ff3be5c731eb7fcc2b8b2c31540d5faeca794bf4d/orjson-3.3.1.tar.gz#sha256=149d6a2bc71514826979b9d053f3df0c2397a99e2b87213ba71605a1626d662c (from https://pypi.org/simple/orjson/) (requires-python:>=3.6). Command errored out with exit status 1: /Users/runner/hostedtoolcache/Python/3.9.6/x64/bin/python /Users/runner/hostedtoolcache/Python/3.9.6/x64/lib/python3.9/site-packages/pip/_vendor/pep517/in_process/_in_process.py prepare_metadata_for_build_wheel /var/folders/24/8k48jl6d249_n_qfxwsl6xvm0000gn/T/tmpoj1meudm Check the logs for full command output.
Collecting firepit
Downloading firepit-1.0.13-py2.py3-none-any.whl (33 kB)
Downloading firepit-1.0.12-py2.py3-none-any.whl (33 kB)
Downloading firepit-1.0.11-py2.py3-none-any.whl (33 kB)
Downloading firepit-1.0.10-py2.py3-none-any.whl (33 kB)
Downloading firepit-1.0.9-py2.py3-none-any.whl (33 kB)
Downloading firepit-1.0.8-py2.py3-none-any.whl (32 kB)
Downloading firepit-1.0.7-py2.py3-none-any.whl (32 kB)
Downloading firepit-1.0.6-py2.py3-none-any.whl (32 kB)
Downloading firepit-1.0.4-py2.py3-none-any.whl (30 kB)
Downloading firepit-1.0.3-py2.py3-none-any.whl (30 kB)
Downloading firepit-1.0.2-py2.py3-none-any.whl (30 kB)
Downloading firepit-1.0.1-py2.py3-none-any.whl (30 kB)
Downloading firepit-1.0.0-py2.py3-none-any.whl (30 kB)
ERROR: Cannot install firepit==1.0.0, firepit==1.0.1, firepit==1.0.10, firepit==1.0.11, firepit==1.0.12, firepit==1.0.13, firepit==1.0.14, firepit==1.0.2, firepit==1.0.3, firepit==1.0.4, firepit==1.0.6, firepit==1.0.7, firepit==1.0.8 and firepit==1.0.9 because these package versions have conflicting dependencies.
The conflict is caused by:
firepit 1.0.14 depends on orjson==3.3.1
firepit 1.0.13 depends on orjson==3.3.1
firepit 1.0.12 depends on orjson==3.3.1
firepit 1.0.11 depends on orjson==3.3.1
firepit 1.0.10 depends on orjson==3.3.1
firepit 1.0.9 depends on orjson==3.3.1
firepit 1.0.8 depends on orjson==3.3.1
firepit 1.0.7 depends on orjson==3.3.1
firepit 1.0.6 depends on orjson==3.3.1
firepit 1.0.4 depends on orjson==3.3.1
firepit 1.0.3 depends on orjson==3.3.1
firepit 1.0.2 depends on orjson==3.3.1
firepit 1.0.1 depends on orjson==3.3.1
firepit 1.0.0 depends on orjson==3.3.1
To fix this you could try to:
1. loosen the range of package versions you've specified
2. remove package versions to allow pip attempt to solve the dependency conflict
ERROR: ResolutionImpossible: for help visit https://pip.pypa.io/en/latest/user_guide/#fixing-conflicting-dependencies
Error: Process completed with exit code 1.
asyncstorage tries to use sync functions from sqlstorage
In AsyncStorage.lookup():
if not col_dict:
col_dict = _get_col_dict(self)
But _get_col_dict comes from sqlstorage.py and is not async:
def _get_col_dict(store):
q = Query('__columns')
col_dict = defaultdict(list)
results = store.run_query(q).fetchall()
for result in results:
col_dict[result['otype']].append(result['path'])
return col_dict
Results in a backtrace with "AttributeError: 'coroutine' object has no attribute 'fetchall'"
A workaround is to pass in a col_dict to lookup and avoid the bug. To generate col_dict (if you haven't already):
dbcache = await get_dbcache(store)
col_dict = dbcache.col_dict
This is probably more efficient anyway.
The real fix is to re-implement an async version of _get_col_dict in asyncstorage.py.
Sorting on referenced properties is broken
See opencybersecurityalliance/kestrel-lang#435
You can reproduce with something like:
query = Query([
Table('conns'),
Order([('dst_ref.value', Order.ASC)]),
])
store.assign_query('sconns', query)
_ = store.lookup('sconns')
This will fail with firepit.exceptions.InvalidAttr: invalid attribute: sconns.dst_ref.value
support for object_refs
Hi there,
I noticed that for objects like note,report,opion and similar that relies on object_refs, the stix id are not normalized in the database.
Is this a known issue?
UnexpectedError: near "JOIN": syntax error
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 = '[email protected]']"
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 = '[email protected]']
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" = '[email protected]')"
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" = '[email protected]')));"
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" = '[email protected]')));
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" = '[email protected]')));: 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 = '[email protected]']" │ │
│ │ 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 = '[email protected]']" │ │
│ │ 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 = '[email protected]']" │ │
│ │ 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
Parentheses in STIX patterns using properties outside projected entity causes error
Passing a STIX pattern such as [(url:value LIKE '%page/1%' AND x-oca-asset:hostName LIKE 'pc%')] to extract with a different entity type would generate invalid SQL:
ERROR firepit.sqlitestorage:sqlitestorage.py:165 CREATE VIEW "nt" AS SELECT "network-traffic".* FROM "network-traffic" WHERE "id" IN (SELECT "network-traffic".id FROM "network-traffic" INNER JOIN __queries ON "network-traffic".id = __queries.sco_id WHERE query_id = 'q1' AND (()));: near ")": syntax error
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.