Open In App

How to prevent SQL Injection in PHP ?

Improve
Improve
Like Article
Like
Save
Share
Report

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.

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




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

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 – 

PHP




<?php
  
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";
}
  
?>




Last Updated : 31 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads