Introduction
When we want to do database operations, we write SQL statements against the database. You can either directly write the query or write it as a template while working in PHP. The prepared statement is about the query template. So, you create the template first using a placeholder for the parameter values and then send the actual values for the parameters before executing the query.
As an example, if we want to select employee details for a specific employee, we can use the SQL statement directly as below:
$sql = "SELECT * FROM EMPLOYEES WHERE EMPLID = '1001'";
Using PHP prepared statement, we will write the query using a placeholder for the emplid:
$sql = "SELECT * FROM EMPLOYEES WHERE EMPLID = ?";
A "?" is used for the placeholder and while executing we will bind the actual value with the placeholder. We will see how we can execute it later in this topic.
Why do we need to use prepared statements?
There are several advantages of using prepared statements
- If you want to execute the same statements multiple times with different parameter values, then prepared statements reduce the parsing time for the SQL statements. Because, once a query template is created, it is parsed by the server once only, but you can run the already parsed query multiple times. That means, the next time you run the same query with different parameter values, the query is already parsed, server just executes it with the new values. As a result, it saves time for the query execution. So, even if your query does not have a parameter (no WHERE condition), still you should use prepared statements.
- Similarly, while binding the parameter values, you are not sending the whole query, you are just sending the values. So, the prepared statement reduces the bandwidth to the server.
- The major advantage of using a prepared statement is that it works well against SQL injection. You can see that the parameter values are separated from the query template. As a result, it is safe from attackers since these values are sent using a different protocol.
Overall, prepared statements are efficient, faster and secure compared to direct SQL statements.
How the prepared statements work?
- You write the SQL query template using "?" in place of actual values for the parameters.
- The server parses it and stores the query template for its execution later.
- You bind the actual values with the placeholders.
- The server executes the already parsed query with the supplied values for the parameters.
How to use prepared statements in PHP?
We are now going to discuss how we can write and execute the SQL queries (INSERT, SELECT, UPDATE and DELETE) using prepared statements in PHP and MySQL.
- Prepared statement for SELECT query
- Prepared statement for INSERT query
- Prepared statement for UPDATE query
- Prepared statement for DELETE query
Prepared statement for SELECT query
A SELECT query can return a single row or multiple rows. So, we will see how we can use prepared statements for both types of SELECT queries.
Select query returning a single row
Here we are looking for a single row output from the query.
- Write the SELECT statement using the WHERE condition with a "?" for the parameter value/s.
- Prepare the statement - use
prepare()method - Bind the parameter/s with the actual value/s - use
bind_param()method - Execute the statement - use
execute()method - Get the result - use
get_result()method - Fetch the row as an associative array - use
fetch_assoc()method - Display the output from the array
Let us see the below SELECT statement that returns a single row:
// Prepare a select statement
$sql = "SELECT * FROM users WHERE user_id = ?";
if($stmt = $conn->prepare($sql)){
$stmt->bind_param("s", $uid); // bind parameter
$uid = 'joe';
if ($stmt->execute()){ // execute the query
$result = $stmt->get_result(); // get the result output
if ($result->num_rows == 1){ // check the number of rows
$row = $result->fetch_assoc(); // fetch the row
$name = $row['user_name'];
$email = $row['email'];
$phone = $row['phone'];
echo "name = ".$name." email = ".$email." phone = ".$phone;
}
}
else
echo "Some error occurred";
$stmt->close();
}
prepare()method parses the query and stores the parsed statement in the server as a query template.bind_param()binds the placeholder with the actual values. The first parameter denotes the type of the variable, i - integer, s - string, d - double, b - blob. In our case, it is a string, so we used "s". The second parameter is the variable that holds the value. If there are multiple parameters, we will use them one after the other in the same sequence as they appear in the query. For example,$stmt->bind_param("ss", $uid, $name);execute()method executes the already parsed statement with the values supplied.get_result()gets the data from the database to PHP as a resultset.fetch_assoc()fetches the data as an associative array from the resultset.
Select query returning multiple rows
In this case, prepare and execution steps will be the same, but since there are multiple rows, we have to use a loop to fetch the data. See the below code:
// Prepared statement for multiple rows
$sql = "SELECT * FROM employees where salary > ?";
if($stmt = $conn->prepare($sql)){
$stmt->bind_param("i", $sal);
$sal = 1000;
if ($stmt->execute()){
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) { // loop
$name = $row['name'];
$email = $row['email'];
$sal = $row['salary'];
echo "name = ".$name." email = ".$email." salary = ".$sal;
}
}
else
echo "Some error occurred";
$stmt->close();
}
Prepare statement for INSERT query
Here, we write an INSERT statement with placeholders for the values and the same way we prepare, bind the parameters and execute it. Since we are not fetching any data, we will just check the successful execution of the statement. See the below code:
// Prepared statement for INSERT
$sql = "INSERT INTO users (user_id, user_name, email, phone) VALUES (?, ?, ?, ?)";
if ($stmt = $conn->prepare($sql)) {
$stmt->bind_param("ssss", $uid, $name, $email, $phone);
$uid = "jdoe";
$price = "John Doe";
$email = "jdoe@example.com";
$phone = "9999999999";
if ($stmt->execute())
echo "User created successfully";
else
echo "Some error occurred";
$stmt->close();
}
Prepared statement for UPDATE query
It works the same way as INSERT. Write the UPDATE query, prepare and bind parameters, and execute. Check for successful execution and give the message accordingly. See the below code:
// Prepared Statement for Update
$sql = "UPDATE users SET phone = ? WHERE user_id = ?";
if ($stmt= $conn->prepare($sql)){
$stmt->bind_param("ss", $phone, $user_id);
$phone = "1111111111";
$user_id = "jdoe";
if ($stmt->execute())
echo "User updated successfully";
else
echo "Some error occurred";
$stmt->close();
}
Prepared statement for DELETE query
Use the same steps as UPDATE. See the below code:
// Prepare delete statement
$sql = "delete from users WHERE user_id = ?";
if ($stmt= $conn->prepare($sql)){
$stmt->bind_param("s", $u_id);
$u_id = "jdoe";
if ($stmt->execute())
echo "User deleted successfully";
else
echo "Oops:some error occurred";
$stmt->close();
}
Conclusion
Prepared statements are always better than direct SQL statements. You are sending the query once to the server and it is parsed and stored. You can execute the same query with different values for the parameters without sending the whole query again to the server. It improves the performance of query execution as well as protects the application from SQL injection. You do not need to escape input using mysqli->real_escape_string(). But remember that any input from external sources or the form must be validated and sanitized before using them in the SQL query.