使用mysqli_real_escape_string时查询不运行 [英] Query does not run when using mysqli_real_escape_string

问题描述

我正在转换旧脚本以使其与MySQLi兼容，并遇到问题...

I'm converting an old script to be compliant with MySQLi and ran in to an issue...

$link = mysqli_connect("localhost", "user", "password", "database");

if (mysqli_connect_errno()) {
     printf("Connect failed: %s\n", mysqli_connect_error());
     exit();
} 

$myQuery = "INSERT INTO table (name, description) VALUES ('$name', '$description')";

if (!mysqli_query($link, $myQuery)) {
    printf('Error');
} else {
    printf('Success');
}

mysqli_close($link);

这正常工作，没有错误.但是，当我添加mysqli_real_escape_string()时，我得到一个错误...

This works fine, no errors. But when I add the mysqli_real_escape_string() I get an error...

$link = mysqli_connect("localhost", "user", "password", "database");

if (mysqli_connect_errno()) {
     printf("Connect failed: %s\n", mysqli_connect_error());
     exit();
} 

$myQuery = "INSERT INTO table (name, description) VALUES ('$name', '$description')";

$myQuery = mysqli_real_escape_string($link, $myQuery);

if (!mysqli_query($link, $myQuery)) {
    printf('Error');
} else {
    printf('Success');
}

mysqli_close($link);

这将返回错误:

您的SQL语法有错误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 在第1行的'\'TestName \'，\'TestDescription \'附近

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'TestName\', \'TestDescription\' at line 1

我错过了一些简单的东西吗?引号?

Am I missing something simple? Quotes?

推荐答案

此行:

$myQuery = mysqli_real_escape_string($link, $myQuery);

那是不对的.

您需要使用$name变量，而不是$myQuery变量.这就是需要转义的内容，而不是整个查询本身.

You need to use $name variable and not the $myQuery variable. That's what need escaping and not the whole query itself.

$myQuery = mysqli_real_escape_string($link, $name);

但是，应将^ $myQuery替换为用于插入的每个变量.

However, ^ $myQuery should be replaced with each of the variables being used to be inserted.

您的查询应更像这样:

$name = "TestName";
$description = "TestDescription";

$name = mysqli_real_escape_string($link, $name);
$description = mysqli_real_escape_string($link, $description);

$myQuery = "INSERT INTO `table` (name, description) VALUES ('$name', '$description')";

if (!mysqli_query($link, $myQuery)) {
    printf('Error');
} else {
    printf('Success');
}

---

Nota:

---

Nota:

您可能希望使用 具有准备好的语句 ，或 具有准备好的语句的PDO ，它们要安全得多.

另外，仅出于辩论目的； table是 MySQL保留字，实际表的名称，并且必须对其进行转义:

Plus, just for argument's sake; table is a MySQL reserved word should that be the actual table's name and is required to be escaped:

$myQuery = "INSERT INTO `table`

• 只是一个见识.

mysqli准备好的语句的示例:

An example of a mysqli prepared statement:

$variable_1 = "Text";
$variable_2 = "More text";

$stmt = $link->prepare("INSERT INTO table_name 
                        (column_1, column_2) 
                        VALUES (?,?)");

$stmt->bind_param('ss', $variable_1, $variable_2);
$stmt->execute();

• 旁注:s用于字符串
• Sidenote: s is for strings

PDO准备语句的示例:

An example of a PDO prepared statement:

$dbh = new PDO('mysql:host=localhost;dbname=your_DB', $user, $pass);

$var_1 = "Text";
$var_2 = "More text";

$stmt = $dbh->prepare("INSERT INTO table_name 
                       (column_1, column_2) 
                       VALUES (:var_1,:var_2)");

$stmt->execute(array(':var_1' => $var_1, ':var_2' => $var_2));

这篇关于使用mysqli_real_escape_string时查询不运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！