Mysqli UPDATE SET WHERE syntax error

Question

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:

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

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.

Avoid using MySQL variables unless its part of a procedure. – thepratt Mar 24 '15 at 22:04 — thepratt, Mar 24 '15 at 22:04

score 5 · Accepted Answer · answered Mar 24 '15 at 21:58

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

http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

Mysqli has a function for that

mysqli_multi_query()

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

Security considerations

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.

Thanks a lot! So if I use real_escape_string to prevent injection, will this be enough? — user3799934, Mar 24 '15 at 22:04
http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string prefer parametrised query's if possible — exussum, Mar 24 '15 at 22:07

Mysqli UPDATE SET WHERE syntax error

1 Answers1