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>";

The attacker manipulates the query with ' OR '1'='1, which always evaluates to true

$name = $_GET['name']; // User input from URL or form
$query = "SELECT * FROM users WHERE name = '$name'";

// If the input is: ' OR '1'='1
// The query becomes: SELECT * FROM users WHERE name = '' OR '1'='1'
// This returns all records, bypassing authentication

Parameterized queries and prepared statements

Parameterized queries and prepared statements are essential for preventing SQL injection attacks. By separating SQL logic from data inputs, they ensure that user-provided inputs are treated as data, not executable code. This prevents attackers from injecting malicious SQL statements.

In PHP, using bindParam() or bindValue() with placeholders for query parameters safely inserts values without modifying the structure of the SQL query, making it a powerful defense against SQL injection vulnerabilities.

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

Parameterized queries and prepared statements

Example

Example

Subscribe