Link Search Menu Expand Document

SQL Injection in Python

Vulnerable example

The following snippet contains a Flask web application written in Python that runs an SQL query to resolve the host supplied by the user.

@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

OWASP - SQL Injection

OWASP - SQL Injection Prevention Cheat Sheet