QeeqBox

Tag: sql

  • SQLite

    SQLite3

    SQLite is a lightweight disk-based database library written in C. You can use the SQLite3 binary directly from the command line interface after installing it or the SQLite3 Python module that’s built-in.

    Command-Line Interface

    sqlite>

    Python

    import sqlite3

    Create a Database

    The .connect()method is used to connect to the local database or create a new one if the file does not exist

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        pass # ‘pass’ is just a placeholder; replace with actual DB operations

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn: 
        pass

    Drop a Table

    To drop a table, use the DROP TABLE keyword and table name,

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS test;
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")

    Create a Table

    To create a table, use the CREATE TABLE keyword and table name, you also need to define the table columns and their types or properties

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")

    List All Tables

    To review all tables in a database, you can get the users table from sqlite_master using the SELECT keyword

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> SELECT name FROM sqlite_master WHERE type=’table’; #Query the SQLite system table ‘sqlite_master’ to list all tables in the database
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> SELECT name FROM sqlite_master WHERE type='table';
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        print(conn.execute(“SELECT name FROM sqlite_master WHERE type=’table’”).fetchall()) #Query the SQLite system table ‘sqlite_master’ to list all tables in the database

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        print(conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall())

    Insert Into a Table

    To add new data, use the INSERT keyword (Always parameterized, you do not want to create SQL injection)

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> INSERT into users(id ,user, hash) values(1, “john”, “e66860546f18”); # Insert a new row into the ‘users’ table 
    sqlite> INSERT into users(id, user, hash) values(2, “jane”, “cdbbcd86b35e”); # Insert a new row into the ‘users’ table 
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> INSERT into users(id ,user, hash) values(1, "john", "e66860546f18");
    sqlite> INSERT into users(id, user, hash) values(2, "jane", "cdbbcd86b35e");
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table 

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))

    Fetching Results

    To all results from the database,  use the SELECT keyword and .fetchall() or use can fetch one result the SELECT keyword and .fetchone()

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> INSERT into users(id ,user, hash) values(1, “john”, “e66860546f18”); # Insert a new row into the ‘users’ table 
    sqlite> INSERT into users(id, user, hash) values(2, “jane”, “cdbbcd86b35e”); # Insert a new row into the ‘users’ table
    sqlite> SELECT * FROM users; # Select all columns and all rows from the ‘users’ table 
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> INSERT into users(id ,user, hash) values(1, "john", "e66860546f18");
    sqlite> INSERT into users(id, user, hash) values(2, "jane", "cdbbcd86b35e");
    sqlite> SELECT * FROM users;
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        print(conn.execute(“SELECT * FROM users”).fetchall()) # Select all columns and all rows from the ‘users’ table 

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        print(conn.execute("SELECT * FROM users").fetchall())

    Output

    [(1, 'john', 'e66860546f18'), (2, 'jane', 'cdbbcd86b35e')]

    Find Data

    You can fetch a specific data using the WHERE keyword

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> INSERT into users(id ,user, hash) values(1, “john”, “e66860546f18”); # Insert a new row into the ‘users’ table 
    sqlite> INSERT into users(id, user, hash) values(2, “jane”, “cdbbcd86b35e”); # Insert a new row into the ‘users’ table
    sqlite> SELECT * FROM users WHERE id=2; # Select all columns from the ‘users’ table where the user’s id is 2
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> INSERT into users(id ,user, hash) values(1, "john", "e66860546f18");
    sqlite> INSERT into users(id, user, hash) values(2, "jane", "cdbbcd86b35e");
    sqlite> SELECT * FROM users WHERE id=2;
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        print(conn.execute(“SELECT * FROM users WHERE id=2”).fetchall()) # Select all columns and all rows from the ‘users’ table 

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        print(conn.execute("SELECT * FROM users WHERE id=2").fetchall())

    Output

    (2, 'jane', 'cdbbcd86b35e')

    Delete Data

    You can delete data by using the DELETE keyword

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> INSERT into users(id ,user, hash) values(1, “john”, “e66860546f18”); # Insert a new row into the ‘users’ table 
    sqlite> INSERT into users(id, user, hash) values(2, “jane”, “cdbbcd86b35e”); # Insert a new row into the ‘users’ table
    sqlite> DELETE from users WHERE id=1; # Delete rows from the ‘users’ table where the id equals 1
    sqlite> SELECT * FROM users; # Select all columns and all rows from the ‘users’ table
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> INSERT into users(id ,user, hash) values(1, "john", "e66860546f18");
    sqlite> INSERT into users(id, user, hash) values(2, "jane", "cdbbcd86b35e");
    sqlite> DELETE from users WHERE id=1
    sqlite> SELECT * FROM users;
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed

    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        conn.execute(“DELETE from users WHERE id=1”) # Delete rows from the ‘users’ table where the id equals 1 
        print(conn.execute(“SELECT * FROM users”).fetchall()) # Select all columns and all rows from the ‘users’ table

    from sqlite3 import connect
    from contextlib import closing

    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        conn.execute("DELETE from users WHERE id=1")
        print(conn.execute("SELECT * FROM users").fetchall())

    Output

    [(2, 'jane', 'cdbbcd86b35e')]

    User Input (SQL Injection)

    A threat actor can construct a malicious query and use it to perform an authorized action (This happens because of format string/string concatenation)

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> INSERT into users(id ,user, hash) values(1, “john”, “e66860546f18”); # Insert a new row into the ‘users’ table 
    sqlite> INSERT into users(id, user, hash) values(2, “jane”, “cdbbcd86b35e”); # Insert a new row into the ‘users’ table
    sqlite> SELECT * FROM users WHERE user=” or ”=” AND hash=” or ”=”; # Select all columns from ‘users’ table, the WHERE clause is crafted to always be TRUE
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> INSERT into users(id ,user, hash) values(1, "john", "e66860546f18");
    sqlite> INSERT into users(id, user, hash) values(2, "jane", "cdbbcd86b35e");
    sqlite> SELECT * FROM users WHERE user='' or ''='' AND hash='' or ''='';
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed
    temp_user = input(“Enter username: “) # Prompt the user to enter a username
    temp_hash = input(“Enter password: “) # Prompt the user to enter a password (Usually, there will be a function to hash the password, it’s removed from here)
    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        print(conn.execute(“SELECT * FROM users WHERE user=’%s’ AND hash=’%s’” % (temp_user,temp_hash)).fetchall()) # Execute a SQL query using string formatting to insert user-controlled values 

    from sqlite3 import connect
    from contextlib import closing
    temp_user = input("Enter username: ")
    temp_hash = input("Enter password: ")
    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        print(conn.execute("SELECT * FROM users WHERE user='%s' AND hash='%s'" % (temp_user,temp_hash)).fetchall())

    Malicious statement

    If a use enter ' or ''=' for both username and password, the 

    SELECT * FROM users WHERE user='' or ''='' AND hash='' or ''=''

    Which will always be true, break the WHERE clause down:

    user='' OR ''='' → FALSE OR TRUE → TRUE
    hash='' OR ''='' → FALSE OR TRUE → TRUE

    Output

    The result is every row in the users table is returned, regardless of username or hash.

    [(1, 'john', 'e66860546f18'), (2, 'jane', 'cdbbcd86b35e')]

    User Input (Blind SQL Injection)

    A threat actor can construct a malicious query and use it to perform an authorized action without getting error messages regarding the injection (This happens because of format string/string concatenation)

    Command-Line Interface

    sqlite> .open database.db # Open (or create if it doesn’t exist) a SQLite database file named ‘database.db’
    sqlite> DROP TABLE IF EXISTS test; # Delete the table named ‘test’ if it exists 
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text); # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
    sqlite> INSERT into users(id ,user, hash) values(1, “john”, “e66860546f18”); # Insert a new row into the ‘users’ table 
    sqlite> INSERT into users(id, user, hash) values(2, “jane”, “cdbbcd86b35e”); # Insert a new row into the ‘users’ table
    sqlite> SELECT * FROM users WHERE user=” OR (SELECT COUNT(*) FROM users) > 0 — AND hash=’test’; # Determine if table users exists using only true/false behavior (e.g., login success vs failure).
    sqlite> .quit # Exit the SQLite command-line interface

    sqlite> .open database.db
    sqlite> DROP TABLE IF EXISTS users;
    sqlite> CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text);
    sqlite> INSERT into users(id ,user, hash) values(1, "john", "e66860546f18");
    sqlite> INSERT into users(id, user, hash) values(2, "jane", "cdbbcd86b35e");
    sqlite> SELECT * FROM users WHERE user='' OR (SELECT COUNT(*) FROM users) > 0 -- AND hash='test';
    sqlite> .quit

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed
    temp_user = input(“Enter username: “) # Prompt the user to enter a username
    temp_hash = input(“Enter password: “) # Prompt the user to enter a password (Usually, there will be a function to hash the password, it’s removed from here)
    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        result = conn.execute(“SELECT * FROM users WHERE user=’%s’ AND hash=’%s’” % (temp_user,temp_hash)).fetchone() # Determine if table users exists using only true/false behavior (e.g., login success vs failure). 
        if result: # If a row is returned
            print(“Login successful”) # Show the successful message 
        else: # If there is no row
            print(“Login failed”) # Show the failed message 

    from sqlite3 import connect
    from contextlib import closing
    temp_user = input("Enter username: ")
    temp_hash = input("Enter password: ")
    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        result = conn.execute("SELECT * FROM users WHERE user='%s' AND hash='%s'" % (temp_user,temp_hash)).fetchone()
        if result:
            print("Login successful")
        else:
            print("Login failed")

    Malicious statement

    If a use enter ' OR (SELECT COUNT(*) FROM users) > 0 -- for the username and any password, it will count how many rows exist in the users table. If at least one user exists, this expression evaluates to TRUE.

    SELECT * FROM users WHERE user='' OR (SELECT COUNT(*) FROM users) > 0 -- AND hash='test'

    Output

    It will show login successful which indicates the users table does exist.

    Login successful

    If a use enter ' OR (SELECT COUNT(*) FROM userx) > 0 -- for the username and any password, it will count how many rows exist in the users table. If at least one user exists, this expression evaluates to TRUE.

    SELECT * FROM users WHERE user='' OR (SELECT COUNT(*) FROM userx) > 0 -- AND hash='test'

    Output

    It will show login successful which indicates the users table does exist.

    Login failed

    Insecure Design

    A threat actor may use any ID to retrieve user info (The logic receives users by incremental ids)

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed
    temp_id = input(“Enter id: “) # Prompt the user to enter a id
    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        print(conn.execute(“SELECT * FROM users WHERE id=?”, (temp_id,)).fetchall()) # Safely query the users table for a specific id using a parameterized query

    from sqlite3 import connect
    from contextlib import closing
    temp_id = input("Enter id: ")
    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        print(conn.execute("SELECT * FROM users WHERE id=?", (temp_id,)).fetchall())

    Statement will be

    SELECT * FROM users WHERE id=1

    Output

    [(1, 'john', 'e66860546f18')]

    User Input (SQL/Blind SQL Injection)

    If you want to pass dynamic values to the SQL statement, make sure to use ? as a placeholder and pass the value in a tuple as (value,). The ? tells the db engine to properly escape the passed values. Escaping means that the value should be treated as string. E.g., if someone enters ' symbol which can be used to close a clause, the db engine will automatically escape it like this \'

    Python

    from sqlite3 import connect # Import the connect function from sqlite3 to interact with SQLite databases
    from contextlib import closing # Import closing from contextlib to ensure the connection is properly closed
    temp_user = input(“Enter username: “) # Prompt the user to enter a username
    temp_hash = input(“Enter password: “) # Prompt the user to enter a password (Usually, there will be a function to hash the password, it’s removed from here)
    with closing(connect(“database.db”,isolation_level=None)) as conn: # Use a context manager to automatically close the database connection when done
        conn.execute(“DROP TABLE IF EXISTS users”) # Delete the table named ‘test’ if it exists 
        conn.execute(“CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)”) # Create a table named ‘users’ if it doesn’t already exist, column ‘id’: stores a numeric identifier for each user, column ‘user’: stores the username as text, column ‘hash’: stores the password hash as text
        conn.execute(“INSERT into users(id ,user, hash) values(?,?, ?)”, (1,”john”, “e66860546f18”)) # Insert a new row into the ‘users’ table 
        conn.execute(“INSERT into users(id, user, hash) values(?,?, ?)”, (2,”jane”, “cdbbcd86b35e”)) # Insert a new row into the ‘users’ table
        print(conn.execute(“SELECT * FROM users WHERE user=? AND hash=?”, (temp_user,temp_hash,)).fetchall()) # Safely query the users table for a specific username and password using a parameterized query

    from sqlite3 import connect
    from contextlib import closing
    temp_user = input("Enter username: ")
    temp_hash = input("Enter password: ")
    with closing(connect("database.db",isolation_level=None)) as conn:
        conn.execute("DROP TABLE IF EXISTS users")
        conn.execute("CREATE TABLE IF NOT EXISTS users (id integer, user text, hash text)")
        conn.execute("INSERT into users(id ,user, hash) values(?,?, ?)", (1,"john", "e66860546f18"))
        conn.execute("INSERT into users(id, user, hash) values(?,?, ?)", (2,"jane", "cdbbcd86b35e"))
        print(conn.execute("SELECT * FROM users WHERE user=? AND hash=?", (temp_user,temp_hash,)).fetchall())