SQL Injection

In this tutorial, you will learn how to fix common database vulnerabilities.

What is SQL injection?

SQL injection is an attack where an attacker can inject or execute malicious SQL code by inputting data into the application server through the browser (such as web form inputs).

It can be used to publicly disclose sensitive information, such as users' contact numbers, email addresses, credit card information, etc. Attackers can even use it to bypass the authentication process and gain access to the entire database. Let's see how it works.

How SQL injection works

Consider the following SQL statement, which is a simple example of using a username and password to authenticate users in a web application.

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

Here,username_valandpassword_valwhich respectively represent the username and password entered by the user. If the user enters such as “john” as the username and “ 123”as a password value, then the resulting statement will be:

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

But suppose, if the user is an attacker, he does not enter a valid username and password in the input field, but enters values similar to the following: ' OR 'x'='x

In this case, the above SQL query will be constructed as:

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

This statement is a valid SQL statement, as WHERE 'x'='x' is always true, so the query will returnusersAll rows in the table. You can see that the attacker can easily access all sensitive information in the database with a small trick.

if}}usersIf the table is very large and contains millions or rows, the single statement may also cause a denial of service attack (DoS attack) by overloading system resources, making your application unavailable to legitimate users.

Prevent SQL injection

Always validate user input and make no assumptions. Never construct SQL statements directly from user input. If you are using PHP and MySQL, you can use the mysqli_real_escape_string() function to create a legal SQL string that can be used in SQL statements.

This is a very basic example of user authentication using PHP and MySQL, demonstrating how to prevent SQL injection when getting input from users.

<?php
//　Starting session
session_start();
　
/*　Attempt to connect to the MySQL server.
　　　Assuming you are running the MySQL server with default settings (user 'root' has no password)　*/
$link = mysqli_connect("localhost", "root", "", "demo");
　
//　Check connection
if($link === false){
　　　　die("Error: Unable to connect to the database.");
}
　
//　Escape user input to ensure 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 to 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 has been authenticated, please proceed with the operation here
　　　　　　　　　　　　$row = mysqli_fetch_array($result);
　　　　　　　　　　　　/*Save the values in the session variables
　　　　　　　　　　　　　　　so that it can be accessed later in the same session reference　*/
　　　　　　　　　　　　$_SESSION['user_id'] = $row['user_id'];
　　　　　　　　　　　　$_SESSION['first_name'] = $row['first_name'];
　　　　　　　　　　　　header('Location: welcome.html');
　　　　　　　　}
　　　　　　　　　　　　echo "Error! Invalid username or password.";
　　　　　　　　}
　　　　}
　　　　　　　　echo "Error: something went wrong. Please try again.";
　　　　}
}
　
//　Close connection Close connection
mysqli_close($link);
?>