Flask SQL Injection
Introduction
SQL Injection is one of the most common and dangerous web application vulnerabilities. It occurs when untrusted user input is improperly incorporated into database queries, allowing attackers to manipulate the structure of the query itself. In Flask applications that interact with databases, SQL injection vulnerabilities can lead to unauthorized data access, data corruption, or even complete system compromise.
This guide will explain what SQL injection is, how it affects Flask applications, and most importantly, how to prevent it in your Flask projects.
What is SQL Injection?
SQL Injection is a code injection technique that exploits vulnerabilities in applications that build SQL queries by concatenating strings, including user-supplied data, without proper validation or sanitization.
Consider this vulnerable Flask route:
@app.route('/users')
def get_user():
username = request.args.get('username')
query = "SELECT * FROM users WHERE username = '" + username + "'"
results = db.execute(query).fetchall()
return render_template('users.html', users=results)
If a user provides a normal input like alice, the query becomes:
SELECT * FROM users WHERE username = 'alice'
However, if an attacker inputs ' OR '1'='1, the query becomes:
SELECT * FROM users WHERE username = '' OR '1'='1'
Since '1'='1' is always true, this query returns all users in the database instead of a specific user. This is a simple example, but attackers can use more complex techniques to extract sensitive data, modify database content, or even delete entire tables.
Common SQL Injection Vulnerabilities in Flask
1. Direct String Concatenation
The most obvious vulnerability is building SQL queries by directly concatenating strings:
# Vulnerable code
user_id = request.args.get('id')
query = "SELECT * FROM users WHERE id = " + user_id
result = db.execute(query).fetchone()
2. Format String Vulnerabilities
Even using string formatting is unsafe:
# Still vulnerable
username = request.form['username']
query = "SELECT * FROM users WHERE username = '%s'" % username
result = db.execute(query).fetchone()
3. Unsafe Use of Flask-SQLAlchemy
Even with ORMs, raw SQL can still be vulnerable:
# Vulnerable despite using SQLAlchemy
search = request.args.get('search')
users = User.query.filter(f"username LIKE '%{search}%'").all()
How to Prevent SQL Injection in Flask
1. Use Parameterized Queries
Parameterized queries are the most effective defense against SQL injection. With parameterized queries, the database distinguishes between code and data regardless of user input.
# Safe approach using parameterized queries
@app.route('/user')
def get_user():
username = request.args.get('username')
# Using parameters with a question mark placeholder
query = "SELECT * FROM users WHERE username = ?"
results = db.execute(query, (username,)).fetchall()
return render_template('users.html', users=results)
Or with named parameters:
# Safe approach using named parameters
username = request.args.get('username')
query = "SELECT * FROM users WHERE username = :username"
results = db.execute(query, {"username": username}).fetchall()
2. Use ORM Properly (SQLAlchemy)
Flask-SQLAlchemy provides an Object-Relational Mapping (ORM) layer that helps prevent SQL injection when used correctly:
# Safe SQLAlchemy approach
@app.route('/users/search')
def search_users():
search = request.args.get('search', '')
# Safe way to use SQLAlchemy
users = User.query.filter(User.username.like(f'%{search}%')).all()
return render_template('search_results.html', users=users)
Flask-SQLAlchemy automatically handles parameter sanitization.
3. Input Validation and Sanitization
While parameterized queries should be your primary defense, input validation adds an additional layer of security:
@app.route('/user/<int:user_id>')
def get_user_by_id(user_id):
# Flask's converter ensures user_id is an integer
user = User.query.get_or_404(user_id)
return render_template('user_profile.html', user=user)
4. Use Database-Specific Security Features
Many databases offer security features that can help mitigate SQL injection:
# Example: Setting up a restricted database user in app configuration
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://read_only_user:password@localhost/mydb'
Practical Examples
Example 1: User Login System
Let's compare vulnerable vs. secure implementations of a login system:
Vulnerable Login:
@app.route('/login', methods=['POST'])
def login_vulnerable():
username = request.form.get('username')
password = request.form.get('password')
# VULNERABLE: Direct string concatenation
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
user = db.execute(query).fetchone()
if user:
session['user_id'] = user['id']
return redirect(url_for('dashboard'))
else:
flash('Invalid credentials')
return redirect(url_for('login'))
Secure Login:
@app.route('/login', methods=['POST'])
def login_secure():
username = request.form.get('username')
password = request.form.get('password')
# SECURE: Parameterized query
query = "SELECT * FROM users WHERE username = ?"
user = db.execute(query, (username,)).fetchone()
# SECURE: Password verification outside SQL query
if user and check_password_hash(user['password'], password):
session['user_id'] = user['id']
return redirect(url_for('dashboard'))
else:
flash('Invalid credentials')
return redirect(url_for('login'))
Note how the secure version:
- Uses parameterized queries
- Separates password verification from the SQL query
- Uses password hashing (always store hashed passwords!)
Example 2: Search Functionality
Vulnerable Search:
@app.route('/products/search')
def search_products_vulnerable():
search_term = request.args.get('q', '')
# VULNERABLE: Direct string interpolation in query
query = f"SELECT * FROM products WHERE name LIKE '%{search_term}%' OR description LIKE '%{search_term}%'"
products = db.execute(query).fetchall()
return render_template('products.html', products=products)
Secure Search with SQLAlchemy:
@app.route('/products/search')
def search_products_secure():
search_term = request.args.get('q', '')
# SECURE: Using SQLAlchemy's filtering
products = Product.query.filter(
or_(
Product.name.like(f'%{search_term}%'),
Product.description.like(f'%{search_term}%')
)
).all()
return render_template('products.html', products=products)
Secure Search with Raw SQL:
@app.route('/products/search')
def search_products_secure_raw():
search_term = request.args.get('q', '')
# SECURE: Parameterized query with raw SQL
query = """
SELECT * FROM products
WHERE name LIKE ? OR description LIKE ?
"""
search_pattern = f'%{search_term}%'
products = db.execute(query, (search_pattern, search_pattern)).fetchall()
return render_template('products.html', products=products)
Testing for SQL Injection Vulnerabilities
To ensure your Flask application is secure, you can test for SQL injection vulnerabilities:
# Test route showing how to identify vulnerable points
@app.route('/test-injection')
def test_injection():
# Try various test inputs like:
# - username=admin' OR '1'='1
# - username=admin'; DROP TABLE users; --
username = request.args.get('username')
# Vulnerable method (for demonstration only!)
vulnerable_query = f"SELECT * FROM users WHERE username = '{username}'"
# Safe method
safe_query = "SELECT * FROM users WHERE username = ?"
safe_result = db.execute(safe_query, (username,)).fetchall()
return render_template('injection_test.html',
username=username,
vulnerable_query=vulnerable_query,
safe_result=safe_result)
Best Practices Summary
- Always use parameterized queries or prepared statements
- Use ORMs correctly - SQLAlchemy provides security when used properly
- Validate input to ensure it meets expected formats and types
- Apply the principle of least privilege for database users
- Never build SQL queries through string concatenation or interpolation
- Use HTTPS to prevent network eavesdropping of credentials
- Keep your database software updated to patch known vulnerabilities
- Implement proper error handling that doesn't expose SQL data
Common Mistakes to Avoid
- Trusting client-side validation alone: Always validate on the server
- Using string concatenation for query building, even if you think the input is "safe"
- Displaying detailed database error messages to users
- Using the same database user for all operations instead of limited-privilege users
- Storing sensitive data without encryption
Summary
SQL injection remains one of the most dangerous vulnerabilities in web applications. By understanding how SQL injection works and implementing proper defenses like parameterized queries and ORMs, you can protect your Flask applications from these attacks.
Always treat all user input as potentially malicious, use parameterized queries, properly implement ORMs like SQLAlchemy, and follow security best practices to keep your Flask applications secure.
Additional Resources
- OWASP SQL Injection Prevention Cheat Sheet
- Flask-SQLAlchemy Documentation
- SQLite Parameterized Queries
- Bobby Tables: A Guide to Preventing SQL Injection
Exercises
- Take the vulnerable code examples in this guide and fix them to prevent SQL injection.
- Create a simple Flask application with a search function and implement it using parameterized queries.
- Try to identify SQL injection vulnerabilities in an existing project and fix them.
- Write tests that attempt common SQL injection attacks against your routes to verify they're secure.
Remember: Security is not a feature but a continuous process. Regularly review your code for potential vulnerabilities and keep up with the latest security recommendations.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)