A Vulnerable Search Feature
This search feature supports advanced query patterns and demonstrates how dynamic SQL queries can introduce security risks when user input is not handled safely.
Specifically, we will use the poll search feature to extract user account information from the system's user table.
' AND 1=0 UNION SELECT id, username, 'user' FROM auth_user --
Observe how results unrelated to polls may appear.
SQL Injection Explained
SQL Injection (SQLi) is a vulnerability that occurs when an application includes untrusted user input directly in a database query without proper protection. Instead of being treated strictly as data, the input is interpreted as executable SQL.
Attackers can manipulate queries to:
- bypass authentication
- extract sensitive data
- modify database records
- delete information
- escalate privileges
Vulnerable Query Example
When querying the database, a developer may write such a code block.
sql = f"""
SELECT id, question, category
FROM polls_poll
WHERE question LIKE '%{query}%'
"""
Because the input is directly inserted into the SQL string, an attacker can inject SQL syntax, like we did in the search bar. This alters the query structure and allows the database to return user records instead of only polls.
Even when passwords are hashed, exposing user data is considered a high severity vulnerability. The following is a query that will return the password hashes of the users in this vulnerable system!
' AND 1=0 UNION SELECT id, username, password FROM auth_user --
You can also experiment with different queries to see what other data you can retrieve.
Secure Approach
The secure implementation of the polls application prevents SQL injection by using parameterized queries, ensuring user input is always treated strictly as data. The secure parameterized server-side query for the search bar is:
cursor.execute(
"""
SELECT id, question, category
FROM polls_poll
WHERE question LIKE %s
""",
[f"%{query}%"])
Parameterized queries prevent attackers from altering the structure of SQL statements.
Key takeaway: Never trust user input inside SQL queries. Always use parameterized queries or Django ORM filtering.