Mysqli UPDATE SET WHERE 语法错误

本文介绍了Mysqli UPDATE SET WHERE 语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

所以我有这块php代码

So I had this chunk of php code

if($_POST['action']=='newComment') { $mysqli = new mysqli("localhost", "root", "", "nested_comment"); $new_post = $mysqli->real_escape_string($_POST['content']); $result = $mysqli->query("SELECT @myLeft := lft FROM comment WHERE lft = '1'; UPDATE comment SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE comment SET lft = lft + 2 WHERE lft >= @myLeft; INSERT INTO comment(content, lft, rgt) VALUES('$new_post', @myLeft, @myLeft + 1);"); if($result) echo "ok"; else echo $mysqli->error; }

当我运行这个时，抛出一个错误:

When I run this, an error is thrown:

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在更新注释 SET rgt = rgt + 2 WHERE rgt > @myLeft;"附近使用的正确语法.在第 3 行更新注释 SET'

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 'UPDATE comment SET rgt = rgt + 2 WHERE rgt > @myLeft; UPDATE comment SET' at line 3

但是当我将 sql 查询放入 Sequel Pro(Mac) 时，它运行良好.我尝试了许多其他帖子的解决方案，但都没有奏效.我的语法有问题，还是 mysql 版本有问题?非常感谢.

But when I put the sql query into Sequel Pro(Mac), it works well. I tried many of other posts' solution and none of them work. Is there something wrong with my syntax, or something wrong with mysql version? Thanks a lot.

推荐答案

这是因为你试图一次执行多个语句

This is because you are trying to execute multiple statements in one go

php/manual/en/mysqli.quickstart.multiple-statement.php

Mysqli 有一个功能

Mysqli has a function for that

mysqli_multi_query()

请注意，现在可以将找到的任何 SQL 注入链接在一起.使用时要小心

Please note that any SQL injections found can now be chained together. Be careful when using it

安全注意事项

API 函数 mysqli_query() 和 mysqli_real_query() 没有设置在服务器中激活多查询所需的连接标志.一个额外的 API 调用用于多个语句以减少意外 SQL 注入攻击的可能性.攻击者可能会尝试添加诸如 ;删除数据库 mysql 或 ;选择睡眠(999).如果攻击者成功地将 SQL 添加到语句字符串中，但是mysqli_multi_query 没有使用，服务器不会执行二、注入恶意SQL语句.

The API functions mysqli_query() and mysqli_real_query() do not set a connection flag necessary for activating multi queries in the server. An extra API call is used for multiple statements to reduce the likeliness of accidental SQL injection attacks. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query is not used, the server will not execute the second, injected and malicious SQL statement.