SQLite3 Injection demonstration

foo.py

import sqlite3


conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE user (user_id INTEGER PRIMARY KEY, name TEXT)')
conn.commit()

cursor = conn.cursor()
for name in ['alice', 'bob', 'carol', 'david']:
    cursor.execute('INSERT INTO user (name) VALUES (?)', (name,))
conn.commit()


name_from_attacker = "alice' OR name NOTNULL--"


def print_all_rows():
    print('here are the current rows')
    for row in cursor.execute('SELECT * FROM user'):
        print(row)


def execute_safe_query():
    print('executing safe query')
    query = "DELETE FROM user WHERE name = ?"
    cursor.execute(query, (name_from_attacker,))



def execute_injectable_query():
    print('executing injectable query')
    query = "DELETE FROM user WHERE name = '%s'" % (name_from_attacker,)
    cursor.execute(query)
        

print_all_rows()
execute_safe_query()
print('after safe query')
print_all_rows()
execute_injectable_query()
print('after injectable query')
print_all_rows()

output.txt

here are the current rows
(1, u'alice')
(2, u'bob')
(3, u'carol')
(4, u'david')
executing safe query
after safe query
here are the current rows
(1, u'alice')
(2, u'bob')
(3, u'carol')
(4, u'david')
executing injectable query
here are the current rows