What is SQL Injections?
We'll discuss what SQL injection is, how it works, and most importantly, how to prevent it. By the end of this read, you'll have a solid understanding of SQL injection and the confidence to safeguard your applications against this pervasive threat.
SQL injection is a type of attack that allows attackers to execute malicious SQL statements in your application's database. It occurs when input from a user is directly incorporated into SQL statements without proper sanitization or validation. This can lead to unauthorized access, data manipulation, and even data loss. Here's an example to illustrate SQL injection:
Consider a simple Form and PHP login script:
<form method="post"> <input type="text" name="username" placeholder="username"> <input type="password" name="password" placeholder="password"> <input type="submit" name="submit"> </form> <?php if(isset($_POST['submit'])) { $username = $_POST['username']; $password = $_POST['password']; $conn = new mysqli('localhost', 'username', 'password', 'tablename'); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo 'Username: '.$username.'</br>'; echo 'Password: '.$password.'</br>'; $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'"; $result = $conn->query($sql); if ($result->num_rows > 0) { echo "Login successful"; } else { echo "Invalid username or password"; } // Close connection $conn->close(); }
Now, suppose an attacker knows your username but not your password, and they enter the following username in the login form:
username: ' OR '1'='1 password: anythingThe resulting SQL query will be:
SELECT * FROM users WHERE username='$username' OR '1'='1' AND password='$password'
In MySQL, logical operators like AND have higher precedence than OR.
The AND condition will be evaluated first because it has higher precedence.
Then, the OR condition will be evaluated.
So, effectively, your query is equivalent to:
SELECT * FROM users WHERE (username = '$username') OR (('1'='1') AND (password = '$password'))The condition '1'='1' is always true, so it effectively becomes:
SELECT * FROM users WHERE (username = '$username') OR (true AND (password = '$password'))
This means:
- If the provided username matches any username in the users table, the row will be selected.
- If the provided password matches any password in the users table, the row will be selected, but only if the first condition '1'='1' is true.
Given that '1'='1' is always true, this effectively makes the query select any row where the username matches the provided username OR any row where the password matches the provided password, regardless of the actual username/password combination.
So, the query could potentially return true for any provided username and password combination, which is likely not the intended behavior for a login query. It's a security vulnerability, and this attack is called SQL injection, which should be avoided.
How can SQL injection be prevented?
Input Validation:
<?php // Sample user input (from a form, for example) $userInput = $_POST['input_field']; // Sanitize the input using htmlentities function to escape special characters $sanitizedInput = htmlentities($userInput, ENT_QUOTES, 'UTF-8'); // Sanitize the input using mysqli_real_escape_string $sanitizedInput = mysqli_real_escape_string($conn, $userInput); // Now you can use $sanitizedInput safely in your application ?>
Use Parameterized Queries or Prepared Statements:
<?php $username = $_POST['username']; $password = $_POST['password']; $query = "SELECT * FROM users WHERE username=? AND password=?"; $stmt = mysqli_prepare($conn, $query); mysqli_stmt_bind_param($stmt, "ss", $username, $password); mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); if(mysqli_num_rows($result) > 0) { // Login successful } else { // Login failed } ?>