unable to start: sqlite3.OperationalError: Error creating function

[blshkv]

Hi, I'm trying to port spiderfoot to Pentoo and facing the following error while trying to start the tool (spiderfoot-3.5):

python ./sf.py -l localhost:5001 -l localhost:5001 -d

*************************************************************
 Use SpiderFoot by starting your web browser of choice and 
 browse to http://localhost:5001/
*************************************************************

2021-11-11 08:30:39,734 [INFO] sf : Starting web server at localhost:5001 ...
2021-11-11 08:30:39,745 [WARNING] sf : 
********************************************************************
Warning: passwd file contains no passwords. Authentication disabled.
Please consider adding authentication to protect this instance!
Refer to https://www.spiderfoot.net/documentation/#security.
********************************************************************

Traceback (most recent call last):
  File "/usr/share/spiderfoot/sf.py", line 592, in <module>
    main()
  File "/usr/share/spiderfoot/sf.py", line 200, in main
    start_web_server(sfWebUiConfig, sfConfig, loggingQueue)
  File "/usr/share/spiderfoot/sf.py", line 544, in start_web_server
    cherrypy.quickstart(SpiderFootWebUi(sfWebUiConfig, sfConfig, loggingQueue), script_name=web_root, config=conf)
  File "/usr/share/spiderfoot/sfwebui.py", line 102, in __init__
    secure.ContentSecurityPolicy()
AttributeError: module 'secure' has no attribute 'ContentSecurityPolicy'
Error in atexit._run_exitfuncs:
Traceback (most recent call last):
  File "/usr/share/spiderfoot/spiderfoot/db.py", line 322, in __init__
    self.conn.create_function("REGEXP", 2, __dbregex__)
sqlite3.OperationalError: Error creating function

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/share/spiderfoot/spiderfoot/db.py", line 362, in create
    self.dbh.execute(qry)
sqlite3.OperationalError: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/share/spiderfoot/spiderfoot/db.py", line 326, in __init__
    self.create()
  File "/usr/share/spiderfoot/spiderfoot/db.py", line 376, in create
    raise IOError(f"SQL error encountered when setting up database: {e.args[0]}")
OSError: SQL error encountered when setting up database: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/share/spiderfoot/spiderfoot/db.py", line 329, in __init__
    raise IOError(f"Tried to set up the SpiderFoot database schema, but failed: {e.args[0]}")
OSError: Tried to set up the SpiderFoot database schema, but failed: SQL error encountered when setting up database: table tbl_event_types already exists

FYI, the database is getting created in ~/.spiderfoot/spiderfoot.db and there is nothing useful in the logs Let me know if I can provide more details.

[bcoles]

AttributeError: module 'secure' has no attribute 'ContentSecurityPolicy'

Do you have secure version 0.3.0?

# pip3 list | grep secure
secure                         0.3.0

[blshkv]

hm.. I re-installed all deps and the problem disappeared. It must be an issue at my end. Thanks!

[blshkv]

So apparently, this is happens on the second run. Steps to reproduce:

1. rm -rf ~/.spiderfoot/
2. ./sf.py -l localhost:5001
3. ctrl-c
4. ./sf.py -l localhost:5001

./sf.py -l localhost:5001   

*************************************************************
2021-11-11 09:49:30,879 [INFO] sf : Starting web server at localhost:5001 ...
 Use SpiderFoot by starting your web browser of choice and 
 browse to http://localhost:5001/
*************************************************************

2021-11-11 09:49:30,889 [WARNING] sf : 
********************************************************************
Warning: passwd file contains no passwords. Authentication disabled.
Please consider adding authentication to protect this instance!
Refer to https://www.spiderfoot.net/documentation/#security.
********************************************************************

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 322, in __init__
    self.conn.create_function("REGEXP", 2, __dbregex__)
sqlite3.OperationalError: Error creating function

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 362, in create
    self.dbh.execute(qry)
sqlite3.OperationalError: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 326, in __init__
    self.create()
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 376, in create
    raise IOError(f"SQL error encountered when setting up database: {e.args[0]}")
OSError: SQL error encountered when setting up database: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/./sf.py", line 592, in <module>
    main()
  File "/home/blshkv/Downloads/spiderfoot-3.5/./sf.py", line 200, in main
    start_web_server(sfWebUiConfig, sfConfig, loggingQueue)
  File "/home/blshkv/Downloads/spiderfoot-3.5/./sf.py", line 544, in start_web_server
    cherrypy.quickstart(SpiderFootWebUi(sfWebUiConfig, sfConfig, loggingQueue), script_name=web_root, config=conf)
  File "/home/blshkv/Downloads/spiderfoot-3.5/sfwebui.py", line 82, in __init__
    dbh = SpiderFootDb(self.defaultConfig, init=True)
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 329, in __init__
    raise IOError(f"Tried to set up the SpiderFoot database schema, but failed: {e.args[0]}")
OSError: Tried to set up the SpiderFoot database schema, but failed: SQL error encountered when setting up database: table tbl_event_types already exists
Error in atexit._run_exitfuncs:
Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 322, in __init__
    self.conn.create_function("REGEXP", 2, __dbregex__)
sqlite3.OperationalError: Error creating function

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 362, in create
    self.dbh.execute(qry)
sqlite3.OperationalError: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 326, in __init__
    self.create()
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 376, in create
    raise IOError(f"SQL error encountered when setting up database: {e.args[0]}")
OSError: SQL error encountered when setting up database: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 329, in __init__
    raise IOError(f"Tried to set up the SpiderFoot database schema, but failed: {e.args[0]}")
OSError: Tried to set up the SpiderFoot database schema, but failed: SQL error encountered when setting up database: table tbl_event_types already exists

[bcoles]

Strange.

The errors described above are raised from db.py which is used for handling database initialization and modification.

The database initialization occurs during application startup, for every startup, regardless of whether the database already exists. This is to ensure that the database is fully up to date. ie, newer releases of SpiderFoot may introduce new settings and data types, and these must be inserted into the appropriate database table.

However, I can't reproduce this issue on a fresh user account with no existing database on any of my test boxes (Kali, Parrot, Linux Mint 20). In all instances, launching SpiderFoot works first try. Upon second launch, trying to create a function or table that already exists does not raise an error.

This issue is likely easily worked around with a patch similar to the following. However, the difference between failing and failing silently warrants further review.

diff --git a/spiderfoot/db.py b/spiderfoot/db.py
index 2bba2a26..c5352eac 100644
--- a/spiderfoot/db.py
+++ b/spiderfoot/db.py
@@ -321,12 +321,13 @@ class SpiderFootDb:
                 self.dbh.execute('SELECT COUNT(*) FROM tbl_scan_config')
                 self.conn.create_function("REGEXP", 2, __dbregex__)
             except sqlite3.Error:
-                # .. If not set up, we set it up.
-                try:
-                    self.create()
+                if 'already exists' not in e.message:
                     init = True
-                except Exception as e:
-                    raise IOError(f"Tried to set up the SpiderFoot database schema, but failed: {e.args[0]}")
+                    # .. If not set up, we set it up.
+                    try:
+                        self.create()
+                    except Exception as e:
+                        raise IOError(f"Tried to set up the SpiderFoot database schema, but failed: {e.args[0]}")
 
             if init:
                 for row in self.eventDetails:
@@ -373,7 +374,8 @@ class SpiderFootDb:
                     ))
                 self.conn.commit()
             except sqlite3.Error as e:
-                raise IOError(f"SQL error encountered when setting up database: {e.args[0]}")
+                if 'already exists' not in e.message:
+                    raise IOError(f"SQL error encountered when setting up database: {e.args[0]}")
 
     def close(self):
         """Close the database handle."""

[bcoles]

As for the initial error sqlite3.OperationalError: Error creating function - the following diff may shed some light on why creating the function failed.

However, given that SpiderFoot worked for you the first time, but failed the second, I presume it is related to the error above. That is, it fails because the function already exists.

diff --git a/spiderfoot/db.py b/spiderfoot/db.py
index 2bba2a26..cd04f6ec 100644
--- a/spiderfoot/db.py
+++ b/spiderfoot/db.py
@@ -307,11 +307,8 @@ class SpiderFootDb:
         # SQLite doesn't support regex queries, so we create
         # a custom function to do so..
         def __dbregex__(qry, data):
-            try:
-                rx = re.compile(qry, re.IGNORECASE | re.DOTALL)
-                ret = rx.match(data)
-            except Exception:
-                return False
+            rx = re.compile(qry, re.IGNORECASE | re.DOTALL)
+            ret = rx.match(data)
             return ret is not None
 
         # Now we actually check to ensure the database file has the schema set

[blshkv]

The first patch is broken, and no help with the second one.

Btw, it looks like the database initialisation fails on the first run, because I'm getting an "error" message if I visit http://localhost:5001, and also getting the same message after I press ctrl^c on a first run.

./sf.py -l 127.0.0.1:5001

*************************************************************
 Use SpiderFoot by starting your web browser of choice and 
 browse to http://127.0.0.1:5001/
*************************************************************

2021-11-12 11:31:13,470 [INFO] sf : Starting web server at 127.0.0.1:5001 ...
2021-11-12 11:31:13,480 [WARNING] sf : 
********************************************************************
Warning: passwd file contains no passwords. Authentication disabled.
Please consider adding authentication to protect this instance!
Refer to https://www.spiderfoot.net/documentation/#security.
********************************************************************

^CError in atexit._run_exitfuncs:
Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 319, in __init__
    self.conn.create_function("REGEXP", 2, __dbregex__)
sqlite3.OperationalError: Error creating function

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 357, in create
    self.dbh.execute(qry)
sqlite3.OperationalError: table tbl_event_types already exists

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 322, in __init__
    self.create()
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 371, in create
    if 'already exists' not in e.message:
AttributeError: 'OperationalError' object has no attribute 'message'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/blshkv/Downloads/spiderfoot-3.5/spiderfoot/db.py", line 324, in __init__
    raise IOError(f"Tried to set up the SpiderFoot database schema, but failed: {e.args[0]}")
OSError: Tried to set up the SpiderFoot database schema, but failed: 'OperationalError' object has no attribute 'message'

[blshkv]

Ping