Parameterized Query using PDO

PHP PDO & MYsQL Displaying records

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

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.
iduseridpasswordnamestatus
1admin1test1AdminT
2admin2test2AdminF
3admin3test3AdminT
6admin4test4AdminT
7admin5test5AdminA
8adm#in6test6AdminA
9admin7test7AdminA
10admi'n8test8AdminA
12admin9test9AdminA
13Admin10test10AdminA
Download Zip file to test your PHP PDO script
Inserting record to table using Parameterized query
PDO References Collecting single record from table

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Post your comments , suggestion , error , requirements etc here




    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer