search

NathanGibbs3 / BASE

The continuation of BASE (Basic Analysis and Security Engine), by @secureideas, et al. A web app to query and analyze alerts coming from a SNORT IDS.
GNU General Public License v2.0
14 stars 3 forks source link

Use Transactions for database interactions #95

Open FalcoGer opened 3 years ago

FalcoGer commented 3 years ago
Item Description
File: includes/base_action.inc.php
Class: N/A
Function: PurgeAlert()
Similar Issues: #96 https://sourceforge.net/p/secureideas/feature-requests/72/ #103
Depends on Issue(s): N/A
Dependency Type: N/A
Misc. Info.: Code currently does this via a for loop for DELETES.

Expected Behavior: Transactions are used to do deletes.

NG3-Note: On MySQL / MariaDB the ability to do this will be storage engine dependent. https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

Current Behavior: I have noticed that for instance deleting items will delete from several different tables, but it is not using transactions to do so:

2021-02-11T07:16:53.494828Z     208662 Query    DELETE FROM event WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.695657Z     208662 Query    DELETE FROM iphdr WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.740859Z     208662 Query    DELETE FROM tcphdr WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.808763Z     208662 Query    DELETE FROM udphdr WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.809905Z     208662 Query    DELETE FROM icmphdr WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.810839Z     208662 Query    DELETE FROM opt WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.811858Z     208662 Query    DELETE FROM data WHERE sid='1' AND cid='5293'
2021-02-11T07:16:53.922471Z     208662 Query    DELETE FROM acid_ag_alert WHERE ag_sid='1' AND ag_cid='5293'
2021-02-11T07:16:53.923848Z     208662 Query    DELETE FROM acid_event WHERE sid='1' AND cid='5293'

This means that if the deletion is stopped midway through (for example because the user's browser was closed while waiting for half an hour to delete 18469x tcp data after reset), the database will be in an inconsistent state, having deleted some entries in some tables while leaving the others with a floating reference.

NathanGibbs3 commented 2 years ago

If you can enable Referential Integrity for you DB environment, this may speed up deletion. See $use_referential_integrity in base_conf.php. MSSQL & PostgreSQL can support that by using the create_base_tbls_*_extra.sql in the sql directory. See Issue #103 if you are running on Oracle.

mesteele commented 1 year ago

I believe that MySQL is also now capable of using $use_referential_integrity in base_conf.php file like PostgreSQL. Not real sure what needs to be done in order to bring BASE up to doing that for MySQL?

NathanGibbs3 commented 1 year ago

I've often considered this. Check out #103 . Apart form line ending characters and minor formatting, the scripts to add this to the DB server are the same. They are standard SQL commands. It may be possible to consolidate them. Would the unix line endings trip up a legacy version is MsSQL server, that is my only concern there.

I wouldn't be surprised if what we already have would just work on a MySQL that supported it. I wouldn't recommend it on "production" systems, however try it if you are feeling adventurous.

Next we would need to know the minimum version of the DB server software that supported this for each DB platform.

I'd have to add DB server version checks to ignore $use_referential_integrity on installations where the DB server did not support it. Imagine the disaster that would ensue on the DB if BASE just blindly followed the config setting when the DB server did not support it.

And then there is Oracle, it's own unique snowflake that does everything a bit differently. I have no clue about how to set it up there. What we have "may" work, or it may not.

Opened #212 to track this conversation.