In this article, we are going to discuss how to prevent SQL injection in PHP. The prerequisite of this topic is that you are having XAMPP in your computer.
Why SQL injection occurs?SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).
In this type of technique, the hacker or attacker uses some special characters which convert the SQL query into a new SQL query and the attacker can manipulate the query by entering more kinds of keywords.
Step 2: Use this database –USE GFG;
Step 3: Create a login credentials table in GFG database – CREATE TABLE users( id int(10) PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255) );
Step 4: Insert some data into database – INSERT INTO users VALUES(1, 'idevesh', '1234'); INSERT INTO users VALUES(2, 'geeksforgeeks', 'gfg');
Data After insertion
Step 5: Now create a PHP script for login page –
(a) Create a DB Connection File (dbconnection.php) –
$db = mysqli_connect("localhost","root","","GFG");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: "
. mysqli_connect_error();
}
?>
(b) Create a HTML form to input from the USER –
<html>
<head>
<title>GFG SQL Injection Article</title>
<link rel="stylesheet" type="text/css"
href="style.css">
</head>
<body>
<div id="form">
<h1>LOGIN FOR SQL INJECTION</h1>
<form name="form"
action="verifyLogin.php" method="POST">
<p>
<label> USER NAME: </label>
<input type="text" id="user"
name="userid" />
</p>
<p>
<label> PASSWORD: </label>
<input type="text" id="pass"
name="password" />
</p>
<p>
<input type="submit"
id="button" value="Login" />
</p>
</form>
</div>
</body>
</html>
(c) Create a file verifyLogin.php for validating the user input –
include 'dbconnection.php';
$userid = $_POST['userid'];
$password = $_POST['password'];
$sql =
"SELECT * FROM users WHERE username = '$userid' AND password = '$password'";
$result = mysqli_query($db, $sql) or die(mysqli_error($db));
$num = mysqli_fetch_array($result);
if($num > 0) {
echo "Login Success";
}
else {
echo "Wrong User id or password";
}
?>
Step 6: Now we will pass a poisoned password to get entry into the user profile –
Why SQL injection occurs?SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).
In this type of technique, the hacker or attacker uses some special characters which convert the SQL query into a new SQL query and the attacker can manipulate the query by entering more kinds of keywords.
Let us make a SQL injection scenario then we will learn how to fix it.
Step 1: So, let’s start by creating a database – CREATE DATABASE GFG;Step 2: Use this database –USE GFG;
Step 3: Create a login credentials table in GFG database – CREATE TABLE users( id int(10) PRIMARY KEY AUTO_INCREMENT, username VARCHAR(255), password VARCHAR(255) );
Step 4: Insert some data into database – INSERT INTO users VALUES(1, 'idevesh', '1234'); INSERT INTO users VALUES(2, 'geeksforgeeks', 'gfg');
Data After insertion
Step 5: Now create a PHP script for login page –
(a) Create a DB Connection File (dbconnection.php) –
PHP
<?php$db = mysqli_connect("localhost","root","","GFG");
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: "
. mysqli_connect_error();
}
?>
(b) Create a HTML form to input from the USER –
PHP
<!DOCTYPE html><html>
<head>
<title>GFG SQL Injection Article</title>
<link rel="stylesheet" type="text/css"
href="style.css">
</head>
<body>
<div id="form">
<h1>LOGIN FOR SQL INJECTION</h1>
<form name="form"
action="verifyLogin.php" method="POST">
<p>
<label> USER NAME: </label>
<input type="text" id="user"
name="userid" />
</p>
<p>
<label> PASSWORD: </label>
<input type="text" id="pass"
name="password" />
</p>
<p>
<input type="submit"
id="button" value="Login" />
</p>
</form>
</div>
</body>
</html>
(c) Create a file verifyLogin.php for validating the user input –
PHP
<?phpinclude 'dbconnection.php';
$userid = $_POST['userid'];
$password = $_POST['password'];
$sql =
"SELECT * FROM users WHERE username = '$userid' AND password = '$password'";
$result = mysqli_query($db, $sql) or die(mysqli_error($db));
$num = mysqli_fetch_array($result);
if($num > 0) {
echo "Login Success";
}
else {
echo "Wrong User id or password";
}
?>
Step 6: Now we will pass a poisoned password to get entry into the user profile –
Poisoned password = ' or 'a'='a
So, as you can see the above-mentioned poisoned string can make any user login in the geeksforgeeks username so this is called SQL Injection.
Now to avoid this type of SQL injection, we need to sanitize the password input and username input using mysqli_real_escape_string() function.
The mysqli_real_escape_string() function takes the special characters as they were as an input from the user and doesn’t consider them as query usage.
So new code for verifyLogin.php will be –
include 'dbconnection.php';
$userid = $_POST['userid'];
$password = $_POST['password'];
$sanitized_userid =
mysqli_real_escape_string($db, $userid);
$sanitized_password =
mysqli_real_escape_string($db, $password);
$sql = "SELECT * FROM users WHERE username = '"
. $sanitized_userid . "' AND password = '"
. $sanitized_password . "'";
$result = mysqli_query($db, $sql)
or die(mysqli_error($db));
$num = mysqli_fetch_array($result);
if($num > 0) {
echo "Login Success";
}
else {
echo "Wrong User id or password";
}
?>
So, as you can see the above-mentioned poisoned string can make any user login in the geeksforgeeks username so this is called SQL Injection.
Now to avoid this type of SQL injection, we need to sanitize the password input and username input using mysqli_real_escape_string() function.
The mysqli_real_escape_string() function takes the special characters as they were as an input from the user and doesn’t consider them as query usage.
So new code for verifyLogin.php will be –
PHP
<?phpinclude 'dbconnection.php';
$userid = $_POST['userid'];
$password = $_POST['password'];
$sanitized_userid =
mysqli_real_escape_string($db, $userid);
$sanitized_password =
mysqli_real_escape_string($db, $password);
$sql = "SELECT * FROM users WHERE username = '"
. $sanitized_userid . "' AND password = '"
. $sanitized_password . "'";
$result = mysqli_query($db, $sql)
or die(mysqli_error($db));
$num = mysqli_fetch_array($result);
if($num > 0) {
echo "Login Success";
}
else {
echo "Wrong User id or password";
}
?>
Post a Comment