mysqli returns error but the query works properly in phpmyadmin

Question

the problem is the following: I have a series of INSERT query packed in a php array. Through an implode function I send the total query to the db engine (using mysqli class).

PHP Code:

$query = array();
foreach ($intestazione as $i => $val) {
$query[] = "INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) "
            . "VALUES ('$id_tabella', '$val', '$i') ";
}
if ($matper_connection->query(implode(';',$query)) === FALSE) {
    die ("Query error: <br />".$matper_connection->error."<br>".implode(';<br />',$query));
}

Browser result:

Query non riuscita: 
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 `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'AfS',' at line 1
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'AfS Pulito', '0') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'AfS', '1') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'Denominazione', '2') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'TR di Riferimento', '3') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'Riferimento schematico', '4') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'Campo', '5') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'Applicabilità', '6') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'Evento', '7') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', '', '8') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'PER', '9') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'ROE', '10') ;
INSERT INTO `intestazioni` (`id_tabella`, `nome`, `ordine`) VALUES ('18', 'Note', '11')

If I copy the exact output text (also erasing the <br /> added during debugging) into SQL field of phpMySql, the total query works correctly.

I can't figure out what's the problem...

Thank's in advance for any advice...

MySQLi can't multiple queries in one `query()`. Either use a loop, or `mysqli_multi_query()`. — Qirel, Mar 23 '17 at 19:44
[Why not use an INSERT with multiple VALUE statements?](http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql) — Jay Blanchard, Mar 23 '17 at 19:49
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Mar 23 '17 at 19:53

Cultti · Answer 1 · 2017-03-24T17:57:00.820

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. http://php.net/manual/en/mysqli.quickstart.multiple-statement.php

For multiple queries in single command you have use for example mysqli::multi_query.

Basicly you can just change

$matper_connection->query(implode(';',$query)) === FALSE

to

$matper_connection->multi_query(implode(';',$query)) === FALSE

Documentation for mysqli::multi_query can be found at: http://php.net/manual/en/mysqli.multi-query.php

Suggestion

I would suggest that you modify your code so that inserts everything in one statement if you allow user input in your SQL. Example:

INSERT INTO 
    `intestazioni` (`id_tabella`, `nome`, `ordine`) 
    VALUES 
        ('18', 'AfS Pulito', '0'), 
        ('18', 'AfS', '1'), 
        ('18', 'Denominazione', '2'),
        ...

In this way you can use the normal mysql::query without worrying SQL injections that executes more than one queries. You still need to escape user's input to secure your queries.

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/15631457) — Tom, Mar 24 '17 at 15:54
@thebluefox thanks for feedback. I have edited my post to contain more information about this problem. — Cultti, Mar 24 '17 at 17:58

mysqli returns error but the query works properly in phpmyadmin

PHP Code:

Browser result:

1 Answers1

Suggestion