SQL Injection in Python
Vulnerable example
The following snippet contains a Flask web application written in Python that checks the user-provided credentials against the SQL database.
@app.route("/login")
def login():
username = request.values.get('username')
password = request.values.get('password')
# Prepare database connection
db = pymysql.connect("localhost")
cursor = db.cursor()
# Execute the vulnerable SQL query concatenating user-provided input.
cursor.execute("SELECT * FROM users WHERE username = '%s' AND password = '%s'" % (username, password))
# If the query returns any matching record, consider the current user logged in.
record = cursor.fetchone()
if record:
session['logged_user'] = username
# disconnect from server
db.close()
Since the SQL query is built concatenating username
and password
user inputs, an attacker could manipulate the query to return at least one record and bypass the login mechanism.
For example, injecting ' OR 'a'='a';--
in the username and any character in the password fields, the query becomes:
SELECT * FROM users WHERE username = '' OR 'a'='a';-- AND password = '';
The manipulated query returns any entry in the users
table that has an empty username, or if a
equals a
, and comments out the final part of the original query. Since the statement is always true, cursor.fetchone()
returns the first record letting the attacker log in as the first user.
Prevention
Python libraries provide the API to perform parameterized queries on most of the database technologies available.
The code in each snippet shows a recommended way to call methods using parameterized queries with some of the most common Python database libraries.
PyMySQL, MySQL-python, MySQL connector, PyGreSQL, Psycopg, pymssql
cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
SQLAlchemy
stmt = sqlalchemy.sql.text("SELECT * FROM users WHERE username = :username and password = :password")
conn.execute(stmt, {"username": username, "password": password })
sqlite3, pyodbc
cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))
References
CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command