UPDATE request for SQL fails on querying from PHP but works good on SQL command line

Question

I am developing a page for stock maintenance. I am encountering a bizarre issue. When I run the PHP through AJAX everything works fine except for the mysqli_query part.

<?php
include 'dbconnector.php';
$item_code  =   json_decode(filter_input(INPUT_POST,'icode')) or die('line1');
$item_count =   json_decode(filter_input(INPUT_POST,'icount')) or die('line2');

$sql_string =   '';

for($i=0; $i<sizeOf($item_code); $i++)
{
    $sql_string.=" UPDATE stock_main SET iSTOK=".$item_count[$i]." WHERE iCODE=\"".$item_code[$i]."\"; ";
}

    echo $sql_string;
$query_sql  =   mysqli_query($dbconnector,$sql_string);
if($query_sql>0)
    echo 'SUCCESS';
else
    echo mysqli_error($query_sql); 
?>

The issue is when I run for a single statement it works fine. When multiple statements are concatenated it returns false. But the multiple statement scenario works fine in phpMyAdmin command-line. When tried to check why it fails mysqli_error returns saying the argument should be of mysqli type but it was boolean.

Answer 1

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough!

Do not use mysqli_multi_query() as suggested by other answers as it only makes you more vulnerable to SQL injection. You really do not need this function! Use prepared statements, which are not only easier and safer, but also less error prone.

// Switch error reporting!
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
include 'dbconnector.php';
$item_code = json_decode(filter_input(INPUT_POST, 'icode')) or die('line1');
$item_count = json_decode(filter_input(INPUT_POST, 'icount')) or die('line2');

// Prepare query
$stmt = $dbconnector->prepare('UPDATE stock_main SET iSTOK=? WHERE iCODE=?');

// bind and execute multiple times
for ($i = 0; $i < sizeof($item_code); $i++) {
    $stmt->bind_param('ss', $item_count[$i], $item_code[$i]);
    $stmt->execute();
}