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())