Fix SQL Injection (Legacy & Modern) in Flask

SQLi in Flask persists because developers still treat SQL as a string manipulation problem rather than a data-binding problem. Whether you are using raw cursor execution or SQLAlchemy's abstraction layer, if you are f-stringing user input into a query, you are handing over your database keys. To kill SQLi, you must enforce a strict separation between the SQL command and the data.

The Vulnerable Pattern

from flask import request
from models import db
@app.route(‘/user’)
def get_user():
# VULNERABLE: String formatting/f-strings create a direct injection vector
user_id = request.args.get(‘id’)
query = f”SELECT username, email FROM users WHERE id = {user_id}”
result = db.session.execute(query).fetchone()
return str(result)

The Secure Implementation

The vulnerability exists because f-strings and % formatting allow an attacker to break out of the intended logic (e.g., inputting '1 OR 1=1' to dump the table). The modern fix utilizes the SQLAlchemy ORM, which abstracts the SQL generation entirely. For cases requiring raw SQL, the 'text()' construct with bind parameters is mandatory. This sends the SQL template and the user data to the database driver separately, preventing the input from ever being interpreted as an executable part of the SQL Abstract Syntax Tree (AST).

from flask import request
from sqlalchemy import text
from models import db, User
@app.route(‘/user’)
def get_user_secure():
user_id = request.args.get(‘id’)
# FIX 1: Modern ORM approach (Highly Recommended)
# SQLAlchemy handles sanitization and type checking automatically
user = User.query.filter_by(id=user_id).first()

# FIX 2: Parameterized Raw SQL (Legacy/Performance use cases)
# Use bind parameters (:id) to ensure input is treated as data, not code
stmt = text("SELECT username, email FROM users WHERE id = :val")
result = db.session.execute(stmt, {"val": user_id}).fetchone()

return str(result)</code></pre>