How to Securely Update a MySQL Database Using the MySQLi Extension in PHP?

Perfect MySQL Extension Code Sample

The goal of this question is to provide a concise and secure PHP code sample using the MySQL extension. The code should avoid common errors associated with the mysql_* functions, including:

• SQL injection
• Broken error reporting
• Cross-site scripting (XSS) injections

Code Sample:

<?php

header('Content-type: text/html; charset=utf-8');
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);

$config = [
    'host' => '127.0.0.1',
    'user' => 'my_user',
    'pass' => 'my_pass',
    'db' => 'my_database'
];

$connection = @mysql_connect($config['host'], $config['user'], $config['pass']);

if (!$connection) {
    trigger_error('Unable to connect to database: ' . mysql_error(), E_USER_ERROR);
}

if (!mysql_select_db($config['db'])) {
    trigger_error('Unable to select db: ' . mysql_error(), E_USER_ERROR);
}

if (!mysql_set_charset('utf8')) {
    trigger_error('Unable to set charset for db connection: ' . mysql_error(), E_USER_ERROR);
}

$result = mysql_query(
    'UPDATE tablename SET name = "' . mysql_real_escape_string($_POST['name']) . '" WHERE id = "' . mysql_real_escape_string($_POST['id']) . '"'
);

if ($result) {
    echo htmlentities($_POST['name'], ENT_COMPAT, 'utf-8') . ' updated.';
} else {
    trigger_error('Unable to update db: ' . mysql_error(), E_USER_ERROR);
}
?>

Explanation:

• This code connects to the MySQL database using mysql_connect() and specifies the database to use with mysql_select_db().
• The mysql_set_charset() function is used to set the character set for the connection to UTF-8.
• The SQL update query is constructed using placeholders and the mysql_real_escape_string() function to prevent SQL injection.
• The query result is checked, and the message "$name updated." is displayed if the update was successful. Otherwise, an error is triggered.
• Error reporting is enabled to allow for better debugging during development. In production mode, this setting can be disabled to suppress PHP error messages to the end user.

Note that this code sample is provided as a reference and should not be considered a complete solution for all PHP applications. It is recommended to use PDO extension for modern MySQL applications, as it provides enhanced security and performance.

The above is the detailed content of How to Securely Update a MySQL Database Using the MySQLi Extension in PHP?. For more information, please follow other related articles on the PHP Chinese website!