Error regarding SQLAlchemy after the start

[Nostahz]

When I lunch the tool :

[root@localhost waf-comparison-project]# python3 runner.py
DEBUG | Initiating health check to confirm proper connectivity configurations.
INFO | Health check passed - WAF: TEST 1
INFO | Health check passed - WAF: TEST 2
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: TEST 1
INFO | WAF functionality check passed - WAF: TEST 2
DEBUG | All tests have been successfully completed.
/root/waf-comparison-project/runner.py:22: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
_ = pd.read_sql_query("SELECT 1", engine)
Traceback (most recent call last):
File "/root/waf-comparison-project/runner.py", line 159, in
main()
File "/root/waf-comparison-project/runner.py", line 152, in main
check_engine_connection()
File "/root/waf-comparison-project/runner.py", line 22, in check_engine_connection
_ = pd.read_sql_query("SELECT 1", engine)
File "/usr/local/lib64/python3.9/site-packages/pandas/io/sql.py", line 526, in read_sql_query
return pandas_sql.read_query(
File "/usr/local/lib64/python3.9/site-packages/pandas/io/sql.py", line 2738, in read_query
cursor = self.execute(sql, params)
File "/usr/local/lib64/python3.9/site-packages/pandas/io/sql.py", line 2672, in execute
cur = self.con.cursor()
AttributeError: 'Engine' object has no attribute 'cursor'

[Mighty-Omega]

Same issue - did you find a fix?

[Mighty-Omega]

In the config.py file:
engine = create_engine(rf"sqlite:///waf_comparison.db")

This is referring to a waf_comparison.db

Should this file already exist in the repo? or does the runner.py create this file when calling engine in config.py and then populate it?

[Mighty-Omega]

Looks like there is a bug / issue with using panda to call an engine that doesnt exist. I replaced the def check_engine_connection() function with the following:

def check_engine_connection():
    """
    Function to check if a successful connection to the database engine can be established.
    """
    try:
        # Use the engine to get a connection and execute the query
        with engine.connect() as connection:
            # Execute the query and fetch the results
            result = connection.execute("SELECT 1").fetchall()

            # Check if the query returned a result
            if result:
                print("Database Connected Successfully")
            else:
                print("No result returned from database query")
                
    except ObjectNotExecutableError:
        raise ObjectNotExecutableError("Connection to the database failed")

This seems to have got past the health check and the scanner is now running - unsure if that has resolved the issue all together or just managed to bypass the healthcheck and will have issues further downstream.

[Mighty-Omega]

the first scan runs now but failed to upload results into the wafcomparison.db, and doesnt run subsequent scans.

This appears to be related to the following command not working for some reason:

# Upload the DataFrame to the Database
        dff.to_sql('waf_comparison', engine, if_exists='append', index=False)

This is potentially related to the above change to the way the engine check works too and appears to be related to the way pandas and sqlalchemy work together - pandas is trying to call the engine object but failing.

Fixed this by:

Update sqlalchemy to the latest version:
pip update sqlalchemy

This then breaks existing connection commands, because now it needs to use text instead of feeding in a raw string, but that is easy enough to fix as per below.

Add the following liberary:
from sqlalchemy import text

Update the check_engine_connection():

def check_engine_connection():
    """
    Function to check if a successful connection to the database engine can be established.
    """
    try:
        # Use the engine to get a connection and execute the query
        with engine.connect() as connection:
            # Execute the query and fetch the results
            result = connection.execute(text("SELECT 1")).fetchall()

            # Check if the query returned a result
            if result:
                print("Database Connected Successfully")
            else:
                print("No result returned from database query")
                
    except ObjectNotExecutableError:
        raise ObjectNotExecutableError("Connection to the database failed")

Now the first scan has successfully run, and the second scan, and third, etc. Waiting for ~699 scans to complete to verify if there was an issue loading the data / any other issues on the formating of the data with this update to sqlalchemy.

Note issue to note is that it appears there is not a rate limit setting, which in my leads to the scans sending a lot of requests to the WAF and triggering a rate limit response on both legitimate and malicious requests. Would be ideal to include a config to adjust how many requests per second can be sent to evade rate limit controls.