用 PDO 和准备好的语句替换 mysql_* 函数 [英] Replacing mysql_* functions with PDO and prepared statements

问题描述

mysql_connect、mysql_pconnect的简单连接我一直都是做的:

I've always done the simple connection of mysql_connect, mysql_pconnect:

$db = mysql_pconnect('*host*', '*user*', '*pass*');

if (!$db) {
    echo("<strong>Error:</strong> Could not connect to the database!");
    exit;
}

mysql_select_db('*database*');

在使用这个时，我一直使用简单的方法在进行查询之前转义任何数据，无论是INSERT、SELECT、UPDATE 或 DELETE 使用 mysql_real_escape_string

While using this I've always used the simple method to escape any data before making a query, whether that be INSERT, SELECT, UPDATE or DELETE by using mysql_real_escape_string

$name = $_POST['name'];

$name = mysql_real_escape_string($name);

$sql = mysql_query("SELECT * FROM `users` WHERE (`name` = '$name')") or die(mysql_error());

现在我明白这在一定程度上是安全的！

Now I understand this is safe, to an extent!

它转义危险字符；但是，它仍然容易受到其他攻击，这些攻击可能包含安全字符，但可能对显示数据或在某些情况下恶意修改或删除数据有害.

It escapes dangerous characters; however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

所以，我稍微搜索了一下，发现了 PDO、MySQLi 和准备好的语句.是的，我可能会迟到，但我已经阅读了很多教程(tizag、W3C、博客、谷歌搜索)，但没有一个提到过这些.至于为什么，这似乎很奇怪，因为转义用户输入确实不安全，至少可以说不是好的做法.是的，我知道您可以使用 Regex 来解决它，但是，我很确定这还不够?

So, I searched a little bit and found out about PDO, MySQLi and prepared statements. Yes, I may be late to the game but I've read many, many tutorials (tizag, W3C, blogs, Google searches) out there and not a single one has mentioned these. It seems very strange as to why, as just escaping user input really isn't secure and not good practice to say the least. Yes, I'm aware you could use Regex to tackle it, but still, I'm pretty sure that's not enough?

据我所知，当变量由用户输入给出时，使用 PDO/准备好的语句是一种更安全的方式来存储和检索数据库中的数据.唯一的问题是，转换(特别是在我以前编码的方式/习惯非常卡住之后)有点困难.

It is to my understanding that using PDO/prepared statements is a much safer way to store and retrieve data from a database when the variables are given by user input. The only trouble is, the switch over (especially after being very stuck in my ways/habits of previous coding) is a little difficult.

现在我知道要使用 PDO 连接到我的数据库，我会使用

Right now I understand that to connect to my database using PDO I would use

$hostname = '*host*';
$username = '*user*';
$password = '*pass*';
$database = '*database*'

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);

if ($dbh) {
    echo 'Connected to database';
} else {
    echo 'Could not connect to database';
}

现在，函数名不同了，所以我的 mysql_query、mysql_fetch_array、mysql_num_rows 等将不再起作用.所以我不得不阅读/记住大量的新内容，但这正是我感到困惑的地方.

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work. So I'm having to read/remember a load of new ones, but this is where I'm getting confused.

如果我想从注册/注册表格中插入数据，我将如何执行此操作，但主要是我将如何安全地执行此操作?我认为这是准备好的语句出现的地方，但是通过使用它们是否不需要使用诸如 mysql_real_escape_string 之类的东西?我知道 mysql_real_escape_string 要求你通过 mysql_connect/mysql_pconnect 连接到数据库，所以现在我们不使用这个函数只是产生一个错误?

If I wanted to insert data from say a sign up/registration form, how would I go about doing this, but mainly how would I go about it securely? I assume this is where prepared statements come in, but by using them does this eliminate the need to use something like mysql_real_escape_string? I know that mysql_real_escape_string requires you to be connected to a database via mysql_connect/mysql_pconnect so now we aren't using either won't this function just produce an error?

我也看到了处理 PDO 方法的不同方式，例如，我看到了 :variable 和 ? 作为我认为的占位符(对不起，如果那是错误的).

I've seen different ways to approach the PDO method too, for example, I've seen :variable and ? as what I think are known as place holders (sorry if that is wrong).

但我认为这大致就是从数据库中获取用户应该怎么做的想法

But I think this is roughly the idea of what should be done to fetch a user from a database

$user_id = $_GET['id']; // For example from a URL query string

$stmt = $dbh->prepare("SELECT * FROM `users` WHERE `id` = :user_id");

$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);

但是后来我遇到了一些问题，如果变量不是数字而是一串文本，则必须在 PDO:PARAM_STR 之后给出一个长度，如果我是没看错.但是，如果您不确定从用户输入的数据中给出的值，您怎么能给出一个设定的长度，它每次都会有所不同?无论哪种方式，据我所知显示您然后做的数据

But then I'm stuck on a couple things, if the variable wasn't a number and was a string of text, you have to given a length after PDO:PARAM_STR if I'm not mistaken. But how can you give a set length if you're not sure on the value given from user in-putted data, it can vary each time? Either way, as far as I know to display the data you then do

$stmt->execute();

$result = $stmt->fetchAll();

// Either

foreach($result as $row) {
    echo $row['user_id'].'<br />';
    echo $row['user_name'].'<br />';
    echo $row['user_email'];
}

// Or

foreach($result as $row) {
    $user_id = $row['user_id'];
    $user_name = $row['user_name'];
    $user_email = $row['user_email'];
}

echo("".$user_id."<br />".$user_name."<br />".$user_email."");

现在，这一切安全吗?

如果我是对的，插入数据是否相同，例如:

If I am right, would inserting data be the same for example:

 $username = $_POST['username'];
 $email = $_POST['email'];

 $stmt = $dbh->prepare("INSERT INTO `users` (username, email)
                        VALUES (:username, :email)");

 $stmt->bindParam(':username, $username, PDO::PARAM_STR, ?_LENGTH_?);
 $stmt->bindParam(':email, $email, PDO::PARAM_STR, ?_LENGTH_?);

$stmt->execute();

那行得通吗，那也安全吗?如果正确，我会为 ?_LENGTH_? 输入什么值?我完全错了吗?

Would that work, and is that safe too? If it is right what value would I put in for the ?_LENGTH_?? Have I got this all completely wrong?

更新

到目前为止我收到的回复非常有帮助，对你们的感激不尽！每个人都因为让我睁开眼睛看到一些不同的东西而得到了 +1.很难选择最佳答案，但我认为 Shrapnel 上校应得的，因为几乎涵盖了所有内容，甚至使用我不知道的自定义库进入其他数组！

The replies I've had so far have been extremely helpful, can't thank you guys enough! Everyone has got a +1 for opening my eyes up to something a little different. It's difficult to choose the top answer, but I think Col. Shrapnel deserves it as everything is pretty much covered, even going into other arrays with custom libraries which I wasn't aware of!

但是谢谢大家:)

推荐答案

感谢您提出有趣的问题.给你:

Thanks for the interesting question. Here you go:

它转义危险字符，

你的概念完全错误.
事实上，危险人物"是一个神话，没有.而 mysql_real_escape_string 转义只是一个字符串分隔符.从这个定义你可以得出它的局限性——它只适用于字符串.

Your concept is utterly wrong.
In fact "dangerous characters" is a myth, there are none. And mysql_real_escape_string escaping but merely a string delimiters. From this definition you can conclude it's limitations - it works only for strings.

然而，它仍然容易受到其他攻击，这些攻击可能包含安全字符，但可能对显示数据或在某些情况下恶意修改或删除数据有害.

however, it is still vulnerable to other attacks which can contain safe characters but may be harmful to either displaying data or in some cases, modifying or deleting data maliciously.

你在这里混合了一切.
说到数据库，

You're mixing here everything.
Speaking of database,

• 对于字符串，它不易受到攻击.只要您的字符串被引用和转义，它们就不能恶意修改或删除数据".*
• 对于其他数据类型数据 - 是的，它没用.但不是因为它有点不安全"，而是因为使用不当.

• for the strings it is NOT vulnerable. As long as your strings being quoted and escaped, they cannot "modify or delete data maliciously".*
• for the other data typedata - yes, it's useless. But not because it is somewhat "unsafe" but just because of improper use.

至于显示数据，我想它在 PDO 相关问题中是 offtopic，因为 PDO 也与显示数据无关.

As for the displaying data, I suppose it is offtopic in the PDO related question, as PDO has nothing to do with displaying data either.

转义用户输入

^^^ 另一个需要注意的错觉！

^^^ Another delusion to be noted!

• 用户输入与转义完全无关.正如您可以从前一个定义中了解到的，您必须转义字符串，而不是用户输入".所以，再次:

• a user input has absolutely nothing to do with escaping. As you can learn from the former definition, you have to escape strings, not whatever "user input". So, again:

• 您有转义字符串，无论其来源如何
• 转义其他类型的数据是没有用的，无论来源如何.

明白了吗?
现在，我希望您了解逃逸的局限性以及危险人物"的误解.

Got the point?
Now, I hope you understand the limitations of escaping as well as the "dangerous characters" misconception.

据我所知，使用 PDO/准备好的语句更安全

It is to my understanding that using PDO/prepared statements is a much safer

不是真的.
事实上，我们可以动态添加四个不同的查询部分:

Not really.
In fact, there are four different query parts which we can add to it dynamically:

• 一个字符串
• 一个数字
• 标识符
• 语法关键字.

因此，您可以看到转义仅涵盖一个问题.(但当然，如果您将数字视为字符串(将它们放在引号中)，如果适用，您也可以使它们安全)

so, you can see that escaping covers only one issue. (but of course, if you treat numbers as strings (putting them in quotes), when applicable, you can make them safe as well)

虽然准备好的声明涵盖 - 呃 - 整个 2 问题！一个大问题;-)

while prepared statements cover - ugh - whole 2 isues! A big deal ;-)

对于其他 2 个问题，请参阅我之前的回答，在 PHP 中向数据库提交字符串时，我应该使用 htmlspecialchars() 还是使用正则表达式来处理非法字符?

For the other 2 issues see my earlier answer, In PHP when submitting strings to the database should I take care of illegal characters using htmlspecialchars() or use a regular expression?

现在，函数名不同了，所以我的 mysql_query、mysql_fetch_array、mysql_num_rows 等将不再起作用.

Now, function names are different so no longer will my mysql_query, mysql_fetch_array, mysql_num_rows etc work.

这是另一个PHP用户的严重错觉，一场自然灾害，一场灾难:

That is another, grave delusion of PHP users, a natural disaster, a catastrophe:

即使使用旧的 mysql 驱动程序，也不应该在他们的代码中使用裸 API 函数！必须将它们放入一些库函数中以供日常使用！(不是作为某种魔法仪式，而是为了使代码更短、更少重复、防错、更一致和可读).

Even when utilizing old mysql driver, one should never use bare API functions in their code! One have to put them in some library function for the everyday usage! (Not as a some magic rite but just to make the code shorter, less repetitive, error-proof, more consistent and readable).

PDO 也是如此！

现在再次回答你的问题.

Now on with your question again.

但是通过使用它们是否不需要使用诸如 mysql_real_escape_string 之类的东西?

but by using them does this eliminate the need to use something like mysql_real_escape_string?

是的.

但我认为这大致就是从数据库中获取用户应该怎么做的想法

But I think this is roughly the idea of what should be done to fetch a user from a database

不是获取，而是向查询中添加任何数据！

如果我没记错的话，你必须在 PDO:PARAM_STR 之后给出一个长度

you have to given a length after PDO:PARAM_STR if I'm not mistaken

可以，但没必要.

现在，这一切安全吗?

就数据库安全而言，这段代码没有任何弱点.这里没有什么可确保的.

In terms of database safety there are just no weak spots in this code. Nothing to secure here.

为了显示安全 - 只需在此站点中搜索 XSS 关键字即可.

for the displaying security - just search this site for the XSS keyword.

希望我对此事有所了解.

Hope I shed some light on the matter.

顺便说一句，对于长插入，你可以使用我有一天写的函数，使用 PDO 插入/更新辅助函数

BTW, for the long inserts you can make some use of the function I wrote someday, Insert/update helper function using PDO

然而，我目前不使用准备好的语句，因为我更喜欢我自己制作的占位符，使用我上面提到的库.所以，为了反驳下面 riha 发布的代码，它会像这两行一样短:

However, I am not using prepared statements at the moment, as I prefer my home-brewed placeholders over them, utilizing a library I mentioned above. So, to counter the code posted by the riha below, it would be as short as these 2 lines:

$sql  = 'SELECT * FROM `users` WHERE `name`=?s AND `type`=?s AND `active`=?i';
$data = $db->getRow($sql,$_GET['name'],'admin',1);

当然，您也可以使用准备好的语句来获得相同的代码.

But of course you can have the same code using prepared statements as well.

*(是的，我知道 Schiflett 的恐怖故事)

这篇关于用 PDO 和准备好的语句替换 mysql_* 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！