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

问题描述

这是为了创建社区学习资源.我们的目标是拥有一些好的代码示例，这些示例不会重复出现在复制/粘贴的PHP代码中的常见错误.我已要求将其设为Community 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已更新."

• 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.

奖励积分可带来良好的评价.

Bonus points for good comments.

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

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屋！