How to Securely Use the MySQL Extension in PHP to Prevent SQL Injection?

How to Craft Secure Database Queries with MySQL Extension

The MySQL extension offers a rudimentary yet functional approach to database interactions in PHP. While not the most advocated option, its proper usage can mitigate potential security and usability pitfalls.

Essential Considerations for Secure Queries

To safeguard against common vulnerabilities, it's crucial to adhere to best practices:

• Prevent SQL injection by escaping user input.
• Avoid dynamic SQL execution, such as LIMIT clauses or table names.
• Implement robust error reporting for debugging and production usage.
• Secure against cross-site scripting (XSS) by sanitizing output.

Sample Code for Secure MySQL Interaction

Let's illustrate a secure PHP script that performs an UPDATE query on the 'tablename' table:

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

$config = array(
    '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

• Escape User Input: Input values (id and name) are escaped using mysql_real_escape_string to prevent SQL injection.
• Disable Error Display: Error display is disabled during production mode (display_errors set to 0) for security purposes.
• Unicode Support: Unicode support is enabled via mysql_set_charset('utf8') to handle international characters.
• Error Handling: Detailed errors are triggered in case of failure, aiding in debugging and error reporting. The trigger_error() function can be customized as needed.

This code sample demonstrates how to execute a secure update query using the MySQL extension. It serves as a reference for developers seeking to implement best practices in their own database interactions.

The above is the detailed content of How to Securely Use the MySQL Extension in PHP to Prevent SQL Injection?. For more information, please follow other related articles on the PHP Chinese website!