How Can I Securely Use the MySQL Extension in PHP to Avoid SQL Injection and Other Pitfalls?

Safe MySQL Querying with the MySQL Extension

Introduction

Despite the prevalence of PDO, the mysql_* family of functions remains a common choice for PHP database interaction. While this library is perfectly safe when used properly, its use often leads to security and technical issues. This article provides a perfect code sample that showcases how to use the MySQL extension securely.

Avoiding Common Pitfalls

The mysql_* functions are notorious for several common problems, including:

• SQL injection
• Error handling issues
• Cross-site scripting (XSS) injection

Sample Code

The following code sample implements a simple PHP script that updates a database table row based on POST variables while mitigating the above risks:

<?php

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);
}
?>

Key Features

• Error handling: Errors are captured and reported with trigger_error(), suppressing detailed error messages in production mode.
• SQL injection prevention: POST values are escaped using mysql_real_escape_string() to prevent SQL injection attacks.
• Unicode support: The database connection is configured to support Unicode.
• Production mode: Error messages can be suppressed by setting display_errors to 0 in production mode.

Conclusion

This code sample provides a solid foundation for safe MySQL queries using the mysql_* family of functions. It addresses common pitfalls by implementing proper error handling, preventing SQL injection, and supporting Unicode. By adopting these practices, developers can write secure and reliable database-driven applications.

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