Mysqli doesn't allow multiple queries?

Question

I am running a script in PHP that uisng a loop creates a string query for MySQL.

After executing the script I get the following 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 'UPDATE BANNERS SET pos=1 WHERE BID=5; UPDATE BANNERS SET pos=2 WHERE BID=1' at line 2"

right after the error I echo the query and it looks like this:

UPDATE BANNERS SET pos=0 WHERE BID=6;
UPDATE BANNERS SET pos=1 WHERE BID=5;
UPDATE BANNERS SET pos=2 WHERE BID=1;

When I copy and paste it into phpmyadmin, it obviously gets executed without any problem.

Any Ideas?

Here is the PHP code:

There is an array that looks like this:

$order[0] = 'tr_6';
$order[1] = 'tr_5';
$order[2] = 'tr_1';

$query = "";

foreach($order as $pos => $value){
   $idvalue = str_replace('tr_','',$value);
   $query .= "UPDATE BANNERS  SET pos=$pos WHERE BID=$idvalue;\n";
}

mysqli_query($connection,$query) or die(mysqli_error($connection)."<br/>$query");

Thanks!

You **cannot** do it that way. You have to query in your foreach loop and remove `;\n` in your queries — Justin Iurman, Feb 15 '14 at 22:21
the \n I just added it now to create a nice output and read the query, but even without it it gives me the error, Are you saying I cant execute multiple queries at once? If I copy the whole thing into phpmyadmin and execute the query it works just fine — multimediaxp, Feb 15 '14 at 22:22
Well maybe you can try to execute multiple queries at once but you have to remove `\n`. I don't know if `mysqli_query` allows it, try it and you will see — Justin Iurman, Feb 15 '14 at 22:26
That;s how it was at the beginning, without \n, So that is the problem? Mysqli doesn't allow multiple queries? — multimediaxp, Feb 15 '14 at 22:27
Use mysqli_multi_query() http://www.php.net/manual/en/mysqli.multi-query.php — Mihai, Feb 15 '14 at 22:32
@Mihai - No, don't. Vulnerable to SQL injection attacks. See my comment on the accepted answer, that links to further detail, and what to do instead. — ToolmakerSteve, Aug 14 '20 at 01:18

score 0 · Accepted Answer · answered Feb 15 '14 at 22:32

0

mysqli allow multiple queries with mysqli_multiple_query function like this:

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

/* execute multi query */
if (mysqli_multi_query($link, $query)) {
    do {
        /* store first result set */
        if ($result = mysqli_store_result($link)) {
            while ($row = mysqli_fetch_row($result)) {
                printf("%s\n", $row[0]);
            }
            mysqli_free_result($result);
        }
        /* print divider */
        if (mysqli_more_results($link)) {
            printf("-----------------\n");
        }
    } while (mysqli_next_result($link));
}

note that you need to use semicolon after each query.

answered Feb 15 '14 at 22:32

Volkan Ulukut

4,230
1
20
38

added full example from php.net. – Volkan Ulukut Feb 15 '14 at 22:35
See [Your Common Sense's answer on a different question, re security and SQL injection](https://stackoverflow.com/a/58783101/199364). Bottom line: **Using mysqli_multi_query opens yourself up to SQL injection attacks. To be safe, don't call mysqli_multi_query.** Instead, call `mysqli_query` multiple times, with one query string each time. (If writing a function to help with this, pass in an array of queries - don't break apart a multi-query; might be fooled by certain attacks.) This will avoid some but not all SQL injection attacks. [**To avoid *all* attacks, *parameterize* all queries.**] – ToolmakerSteve Aug 14 '20 at 01:13

Mysqli doesn't allow multiple queries?

1 Answers1

Linked

Related