Time-based SQL Injection
Need
Prevention of time-based SQL injection attacks
Context
- Usage of Python 3 for developing applications and scripts
- Usage of Django for building web applications in Python
Description
Non compliant code
from django.http import HttpResponse
from django.db import connection
def user_login(request):
username = request.GET['username']
password = request.GET['password']
cursor = connection.cursor()
cursor.execute("SELECT * FROM users WHERE username = '%s' AND password = '%s'" % (username, password))
user = cursor.fetchone()
if user:
return HttpResponse("User logged in.")
else:
return HttpResponse("Invalid credentials.")
The above code represents a view function in Django that handles user login. It takes a username and password from the HTTP GET request and uses these to query the users table in the database and fetch the corresponding user.
The vulnerability lies in the way the SQL query is constructed. The code directly uses the username and password from the GET request to create the SQL query. This means that an attacker could manipulate the username or password parameter in the GET request to inject malicious SQL code.
For instance, an attacker could set the username parameter to ' OR '1'='1' AND SLEEP(10)-- and the password parameter to any value. The resulting SQL query would be:
SELECT * FROM users WHERE username = '' OR '1'='1' AND SLEEP(10)-- ' AND password = 'any_value'
This would cause the database to sleep for 10 seconds before returning the result, effectively creating a time delay. By observing this delay, the attacker can confirm that the SQL injection was successful. They can then proceed to exploit this vulnerability to extract sensitive information from the database.
To prevent this vulnerability, you should never directly include user input in SQL queries. Instead, use parameterized queries or Django's built-in ORM, which automatically escapes user input to prevent SQL injection.
Steps
- Avoid constructing SQL queries using string formatting. This leaves the code open to SQL injection attacks.
- Use parameterized queries or prepared statements instead. These methods automatically escape special characters and protect against SQL injection.
- In Django, you can use the 'params' argument in the 'execute' method to pass in parameters. The SQL query should contain placeholders (%s) for parameters.
- Never trust user input. Always validate and sanitize user input before using it in your application.
- Consider using Django's built-in authentication system which provides a secure way to handle user authentication.
Compliant code
from django.http import HttpResponse
from django.db import connection
def user_login(request):
username = request.GET['username']
password = request.GET['password']
cursor = connection.cursor()
cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", [username, password])
user = cursor.fetchone()
if user:
return HttpResponse("User logged in.")
else:
return HttpResponse("Invalid credentials.")
The original code was vulnerable to Time-based SQL Injection. This vulnerability could allow an attacker to inject a SQL command to the server with code to force a delay in the execution of the queries or with a heavy query that generates this time delay. Depending on the time response, it is possible to deduct some information and determine if a vulnerability is present to exploit it.
The fixed code now uses parameterized queries to prevent SQL injection. Instead of formatting the SQL query with user input, it uses placeholders (%s) and passes the user input as parameters to the execute method. This method automatically escapes special characters and protects against SQL injection.
The SQL query in the execute method now looks like this: "SELECT * FROM users WHERE username = %s AND password = %s". The user input is passed as a list of parameters: [username, password].
This way, even if an attacker tries to inject SQL commands into the 'username' or 'password' fields, the execute method will treat them as literal strings, not part of the SQL command.
It's also worth noting that user input should never be trusted. Always validate and sanitize user input before using it in your application. Consider using Django's built-in authentication system which provides a secure way to handle user authentication.