How To Prevent SQL Injection In PHP

These days attacking on the websites is normal issue for web server administrator. Attacker uses lots of methods to exploit your web server such as SQL Injections,File Injections, and Exploits etc.. In SQL Injection attacker execute malicious SQL Statements it is also called malicious payload. Relational Database Management System (RDBMs) , we can also say database server which manage and control web applications SQL Injection vulnerability affect any websites which is SQL-Base database

In this tutorial I will describe how you can prevent SQL Injection in PHP using PHP-MySQLi and PHP-PDO drivers.

First have a look on simple SQL Injection Example.

SQL Injection

For Example, suppose you have a school websites and you have provided a web interface to school students to view their roll number and marks.

For this activity your school websites uses URL like:

http://school-example.com/student_marks.php?roll_nu=201 to fetch result from the database.

student_marks.php file code example:

$roll_NU = $_GET['roll_nu'];
$query = "SELECT rollNumber, marks FROM students WHERE roll_NU = $roll_nu";

Above you can see student rollnumber has been defined in MySQL query string as roll_nu.

Student’s roll number 201 and it is given through query string like above URL. The result of the PHP script query will be like below.

$query = "SELECT rollNumber, marks FROM students WHERE roll_NU = 201";

mysql>
mysql> SELECT rollNumber, marks FROM studentss WHERE roll_NU = 102;
+--------------------+----------------------------------------------------+
| rollNumber         | marks                                              |
+--------------------+----------------------------------------------------+
| 201                | 500                                                |
+--------------------+----------------------------------------------------+
1 rows in set (0.00 sec)

mysql>

You can see the rollNumber and marks details are fetched for for roll number 201 and displayed to students.

Another Scenario

Let’s assume another scenario on over smart student has passed roll number 0 OR 1=1 in query string. What result they will fond? The result of the PHP script query will be like below and executed on the database.

$query = "SELECT rollNumber, marks FROM students WHERE roll_NU = 0 OR 1=1";

mysql>
mysql> SELECT rollNumber, marks FROM studentss WHERE roll_NU = 0 OR 1=1;
+--------------------+----------------------------------------------------+
| rollNumber         | marks                                              |
+--------------------+----------------------------------------------------+
| 201                | 500                                                |
+--------------------+----------------------------------------------------+
| 206                | 468                                                |
+--------------------+----------------------------------------------------+
| 204                | 390                                                |
+--------------------+----------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Now see query created by PHP script and result returned by the database. Query returned all roll numbers with student marks.

This is called SQL Injection. This is the simple example, and there are lots of method doing like this.

Now I am going to describe, how you can prevent SQL injection using PHP MySQLi and PHP PDO extension/driver.

Using PHP MySQL Driver

You can use the php-mysqli driver to avoid these type of injections. Write php script like below to prevent SQL injection.

$roll_NU = $_GET['roll_nu'];
 
if ($stmt = $mysqli->prepare('SELECT rollNumber, marks FROM students WHERE roll_nu = ?')) {
  
    $stmt->bind_param("s", $roll_nu);
 
    $stmt->execute();
 
 $result = $stmt->get_result();
 
 while ($row = $result->fetch_assoc()) {
 // do something here
 }
 
    $stmt->close(); 
}

Now follow the second method to avoid SQL injections.

Using PHP-PDO Driver

You can also use PHP-PDO driver prepare statements to prevent SQL injections. Write php script like below to prevent SQL injection.

$roll_NU = $_GET['roll_nu'];
 
if ($stmt = $pdo->prepare('SELECT rollNumber, marks FROM students WHERE roll_nu = :roll_NU')) {
  
    $stmt->execute(array('name' => $name));
 
 foreach ($stmt as $row) {
 // do something here
 }
 
    $stmt->close(); 
}

I hope this article will help you to prevent SQL injection in PHP. If you have any queries and problem please comment in comment section.

Thanks:)