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: anything

The 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?

you can prevent SQL injection attacks. Here are some best practices to help prevent SQL injection:

Input Validation: 

Validate and sanitize user input on the server-side to ensure that it conforms to expected formats and types.

Sanitizing input fields involves removing or escaping potentially harmful characters from user input to prevent security vulnerabilities like XSS (Cross-Site Scripting) or SQL injection. Here's an example of sanitizing input fields in PHP:

<?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
?>

After sanitizing the input, $sanitizedInput can be safely used in your application without the risk of XSS vulnerabilities.

Sanitizing input alone is not sufficient to protect against SQL injection. While input sanitization can help mitigate certain types of attacks by removing or escaping potentially harmful characters, it is not foolproof. Attackers can still find ways to bypass sanitization techniques.

Use Parameterized Queries or Prepared Statements: 

The most effective way to prevent SQL injection attacks is to use parameterized queries or prepared statements. These methods ensure that user input is treated as data rather than executable code. With parameterized queries, the SQL engine automatically handles the escaping of special characters, making it virtually impossible for attackers to inject malicious SQL code.

<?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
}
?>

So, while sanitizing input is a good practice for data validation, it should be complemented with the use of parameterized queries or prepared statements for robust protection against SQL injection attacks.

Thank You!