Mitigating SQL Injection: Techniques and Their Technical Details
Building on our earlier discussion of Cross-Site Scripting attacks (Refer Here) , let’s now delve into SQL injection. SQL Injection is another widely recognized and exploited web application vulnerability. According to OWASP ( Open Worldwide Application Security Project ), SQL injection was number #1 on the most frequently found vulnerabilities in web application in 2017, and number #3 in 2021, which indicates the persistent existence of the threat of such vulnerabilities.
But before studying the mitigation and prevention techniques, we must first understand how SQL Injection works, and in what way does it affect web applications.
How Does SQL Injection work?
SQL injection is a malicious method that takes advantage of weaknesses in inadequately validated user inputs within web applications to manipulate a database. This technique involves inserting harmful SQL (Structured Query Language) statements into input fields where the application improperly processes or trusts user-provided information without proper validation.
Let us consider a vulnerable login page, for example:
$username = $_POST['username'];
$password = $_POST['password'];
$query = "SELECT * FROM users WHERE username='$username' AND password='$password'";In this code example, if there is no input validation, an attacker can exploit SQL injection. They could enter a username such as “ ’ OR ‘1’=’1 -- ” , and leave the password field empty. This would transform the SQL query into:
SELECT * FROM users WHERE username='' OR '1'='1' -- AND password=''This manipulated query would always evaluate to true for the username condition (‘1’=’1'), The- -is an SQL comment indicator. Anything after -- is treated as a comment and is not executed as part of the query. In this case, it effectively comments out the remaining part of the original query, which includes the password check.
As a result, the SQL query becomes:
SELECT * FROM users WHERE username='' OR '1'='1'Which would consequently returns all rows from the ‘users’ table where either the username matches the empty string or the condition ‘1’=’1' which is always. Therefore, the password check is bypassed, and the attacker gains unauthorized access to all application’s data .
Now, since we have the basic understanding of How SQL Injection works in concept, we can start discussing the various ways to mitigate and prevent the the attack of SQL Injection.
Mitigation and preventions
Mitigating SQL injection hinges on rigorous input validation and blocking malicious user inputs from accessing or altering the database. By implementing techniques like Input validation, Sanitization libraries and Web application firewall (WAF).
1- Input Validation
Input validation, such as the basic form demonstrated here, is a foundational technique in web application security. It is particularly suitable for cases where specific input, like usernames, is expected to adhere to well-defined patterns without special characters. In this example, an example validating function can employ regular expressions to enforce these criteria. It ensures that provided usernames consist only of alphanumeric characters, underscores, or hyphens, For example:
import re
def is_valid_username(username):
# Define a regular expression pattern for a valid username
pattern = r'^[a-zA-Z0-9_-]+$'
# Check if the provided username matches the pattern
if re.match(pattern, username):
return True
else:
return FalseIn this case, if the user inputs special characters, the input is automatically rejected, and hence not passed to the database. it’s important to note that this can not be most suitable to use in all cases; As some inputs are expected to have specials characters in them. That is where we depend more on sanitization libraries, prepared statements, stored procedures and WAFs.
2 -Prepared statements
Prepared statements are one of the most reliable ways to prevent SQL Injection. The general concept of prepared statements is that you first prepare a statement with placeholders, that would later carry the user input, and a separate variable that would carry the user input, Instead of directly inserting the user input into the query statement. after that you bind the user input into the placeholders, at this point, the database server knows what type of data to except, For example, an Integer, or a string, and not as an SQL command, therefore escaping any characters that would rather alter the query’s logic or change it, and then the database sever can executes it safely.
For example:
// Establish a database connection using PDO
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");
// User-generated input
$user_input = "'; DROP TABLE users; --";
// Create a prepared statement
$query = $pdo->prepare("SELECT * FROM users WHERE username = :username");
// Bind the user input securely to the parameter
$query->bindParam(":username", $user_input, PDO::PARAM_STR);
// Execute the query
$query->execute();
// Fetch and process the query results safely
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
echo "User ID: " . $row['id'] . ", Username: " . $row['username'];
}We can notice here how the query is first prepared with a place holder for the $username, which is then bind with the user input, therefore treating it as a literal string in this case, regardless of what the $username variable contains in it.
Prepared statements are effective at preventing SQL Injection, as the database server is aware of the intended structure of the query. If an attacker attempts to inject malicious SQL code, it will be treated as part of the data rather than executed as a command, maintaining the security and integrity of the database.
3 -Stored procedures
Stored procedures another measure for preventing SQL injection. It works by encapsulating SQL logic within the database itself, instead of encapsulating it in the application side, then sending the query to the database for excution. These procedures consist of pre-defined SQL statements that can accept parameters. When executed, stored procedures automatically handle input sanitization and ensure that user input is treated as data, not executable SQL code. This eliminates the risk of SQL injection attacks because the database engine understands the intended structure of the SQL query.
An example for creating Stored procedures in the database:
-- Create the stored procedure within the database
CREATE PROCEDURE GetUserByUsername(username_param VARCHAR(255))
BEGIN
SELECT * FROM users WHERE username = username_param;
END;
Once the stored procedure is created in the database, the application can then call it, passing the user input as a parameter, and then the database server executes against the database and return the results
An Example of the code on the application side:
// Establish a database connection using PDO
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");
// User-generated input
$user_input = "Ahmed";
// Call the stored procedure with user input
$stmt = $pdo->prepare("CALL GetUserByUsername(:username)");
$stmt->bindParam(":username", $user_input, PDO::PARAM_STR);
$stmt->execute();
// Fetch and process the query results safely
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "User ID: " . $row['id'] . ", Username: " . $row['username'];
}In the application side code, we call a stored procedure after it has been defined in the database. The stored procedure takes user input as a parameter and automatically handles input sanitization within the database, preventing SQL injection vulnerabilities.
4 -Web Application firewall ( WAF )
Web Application Firewalls (WAF)s are an important asepect in protecting web applications, including web attacks such as Cross-site scripting and SQL Injection, alongside with many other attacks. They work by acting as the front shield for the web applications, intercepting and inspecting the incoming requests for malicious patterns or behaviors, and blocking or allowing traffic based on predefined rules and policies. To prevent SQL injection, WAFs employ a set of rules that scrutinize incoming data for known SQL injection attack patterns. When a request contains suspicious SQL-related content, the WAF can block or sanitize the input before it reaches the web application.
WAFs typically use regular expressions, signatures, and heuristics to detect SQL injection attempts, For example:
# Enable ModSecurity
SecRuleEngine On
# SQL Injection Protection Rule
SecRule ARGS "@rx ^[\"'()0-9a-zA-Z\s]+$" \
"id:1, \
phase:2, \
t:none, \
nolog, \
deny, \
status:403, \
msg:'SQL Injection Attack Detected'"WAFs can prevent and alert security teams monitoring the security status of the web applications, through logs that provides detailed information about the malicious requests prevented or detected, for example:
SecRule ARGS "@contains 'DROP TABLE'" "id:1,deny,status:403,msg:'SQL Injection detected'"While WAFs can help prevent and mitigate attacks such as SQL Injection, and provide an additional layer of security to the web application, they are not enough to be relied on in order to prevent such attacks, it should rather be used as a complementing layer to the other security measures like input validation, prepared statements and stored procedures, As they are most important line of defense against malicious user inputs.
Conclusion
In summary, preventing SQL injection is crucial to keep web applications safe. To do this, we need to understand how SQL injection works. It’s like a hacker sneaking bad code into a website’s search bar or login page. There are several ways to stop this, as mentioned above. Using these techniques together makes our web apps much safer.
