Update data in MySQL with PHP using Prepared Statements to Prevent SQL Injection

Guide on using Prepared Statements in PHP to update data in MySQL securely and efficiently. This PHP code helps prevent SQL Injection vulnerabilities when working with databases.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare SQL statement with placeholders
$sql = "UPDATE table_name SET column1 = ?, column2 = ? WHERE id = ?";

// Create Prepared Statement
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    die("Prepare failed: " . $conn->error);
}

// Bind parameters to the statement
$stmt->bind_param("ssi", $value1, $value2, $id);

// Set values for the parameters
$value1 = "New Value 1";
$value2 = "New Value 2";
$id = 1;

// Execute the statement
if ($stmt->execute()) {
    echo "Data updated successfully!";
} else {
    echo "Error updating data: " . $stmt->error;
}

// Close connection
$stmt->close();
$conn->close();
?>

Detailed explanation:

1. Create connection:

   • new mysqli($servername, $username, $password, $dbname): Creates a connection to the MySQL database.

2. Check connection:

   • if ($conn->connect_error): Checks if there is a connection error.

3. Prepare SQL statement with placeholders:

   • UPDATE table_name SET column1 = ?, column2 = ? WHERE id = ?: Uses question marks (?) as placeholders for parameters.

4. Create Prepared Statement:

   • $conn->prepare($sql): Creates a Prepared Statement object.

5. Bind parameters to the statement:

   • $stmt->bind_param("ssi", $value1, $value2, $id): Binds parameters to the SQL statement. The characters "ssi" indicate the data types of the parameters (s for strings, i for integers).

6. Set values for the parameters:

   • $value1 = "New Value 1";, $value2 = "New Value 2";, $id = 1;: Assigns specific values to the parameters.

7. Execute the statement:

   • if ($stmt->execute()): Executes the statement and checks the result.

8. Close connection:

   • $stmt->close();, $conn->close();: Closes the Prepared Statement and database connection.

PHP Version:

This code can run on PHP versions from 5.0 and above and requires the MySQLi extension.