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.

score 0 · Accepted Answer · answered Dec 07 '19 at 15:55

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();
}

So what you are suggesting is that I should opt for mysqli_query in a loop instead of going for multi_query right? — Arunkumar Palanisamy, Dec 08 '19 at 05:30
No! I am clearly stating that you should use prepared statements in a loop. — Dharman, Dec 08 '19 at 08:59

score -1 · Answer 2 · answered Dec 07 '19 at 07:35

-1

mysqli_query function does not support multiple sql query at one time. It's recommended to use mysqli_multi_query() function;

Your line of code will be like this:-

$query_sql = mysqli_multi_query($dbconnector, $sql_string);

answered Dec 07 '19 at 07:35

Dinkar Kumar

49
2

Armino Popp · Answer 3 · 2019-12-08T14:39:18.320

In order to use multiple queries, you can use

mysqli_multi_query ( mysqli $link , string $query )

From the PHP manual:

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. An attacker may try to add statements such as ; DROP DATABASE mysql or ; SELECT SLEEP(999). If the attacker succeeds in adding SQL to the statement string but mysqli_multi_query is not used, the server will not execute the second, injected and malicious SQL statement.

So your code should be:

<?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_multi_query($dbconnector, $sql_string);
if($query_sql>0)
    echo 'SUCCESS';
else
    echo mysqli_error($query_sql); 
?>

Check more on Php.net Multiple Statements

For clarification

As others suggested, it is better to use prepared statements from a SQL Injection Attack perspective, as the data will be automatically formatted and filtered. However, this doesn't mean that normal queries are completely bad, as long as you take all the necessary steps to ensure you are protected.

In fact from a security perspective, Dharman is right. Prepared statements are more suited to protect against SQL injection as you will not have to worry about manually formatting or filtering the data. However, you should still validate your user input to prevent XSS attacks. — Armino Popp, Dec 07 '19 at 17:16

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

3 Answers3

For clarification