PHP - MySQL Injection

SQL injection is an attack wherein an attacker can inject or execute harmful SQL code via input data from the browser to the application server. It can be used to expose sensitive information like user's contact number, email address, credit card information and so on. An attacker can even use it to bypass authentication process and get access to the entire database.

How SQL Injection Works

Consider the following SQL statement which is a simple example of authenticating a user with a username and password in a web application.

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

If user is an attacker and instead of entering a valid username and password in the input fields, he entered the values something like: ' OR 'x'='x

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

This statement is a valid SQL statement and since WHERE 'x'='x' is always true, the query will return all rows from the users table. You can see how easily an attacker can get access to all the sensitive information of a database with just a little trick.

Preventing SQL Injection

Always validate user input and make no assumptions. Never build SQL statements directly from user input. You can use mysqli_real_escape_string() function to create a legal SQL string.

Example of user authentication using PHP and MySQL that demonstrates how to prevent SQL injection while taking input from users.

<?php

$conn = mysqli_connect("localhost", "root", "", "demo");
 
if($conn === false){
    die("ERROR: Could not connect to database.");
}

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

$password_val = mysqli_real_escape_string($conn, $_POST['password']);

$sql = "SELECT * FROM users WHERE username='" . $username_val . "' AND password='" . $password_val . "'";

?>