FortifySQL

Database connection/managment

It is recomended to import FortifySQL as such:


    from FortifySQL import Database, sqlite3

Connecting

connection to the database happens during the initialisation of the Database class. When creating the database class, the file path for the database is passed through as the singular argument. e.g.

   database = Database("my-database-path.db")

optional argument 'check_same_thread' configures the connection to check that a request is made on the same thread, by default this is disabled

Error Catching

Error Catching in QuickSQLite is very simple. All it does is it wraps the query with an error catcher and prints any exceptions that occur. Error catching is disabled by default, to enable/disable:


    database = Database("mydb.db")
    database.error_catching(True) # enables error catching
    database.error_catching(False) # disables error catching

When error catching is enabled, there is the option to log errors to console, this is the second optional argument


    database.error_catching(True, True) # enables error catching with console logging
    database.error_catching(True, False) # enables error catching without console logging

Allow Drop

Enabling Drop on a database will Allow SQL queries such as DROP DATABASE and DROP TABLE. If these queries are never going to be needed to be excecuted with the Database class then Drop should be disabled:


    database = Database("mydb.db")
    database.allow_drop(True) # enables drop
    database.allow_drop(False) # disables drop

Banned Statements

If a statement is added it measn it cannot be run on the database unless it is removed with remove_banned_statement()


    database.add_banned_statement("DELETE") # the DELETE statement will never be executed
    database.add_banned_statement(("DELETE", "DROP", "INSERT")) # the DELETE, DROP and INSERT statement will never be executed

remove_banned_statement() allows a once banned statement to be executed on the database


    database.remove_banned_statement("DELETE") # the DELETE statement can now be executed
    database.remove_banned_statement(("DELETE", "DROP", "INSERT")) # the DELETE, DROP and INSERT statement can now be executed

Row Factory

In SQLite3, row factories can make handling data that comes from requests easier for python, refer to The SQLite3 Documentation for more info
They can be added with the row_factory() method


    database.row_factory(sqlite3.Row)

Delete Checking

Delete checking in FortifySQL is enabled by default. It creates a temporary copy of a table before executing a delete statement, it will check that the table still exists after the delete statement \n This can be computationally expensive for very large tables.
To enable/disable:


    database.delete_checking(True)
    database.delete_checking(False)

Backups

database.backup(path, extension), Creates a backup of the database as path/time.extension ("/time.db" by default) where time is the time of the backup

Logging

sometimes it is helpfull to see what requests are being executed on a database, this can be configured with the query_logging() method.


    database.query_logging(True)
    database.query_logging(False)

If a custom logging function is warrented then that can be passed through


    def log_query(statement):
        print(f"the executed statement is: {statement}")
    
    database.query_logging(True, log_query)
    database.query("SELECT * FROM myTable")


    the excecuted statement is: BEGIN
    the excecuted statement is: SELECT * FROM myTable
    the excecuted statement is: COMMIT

Queries

In FortifySQL, every query will go through a series of security checks. One of the most important feature is the checking for multiple statements. If executing multiple statements in a single query is required then use the multi_query() method. Otherwhise, ALWAYS use the query() method. This prevents injection of a second statement.

The use of formatted strings or any other form of prepared statement is highly discouraged to preven SQL injection. While this Library does have some mitigations for SQL injection, IT IS NOT a full security solution. using parameterisation is a must

Query

The query method handles querying a database, it includes paramaterisation for safe user inputing. \n SECURITY NOTE: this allows a single statement to be excecuted

Paramaterisation works the same as SQLite3 for the query:


    database.query("SELECT * FROM myTable WHERE id=?;", (23,))

the question mark is replaced with 23. So the actual query executed will be:


    SELECT * FROM myTable WHERE id=23;

Multi-Query

multi_query() has the same functionality as query() (refer above) however a query with multiple statements is allowed, it is not recomended to use multi_query() unless necessary

Installation Guide