Using Prepared Statement with PHP & MySQL

PHPZAG Team PHP, Tutorials

In our previous PHP tutorial, we have explained how to convert an Array to JSON in PHP. In this tutorial we will explain how to use Prepared Statement to avoid SQL injection with PHP and MySQL.

A Prepared Statement (parameterized statement) are created with query template with placeholders instead of providing actual values. The placeholders later replaced with actual values when bind parameters in statement and execute it.

The prepared statement also provide protection against SQL injection as the query created with placeholders and replace with input values later during bind param and execute. There are also no need of escaping input values as the it’s treat the values as literals and avoid the threat of SQL injection.

Also, read:

So here in this tutorial, we will explain about the SQL injection and how to use prepared statement to avoid threat of SQL injection.


How SQL Injection Happen

Here we will explain how SQL injection threat happen. Normally we create SQL queries by passing values and execute like below.

<?php

$userName = $_POST['user_name'];
$mysqli->query("SELECT * FROM tablename WHERE user ='$userName'");

?>

The problem with above queries is that if it is user input and a user could do ‘ OR ‘1’=’1. In this case, the query will always evaluate to true due to 1=1 and malicious users can create threat by accessing entire tables. Suppose if the same happen with DELETE query, they can delete everything from your tables.

if the query with user input like below then it can be very dangerous for your application.

SELECT * FROM tablename WHERE user ='' OR '1'='1'

We can use escaping to add quotes to string avoid this threat with strings but what about for numbers. 

<?php

$userName = $mysqli->real_escape_string($_POST['user_name']);
$mysqli->query("SELECT * FROM tablename WHERE user ='$userName'");

?>

So the solution to avoid SQL injection is to use prepare statement.


How To Use Prepared Statement

The prepare statement is a SQL statement template that created and sent to the database. It is created with empty values as placeholders with question mark (?) for each value. The variable values are bind to the placeholders with it’s type like integer, string etc.

There are four types allowed in bind variables:

  • i : Integer
  • d : Doubles
  • b : Blob
  • s : String

Here is the example of prepared statement with bind and execute to SELECT everything. 

<?php

$sqlQuery = "SELECT * FROM tablename WHERE id = ?";		
$stmt = $this->dbConnection->prepare($sqlQuery);
$stmt->bind_param("i", $_POST['id']);	
$stmt->execute();

$result = $stmt->get_result();			
while ($product = $result->fetch_assoc()) { 

	echo $product['id'];
	echo $product['name'];

}
?>

Here we are selecting everything from table where id equal to ?. Here the question mark is just a placeholder for where the input values will go.

Here is the example of prepared statement with bind and execute to INSERT a record. 


<?php

$sqlQuery = "INSERT INTO tablename (name, quantity, price) VALUES(?,?,?)";
$stmt = $this->dbConnection->prepare($sqlQuery);

$stmt->bind_param("sid", $_POST['name'], $_POST['quantity'], $_POST['price']);

if($stmt->execute()){
	return true;
}

?>

You can get the last inserted id by below code after executing the insert prepared statement.

echo $this->dbConnection->insert_id;

Here is the example of prepared statement with bind and execute to UPDATE a record.

<?php

$sqlQuery = "UPDATE tablename SET name = ?, quantity = ?, price = ? WHERE id = ?";
$stmt = $this->dbConnection->prepare($sqlQuery);

$stmt->bind_param("sidi", $_POST['name'], $_POST['quantity'], $_POST['price'], $_POST['id']);

if($stmt->execute()){
	return true;
}

?>

Here is the example of prepared statement with bind and execute to DELETE a record.

<?php

$sqlQuery = "DELETE FROM tablename WHERE id = ?";
$stmt = $this->dbConnection->prepare($sqlQuery);

$stmt->bind_param("i", $_POST['id']);

if($stmt->execute()){
	return true;
}

?>

So here in this tutorial, we have explained how to use prepared statement to avoid SQL Injection. You can also validate and sanitize your data before inserting into database by using function like htmlspecialchars() etc.

You may also like: