mysqli_real_escape_string-100％安全示例 [英] mysqli_real_escape_string - example for 100% safety

问题描述

我知道已经有很多关于此主题的问题.我也知道，要走的路是准备好的陈述.但是，我仍然不完全了解以下情况是否或如何可能成为安全问题:

I know there have been a lot of questions asked already about this topic. And I also know that the way to go are prepared statements. However I have still not completely understood if or how the following could become a security problem:

$mysqli = new mysqli("localhost", "root", "", "myDatabase");
$mysqli->set_charset("utf8");
$pw = mysqli_real_escape_string($mysqli,$_POST['pw']);
$username = mysqli_real_escape_string($mysqli,$_POST['username']);

$str = "SELECT * FROM users WHERE id='".$id."' AND username='".$username."'";
$result = $this -> mysqli -> query($qstr);

if($result->num_rows > 0){
    //user logged in
}

我尝试了注射备忘单中的许多不同输入，但是找不到通过查询的任何内容.例如.如果我输入带有;"的任何内容然后$ result变为false，因为据我所知一个查询不能包含两个单独的语句.任何带有'或'的输入都会被 mysqli_real_escape_string.

I tried many different inputs from a injection cheat sheet but could not find anything that passed the query. E.g. if I entered anything with an ";" then $result became false because one query cannot contain two separate statements as far as I know. Any input having an ' or " was sanitized by mysqli_real_escape_string.

请您向我解释一下，上面的代码如何被利用?如果您有一个链接，可以解释它，我也很高兴阅读它！

Could you please explain to me, how the code above could be exploited? If you have a link, which explains it I am more than happy to read it, too!

欢呼

已在以下答案中得到解答:

绕过mysql_real_escape_string()的SQL注入

但是，这个问题是关于较旧版本的mysql而非mysqli的.其次，得票最多的答案指出了以下可以解决的示例:

This question however was about the older version of mysql but not mysqli. Secondly the answer with the most up votes statet the following example that could get around it:

mysql_query('SET NAMES gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

但是，我对此并不完全理解.第一行

However I do not fully understand this. The first line

mysql_query('SET NAMES gbk');

不能从外面设置，对吗?这只是一个例子，如果有人在他的程序中设置了"gbk".因此，如果我使用

can not be set from the outside, correct? This is just an example if someone set 'gbk' in his program. So if I used

$mysqli->set_charset("utf8");

，也曾用过

id='".$id."' (single quotes around $id) 

那我会100％安全吗?

then I would be 100% safe, correct?

推荐答案

您孤立和简化的示例在技术上是安全的.

Your isolated and simplified example is technically safe.

但是，它仍然存在两个问题:

However, there are still two problems with it:

• 假设:问题的陈述是基于mysqli_real_escape_string()与任何安全性问题有关的假设做出的.这不过是一个严重的幻想.这是一个字符串格式设置功能，仅作为副作用，可防止SQL注入.但是，这种保护既不是功能的目的也不是目的.因此，永远不要将其用于此目的.
您提出的代码的
• 固有的可分离性.保护由三个部分组成:
  • 设置正确的编码
  • 转义特殊字符
  • 将转义的值括在引号中
  • the assumption: the very statement of question is made out of the assumption that mysqli_real_escape_string() is related to any security issues. Which is but a grave delusion. This is a string formatting function, that protects you from SQL injections only as a side effect. But such a protection is neither the goal nor the purpose of the function. And therefore it should never be used for the purpose.
  • the inherent separability of the code you posed. The protection consists of three parts:
    • setting the correct encoding
    • escaping special characters
    • wrapping the escaped value in quotes

    不仅这些强制性措施中的一些可以被遗忘的事实，问题陈述只强调一个部分-转义.总是着重强调逃避，而几乎没有提到另外两个措施.只要看看您的问题-您的意思是 code ，但询问有关 function 的问题.因此，对您所提问题的任何字面回答都会给人致命的错误印象，即mysqli_real_escape_string()没问题.

    It is not only the fact that some of these obligatory measures could be forgotten but again, the statement of question stresses only on a single part - escaping. It is only escaping which is always accented on, while two other measures get hardly mentioned at all. Just look at your question - you meant the code but asked about a function. So any literal answer to the question you asked will make a fatally wrong impression that mysqli_real_escape_string() is all right.

    简而言之，问题陈述有助于促进与PHP相关的错觉中最危险的事情:该函数可以防止SQL注入.

    In short, the statement of question helps to promote the most dangerous of PHP related delusions: that this function protects from SQL injection.

    与这个复杂的三部分方程式不同，准备好的语句构成了不可分离的度量.你不能忘记一个部分.您不能滥用它.尝试使用mysqli_real_escape_string()保护一个标识符，它会被忽略，直到实际注入发生为止.尝试使用标识符准备好的语句-并收到错误消息.

    Unlike this complex three-part equation, prepared statements constitute an inseparable measure. You cannot forget one part. You cannot misuse it. Try mysqli_real_escape_string() to protect an identifier, and it will silently go unnoticed, until the actual injection happen. Try a prepared statement for an identifier - and get an error.

    这篇关于mysqli_real_escape_string-100％安全示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！