phpmyadmin Question posted in
The official documentation can be found here.

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

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.

Tags:

3

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search