PHP SQLite3 bindParam, binds a parameter to a statment variable

A SQLite3 parameterized query is a query that uses placeholders to represent values that will be provided at runtime. This helps to prevent SQL injection attacks, which occur when an attacker inserts malicious code into a query.

$sql=$my_conn->prepare("INSERT INTO student_join 
    (id,name,mark,photo,admin) 
    values(:id,:name,:mark,:photo,:admin)");

Parameterized queries work by separating the SQL statement from the data that is being used in the query. The data is then bound to the placeholders in the query before it is executed. This prevents the attacker from being able to insert malicious code into the query.

bindParam & bindValue

While binding we can specify the data type of the parameter to bind. Here are a list of data types SQLite3 supports. See the examples below to know how they are used.

SQLITE3_INTEGER, SQLITE3_FLOAT, SQLITE3_TEXT, SQLITE3_BLOB and SQLITE3_NULL

bindParam() : Binds a parameter to a statement variable. It is used if we don't have our variable assigned yet.

$sql->bindParam(':id',$id,SQLITE3_INTEGER); // Parameter is used
$id=$_GET['id'];// assigned after bindParam()

bindValue() have to be used with existing value only.

$sql->bindValue(':id',5,SQLITE3_INTEGER); // Value is used

We can create a single prepared statement with Parameter binding and insert multiple rows of data with different values.

$sql=$my_conn->prepare("INSERT INTO student_join (id,name,mark,photo,admin) 
        values(:id,:name,:mark,:photo,:admin)");
$sql->bindParam(':id',$id,SQLITE3_INTEGER);
$sql->bindParam(':name',$name,SQLITE3_TEXT);
$sql->bindParam(':mark',$mark,SQLITE3_FLOAT);
$sql->bindParam(':photo',$photo,SQLITE3_BLOB);
$sql->bindParam(':admin',$admin,SQLITE3_NULL);
// insert data //////
$id=1;
$name='plus2net'; 
$mark=50.45;               
$photo= file_get_contents('1.png'); // reading binary data
$admin=Null; 

$sql->execute();

/// Next set of data ////
$id=2;
$name='Alex '; 
$mark=60.45;               
$photo= file_get_contents('2.png'); // reading binary data
$admin=Null; 

$sql->execute();

echo "<br>Last Inserted ID : ".$my_conn->lastInsertRowID();

named Parameters and positional parameters

Parameters defined with : and a name are called named parameters.
Parameters defined with ? are called positional parameters.
We can't mix the named and positional parameters in our prepared statement.

Example of named Parameters.

$sql=$my_conn->prepare("INSERT INTO student_join (id,name,mark,photo,admin) 
        values(:id,:name,:mark,:photo,:admin)");
$sql->bindParam(':id',$id,SQLITE3_INTEGER);
$sql->bindParam(':name',$name,SQLITE3_TEXT);
$sql->bindParam(':mark',$mark,SQLITE3_FLOAT);
$sql->bindParam(':photo',$photo,SQLITE3_BLOB);
$sql->bindParam(':admin',$admin,SQLITE3_NULL);

Example of Positional Parameters.

$sql=$my_conn->prepare("INSERT INTO student_join (id,name,mark,photo,admin) 
        values(?,?,?,?,?)");
$sql->bindParam(1,$id,SQLITE3_INTEGER);
$sql->bindParam(2,$name,SQLITE3_TEXT);
$sql->bindParam(3,$mark,SQLITE3_FLOAT);
$sql->bindParam(4,$photo,SQLITE3_BLOB);
$sql->bindParam(5,$admin,SQLITE3_NULL);

Error Handling while using Parameterized query

Our execute() statement returns False on failure and return SQLite3Result object on successful execution.
We are using one if-else condition check to display error message and error code if execute() returns False.

if($sql->execute()){ // If successful 

echo "<br><br>Number of rows Inserted  : ".$my_conn->changes();
echo "<br>Last Inserted ID : ".$my_conn->lastInsertRowID();
}
else{ // show error message. 
echo "Error Message : ".$my_conn->lastErrorMsg();
 echo "<BR>Error Code : ".$my_conn->lastErrorCode();
}

Delete Multiple rows of Data using Parameterized query

Here mark value can be taken from user entered data or from the get method ( from URL ).

$my_conn = new SQLite3('my_db.db');// Connect to Database
$sql="DELETE FROM student_join WHERE mark < :mark"; // Query
$sql=$my_conn->prepare($sql);
$sql->bindParam(':mark',$mark,SQLITE3_INTEGER);
//$mark=$_GET['mark'];// Collect mark from query string
$mark=80; // set the value 
if($sql->execute()){
echo "<br><br>Number of rows deleted  : ".$my_conn->changes();
}

Update Multiple rows of Data using Parameterized query

Here we are taking different class and different increment value for mark as user input.

$my_conn = new SQLite3('my_db.db');// Connect to Database
$sql="UPDATE student_join set mark = mark + :mark WHERE name=:name";
$sql=$my_conn->prepare($sql);
$sql->bindParam(':mark',$mark,SQLITE3_FLOAT);
$sql->bindParam(':name',$name,SQLITE3_TEXT);
//$mark=$_GET['mark'];// Collect mark from query string
$mark=5.5;
$name='plus2net';
$id=3;
if($sql->execute()){
echo "<br><br>Number of rows updated  : ".$my_conn->changes();
}else{
echo "Error Message : ".$my_conn->lastErrorMsg();
 echo "<BR>Error Code : ".$my_conn->lastErrorCode();	
}

In above code only one record will be updated as we have one matching name column only. Below code will update multiple rows as we have used id less than or equal to 3.

$sql="UPDATE student_join set mark = mark + :mark WHERE id<=:id";
$sql=$my_conn->prepare($sql);
$sql->bindParam(':mark',$mark,SQLITE3_FLOAT);
$sql->bindParam(':id',$id,SQLITE3_INTEGER);
//$mark=$_GET['mark'];// Collect mark from query string
$mark=5.5;
$id=3;

Output

Number of rows updated : 3

Parameterized SELECT query with LEFT JOIN

Here we are displaying matching records by LEFT JOIN of two tables.
Here student table class value is binded to the parameter.

$my_conn = new SQLite3('my_db.db');// Connect to Database
$sql="SELECT * FROM `student` a 
 LEFT JOIN student_baseball b on a.id=b.b_id 
 WHERE a.class=:class and b.b_id IS NOT NULL";

$sql=$my_conn->prepare($sql);
$sql->bindParam(':class',$class,SQLITE3_TEXT);
$class='Four';
$result=$sql->execute();

echo "<table>";
while ($row=$result->fetchArray()) {
 echo "<tr ><td>$row[id]</td><td>$row[name]</td>
	<td>$row[mark]</td><td>$row[b_id]</td></tr>";
}echo "</table>";

Full code to create table and insert records in different tables

<?php
$my_conn = new SQLite3('my_db.db');// Connect to Database
$sql="CREATE TABLE IF NOT EXISTS
				student_join(id integer primary key, 
                      name text, 
                      mark float, 
                      photo blob,
					  admin text 
                      )";
$result=$my_conn->exec($sql);
if($result !=False){
	echo "<br> Table created ";
}else{
	echo "<BR><p style='color:tomato;'>Error Message : ".$my_conn->lastErrorMsg()."</p>";
}
 
 

$sql=$my_conn->prepare("INSERT INTO student_join (id,name,mark,photo,admin) 
        values(:id,:name,:mark,:photo,:admin)");
$sql->bindParam(':id',$id,SQLITE3_INTEGER);
$sql->bindParam(':name',$name,SQLITE3_TEXT);
$sql->bindParam(':mark',$mark,SQLITE3_FLOAT);
$sql->bindParam(':photo',$photo,SQLITE3_BLOB);
$sql->bindParam(':admin',$admin,SQLITE3_NULL);


////////////Collect  data/////////////
$id=1;
$name='plus2net'; 
$mark=50.45;               
$photo= file_get_contents('1.png'); // reading binary data
$admin=Null; 

///////// End of data collection ///

if($sql->execute()){

echo "<br><br>Number of rows Inserted  : ".$my_conn->changes();
echo "<br>Last Inserted ID : ".$my_conn->lastInsertRowID();
}
else{
echo "Error Message : ".$my_conn->lastErrorMsg();
 echo "<BR>Error Code : ".$my_conn->lastErrorCode();
}
///////////////
////////////Collect  data/////////////
$id=2;
$name='Alex'; 
$mark=60.45;               
$photo= file_get_contents('2.png'); // reading binary data
$admin=Null; 

///////// End of data collection ///

if($sql->execute()){

echo "<br><br>Number of rows Inserted  : ".$my_conn->changes();
echo "<br>Last Inserted ID : ".$my_conn->lastInsertRowID();
}
else{
echo "Error Message : ".$my_conn->lastErrorMsg();
 echo "<BR>Error Code : ".$my_conn->lastErrorCode();
}
?>

Sample script using PHP , SQLite3 connection and SQLite database : plus2net_sqlite3_v1

These scripts are developed by using PHP SQLite3 connection to learn different queries and how to execute to mange SQLite database.
Download sample scripts using SQLite3 with instructions on how to use.

Questions

What is a parameterized query in SQLite3?
Why should I use parameterized queries in SQLite3?
How do I create a parameterized query in SQLite3?
How do I bind values to placeholders in a parameterized query?
What are the benefits of using parameterized queries in SQLite3?
What are the drawbacks of using parameterized queries in SQLite3?
When should I use a parameterized query in SQLite3?
When should I avoid using a parameterized query in SQLite3?
Are parameterized queries in SQLite3 secure against SQL injection attacks?
How can I use parameterized queries in SQLite3 to prevent SQL injection attacks?
How can I use parameterized queries in SQLite3 to improve the performance of my queries?
Are there any limitations to using parameterized queries in SQLite3?
How can I use parameterized queries in SQLite3 with user input?
How can I use parameterized queries to insert multiple rows of data into a SQLite3 table?
How can I use parameterized queries to update multiple rows of data in a SQLite3 table?
How can I use parameterized queries to delete multiple rows of data from a SQLite3 table?
How can I use parameterized queries to perform complex queries in SQLite3?
How can I use parameterized queries in SQLite3 to join multiple tables?
How can I use parameterized queries in SQLite3 to group data?
How can I use parameterized queries in SQLite3 to aggregate data?

SQLite SQLite3 & PHP

Subscribe to our YouTube Channel here

bindParam(): Binds a parameter to statement