MySQL / MariaDB - Referential Integrity Support.

[NathanGibbs3]

Item	Description
File(s):
Class:
Function:
Similar Issues:	#95 #96 #103
Depends on Issue(s):
Dependency Type:
Misc. Info.:

• [ ] Update Documentation RE: ALTER DB privileges.

  Expected Behavior: Can we support this on newer versions of MySQL / MariaDB & how do we do that? Current Behavior: Apparently not supported.

[NathanGibbs3]

It's storage engine dependent. MyISAM is a no go. https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-foreign-keys.html https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html Also, at present BASE doesn't have a clue about MySQL / MariaDB storage engines.

[NathanGibbs3]

@mesteele

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?

If these tables, 'iphdr', 'tcphdr', 'udphdr', 'icmphdr', 'opt', 'data', 'acid_ag_alert', 'acid_event' in your MySQL DB are using the InnoDB or NDB storage engine, you should be good to go.

Note: Although it is not recommended, it could be possible to use different storage engines for other tables in the same DB.

I would strongly recommend doing this on a test system vs a production system, in case it falls apart. To try it:

• Use the PostgreSQL referential integrity script on the MySQL server.
  • In the devel branch the file has been renamed to sql/enable_RI.sql
• Set the config variable.

So you could, get it working now for testing.

I have code in the unreleased pipeline that will automatically do the following. #214

• Check if $use_referential_integrity is set.
• Check if the DB server software is a release that supports RI.
  • On MySQL, this also checks the table Storage Engines.
• Checks if the RI constraints have not yet been added to the DB.

If the above checks out, BASE would automatically perform the equivalent of the sql/enable_RI.sql script on DB connection initialization. This would only have to happen once, since after it ran, any future connects to the DB would see the constraints in the tables and skip this step. Basically, on the fly RI support.

It can also run SQL that will remove the RI constraints from the DB tables, so we can toggle the whole feature with just the config var.

I'm still working on it / testing it out. We don't want BASE to automatically trash a production DB.

[mesteele]

I have implemented RI as described for MySQL. I've moved, copied and deleted multiple events with no issues. Not sure if it's really working. I'm guessing that I'll see a database failure if it fails. Not real sure how to test this?

[NathanGibbs3]

Great. :smile: I also have a test mysql DB with verified RI.

The Only issue right now is that the RI scripts will run without error on mysql, even when the snort DB is using storage engines that don't support RI. To the end user, it looks like RI was enabled on the DB, when it isn't.

They enable the $use_referential_integrity and go on their way running BASE in it's current state. When they delete alerts, only the alert data in the event table gets deleted. All the data for that alert in the other tables is left there.

I'm currently working on code to make sure that this does not happen.

Just an FYI

• In future releases of BASE, the DB user will need to have ALTER privileges as well, so BASE can control this on the fly.
• We have to execute ALTER TABLE and DROP CONSTRAINT or DROP FOREIGN KEY on the DB Server to do this.
• Being able to toggle RI from within BASE will make testing around this feature much easier.
• [ ] I Need to update the documentation about this as well.

[mesteele]

We are just talking about MySQL right now, correct?

I'm assuming that RI is working as intended on PostgreSQL, or is it?

[NathanGibbs3]

PostgreSQL 7+ is fine. On a pre 7x version, the same problem would happen.

The problem is #215 We actually inherited this bug from the ACID project.

PostgreSQL has had RI since 7.0 Released 2000-05-08 https://en.wikipedia.org/wiki/PostgreSQL ACID was first publicly release d 2000-09-11 RI support was added to ACID 0.9.6b22 2002-10-06 BASE first public release 2004-09-01

I think the assumption was that if an end user enabled this, they knew what they were doing. Not necessarily always the case. :smile:

When I get this mess sorted, even if someone were to run BASE using a PostgreSQL 6x server and enabled RI in base_conf, BASE will run as if RI were disabled. We have version/feature checks for MySQL, PostgreSQL, & MsSQL, Oracle is still an unknown, so right now RI won't be enabled for them.

[mesteele]

I'm still at a loss on how MSSQL is even possible with Barnyard2 as I've never seen anything on compiling Barnyard2 with MSSQL or Oracle. I just went back through my docs and the last time I supported MSSQL was back in 2012. There were shops that could only use Microsoft products. I'm willing to try MSSQL if you have any idea how to compile Barnyard2 with MSSQL :)

[NathanGibbs3]

I have no idea RE: Barnyard2 & MsSQL. Looking at their source, and knowing where they got it form, it "should" be possible. The specifics of "exactly how", to get MsSQL support working in Barnyard2 I don't know. As we know, the Barnyard documentation is a train wreck.

At some point I hope to get a free version of MsSQL, probably MSDE or SQLExpress running on a windows box, and can then play around with it. If I can run the creation scripts and get BASE to connect to it as a DB server, then from the BASE side, we are in business. I may even be able to expand the Unit tests that we now have to cover MsSQL.

[mesteele]

At some point I hope to get a free version of MsSQL, probably MSDE or SQLExpress running on a windows box, and can then play around with it. If I can run the creation scripts and get BASE to connect to it as a DB server, then from the BASE side, we are in business. I may even be able to expand the Unit tests that we now have to cover MsSQL.

I tried MSSQL Express 2008 specifying ODBC and MSSQL and Barnyard2 says they need to be compiled in. I'm guessing that BASE could be capable but would need a separate program, or the drain on BASE would be too great.

Barnyard2 used the code from Snort and I'm thinking that is why it is littered with code referencing DB's that not supported.

At one point when I was using Snort to access MSSQL, I used ODBC to make the DB connection.

[NathanGibbs3]

I tried MSSQL Express 2008 specifying ODBC and MSSQL and Barnyard2 says they need to be compiled in.

So, for barnyard2, it's an issues of

1. Instructing it to compile in MsSQL support.
2. Making sure that the MsSQL libraries are available to be compiled into barnyard2.
   1. Those may be available on this page. https://learn.microsoft.com/en-us/sql/connect/sql-connection-libraries?view=sql-server-ver16
3. Verify that barnyard2 is actually inserting alerts into the DB as expected.

Were you able to run the DB creation script from BASE on the MsSQL server successfully? If those worked, it should be possible to hook BASE up to it and do some limited testing on an empty database.

I opened #216 to add the MsSQL DB option to the PPHPUnit test set. Once I get that done, it should be possible to run the test sets using a MsSQL DB server. That could give is a better understanding of where BASE stands RE: MsSQL compatibility.

[NathanGibbs3]

I'm not sure if the Windows build process for barnyard2 follows the same path as Linux, aka, configure, make, make install, however these options when building barnyard2 might give you what you want. /configure --with-obdc or ./configure --with-odbc= Directory where ODBC development header files & libraries are located.

[mesteele]

I have never used BASE to configure the DB's as I do that manually.

I had no problem adding in all the .sql's and user authentications. In theory BASE should have been able to connect but I didn't try after Barnyard2 refused to connect. I'll have to setup another test system this weekend and see if BASE will connect to MSSQL.

I'm compiling Barnyard2 on Cygwin which is a Unix-like environment and command-line interface for Windows.

I would use the same command as Linux to compile.

I'm sure compiling MSSQL or ODBC into Barnyard2 would require external files which I have no idea what that would be :)

[NathanGibbs3]

I'm compiling Barnyard2 on Cygwin which is a Unix-like environment and command-line interface for Windows.

I would use the same command as Linux to compile.

That makes life easier.

I'm sure compiling MSSQL or ODBC into Barnyard2 would require external files which I have no idea what that would be :)

I'm not sure how the package management works in Cygwin, but this might help you. MS ODBC drivers for Linux. https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16

It looks like ODBC would be how we would get alerts from barnyard to MsSQL. MsSQL to BASE we should be able to stick with configuring BASE to use mssql.

[NathanGibbs3]

I had no problem adding in all the .sql's and user authentications. In theory BASE should have been able to connect

When you get a chance, can you post the command lines you used to set this up for MsSQL.

I'm sure like MySQL & PostgreSQL, MsSQL has it's own CLI tool with its own unique command line options to do this.

I'll incorporate them into the setup script for the PHPUnit Tests.

[NathanGibbs3]

I'm compiling Barnyard2 on Cygwin which is a Unix-like environment and command-line interface for Windows.

I did some research, for instance if I was building barnyard2 to use ODBC on debian, I would install the unixodbc-dev package, which would probably allow me to use the configure --with-obdc setting to build Barnyard2 with an ODBC interface.

I found this online. https://www.cygwin.com/packages/package_list.html

In the list I found libiodbc-devel & libiodbc-devel. One of those might be your animal.

[mesteele]

I have tried MSSQL 2008 and MSSQL 2022. I have installed the correct extension for PHP 8.2 along with the ODBC driver. BASE is throwing the error below.

[30-Jun-2023 22:27:19 America/New_York] PHP Fatal error:  PHP build incomplete: the prerequisite MS SQL Server support required to read the alert database was not built into PHP.  

Please recompile PHP with the necessary library (--enable-mssql)  in D:\winids\Apache24\htdocs\base\includes\base_log_error.inc.php on line 247

[NathanGibbs3]

Opened #226 to track this one. Will fix it via a transparent siwtch over to the sqlsrv driver. Similar to what we did for mysql.

[NathanGibbs3]

Could you also give me the output of PHP -m on that system. I want to make sure I get the module name right.

[NathanGibbs3]

In #192 you mentioned

In Windows, the extensions are activated in the php.ini file. To use mysqli I have to enable the extension in the php.ini configuration file.

What would the php.ini lines be for enabling the following:

• [ ] The old MySQL Driver.
• [x] The new Mysqli Driver.
• [x] The PostgreSQL driver.
• [ ] The old MsSQL driver.
• [x] The new MsSQL driver, I'm guessing Extension=php_sqlsrv.dll for that one.

We could detect when BASE is running on windows and issue a more appropriate error message about enabling the necessary extension.

Unless, I'm missing something, error messages with advice about options to use when compiling PHP from source are not a lot of help.

[mesteele]

I'm using Apache2

extension=sqlsrv_82_ts

The extension for PHP 8 is installed and gets loaded as there is no error.

[mesteele]

Could you also give me the output of PHP -m on that system. I want to make sure I get the module name right.

[PHP Modules]
bcmath
calendar
Core
ctype
date
dom
filter
gd
gmp
hash
iconv
json
libxml
mysqlnd
pcre
PDO
Phar
random
readline
Reflection
session
SimpleXML
SPL
sqlsrv
standard
tokenizer
xml
xmlreader
xmlwriter
zlib

[Zend Modules]

[mesteele]

Opened #226 to track this one. Will fix it via a transparent siwtch over to the sqlsrv driver. Similar to what we did for mysql.

I can manually connect to the MSSQL 2022 Express DB using all the credentials from the base configuration but base fails to connect.

[30-Jun-2023 22:27:19 America/New_York] PHP Fatal error:  PHP build incomplete: the prerequisite MS SQL Server support required to read the alert database was not built into PHP.  

Please recompile PHP with the necessary library (--enable-mssql)  in D:\winids\Apache24\htdocs\base\includes\base_log_error.inc.php on line 247

BASE has to make the connection to the MSSQL server first :)

[NathanGibbs3]

Fix is in the devel branch,. Back to the topic of this issue, how is RI support behaving on MySQL these days?

[mesteele]

Fix is in the devel branch,.

I had to switch back to ODBC 17 as I was getting the below error on ODBC 18:

Error (p)connecting to DB : snort@winids:1433

Check the DB connection variables in base_conf.php

               = $alert_dbname   : MySQL database name where the alerts are stored 
               = $alert_host     : host where the database is stored
               = $alert_port     : port where the database is stored
               = $alert_user     : username into the database
               = $alert_password : password for the username

Database ERROR: SQLState: 08001 Error Code: -2146893019 Message: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted. SQLState: 08001 Error Code: -2146893019 Message: [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection SQLState: 08001 Error Code: -2146893019 Message: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted. SQLState: 08001 Error Code: -2146893019 Message: [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

After switching back to ODBC 17 I'm now getting BASE but it has an error:

PHP Error: php_errors.log

I remembered that for MSSQL a line needed changing in the base_stat_common.php file. I'm not sure if it messes with any of the other DB's.

At the CMD prompt type 'notepad d:\winids\apache24\htdocs\base\base_stat_common.php' (less the outside quotes), and tap the 'Enter' key.

Use the Find in Notepad to locate and change the variables below.
Original Line(s):

$result = $db->baseExecute("SELECT (SELECT timestamp FROM acid_event ORDER BY timestamp ASC LIMIT 1), ". 
                           "(SELECT timestamp FROM acid_event ORDER BY timestamp DESC LIMIT 1)"); 

Change to:
$result = $db->baseExecute("SELECT (SELECT TOP 1 timestamp FROM acid_event ORDER BY timestamp ASC ), ". 
                           "(SELECT TOP 1 timestamp FROM acid_event ORDER BY timestamp DESC )");

Save the file, and eXit Notepad.

BASE now comes up completely and I'm able to switch from base DB to archive DB with no issues.

No PHP Errors: php_errors.log

Back to the topic of this issue, how is RI support behaving on MySQL these days?

RI seems to be doing what it is supposed to be doing, no issues.

[NathanGibbs3]

ODBC 18 looks like it is trying to establish an SSL connection to the MsSQL server and can't validate the certificate chain. That would be a client side configuration issue, not BASE. Solutions would be to either set up the necessary certificates on the client, or to tell ODBC 18 not to do SSL connections.

Thanks for the error message on that, it lit up #227 .

ODBC17 YES!, Nice work. 👍 After years of uncertainty, BASE can still connect to MsSQL.

I'll dig into the SQL error and find out why it breaks on MsSQL.

[mesteele]

ODBC 18 looks like it is trying to establish an SSL connection to the MsSQL server and can't validate the certificate chain. That would be a client side configuration issue, not BASE. Solutions would be to either set up the necessary certificates on the client, or to tell ODBC 18 not to do SSL connections.

Online it said exclude SSL or to revert back to ODBC 17 and I did that :)

[NathanGibbs3]

I already have #213 open about this in general. Once I close that, it may be possible to upgrade to ODBC 18 and use SSL if you want to, but right now, BASE has no clue about SSL connections to the DB, although the underlying ADOdb library is capable of using SSL.

[NathanGibbs3]

After switching back to ODBC 17 I'm now getting BASE but it has an error:

Error introduced in BASE 1.4.4. They obviously didn't test that version on MsSQL.