MySQL error - “You have an error in your SQL syntax"

Question

My code is:

if (isset($_POST['add'])) {
    $query = "INSERT INTO ".$dbPrefix."posts (`id`, `user`, `name`, `link`, `content`, `date`, `private`, `password`) VALUES (NULL, '$user', '$name', '$link', '$content', '$date', '$private', '$pass');";
    $mysqli->query($query) OR $status = 'Oprostite, pri dodajanju je prišlo do težave.';
    $id=$mysqli->insert_id;
    foreach ($_POST['categories'] as $category) {
        $categoryQuery.="INSERT INTO ".$dbPrefix."category_posts (`categoryID`, `postID`) VALUES ('".$category."','".$id."');";
    }
    $mysqli->query($categoryQuery) OR $status = $mysqli->error;
}
echo $status;

My variable $_POST is:

Array
(
    [name] => ((((((((((
    [description] => ))))))
    [keywords] => 
    [categories] => Array
        (
            [0] => 1
            [1] => 2
            [2] => 3
        )

    [private] => 0
    [password] => 
    [date] => 0
    [datetime] => 
    [add] => Dodaj
)

If I print the query and run it in phpMyAdmin there is no problem, otherwise it will display 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 'INSERT INTO fc_category_posts (`categoryID`, `postID`) VALUES ('2','116');INSERT' at line 1

Query is:

INSERT INTO fc_category_posts (`categoryID`, `postID`) VALUES ('1','116');
INSERT INTO fc_category_posts (`categoryID`, `postID`) VALUES ('2','116');
INSERT INTO fc_category_posts (`categoryID`, `postID`) VALUES ('3','116');

You just need to change your loop code to this: `foreach ($_POST['categories'] as $category) { $categoryQuery ="INSERT INTO ".$dbPrefix."category_posts (`categoryID`, `postID`) VALUES ('".$category."','".$id."');"; $mysqli->query($categoryQuery);` } — Hackerman, Aug 23 '16 at 14:20
You cannot run more than one query statement in a single `query()` call, as a basic defense mechanism against ONE form of [sql injection attack](http://bobby-tables.com), which your code is utterly vulnerable to. — Marc B, Aug 23 '16 at 14:24

score 4 · Accepted Answer · edited Aug 23 '16 at 14:19

4

If you can only pass the one INSERT then why not do it like this;

INSERT INTO fc_category_posts (`categoryID`, `postID`) 
VALUES ('1','116'),('2','116'),('3','116');

Saves you running multiple INSERT INTO statements.

edited Aug 23 '16 at 14:19

RiggsFolly

93,638
21
103
149

answered Aug 23 '16 at 14:18

Rich Benner

7,873
9
33
39

Would be simpler to just move the query inside the loop in this case, rather than rewriting code to make this work – RiggsFolly Aug 23 '16 at 14:27
Could be, I'm a simple SQL guy so looked for the answer that way. – Rich Benner Aug 23 '16 at 14:48

score 2 · Answer 2 · edited May 23 '17 at 10:29

Just simply move the execution of the query inside the loop

if (isset($_POST['add'])) {
    $query = "INSERT INTO ".$dbPrefix."posts 
                   (`id`, `user`, `name`, `link`, `content`, `date`, 
                    `private`, `password`) 
               VALUES (NULL, '$user', '$name', '$link', '$content', '$date', 
                       '$private', '$pass');";

    $mysqli->query($query) OR $status = 'Oprostite, pri dodajanju je prišlo do težave.';
    $id=$mysqli->insert_id;
    foreach ($_POST['categories'] as $category) {

        //Note I have chnages `.=` to `=` in this statement
        $categoryQuery = "INSERT INTO ".$dbPrefix."category_posts 
                                   (`categoryID`, `postID`) 
                            VALUES ('$category','$id');";

        $mysqli->query($categoryQuery) OR $status = $mysqli->error;
    }

}
echo $status;

However Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements

In this senario it would also be useful for you to consider running these inside a transaction so that if one insert fails you wont leave the darabase in a mess The manual for mysqli::begin_transaction

MySQL error - “You have an error in your SQL syntax"

2 Answers2