PHP not throwing error for multiple queries

Question

I have some PHP code to execute multi queries and one of the queries has an error in it. But PHP is not detecting it. Here's the code

        $updateCategoriesQ = "DELETE FROM category_products WHERE product = $id; INSERT INTO category_products (category, products) VALUES ";
        foreach ($product['categories'] as $key) {
            $updateCategoriesQ .= "(".mysqli_real_escape_string($connect, $key)."', $id), "; //error is here in the quote
        }
        $updateCategoriesQ = rtrim($updateCategoriesQ, ', ');
        o($updateCategoriesQ);

        $updateCategories = mysqli_multi_query($connect, $updateCategoriesQ);
        if($updateCategories){
            o('query ok'); //receives this output (wrapper function for echo)
        }

The query that forms is

DELETE FROM category_products WHERE product = 1; INSERT INTO category_products (category, products) VALUES (1', 1), (2', 1)

The second query has an error but PHP says its ok. If I create an error in the first query (delete) then it does throws the error but not if the error is in the second query. Is there a different method of capturing error here?

do you have error reporting enabled and configured properly? check phpinfo() output to verify your config. — ROOT, Jan 23 '20 at 06:41
`mysqli_multi_query()` only returns the success of the first query. To get the results of the second query you have to call `mysqli_next_result()`. — Barmar, Jan 23 '20 at 06:57
I recommend against using `mysqli_multi_query()`. There's very little gain and it makes things more complicated. — Barmar, Jan 23 '20 at 06:58
Also, you can't use prepared statements with multi-query. This is preferable to using `mysqli_real_escape_string()`. — Barmar, Jan 23 '20 at 06:59
@Barmar I hear your recommendations but could you share an example of this? I haven't been able to find it. I see how to use the results of it but not how to check if query was successful. — Whip, Jan 23 '20 at 07:01
Does this answer your question? [Why can't I run two mysqli queries? The second one fails](https://stackoverflow.com/questions/10924127/why-cant-i-run-two-mysqli-queries-the-second-one-fails) — Dharman, Jan 23 '20 at 23:46
@Dharman I've challenged many commenters like you to SQL inject my code but none have succeeded yet. Thanks for the linked question, I was not able to find it before asking. — Whip, Jan 24 '20 at 06:40

score 0 · Answer 1 · answered Jan 23 '20 at 23:58

Do not use mysqli_multi_query()! You can only use this query if you have no user input and even then it is really cumbersome and unnecessary.

Instead use prepared statements. This is the recommended and secure way.

// Add this before new mysqli() to enable error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$stmtDel = $connect->prepare('DELETE FROM category_products WHERE product = ?');
$stmtDel->bind_param('s', $id);
$stmtDel->execute();

$stmtInsert = $connect->prepare('INSERT INTO category_products (category, products) VALUES(?, ?)');
$stmtInsert->bind_param('ss', $key, $id);
foreach ($product['categories'] as $key) {
    $stmtInsert->execute();
}

As you can see here I have two separate queries and I execute the second one multiple times. This is faster, more secure and it will throw all the errors.

Barmar · Answer 2 · 2020-01-23T07:31:57.657

-1

The documentation of mysqli_multi_query() says:

Returns FALSE if the first statement failed. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first.

$updateCategories = mysqli_multi_query($connect, $updateCategoriesQ);
if($updateCategories){
    o('delete query ok');

    $insertCategories = mysqli_next_result($connect);
    if ($insertCategories) {
        o('insert query ok');
    } else {
        o('insert query failed: ' . mysqli_error($connect));
    }
} else {
    o('delete query failed: ' . mysqli_error($connect));
}

edited Jan 23 '20 at 07:31

answered Jan 23 '20 at 07:02

Barmar

741,623
53
500
612

I don't get any output from this – Whip Jan 23 '20 at 07:13

score -1 · Answer 3 · answered Jan 23 '20 at 07:32

-1

Okay, with the help of @Barmar and this answer here's a good way to capture errors on subsequent queries.

        $updateCategories = mysqli_multi_query($connect, $updateCategoriesQ);
        if($updateCategories){
            do {
                mysqli_next_result($connect);
                $result = mysqli_store_result($connect);
                if (!$result) {
                   o(mysqli_error($connect));
                }
            } while (mysqli_more_results($connect));
        } else {
            o(mysqli_error($connect));
        }

answered Jan 23 '20 at 07:32

Whip

1,891
22
43

You seem to have missed the better answer there: https://stackoverflow.com/a/58783101/1839439 **You should not execute `mysqli_multi_query()` with variable input!** – Dharman Jan 23 '20 at 23:46
I did not miss it. I understand the drawbacks and pitfalls of multi query and I decided to go with it anyway. Also I wanted a solution for this problem even if I don't use it. – Whip Jan 24 '20 at 06:36

PHP not throwing error for multiple queries

3 Answers3