转义字符如$和％| MySQL和PHP [英] Escaping Characters Such as $ and % | MySQL and PHP

问题描述

所以基本上我已经深入到MySQL和PHP的领域...特别是在处理数据库和输入数据时应该采取的安全措施。到目前为止，我发现以下是非常强烈的推荐：

1. 准备好的语句


2. 使用mysqli_real_escape_string（）


3. 不使用魔术报价，因为它混淆了数据库，最终给你的东西像你没有名字是没有...

所有这一切都很棒，我一直在追踪它。但是，我想知道是否还应该逃避诸如美元符号[$]，百分号[％]等可能的字符。查询可能会将美元符号解释为PHP变量吗？听说使用％符号甚至通配符的LIKE语法呢？准备的声明在技术上应该照顾所有这些，但我只是想要安全，并确保我有一切正常逃脱。在我忘记使用准备好的声明或者只是忽略他们的情况下，我希望这个第二道防线可以节省我的头痛。

这是我目前用于转义的内容：

 函数escape（$ connection，$ data）{
 $ new_data =修剪（$数据）; 
 $ new_data = mysqli_real_escape_string（$ connection，$ new_data）; 
 $ new_data = addcslashes（$ new_data，'％_ $'）; 
 $ new_data = htmlspecialchars（$ new_data，ENT_NOQUOTES）; 
 
 return $ new_data; 
 
} 
  

这是正确的吗？我做错了吗？请注意，在撤回数据库数据时，我必须在$，％和_个字符之前删除斜杠。

解决方案

我做某件事情可怕吗？

是的。

准备好的语句是唯一您发现的好东西。

尽管使用mysqli_real_escape_string（假设您正在使用准备好的语句）将是无用且有害的（产生您注意到的结果：你不是名字isn\' ）。

Magic Quotes已经从很久以前就已经从语言中删除了 - 因此，实际上没有什么可以关心的。

所以，即使你的大部分初始场所都显然是错误的。

现在您的问题。

查询不能解释美元签署PHP变量吗？

否。

LIKE语法我听说过使用％符号甚至通配符？

是的，我听说了对。这是LIKE操作符的确切目的 - 执行通配符搜索。禁止这些符号在LIKE将不会有丝毫的意义。每次你要使用LIKE操作符时，你必须决定要使用哪个特定符号，哪个不允许。没有一个解决方案可以使用。更不用说在所有其他mysql交互中，％符号根本没有什么特别的意义。

准备好的语句应该在技术上照顾所有这些

准备的语句与$或％符号无关。准备的语句处理SQL注入，但是两个符号都不会导致它（你不会称之为注入一个适当的使用LIKE操作符，是吗？）。

最后，最可怕的部分。

如果你忘记使用准备好的语句或者忽略它们，

没有任何东西可以救你。

最少的帮助将来自于您开发的功能。

总结起来。

1. 摆脱这个功能。 / li>
   
2. 使用占位符* 来表示查询中的每个变量。


3. Escape ％和 _ 输入数据中的符号只有在LIKE运算符中使用，并且不希望它们被解释时。


4. 使用htmlspecialchars（）输出，而不是mysql输入。





---

*如果您对该术语不熟悉，请阅读准备的声明。

So basically I’ve been digging deep into the realm of MySQL and PHP…specifically the security measures I should take when dealing with a database and form inputs. So far I’ve found that the following are very strongly recommended:

1. Prepared Statements
2. Using mysqli_real_escape_string()
3. NOT using Magic Quotes as it confuses databases and ends up giving you stuff like "You\’re name isn\’t…."

All of this is great and I’ve been following it. However, I was wondering if one should also escape characters such as the dollars sign [$], percentage sign [%], and possibly others. Couldn’t the query interpret the dollar sign as a PHP variable perhaps? What about LIKE syntax I’ve heard that uses the % symbol or even the wildcard sign? Prepared statements should technically take care of all of this, but I just wanted to be safe and make sure I had everything escaped properly. In the case that I forget to use prepared statements or just neglect to do them, I was hoping this second line of defense per-say could save me a loooong headache.

Here is what I use for escaping currently:

function escape($connection, $data){
    $new_data = trim($data);
    $new_data = mysqli_real_escape_string($connection, $new_data);
    $new_data = addcslashes($new_data, '%_$');
    $new_data = htmlspecialchars($new_data, ENT_NOQUOTES);

    return $new_data;

}

So is this proper? Am I doing something horrendously wrong? Notice that I would have to remove back slashes before the $, %, and _ characters when retreiving the database data.

解决方案

Am I doing something horrendously wrong?

Yes.

First on your research.

Prepared Statements is the only great thing you have found.

While use of mysqli_real_escape_string (assuming you are using prepared statements) would be useless and harmful (producing the outcome you have noted yourself: "You\’re name isn\’t….").

And Magic Quotes has been removed from the language long time ago already - thus, nothing to concern actually.

So, even most of your initial premises are plainly wrong.

Now to your question.

Couldn’t the query interpret the dollar sign as a PHP variable perhaps?

No.

What about LIKE syntax I’ve heard that uses the % symbol or even the wildcard sign?

Yes, you've heard it right. That's exact purpose of LIKE operator - to perform a wildcard search. Disabling these symbols in LIKE would make not a slightest sense.

Means every time you are going to use LIKE operator, you have to decide which particular symbol to use and which to disallow. NO one-for-all solution can be used. Not to mention that in all other mysql interactions % sign has no special meaning at all.

Prepared statements should technically take care of all of this

Prepared statements has nothing to do neither with $ nor with % signs. Prepared statements deal with SQL injections, but neither symbol could cause it (wouldn't you call "injection" a proper intended use of LIKE operator, would you?).

Finally, to the most horrendous part.

In the case you forget to use prepared statements or just neglect to do them,

nothing can save you.

And least help would be from the function you developed.

To sum it all up.

1. Get rid of this function.
2. Use placeholders* to represent every single variable in the query.
3. Escape % and _ symbols in the input data only if it's going to be used in LIKE operator and you don't want them to be interpreted.
4. Use htmlspecialchars() for output, not mysql input.

---

*read on prepared statements if the term is unfamiliar to you.

这篇关于转义字符如$和％| MySQL和PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！