不执行数据库查询 mysql_real_escape_string,使用mysqli_real_escape_string时查询不运行

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

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

This returns an error:

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?

解决方案

This line:

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

That isn't right.

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

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

Your query should look more like this:

$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:

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`

Just an insight.

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

Sidenote: s is for strings

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