1. Introduction
In this article, we learn about SQL injection security vulnerability in web application. We see an example of SQL Injection, learn in in-depth how it works, and see how we can fix this vulnerability. We use PHP and MySQL for the examples. The SQL injection is the top exploit used by hackers and is one of the top attacks enlisted by the OWASP community.
2. What is SQL Injection
SQL Injection is a attack mostly performed on web applications. In SQL Injection, attacker injects portion of malicious SQL through some input interfaces like web forms. These injected statements goes to the database server behind a web application and may do unwanted actions like providing access to unauthorised person or deleting or reading sensitive information.
The SQL Injection vulnerability may affect any application powered by database supporting SQL like Oracle, MySQL and others.
SQL Injection attacks are one of the widest used, oldest, and very dangerous application vulnerabilities. The OWASP organization (Open Web Application Security Project) lists SQL Injections in their OWASP Top 10 document as the top threat to web application security.
3. Example of SQL Injection
Let’s create a form in HTML:
<!DOCTYPE html>
<html>
<body>
<h2>SQL injection in web applications</h2>
<form action="/form-handler.php">
Username:<br>
<input type="text" name="username" value="">
<br>
Password:<br>
<input type="password" name="password" value="">
<br><br>
<input type="submit" value="Submit">
</form>
</body>
</html>
When we click on submit, the form above submits to below PHP script:
<?php
mysql_connect('localhost', 'root', 'root');
mysql_select_db('bootsity');
$username = $_POST["username"];
$password = $_POST["password"];
$query = "SELECT * FROM Users WHERE username = " . $username . " AND password =" . $password;
$re = mysql_query($query);
if (mysql_num_rows($re) == 0) {
echo 'Not Logged In';
} else {
echo 'Logged In';
}
?>
4. How SQL Injection works
In the above example, assume that the user fills up the form as below:
Username: ' or '1'='1 Password: ' or '1'='1
Now our $query becomes:
SELECT * FROM Users WHERE username='' or '1'='1' AND password='' or '1'='1';
This query always returns some rows and results in printing Logged In on the browser. So, here the attacker doesn’t know any username or password that are register in the database, but the attacker is still able to log in.
5. Fixing SQL Injection
Now we understand how SQL injection works in PHP. Generally, the best solution is to use prepared statements and parameterized queries. When we use prepared statements and parameterized queries, the SQL statements are parsed separately by the database engine. Let us see these approaches below:
5.1 Using PDO
We can change our form-handler.php to use PDO:
<?php
$dsn = "mysql:host=localhost;dbname=bootsity";
$user = "root";
$passwd = "root";
$pdo = new PDO($dsn, $user, $passwd);
$username = $_POST["username"];
$password = $_POST["password"];
$stmt = $pdo->prepare('SELECT * FROM Users WHERE username = :username AND password = :password');
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();
if (count($stmt) == 0) {
echo 'Not Logged In';
} else {
echo 'Logged In';
}
$stmt->close();
$pdo->close();
?>
5.2 Using MySQLi (Only for MySQL)
We can also use MySQLi in our form-handler.php to fix the SQL Injection issue:
<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "bootsity";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$username = $_POST["username"];
$password = $_POST["password"];
// prepare and bind
$stmt = $conn->prepare('SELECT * FROM Users WHERE username = ? AND password = ?');
$stmt->bind_param($username, $password);
$stmt->execute();
if (count($stmt) == 0) {
echo 'Not Logged In';
} else {
echo 'Logged In';
}
$stmt->close();
$conn->close();
?>
6. More notes on SQL Injection
We saw one way of SQL injection. There can be other ways, as well. It mostly depends on the creativity of the attacker. One other method that may lead to more severe implications is:
Assume the example code:
$username = getRequestString("UserId");
$query = "SELECT * FROM Users WHERE username = " + $username;
If the end user passes the value 1; DROP TABLE profiles;
Then $query becomes SELECT * FROM Users WHERE username = 1; DROP TABLE profiles;
That is why, in our applications, we should use connections having required permissions.
7. Conclusion
In this article, we learned what SQL Injection is and how we can avoid loopholes in our web applications that may result in SQL injections attacks. In the upcoming articles, we will learn more about other security vulnerabilities in web applications. The complete code example that we used is available on Github for your practice. If you have any queries, or you want to share more things related to SQL Injection, you may share in the comments below. Cheers to you.
Leave a Reply