python3 runner.py drops no such table: waf_comparison

[zollerp]

Hi and good evening,

super cool project, but running into an issue and hope you can assist?

Thanks!

python3 runner.py Traceback (most recent call last): File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context self.dialect.do_execute( File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: no such table: waf_comparison

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/Users/xxxxx/GitHub/waf-comparison-project/runner.py", line 10, in from analyzer import analyze_results File "/Users/xxxxx/GitHub/waf-comparison-project/analyzer.py", line 10, in df_results = pd.read_sql_query(""" ^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/pandas/io/sql.py", line 469, in read_sql_query return pandas_sql.read_query( ^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/pandas/io/sql.py", line 1738, in read_query result = self.execute(sql, params) ^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/pandas/io/sql.py", line 1562, in execute return self.con.exec_driver_sql(sql, args) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1774, in exec_driver_sql ret = self._execute_context( ^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context return self._exec_single_context( ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context self._handle_dbapi_exception( File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception raise sqlalchemy_exception.with_traceback(exc_info[2]) from e File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context self.dialect.do_execute( File "/opt/homebrew/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: waf_comparison [SQL: WITH TNR AS ( SELECT "WAF_Name", SUM(CASE WHEN "isBlocked" = False THEN 1.0 ELSE 0.0 END) / count() 100 AS TrueNegativeRate FROM waf_comparison WHERE response_status_code != 0 and "DataSetType" = 'Legitimate' GROUP BY "WAF_Name" ), TPR AS ( SELECT "WAF_Name", SUM(CASE WHEN "isBlocked" = True THEN 1.0 ELSE 0.0 END) / count() * 100 AS TruePositiveRate FROM waf_comparison WHERE response_status_code != 0 and "DataSetType" = 'Malicious' GROUP BY "WAF_Name" ) SELECT TPR."WAF_Name", ROUND(100-TNR.TrueNegativeRate, 3) AS FalsePositiveRate, ROUND(100-TPR.TruePositiveRate, 3) AS FalseNegativeRate, ROUND(TPR.TruePositiveRate, 3) AS TruePositiveRate, ROUND(TNR.TruenegativeRate, 3) AS TruenegativeRate, ROUND((TPR.TruePositiveRate + TNR.TruenegativeRate)/2, 2) AS BalancedAccuracy FROM TPR JOIN TNR on TPR."WAF_Name" = TNR."WAF_Name" ORDER BY BalancedAccuracy DESC ] (Background on this error at: https://sqlalche.me/e/20/e3q8)

[Boris-Rozenfeld]

Uploaded the fix

[Boris-Rozenfeld]

Thanks, @zollerp , for your helpful input!

[zollerp]

Uploaded the fix

Thanks for providing a quick fix! Will test it.

[zollerp]

Hi and thanks again for fixing the previous issue. Additional test drop following error:

python3 runner.py DEBUG | Initiating health check to confirm proper connectivity configurations. INFO | Health check passed - WAF: HM DEBUG | Initiating WAF functionality verification to ensure that the WAF is in prevention mode and is capable of blocking malicious requests. INFO | WAF functionality check passed - WAF: HM DEBUG | All tests have been successfully completed. INFO | Database Connected Successfully DEBUG | Malicious Data Set Already Loaded Traceback (most recent call last): File "/Users/xxxx/GitHub/waf-comparison-project/runner.py", line 158, in main() File "/Users/xxxx//GitHub/waf-comparison-project/runner.py", line 152, in main prepare_data() File "/Users/xxxx//GitHub/waf-comparison-project/helper.py", line 89, in prepare_data LegitimateDataSetPreparation() File "/Users/xxxx/r/GitHub/waf-comparison-project/helper.py", line 75, in LegitimateDataSetPreparation with zipfile.ZipFile(DATA_PATH / 'Legitimate.zip', 'r') as zip_ref: ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/Cellar/python@3.11/3.11.4_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/zipfile.py", line 1302, in init self._RealGetContents() File "/opt/homebrew/Cellar/python@3.11/3.11.4_1/Frameworks/Python.framework/Versions/3.11/lib/python3.11/zipfile.py", line 1369, in _RealGetContents raise BadZipFile("File is not a zip file") zipfile.BadZipFile: File is not a zip file

[Boris-Rozenfeld]

The legitimate dataset, compressed as a zip file, is approximately 0.5GB in size and expands to around 2.5GB when uncompressed. Due to its large size, the dataset is stored using GitHub's Large File Storage (LFS) system, rather than as a regular GitHub file.

To download this dataset correctly, you must ensure that Git LFS is installed on your system. We have updated the repository's README file with the necessary instructions for installing and using Git LFS.

Thank you once again for your valuable input!

[Boris-Rozenfeld]

@zollerp, aiming for simplicity, I have migrated our data sets from GitHub LFS to an Amazon S3 bucket. This means that installing git-lfs is no longer necessary.

[zollerp]

Hi @Boris-Rozenfeld, thanks for your feedback and those enhancements. Question: Running the test takes in avg. 24 hours...is that a wanted behaviour? I guess yes as the script runs about 1 Mio. Requests, right? And where do i find the result of running the test? Thanks and best.

[Boris-Rozenfeld]

Hi, when we run both the testing machine and the WAF systems at the same AWS region (on separate VPCs) it took around 4-6 hours per WAF. I did try to run the test from my local machine to the cloud WAFs, it took a lot longer. 24 hours is a bit much, but overall it is a reasonable amount of time

[Boris-Rozenfeld]

The results will be printed to the screen and saved to "Output" directory as graphs - an information log will also be printed saying so.

[zollerp]

Hi @Boris-Rozenfeld,

running the test to an end and it drops an error - please see below. Any idea of why?

Sending requests: 100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████ Traceback (most recent call last): File "/User/xxxx/GitHub/waf-comparison-project/runner.py", line 158, in main() File "/Users/xxxx/GitHub/waf-comparison-project/runner.py", line 154, in main analyze_results() File "/Users/xxxxx/GitHub/waf-comparison-project/analyzer.py", line 116, in analyze_results create_graph(_dff, metric='False Positive Rate', is_ascending=False) File "/Users/xxxx/GitHub/waf-comparison-project/analyzer.py", line 58, in create_graph _df_sorted = _df.sort_values(metric, ascending=is_ascending).copy() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/frame.py", line 6758, in sort_values k = self._get_label_or_level_values(by, axis=axis) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/opt/homebrew/lib/python3.11/site-packages/pandas/core/generic.py", line 1778, in _get_label_or_level_values raise KeyError(key) KeyError: 'False Positive Rate' xxxxx waf-comparison-project %

[Boris-Rozenfeld]

Hey, @zollerp, a separate issue was already opened for it, and was already fixed. please see my response here.

[zollerp]

Hi @Boris-Rozenfeld thank you. The latest commit solved my issue. Thank you!