PHP MySQLi Prepared Statements for Secure SQL Queries
Updated on
...
22nd August 2023
MySQLi-prepared statement executes queries according to user-supplied input data reducing the risk of SQL injection attacks. it separates the SQL query from user input data for improving security.
Table of contents:
- Steps to create prepared statements
- Multiple methods to use prepared statement
- Multiple examples of MySQLi prepared statement
- Why do we use prepared statement
- What is SQL Injection
Steps to create prepared statements:
- Prepare: First, you have to create a prepared statement by calling the MySQLi connection object's prepare() method. Then you have to write the SQL query with placeholders for the input data. Make sure here you can not use named parameters like PDO
- Bind Parameters: The bind_param() method is used to bind the user input data to the placeholders. This method defines the data types of the parameters and associates them with the query placeholders.
- Execute: After binding the parameters, you have to use the execute() method to execute the prepared statement. The statement is processed by the MySQL server using the input data provided.
- Receive Results: If the prepared statement is a SELECT query, you can receive the results using methods such as fetch_assoc(), fetch_row(), fetch_object(), fetch_all(MYSQLI_ASSOC), fetch_all(MYSQLI_NUM) or bind_result() and fetch().
- Close: When you have finished the prepared statement, use the close() method to close it.
The four variable types allowed:
- i - Integer
- d - Double
- s - String
- b - Blob
Let's see one example:
<?php
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'mysqli_prepared';
// Create a MySQLi connection
$conn = new mysqli($server, $user, $pass, $db);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare the statement
$stmt = $conn->prepare("INSERT INTO students (name, email) VALUES (?, ?)");
// Bind parameters
$name = "Abdul tech hub";
$email = "[email protected]";
$stmt->bind_param("ss", $name, $email);
// Execute the statement
$stmt->execute();
// Close the statement and connection
$stmt->close();
$conn->close();
SELECT single data using bind_result()
$select_query = "SELECT * FROM students WHERE id = ?";
$stmt = $dbconn->prepare($select_query);
$id = 5;
$stmt->bind_param('i', $id);
$stmt->execute();
if ($stmt->execute()) {
$stmt->bind_result($id, $name, $email, $phone, $gender);
$stmt->fetch();
echo 'ID: ' . $id . "<br>";
echo 'Name: ' . $name . "<br>";
echo 'Email: ' . $email . "<br>";
echo 'Phone: ' . $phone . "<br>";
echo 'Gender: ' . $gender . "<br>";
echo "<br>";
}
Why do we use prepared statement?
PHP MySQLi-prepared statements provide various advantages for developing safe and efficient database interactions:
- SQL Injection Prevention: Because prepared statements separate SQL code from user data, they automatically handle escaping and sanitizing user input, preventing SQL injection attacks.
- Security: Prepared statements considerably limit the danger of fraudulent users manipulating queries to gain unauthorised access to your database because they separate SQL code from user input.
- Performance Optimisation: The database server precompiles and optimises prepared statements, allowing for faster execution of subsequent queries with comparable structures. This can lead to greater performance, particularly for repeated searches.
- Reusability: You can run the same prepared statement with various data values numerous times without having to recreate and recompile the query each time.
Prepared statements are useful because they handle
Multiple methods to use prepared statements:
Before moving forward let's see the various mySQLi-prepared statements method.
- new mysqli($server, $user, $pass, $db): Constructor for creating a new MySQLi connection object oriented way.
- $dbconn->connect_errno: Property to check if there was an error during the connection target.
- $dbconn->connect_error: Property that contains the error message in case of a connection error.
- $dbconn->prepare($sql): Method to prepare an SQL statement for execution.
- $stmt->execute(): Method to run the prepared statement.
- $stmt->get_result(): Method to get a result set from a prepared statement.
- $result->fetch_assoc(): Method to execute multiple queries in a single call.
- $stmt->bind_param($types, $param1, $param2, ...): Method to bind variables to a prepared statement as parameters.
- $stmt->bind_result($var1, $var2, ...): Method to bind variables to columns in the result set.
- $stmt->num_rows: Property that returns the number of rows in the result set.
- $stmt->affected_rows: Property that returns the number of affected rows by an UPDATE, DELETE, or INSERT query.
- $stmt->fetch(): Method to fetch the next row from a result set as a numeric or associative array.
- $stmt->fetch_array(): Method to fetch the next row from a result set with numeric and associative array.
- $stmt->store_result(): Method to store the result set in memory for later processing.
- $stmt->free_result(): Method to free the memory associated with a result set.
- $stmt->data_seek($offset): Method to move the result pointer to a specified row in the result set.
- $stmt->insert_id: Property that returns the ID generated by an AUTO_INCREMENT column in the last query.
- $stmt->fetch_assoc(): Method to fetch the next row from a result set as an associative array.
- $stmt->fetch_object(): Method to fetch the next row from a result set as an object.
- $stmt->fetch_row(): Method to fetch the next row from a result set as a numeric array.
- $stmt->field_count: Method to get a result set from a prepared statement for further processing.
- $stmt->reset(): Method to reset a prepared statement and its result set for re-execution.
- $stmt->sqlstate: Method to close a prepared statement.
- $dbconn->multi_query($sql): Method to fetch the next row from the result set as an associative array.
- $stmt->error: Property that contains the error message associated with the last operation on the statement.
- $stmt->close(): Method to close a prepared statement.
- $dbconn->close(): Method to close the database connection.
Multiple examples of MySQLi-prepared statements:
Simple Select query:
<?php
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'mysqli_prepared';
$dbconn = new mysqli($server, $user, $pass, $db);
// Check connection
if ($dbconn->connect_errno) {
echo "Failed to connect to MySQL: " . $dbconn->connect_error;
exit();
}
$select_query = "SELECT * FROM students";
$stmt = $dbconn->prepare($select_query);
$stmt->execute();
if ($stmt->execute()) {
$result = $stmt->get_result();
// $row = $result->fetch_assoc();
// Using while loop to fetch results
while ($row = $result->fetch_assoc()) {
echo 'Name: ' . $row['name'] . "<br>";
echo 'Email: ' . $row['email'] . "<br>";
echo "<br>";
}
$stmt->close();
} else {
echo "Error executing query: " . $stmt->error;
}
$dbconn->close();
above code output:
SELECT query with where clause and bind_param:
$select_query = "SELECT * FROM students where name= ? AND email= ?";
$stmt = $dbconn->prepare($select_query);
$name = 'abdul';
$email = '[email protected]';
$stmt->bind_param('ss', $name, $email);
$stmt->execute();
UPDATE:
$select_query = "UPDATE students SET email = ? where name= ? ";
$stmt = $dbconn->prepare($select_query);
$name = 'abdul';
$email = '[email protected]';
$stmt->bind_param('ss', $email, $name);
$stmt->execute();
UPDATE multiple field:
$select_query = "UPDATE students SET name =?, email = ? where id= ? ";
$stmt = $dbconn->prepare($select_query);
$id = 3;
$name = 'abdul2';
$email = '[email protected]';
$stmt->bind_param('ssi', $name, $email, $id);
$stmt->execute();
INSERT a record:
$select_query = "INSERT INTO students (name, email, phone, gender) VALUES(?,?,?,?)";
$stmt = $dbconn->prepare($select_query);
$name = 'abdul';
$email = '[email protected]';
$phone = '8585858585';
$gender = 'male';
$stmt->bind_param('ssss', $name, $email, $phone, $gender);
$stmt->execute();
INSERT Multiple record:
$select_query = "INSERT INTO students (name, email, phone, gender) VALUES(?,?,?,?)";
$stmt = $dbconn->prepare($select_query);
$name = 'abdul';
$email = '[email protected]';
$phone = '8585858585';
$gender = 'male';
$stmt->bind_param('ssss', $name, $email, $phone, $gender);
$stmt->execute();
$name = 'renu';
$email = '[email protected]';
$phone = '8585858585';
$gender = 'female';
$stmt->bind_param('ssss', $name, $email, $phone, $gender);
$stmt->execute();
$name = 'Sonu';
$email = '[email protected]';
$phone = '8585858585';
$gender = 'male';
$stmt->bind_param('ssss', $name, $email, $phone, $gender);
$stmt->execute();
DELETE:
$select_query = "DELETE FROM students WHERE id = ?";
$stmt = $dbconn->prepare($select_query);
$id = '2';
$stmt->bind_param('i', $id);
$stmt->execute();
if ($stmt->execute()) {
echo "Data deleted";
}
SELECT data using bind_result() for multiple result
$select_query = "SELECT * FROM students WHERE name = ?";
$stmt = $dbconn->prepare($select_query);
$name = 'abdul';
$stmt->bind_param('s', $name);
$stmt->execute();
if ($stmt->execute()) {
$stmt->bind_result($id, $name, $email, $phone, $gender);
// Using while loop to fetch results
while ($stmt->fetch()) {
echo 'ID: ' . $id . "<br>";
echo 'Name: ' . $name . "<br>";
echo 'Email: ' . $email . "<br>";
echo 'Phone: ' . $phone . "<br>";
echo 'Gender: ' . $gender . "<br>";
echo "<br>";
}
}
