如何在PHP中使用mysql_real_escape_string函数

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_string but 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_string part and just run mysql_query on 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_string function. What am I doing wrong?

但是，我需要使用mysql_real_escape_string函数。我究竟做错了什么？

Update:

更新：

Given select * from Actor; as an input, I've found the following.

给出来自Actor的select *;作为输入，我发现了以下内容。

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_string doing this?

使用echo语句我发现在清理之前，字符串包含值：select * from Actor;哪个是对的。但是，在清理后，它会保持select * \ r \ n from Actor;的错误值，因此出现错误消息。为什么mysql_real_escape_string这样做？

6 个解决方案

#1

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);

#2

Rather than using the outdated mysql extension, switch to PDO. Prepared statement parameters 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脚本”。

#3

mysql_real_escape_string() is the string escaping function. 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字符串的单引号括起来。（否则逃跑将毫无意义。）

#4

You should use mysql_real_escape_string to 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);

#5

manual mysql_real_escape_string()

手动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\');

插入some_table VALUES（\'xyz \'，\'abc \'，\'123 \'）;

and that is not valid query any more.

那不再是有效的查询。

#6

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"); 
?>

#1