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

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
– Hackerman, Commented Aug 23, 2016 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, which your code is utterly vulnerable to. — Marc B
– Marc B, Commented Aug 23, 2016 at 14:24

RiggsFolly · Accepted Answer · 2016-08-23 14:19:20Z

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, 2016 at 14:19

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Aug 23, 2016 at 14:18

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RiggsFolly Over a year ago

Would be simpler to just move the query inside the loop in this case, rather than rewriting code to make this work

Rich Benner Over a year ago

Could be, I'm a simple SQL guy so looked for the answer that way.

Community · Accepted Answer · 2017-05-23 10:29:14Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related