php PHP中如何使用mysql_real_escape_string函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5691474/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use mysql_real_escape_string function in PHP
提问by Casey Patton
So in this program I'm writing, I actually grab a SQL query from the user using a form. I then go on to run that query on my database.
所以在我编写的这个程序中，我实际上是使用表单从用户那里获取 SQL 查询。然后我继续在我的数据库上运行该查询。
I know not to "trust" user input, so I want to do sanitization on the input. I'm trying to use mysql_real_escape_stringbut have been unsuccessful in getting it to work.
我知道不要“信任”用户输入，所以我想对输入进行清理。我正在尝试使用，mysql_real_escape_string但未能成功使其正常工作。
Here's what I'm trying, given the input:
select * from Actor;
鉴于输入，这就是我正在尝试的内容：
select * from Actor;
//"query" is the input string:
$clean_string = mysql_real_escape_string($query, $db_connection);
$rs = mysql_query($clean_string, $db_connection);
if (!$rs)
{
echo "Invalid input!";
}
This is ALWAYS giving me the
这总是给我
"Invalid input!"
“输入无效！”
error.
错误。
When I take out the clean_stringpart and just run mysql_queryon query, the
当我取出clean_string零件并运行mysql_query查询时，
"invalid input"
“输入无效”
message is not output. Rather, when I do this:
不输出消息。相反，当我这样做时：
$rs = mysql_query($query, $db_connection);
if (!$rs)
{
echo "Invalid input!";
}
It does NOT output
它不输出
"invalid input".
“输入无效”。
However, I need to use the mysql_real_escape_stringfunction. What am I doing wrong?
但是，我需要使用该mysql_real_escape_string功能。我究竟做错了什么？
Update:
更新：
Given
select * from Actor;as an input, I've found the following.
鉴于
select * from Actor;作为输入，我发现以下。
Using echo statements I've
found that before sanitizing, the string holds the value:
select * from Actor;which is correct. However, after sanitizing it holds the incorrect
value of select *\r\nfrom Actor;, hence the error message. Why is
mysql_real_escape_stringdoing this?
使用 echo 语句，我发现在清理之前，字符串保存的值是：
select * from Actor;这是正确的。但是，在清理后它保存了不正确的值select *\r\nfrom Actor;，因此出现了错误消息。为什么
mysql_real_escape_string要这样做？
回答by Crayon Violent
use it on the actual values in your query, not the whole query string itself.
在查询中的实际值上使用它，而不是整个查询字符串本身。
example:
例子：
$username = mysql_real_escape_string($_POST['username']);
$query = "update table set username='$username' ...";
$rs = mysql_query($query);
回答by outis
Rather than using the outdated mysql extension, switch to PDO. Prepared statementparameters aren't vulnerable to injection because they keep values separate from statements. Prepared statements and PDO have other advantages, including performance, ease of use and additional features. If you need a tutorial, try "Writing MySQL Scripts with PHP and PDO".
与其使用过时的 mysql 扩展，不如切换到PDO。准备好的语句参数不易受到注入，因为它们将值与语句分开。准备好的语句和 PDO 还具有其他优势，包括性能、易用性和附加功能。如果您需要教程，请尝试“使用 PHP 和 PDO 编写 MySQL 脚本”。
回答by mario
mysql_real_escape_string()is the string escapingfunction. It does not make any input safe, just string values, not for use with LIKE clauses, and integers need to be handled differently still.
mysql_real_escape_string()是字符串转义函数。它不会使任何输入安全，只是字符串值，不用于 LIKE 子句，并且仍然需要以不同方式处理整数。
An easier and more universal example might be:
一个更简单、更通用的例子可能是：
$post = array_map("mysql_real_escape_string", $_POST);
// cleans all input variables at once
mysql_query("SELECT * FROM tbl WHERE id='$post[id]'
OR name='$post[name]' OR mtime<'$post[mtime]' ");
// uses escaped $post rather than the raw $_POST variables
Note how each variable must still be enclosed by 'single quotes for SQL strings. (Otherwise the escaping would be pointless.)
请注意，'对于 SQL 字符串，每个变量仍然必须用单引号括起来。（否则逃避将毫无意义。）
回答by dwolf
This worked for me. dwolf (wtec.co)
这对我有用。dwolf (wtec.co)
<?php
// add data to db
require_once('../admin/connect.php');
$mysqli = new mysqli($servername, $username, $password, $dbname);
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$post = $mysqli->real_escape_string($_POST['name']);
$title = $mysqli->real_escape_string($_POST['message']);
/* this query with escaped $post,$title will work */
if ($mysqli->query("INSERT into press (title, post) VALUES ('$post', '$title')")) {
printf("%d Row inserted.\n", $mysqli->affected_rows);
}
$mysqli->close();
//header("location:../admin");
?>
回答by Zach Rattner
You should use mysql_real_escape_stringto escape the parameters to the query, not the entire query itself.
您应该使用mysql_real_escape_string将参数转义到查询，而不是整个查询本身。
For example, let's say you have two variables you received from a form. Then, your code would look like this:
例如，假设您有两个从表单接收的变量。然后，您的代码将如下所示：
$Query = sprintf(
'INSERT INTO SomeTable VALUES("%s", "%s")',
mysql_real_escape_string($_POST['a'], $DBConnection),
mysql_real_escape_string($_POST['b'], $DBConnection)
);
$Result = mysql_query($Query, $DBConnection);
回答by Wh1T3h4Ck5
manual mysql_real_escape_string()
Escapes special characters in a string for use in an SQL statement
转义字符串中的特殊字符以用于 SQL 语句
So you can't escape entire query, just data... because it will escape all unsafe characters like quotes (valid parts of query).
所以你不能转义整个查询，只能转义数据……因为它会转义所有不安全的字符，比如引号（查询的有效部分）。
If you try something like that (to escape entire query)
如果您尝试类似的操作（以逃避整个查询）
echo mysql_real_escape_string("INSERT INTO some_table VALUES ('xyz', 'abc', '123');");
Output is
输出是
INSERT INTO some_table VALUES (\'xyz\', \'abc\', \'123\');
INSERT INTO some_table VALUES (\'xyz\', \'abc\', \'123\');
and that is not valid query any more.
而这不是有效的查询更多。
