SQLite Inside Python: Part 2
CMPUT 291
Introduction to File and Database Management Systems
University of Alberta
Department of Computing Science
Page 1
Single query with different values
executemany() runs a single query with different values for its parameters.
Inserting a list of values into the database:
insertions = [
("Spiderman2" , 4, 2002, 200),
("The Dark Knight2", 5, 2010, 160),
("Zootopia2" , 6, 2018, 208),
]
cur.executemany(
"INSERT INTO movie VALUES (?, ?, ?, ?);",
insertions,
)
•
•
Page 2
Rollback
rollback() undoes any changes to the database since the last call to commit()
cur.execute("INSERT INTO movie VALUES('a', 4, 2000, 100);")
conn.rollback()
•
Page 3
Be careful about SQL injection!
username = get_username_from_user()
password = get_password_from_user()
You want to check whether the user is valid or not. So, you check the database.
If you concatenate variables using Python string operators (+, %, str.format):
cur.execute("""
SELECT * FROM users WHERE
username = '{}' AND password = '{}';
""".format(username, password))
Then you can check the validity of a normal user:
SELECT * FROM users WHERE
username = 'root' AND password = 'root123';
The above query is executed by the database. If you get just one row as a result, it means the
user is valid and the end user is the 'root'.
•
◦
◦
Page 4
How about an attacker?
username = "root"
password = "attacker' OR '1' = '1"
The final SQL query that will be executed by the database:
SELECT * FROM users WHERE
username = 'root' AND password = 'attacker' OR '1' = '1';
The attacker logs in as the root of the system!
There are many types of SQL injection attacks and even tools (e.g. sqlmap) that automatically
attack a database from a single login webpage.
Special characters must be escaped. For example, the quotes in password need to be
escaped ("\\'"):
password = "attacker\\' OR \\'1\\' = \\'"
However, doing this manually is error-prone. The next slide shows how to use the library to do it
automatically.
•
•
Page 5
Simple ways to prevent SQL injections
Never use Python string operators (+, %, str.format) to pass variables into a SQL query!
Instead, use named (:name) or positional (?) parameters in execute(). This will automatically
escape special characters in variables.
Named parameters:
c.execute(
'SELECT * FROM users WHERE username = :uname AND password = :pw;',
{ 'uname': username, 'pw': password },
)
Positional parameters:
c.execute(
'SELECT * FROM users WHERE username = ? AND password = ?;',
(username, password),
)
Page 6
Finding Errors
complete_statement() checks that the input is a complete SQL query ending with
semicolons. For example:
if sqlite3.complete_statement(text):
cur.execute(text)
Try-catching for errors:
try:
cur.execute(text)
except sqlite3.Error as e:
print(e)
•
•
Page 7
A terminal with Python for SQLite!
Read the code and try to understand how it works. Try using it to run SQL queries.
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
buffer = ''
print("Enter your SQL commands to execute in sqlite3.")
print("Enter a blank line to exit.")
while True:
line = input()
if line == '':
break
buffer += line
if sqlite3.complete_statement(buffer):
try:
buffer = buffer.strip()
cur.execute(buffer)
conn.commit()
if buffer.lstrip().upper().startswith('SELECT'):
print(cur.fetchall())
except sqlite3.Error as e:
print("An error occurred:", e)
buffer = ''
conn.close()
Page 8
User-Defined SQL functions
conn.create_function(name, num_params, func)
Allows a user-defined Python function to be used in an SQLite query as if it's an SQL function.
name: The name of the function which will be used in SQL queries.
num_params: The number of the parameters it will take in SQL queries.
func: The function in Python that actually implements the name.
In other database engines, you would write custom functions in SQL itself. These are called
"stored procedures".
•
•
•
Page 9
User-defined Function Example
def my_function(a, b):
return a + b
conn = sqlite3.connect(':memory:')
conn.create_function('add_numbers', 2, my_function)
cur = conn.cursor()
cur.execute("""
CREATE TABLE movie(name TEXT, imax_theatres INT, regular_theatres INT);
""")
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", [
("Inception", 1, 2),
("Interstellar", 3, 3),
("Tenet", 6, 4),
])
cur.execute("""
SELECT * FROM movie WHERE
add_numbers(movie.imax_theatres, movie.regular_theatres) > 5;
""")
Page 10
Handling Passwords
Never store passwords as plaintext! If there's ever a database leak, the attacker will know
every user's password. Nor should passwords be encrypted, because that introduces a single
point of failure, the master key used to encrypt the passwords.
Instead, passwords should be hashed. Hashing is a one-way transformation of the
password. That means that even if someone has the hash, they can't easily find out the
corresponding password.
In production system, you should also add a salt (or pepper) to the password for extra security.
For more info about password security, see:
https://ehikioya.com/password-hashing-salting-peppering•
Page 11
Handling Passwords: Storing the Hash
import hashlib
def hash_password(password):
alg = hashlib.sha256()
alg.update(password.encode('utf-8'))
return alg.hexdigest()
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("""
INSERT INTO member (username, password_hash, name, address)
VALUES (:username, :password_hash, :name, :address);
""", {
'username': username, 'password_hash': hash_password(password),
'name': name, 'address': address,
})
Page 12
Handling Passwords: Check Passwords
import hashlib
def hash_password(password):
alg = hashlib.sha256()
alg.update(password.encode('utf-8'))
return alg.hexdigest()
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute("""
SELECT address FROM member
WHERE password_hash LIKE :password_hash;
""", { 'password_hash': hash_password(password) })
We get the address if the hash of the entered password is what we have in DB.
Page 13
Some Meta Data
cursor.description contains the name of the columns. It returns a 7-tuple for each column
where the last six items of each tuple are None (for historical reasons).
cur.execute("SELECT * from member;")
print("name of the first column:", cur.description[0][0])
Page 14
Row Factory
conn.row_factory can be set to a function by which we can define more advanced ways of
returning results. Let's return results as a dictionary:
# Always takes these two arguments.
def dictionary_factory(cursor, row):
row_dict = {}
for i, col in enumerate(cursor.description):
row_dict[col[0]] = row[i]
return row_dict
conn = sqlite3.connect(":memory:")
# Set it before creating a cursor object
conn.row_factory = dictionary_factory
cur = conn.cursor()
cur.execute("SELECT * from member;")
result = cur.fetchone()
print("the first column:", result['username'])
This is common, so SQLite provides a built-in way of doing this: conn.row_factory =
sqlite3.Row.
Page 15
Null Handling #1
Python's None ≈ SQL NULL.
Querying:
The operators IS NULL and IS NOT NULL may be used in queries inside SQLite.
Updating:
cur.execute("INSERT INTO table_name VALUES('att_value1', NULL);")
Page 16
Null Handling #2
To only return fully defined records:
cur.execute(
"SELECT att_name FROM table_name WHERE att_name IS NOT NULL;"
)
To return only records that contain null(s):
cur.execute(
"SELECT att_name FROM table_name WHERE att_name IS NULL;"
)
Note: this will require some prior knowledge of what attributes may have missing values.
Page 17
Null Handling #3
INSERT INTO movie VALUES
('The Matrix', 1, 2000, 120),
('Hello' , 2, 2016, 128),
('La La Land', 3, NULL, NULL);
cur = conn.cursor()
cur.execute('SELECT * FROM movie')
movies = cur.fetchall()
for movie in movies:
for attribute in movie:
if attribute == None:
print(movie)
break
Output:
('La La Land', 3, None, None)
Page 18
Continuing Last Lab's Example
Schema:
course (course_id, title, seats_available)
student (student_id, name)
enroll (student_id, course_id, enroll_date, grade)
Our department offers some courses and we have a table for the students.
Every student can register in a course.
Students can drop courses.
The system keeps track of the grades for each student in every course.
•
◦
◦
◦
•
•
•
•
Page 19
Example
Download sqlite3-example2.py from eClass.
Read the code.
Complete the drop function which drops a course for a student.
Define the Python GPA function which maps each grade to a numerical value:
Grade = 'A' -> GPA = 4
Grade = 'B' -> GPA = 3
Grade = 'C' -> GPA = 2
Else -> GPA = 0
Use the GPA function to get a sorted list of student names with their average GPAs.
1.
2.
3.
4.
1.
2.
3.
4.
5.
Page 20
Example
def drop(student_id, course_id):
global connection, cursor
# Drop the course for the student and
# update the seats_available column
connection.commit()
def GPA(grade):
# Map the grade to a numerical value
return 0
Page 21
Example
def main():
global connection, cursor
path = "./register.db"
connect(path)
connection.create_function('GPA', 1, GPA)
define_tables()
insert_data()
enroll_assign_grades()
# Use the GPA function to get a sorted list of the student names
# with their average GPAs.
connection.commit()
connection.close()
Page 22
Resources
Documentation for Python 3's sqlite module: https://docs.python.org/3/library/sqlite3.html
SQLite documentation: https://sqlite.org/docs.html
SQLMap: http://sqlmap.org
hashlib documentation: https://docs.python.org/3/library/hashlib.html
More info on password security: https://ehikioya.com/password-hashing-salting-peppering
1.
2.
3.
4.
5.
Page 23
Example Solution: drop function
def drop(student_id, course_id):
global connection, cursor
data = { 'course_id': course_id, 'student_id': student_id }
cursor.execute("""
DELETE FROM enroll WHERE student_id = ? AND course_id = ?;
""", data)
cursor.execute("""
UPDATE course SET seats_available = seats_available + 1
WHERE course_id = :course_id AND NOT EXISTS (
SELECT * FROM enroll
WHERE student_id = :student_id AND course_id = :course_id
);
""", data)
connection.commit()
Page 24
Example Solution: GPA function
def GPA(grade):
if grade == 'A':
return 4
if grade == 'B':
return 3
if grade == 'C':
return 2
return 0
Page 25
Example Solution: Get Students by GPA
def main():
global connection, cursor
path = "./register.db"
connect(path)
connection.create_function('GPA', 1, GPA)
define_tables()
insert_data()
enroll_assign_grades()
cursor.execute('''
SELECT s.name, AVG(GPA(e.grade)) AS avg_gpa
FROM student AS s, enroll AS e
WHERE s.student_id = e.student_id
GROUP BY s.name
ORDER BY avg_gpa;
''')
all_entry = cursor.fetchall()
for one_entry in all_entry:
print(one_entry)
connection.commit()
connection.close()
Page 26















































































































































































































































































































































































学霸联盟