Skip to Content
PHPCODE
mysql prepared statements in php useful for
php code / August 30, 2021

In this instructional exercise we will disclose how to utilize Prepared Statement to stay away from SQL infusion with PHP and MySQL.

A Prepared Statement (defined proclamation) are made with question layout with placeholders as opposed to giving real qualities. The placeholders later supplanted with genuine qualities when tie boundaries in proclamation and execute it.

The pre-arranged proclamation additionally give security against SQL infusion as the question made with placeholders and supplant with input esteems later during tie param and execute. There are additionally no need of getting away from input esteems as the it’s treat the qualities as literals and keep away from the danger of SQL infusion.

How SQL Injection Happen

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

The issue with above inquiries is that in case it is client input and a client could do ‘ OR ‘1’=’1. For this situation, the question will consistently assess to valid because of 1=1 and malevolent clients can make danger by getting to whole tables. Assume if the equivalent occur with DELETE question, they can erase everything from your tables.

on the off chance that the inquiry with client input like beneath, it tends to be exceptionally hazardous for your application.

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

Here’s 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 choosing everything from table where id equivalent to ?. Here the question mark is only a placeholder for where the info esteems will go.

Here is the case of arranged explanation with tie 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;
}
?>
echo $this->dbConnection->insert_id;
<?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’s 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;
}
?>
PHPCODE © 2024