parameterized queries using prepare() and bindParam() in PDO connection to database table in PHP

While executing any query, we can pass the parameters separately to prevent unwanted data or malicious code to be part of our query. This is required to prevent injection attack through Query. In all real life situation we collect data from different sources like forms, query string, sessions , cookies etc and they became part of the Query string we use. In such situations it is not advisable to use such data directly in our queries.

Parameterized query in PHP PDO using bindParam() in sending data separately to MySQL database

Here is one example where we have directly used data in our query ( through one variable $class ) which is not a good practice.

$sql="SELECT * FROM student WHERE class='$class'";

The value stored in the variable $class can be injected with some codes which can be venerable to attack. We will use parameterized queries to pass these data separately. Database will treat them as data and not treat them as part of the query.
Example of SQL Injection of code through Parameter

Using String as Parameter

We will use one variable $class and pass it as parameter to our query.

require "config.php";// database connection

$class='Three'; // Data can be changed.
$query="select * from student where class=:class";
$step = $dbo->prepare($query);
$step->bindParam(':class', $class,PDO::PARAM_STR,10);
$step->execute();
$step = $step->fetchAll();

echo "<table><tr><th>ID</th><th>Name</th>
  <th>Class</th><th>Mark</th></tr>";

  foreach($step as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td>
  <td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";

Using Multiple String parameters

We will use LIKE Query along with the above query.

$class='Three'; // Data can be changed.
$name='%Max%';
$query="select * from student where class=:class AND name LIKE :name";
$step = $dbo->prepare($query);
$step->bindParam(':class', $class,PDO::PARAM_STR,10);
$step->bindParam(':name', $name,PDO::PARAM_STR,10);
$step->execute();
$step = $step->fetchAll();

echo "<table><tr><th>ID</th><th>Name</th>
  <th>Class</th><th>Mark</th></tr>";

  foreach($step as $row){
echo "<tr><td>$row[id]</td><td>$row[name]</td>
  <td>$row[class]</td><td>$row[mark]</td></tr>";
}
echo "</table>";

Output

ID	Name	Class	Mark
2	Max Ruin Three	85

Using integer as parameter

We will use one variable id and pass the data to query to display details of that record only.

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

if($count->execute()){
$row = $count->fetch(PDO::FETCH_OBJ);
//print_r($row);
echo "<hr><br>id = $row->id";
echo "<br> name =$row->name<br>";
echo "<br> class =$row->class<br>";
}

Output

id = 3
name =Arnold

class =Three

If you are using numbers ( integers ) variables by taking from query string then you have to change it to integer first before using with bindPram
$start=(int)$start; // Now $start is an integer variable

Insert Query with Parameters

Data as entered by users in a form is to be stored in a database table. Here we have to use parameterized query to add records to table.
Check how Parametrized query used while inserting data to table

SQL Injection

While receiving data (from unknown sources ) and using the same inside query, we have to take care of injection attacks.
Example of SQL code Injection through Parameter

Example using pdo_admin table

Here is an example with added checks( by using parameterized query ) to above code.

<?Php
require "config.php";// database connection
$name='Admin';
$query="select * from pdo_admin where name=:name";
$step = $dbo->prepare($query);
$step->bindParam(':name', $name,PDO::PARAM_STR,50);
$step->execute();
$step = $step->fetchAll();

echo "<table>";

echo "<tr><th>id</th><th>userid</th><th>password</th><th>name</th><th>status</th></tr>";

foreach ($step as $row) {
echo "<tr ><td>$row[id]</td><td>$row[userid]</td><td>$row[password]</td><td>$row[name]</td><td>$row[status]</td></tr>";
}

echo "</table>";
?>

Output with limited records are displayed here.

id	userid	password	name	status
1	admin1	test1	Admin	T
2	admin2	test2	Admin	F
3	admin3	test3	Admin	T
6	admin4	test4	Admin	T
7	admin5	test5	Admin	A
8	adm#in6	test6	Admin	A
9	admin7	test7	Admin	A
10	admi'n8	test8	Admin	A
12	admin9	test9	Admin	A
13	Admin10	test10	Admin	A

Download Zip file to test your PHP PDO script

Inserting record to table using Parameterized query
PDO References Collecting single record from table

Subscribe to our YouTube Channel here

Parameterized Query using PDO