Preventing SQL injection attack by using parameterized queries in PHP

Let us understand how code can be injected into SQL code as data through variable.
We are reading id value from string and getting the record details from the table. Our student table has one unique id for each student record. Let us first understand how data from query string is used to develop SQL and applied against any database table.
Data in Query string

This is an URL, we can pass the value of id as part of the query string ( URL ).
On the targeted page the id value will be available like this.

$id=$_GET['id']// reading from URL the id value i.e 2489

We can build a query using this variable like this.

$sql="SELECT * FROM student where id=$id";

We can send the query string like this

<a href='http://www.sitename.com?id=10; DROP TABLE student--&user=tom'>Link</a>

While receiving will get the data ( with variable ) like this.

$id='10; DROP TABLE student--';

The second part of the variable data saying DROP TABLE student-- will be executed as one more query and delete the student table.
The following code will delete the student table.

require "config.php"; // Database Connection
$id=$_GET['id'];
$id='10; DROP TABLE student--';// Injected data for id
$sql="SELECT * FROM student where id=$id";

$step=$dbo->query($sql);
$row = $step->fetch(PDO::FETCH_OBJ);
  echo "<hr><br>Admin = $row->id";
  echo "<br> name =$row->name<br>";
  echo "<hr>";

However by using Parameterized query we can prevent the SQL injection attack. This query won’t able to delete the student table.

$sql="SELECT * FROM student WHERE id=:id";
$count=$dbo->prepare($sql);
$count->bindParam(":id",$id,PDO::PARAM_INT,3);

if($count->execute()){
echo " Success <br>";
$row = $count->fetch(PDO::FETCH_OBJ);
print_r($row);
echo "<hr><br>Admin = $row->userid";
echo "<br> pw =$row->password<br>";

Example

SELECT * FROM student WHERE Id = 105 OR 1=1;

Here we will expose all the columns with all the rows as 1=1 is always True.

Example

We are storing username and password in a table and asked the user to enter userid and password. After receiving these two details our query will be like this, this will expose all the rows with userid and password of the table.

<?php
$userid='"" or ""=""';
$password='"" or ""=""';
echo "SELECT * FROM my_table  WHERE userid=$userid AND password=$password";
//Output//
//SELECT * FROM my_table WHERE userid="" or ""="" AND password="" or ""=""//
?>

Download Zip file to test your PHP PDO script

PDO References Collecting single record from table

Subscribe to our YouTube Channel here

Example of SQL injection attack

Example

Example

Subscribe