SQL Injection

What is SQL Injection?

SQL injection is an attack where malicious SQL code is injected into input data, such as web-form inputs, to execute unauthorized SQL commands on a database.

It can expose sensitive information like user contact details, emails, and credit card data. Attackers can also bypass authentication and gain full database access.

How SQL Injection Works

Imagine a simple SQL statement used for user authentication:

SELECT * FROM users WHERE username='username_val' AND password='password_val';

For example, if a user inputs "john" as username and "123" as password, the query becomes:

SELECT * FROM users WHERE username='john' AND password='123';

However, an attacker can input malicious data like ' OR 'x'='x to construct a valid query that retrieves all rows:

SELECT * FROM users WHERE username='' OR 'x'='x' AND password='' OR 'x'='x';

This query always evaluates to true (WHERE 'x'='x'), fetching all rows from the users table. Such attacks can compromise data integrity and overwhelm system resources, causing denial-of-service.

If the users table is large, this attack can severely impact application performance.

Warning: If your script is vulnerable to SQL injection and generates a DELETE or UPDATE query, the consequences can be severe. Attackers can delete or modify all rows in the table permanently.

Preventing SQL Injection

Always validate user input and avoid directly incorporating it into SQL statements. Instead of using user input directly, ensure it is properly sanitized and escaped. For instance, in PHP and MySQL, you can utilize the mysqli_real_escape_string() function to safely handle user inputs within SQL queries.

Below is a simple example of user authentication using PHP and MySQL, demonstrating how to safeguard against SQL injection:

Example

Download

<?php
// Starting session
session_start();

/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect to database.");
}

// Escape user inputs for security
$username_val = mysqli_real_escape_string($link, $_POST['username']);
$password_val = mysqli_real_escape_string($link, $_POST['password']);

if(isset($username_val, $password_val)){
// Attempt select query execution
$sql = "SELECT * FROM users WHERE username='" . $username_val . "' AND password='" . $password_val . "'";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) == 1){
// User is authenticated do your stuff here
$row = mysqli_fetch_array($result);
/* Holding values in session variable so that it can be
accessed later within the same session reference */
$_SESSION['user_id'] = $row['user_id'];
$_SESSION['first_name'] = $row['first_name'];
header('Location: welcome.php');
} else{
echo "ERROR: Invalid username or password.";
}
} else{
echo "ERROR: Something went wrong. Please try again.";
}
}

// Close connection
mysqli_close($link);
?>

Explore the tutorial on PHP MySQL prepared statements to discover advanced techniques for safeguarding your web applications against SQL injection.

Tip: Validate the size, type, and content of incoming data to your application. Set limits to prevent exploitation of system resources.