参考:使用 MySQL 扩展的完美代码示例是什么? [英] Reference: What is a perfect code sample using the MySQL extension?

问题描述

这是为了创建一个社区学习资源.我们的目标是让好的代码示例不会重复在复制/粘贴的 PHP 代码中经常出现的可怕错误.我已要求将其设为社区 Wiki.

This is to create a community learning resource. The goal is to have examples of good code that do not repeat the awful mistakes that can so often be found in copy/pasted PHP code. I have requested it be made Community Wiki.

这不是一场编码竞赛.这不是寻找最快或最紧凑的查询方式 - 而是提供一个很好的、可读的参考，尤其是对于新手.

This is not meant as a coding contest. It's not about finding the fastest or most compact way to do a query - it's to provide a good, readable reference especially for newbies.

每天都有大量问题涌入，其中包含非常糟糕的代码片段，这些问题使用 Stack Overflow 上的 mysql_* 函数系列.虽然通常最好将这些人引向 PDO，但有时这既不可能(例如继承的遗留软件)也不是现实的期望(用户已经在他们的项目中使用它).

Every day, there is a huge influx of questions with really bad code snippets using the mysql_* family of functions on Stack Overflow. While it is usually best to direct those people towards PDO, it sometimes is neither possible (e.g. inherited legacy software) nor a realistic expectation (users are already using it in their project).

使用 mysql_* 库的代码常见问题包括:

Common problems with code using the mysql_* library include:

• 值中的 SQL 注入
• LIMIT 子句和动态表名中的 SQL 注入
• 没有错误报告(为什么这个查询不起作用?")
• 断层错误报告(即，即使代码投入生产，也总是会出现错误)
• 值输出中的跨站点脚本 (XSS) 注入

让我们编写一个 PHP 代码示例，使用 mySQL_* 系列函数执行以下操作:

Let's write a PHP code sample that does the following using the mySQL_* family of functions:

• 接受两个 POST 值，id(数字)和 name(字符串)
• 对表 tablename 执行 UPDATE 查询，更改 ID id
行中的 name 列
• 失败时，优雅地退出，但仅在生产模式下显示详细错误.trigger_error() 就足够了；或者使用您选择的方法
• 输出消息$name updated".

• Accept two POST values, id (numeric) and name (a string)
• Do an UPDATE query on a table tablename, changing the name column in the row with the ID id
• On failure, exit graciously, but show the detailed error only in production mode. trigger_error() will suffice; alternatively use a method of your choosing
• Output the message "$name updated."

并且没有显示出上面列出的任何弱点.

And does not show any of the weaknesses listed above.

应该尽可能简单.理想情况下，它不包含任何函数或类.目标不是创建一个可复制/可粘贴的库，而是展示使数据库查询安全所需的最少操作.

It should be as simple as possible. It ideally doesn't contain any functions or classes. The goal is not to create a copy/pasteable library, but to show the minimum of what needs to be done to make database querying safe.

好评加分.

目标是使这个问题成为用户在遇到代码错误(即使它根本不是问题的重点)或遇到失败的查询时可以链接到的资源，并且不知道怎么解决.

The goal is to make this question a resource that a user can link to when encountering a question asker who has bad code (even though it isn't the focus of the question at all) or is confronted with a failing query and doesn't know how to fix it.

抢占 PDO 讨论:

是的，通常最好将写这些问题的个人引导给 PDO.当它是一种选择时，我们应该这样做.然而，这并不总是可能的 - 有时，提问者正在处理遗留代码，或者已经在这个库上取得了长足的进步，现在不太可能改变它.此外，如果使用得当，mysql_* 系列函数是完全安全的.所以没有使用 PDO"请在此处回答.

Yes, it will often be preferable to direct the individuals writing those questions to PDO. When it is an option, we should do so. It is, however, not always possible - sometimes, the question asker is working on legacy code, or has already come a long way with this library, and is unlikely to change it now. Also, the mysql_* family of functions is perfectly safe if used properly. So no "use PDO" answers here please.

推荐答案

我的尝试.尽量保持简单，同时仍然保持一些现实世界的便利.

My stab at it. Tried to keep it as simple as possible, while still maintaining some real-world conveniences.

处理 unicode 并使用松散比较以提高可读性.乖一点;-)

Handles unicode and uses loose comparison for readability. Be nice ;-)

<?php

header('Content-type: text/html; charset=utf-8');
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
// display_errors can be changed to 0 in production mode to
// suppress PHP's error messages

/*
Can be used for testing
$_POST['id'] = 1;
$_POST['name'] = 'Markus';
*/

$config = array(
    'host' => '127.0.0.1', 
    'user' => 'my_user', 
    'pass' => 'my_pass', 
    'db' => 'my_database'
);

# Connect and disable mysql error output
$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);
}

这篇关于参考:使用 MySQL 扩展的完美代码示例是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！