How to Prevent SQL Injection in PHP

by

As you know that you must develop a secure web application. So that you can protect your application from hackers. Mostly Hackers can hack your application with SQL injection. So, you need to know more about it How to prevent SQL Injection in PHP.

In this tutorial, you will learn SQL injection in detail with an example. Even you will get the best way to prevent it. Therefore, It is explained with simple & speaking language that will be helpful for you.

prevent sql injection in php

What is SQL Injection

SQL injection is a web hacking technique. So, It can destroy your database with injecting illegal code in SQL Statement

Using SQL Injection, Hackers can hack the user information from the database by inserting illegal code into the Input field & URL string.

SQL Injection Example

SQL Injection usually active whenever a user enters an SQL statement into the input field. and it will unknowingly execute on the database table.

You will completely understand SQL injection through the following examples. So, read all the given points.

SQL Injection 1=1

Hackers can easily access all the user information from the database table by entering 1=1 into the input field. Because of 1=1 is always true for the SQL SELECT statement.

Suppose that you have written the following SQL SELECT statement in PHP to return user information based on  User Id. Even this statement does not prevent SQL Injection.

$user_Id= $_REQUEST['user_id'];
$query= "SELECT * FROM users WHERE user_id=$user_Id"

If Hacker enters the wrong input value like 420 OR 1=1 into the input filed, the above SELECT statement will look like the following statement.

$query= "SELECT * FROM users WHERE user_id= 420 OR 1=1"

At least one of both conditions of the WHERE clause must be true to return all the user information from the users table.

Therefore, If user_id 420  does not exist in the users table, it will be false but 1=1 will be true. So the above statement can return all the user information.

If users table contains three columns like user_id, email & password. The SQL statement will be the same as the following statement.

$query= "SELECT user_id, email, password FROM users WHERE user_id= 420 OR 1=1"

Hence, Hackers can get user_id, email & password of all the registered users by simply entering 420 OR 1=1 into the input field.

SQL Injection OR “”=””

Hackers can easily access all the user information from the database table by entering the value OR “”= “” into the input field. Because of OR “”= “” is always true for the SQL SELECT statement.

Suppose that you have written the following SQL SELECT statement in PHP to return user information based on  email & password . Even this statement does not prevent SQL Injection.

$email= $_REQUEST['email'];
$password= $_REQUEST['password'];
$query= "SELECT * FROM users WHERE email=$email AND password= $password"

If Hacker enters the wrong input value like OR “”= “” into the input filed, the above SELECT statement will look like the following statement.

$query= "SELECT * FROM users WHERE email="" OR ""="" AND password="" OR ""="""

Both conditions of the WHERE clause must be true to return all the user information from the users table.

Therefore, If email & password do not exist in the users table, it will be false but the cause of  OR “”= “” , both conditions will be true. So the above statement can return all the user information.

Hence, Hackers can get user_id, email & password of all the registered users by simply entering OR “”= “” into the input field.

SQL injection – Batched SQL Statements

The batched statement contains two or more SQL statements separated by a comma. Even it can execute in most databases.

SELECT * FROM users; DROP TABLE students;

The above SQL statement is the example of a batch SQL statement, It will return all records from users table then delete the students table.

Suppose that you have written the following SQL SELECT statement in PHP to return user information based on  User Id. Even this statement does not prevent SQL Injection.

$user_Id= $_REQUEST['user_id'];
$query= "SELECT * FROM users WHERE user_id=$user_Id"

If Hacker enters the wrong input value like 420; DROP TABLE students into the input filed, the above SELECT statement will look like the following statement.

SELECT * FROM users WHERE user_id=420; DROP TABLE students;

Hence, Hackers can easily delete the existing table in the database by entering Batched SQL Statements.

Way to Prevent SQL Injection in PHP

We have seen the following SQL statement in the above examples How can hackers hack the user information, If it does not prevent SQL Injection in PHP.

$email= $_REQUEST['email'];
$password= $_REQUEST['password'];
$query= "SELECT * FROM users WHERE email=$email AND password= $password"
$exec= mysqli_query($conn, $query);

Suppose you have the following query for the database connection

$hostName = "localhost";
$userName = "username";
$password = "password";
$databaseName = "myDB";

$conn = new mysqli($hostName, $userName, $password, $databaseName);

Now, you will learn How to prevent SQL injection in the above SQL statements in the following ways.

1. Use Prepared Statements

Prepared statements are the best way to prevent SQL injection in PHP.

It executes similar to SQL statements and binds the value to the parameters to minimize bandwidth to the server. So, It always sanitizes the query which is sent to the database.

As you know that SQL injection directly occurs on the values SQL statements. but In the case of the SQL statement is prepared & replaced values with Question marks ( ? ) and prepared statements, all the values are bound to the parameters. Hence, it can prevent SQL injection.

You can use prepared statements in one of  the following options

 MySQLi for MySQL.

You can prevent SQL injection in the following SQL statement by using a prepared statement in MySQLi.

$email=$_REQUEST['email'];
$password= $_REQUEST['password'];

$stmt = $conn->prepare('SELECT * FROM users WHERE email = ? AND password= ?');
$stmt->bind_param('ss', $email,$password); // 's' specifies the variable type => 'string'

$stmt->execute();

$result = $stmt->get_result();

See the following statement, it is prepared and values of email and password are replaced by question marks.

$conn->prepare('SELECT * FROM users WHERE email = ? AND password= ?');

Even the following statement binds those values of email & password to ss. Here both ss are represented their datatypes. Because email and password is the string type. So, these are bound with ss.

$stmt->bind_param('ss', $email,$password);

You can bind the values with the following arguments

      • s – string
      • i – integer
      • d – double
      • b – BLOB

 PDO for any Supported Database Driver.

You can also prevent MySQL injection in a SQL statement by using prepared statements in PDO.

$email= $_REQUEST['email'];
$password= $_REQUEST['password'];

// prepare sql and bind parameters
$stmt = $conn->prepare("SELECT * FROM users WHERE email=:email AND password=:password");
$stmt->bindParam(':email', $email);
$stmt->bindParam(':password', $password);
$stmt->execute();

2. Escape Strings

You have to escape special characters in the input string to prevent SQL Injection in the SQL statements.

We have mysqli_escape_string() function in MySQL. It can help to remove the special characters from the user input value.

$email = mysqli_real_escape_string($conn,$_POST["email"]);
$password = mysqli_real_escape_string($conn,$_POST["password"]);

3. Create Form Security

Form Security is the most important to prevent SQL injection in PHP.

For the Form Security, make sure that your form contains the following element with attributes

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">

You have to properly validate the user input data using the following variable & functions

  • $_SERVER["PHP_SELF"] variable

It is the Super Global variable. It declares to return the filename of the current file in which the script will be executed. Even It is submitted the user input to the current page itself.

  • trim() function

It can remove white spaces from both sides of the user input

  • stripslashes() function

It can remove the backslashes from the user input.

  • htmlspecialchars() function

It can convert special characters to HTML entities.

Suppose that if hackers try to enter some special characters like the following code

<script>alert('hacking script')</script>

the above code will be converted to the following format

&quot;&gt;&lt;script&gt; alert('hacking script')&lt;/script&gt;

 

User Input Validation

Now, validated the user input using the above functions as the following script then pass it in the SQL statement.

$email    = legal_input($_REQUEST['email']);
$password = legal_input($_REQUEST['password']);

function legal_input($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}

 

Conclusion

Dear Developers, I hope that you have got the above concept of SQL Injection. Now, you are able to prevent SQL injection in PHP. So, Use the above concept and protect your web application from hackers.

If you have any queries related to web technology programming,  Ask me through the below comment box. Even you can suggest the coding topics for sharing tutorials.

I regularly share my coding knowledge with you. So, you should continue to visit my blog and share this tutorial with your friends.

Thanks for giving the time to this tutorial.