why multi query runs only once in a form?

Question

this is Ryham, I am a newbie at code writing and that code I need to know what makes it run successfully but for only one row in my csv file. Code goes as follows. Thank you for any help with this. (:

<?php
ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);
ini_set('max_execution_time',0);
ini_set('upload_max_filesize', '50M');
ini_set('post_max_size', '50M');
ini_set('max_input_time', 0);
ini_set("memory_limit", "-1");
set_time_limit(0);
$ip = getenv('REMOTE_ADDR');
$conn=mysqli_connect("somehost","someusr","somepassword", "somebd") or die("Could not connect");



if(isset($_POST["submit_file"]))
{
 $op = $_POST['op'];
 $month = $_POST['month'];
 $year = $_POST['year'];
 $file = $_FILES["file"]["tmp_name"];
 $file_open = fopen($file,"r");
 while(($csv = fgetcsv($file_open,1000, ",")) !== false)
 {
  $ct = $csv[0];
  $ts = $csv[1];
  $cd = $csv[2];
  $pc = $csv[3];
  $query="INSERT INTO anew(`ct`, `ts`, `cd`, `pc`, `uploadedby`, `op`, `month`, `year`) VALUES ('$ct','$ts','$cd','$pc','$ip', '$op', '$month', '$year'); update anew set wtd = true";
  $result= $conn->multi_query($query);
  if($result)
                {

                    echo "<script type=\"text/javascript\">
                            alert(\"file is upload successfully\");
                            window.location.href = '/insights/datauploader/';
                            
                        </script>";
                
                }
                else{echo"<script type=\"text/javascript\">
                            alert(\"there is some error\");
                            window.location.href = '/insights/datauploader/';
                        </script>";}
 }
}
?>

Perhaps `$csv` returns `false` after the first iteration, perhaps you should debug and tell us more after you have debugged. — Jaquarh, Aug 04 '21 at 14:12
@Jaquarh is there any specific way for doing this the debug? — Ryham Ali Maher Abouelnour, Aug 04 '21 at 14:18
@MRDev Problem is that only one row out of 10 rows from my testing csv file gets inserted to my table in database — Ryham Ali Maher Abouelnour, Aug 04 '21 at 14:19
Well, yeah.. `if($csv === false) var_dump($csv)` or check the dogs if `fgetcsv()` returns an error you can catch — Jaquarh, Aug 04 '21 at 14:23
@MRDev that won't make a difference, the server doesn't execute JavaScript, the Browser does. The likely issue is that an exception is being thrown somewhere or `$csv` returns false after the first iteration.. more debugging is needed to answer this question. — Jaquarh, Aug 04 '21 at 14:28
Also, you can just use `list($ct, $ts, $cd, $pc) = array_slice($csv, 0, 4);` for assignment, your code is so hard to follow along with. — Jaquarh, Aug 04 '21 at 14:32
I would suggest printing $csv inside while loop because if your CSV file includes a blank line then fgetcsv() will return null. And on accessing $csv[0] it should exit with an error. — Sibi Kandathil, Aug 04 '21 at 14:33
You're open to SQLi attacks also, [use prepared statements](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Jaquarh, Aug 04 '21 at 14:45
@Jaquarh would this make a difference? nothing is wrong with csv part as I guess, it works fine only when I don't apply the multi query thing, all data get uploaded. It is only when I attempted to get a multiple query in the picture, this problem only happens. — Ryham Ali Maher Abouelnour, Aug 04 '21 at 14:57
And yes, I am getting this but this code is for internal use only that was why I was not pricey about getting it done using prepared stmt — Ryham Ali Maher Abouelnour, Aug 04 '21 at 14:58
What is meant by `multi query in the picture`? You cannot store a photo in a database, if this is what you're doing, that is your issue. I guess you could base 64 it and store it that way if its a requirement but you should store the photo on the server as a file then store the path to that photo in the database. & [Inside attacks](https://www.imperva.com/learn/application-security/insider-threats/#:~:text=An%20insider%20threat%20is%20a,and%20who%20misuses%20this%20access.) are still a thing. @RyhamAliMaherAbouelnour — Jaquarh, Aug 04 '21 at 15:11
@Jaquarh what I meant by picture was in context of bringing multi query to the equation, obviously I am not trying to upload any pictures in here (: — Ryham Ali Maher Abouelnour, Aug 04 '21 at 15:38
You should never ever use `multi_query`. Use prepared statements always. — Dharman, Aug 04 '21 at 15:49

Jaquarh · Accepted Answer · 2021-08-04T15:46:01.107

If the multi_query is the issue. You can pass the wtd into the INSERT, there is no need to update the row after it gets inserted.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
ini_set('max_execution_time',0);
ini_set('upload_max_filesize', '50M');
ini_set('post_max_size', '50M');
ini_set('max_input_time', 0);
ini_set("memory_limit", "-1");
set_time_limit(0);

if(array_diff([ # all keys should be checked, not just a submit button
    'op',
    'month',
    'year',
    'file'
], array_keys($_POST))) die('Missing required parameters.');

$ip = getenv('REMOTE_ADDR');

$db = new PDO('mysql:host=somehost;dbname=somedb;port=3306', 'someuser', 'somepass', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

# transaction block for integrity in case of large CSV files
$db->beginTransaction();

while(($csv = fgetcsv(file_get_contents($_FILES['file']['tmp_name']), 1000, ',')) !== false)
{
    list($ct, $ts, $cd, $pc) = array_splice($csv, 0, 4);
    
    ($db->Prepare('INSERT INTO anew(`ct`, `ts`, `cd`, `pc`, `uploadedby`, `op`, `month`, `year`, `wtd`) VALUES (?, ?, ?, ?, ?, ?, ? ,?, ?)'))
    ->execute([
        $ct,
        $ts,
        $cd,
        $pc,
        $_POST['op'],
        $_POST['month'],
        $_POST['year'],
        true # Why the update? You can override default values in an insert
    ]);
}

$db->commit();

I used Prepared statements to prevent from SQLi for you...

why multi query runs only once in a form?

1 Answers1