0

It is first time to create a smart uploader that uploads and validates also. Here I have included my code for trying to call a procedure whilst submitting the csv file thru a form, php doesn't complete my code and doesn't show any errors. Code goes as follows. Your help is highly appreciated. Thank you

<?php
error_reporting(-1);
error_reporting(E_ERROR | E_PARSE);
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("localhost","root","password") or die("Could not connect");
mysqli_select_db($conn,"db") or die("could not connect database");

$procedure="
drop procedure if exists dV;
DELIMITER $$ 
CREATE PROCEDURE dV(IN td, ts, cd, pc, lt, nt, xp, fn, sn, ds, pp, uploadedby, op, month, year)
BEGIN
            INSERT INTO temp(`td`, `ts`, `cd`, `pc`, `lt`, `nt`, `xp`, `fn`, `sn`, `ds`, `pp`, `uploadedby`, `op`, `month`, `year`) VALUES 
            ('$td','$ts','$cd','$pc','$lt','$nt','$xp', '$fn', '$sn', '$ds', '$pp', '$ip', '$op', '$month', '$year');
            update anew set xp = true where length(lt) < 6;
END$$ 
DELIMITER ;
";

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];
  $lt= $csv[4];
  $nt= $csv[5];
  $xp = $csv[6];
  $firstname = $csv[7];
  $surname = $csv[8];
  $ds= $csv[9];
  $pp= $csv[10];

  if(mysqli_query($connect, "DROP PROCEDURE IF EXISTS dV"))  
           {  
                if(mysqli_query($connect, $procedure))  
                {  
                     $query = "CALL dV('".$ct."', '".$ts."', '".$cd."', '".$pc."', '".$lt."', '".$nt."', '".$xp."', '".$firstname."', '".$surname."', '".$ds."', '".$pp."', '".$uploadedby."', '".$op."', '".$month."', '".$year."')";  
                     mysqli_query($connect, $query);  
                     echo "<script type=\"text/javascript\">
                            alert(\"File uploaded successfully\");
                            window.location.href = '/insights/datauploader/';
                            
                        </script>"; 
     
                }
                else
                {
                    
                    echo"<script type=\"text/javascript\">
                            alert(\"There is some error. Please try again.\");
                            window.location.href = '/insights/datauploader/';
                        </script>";
                
                }
           } 
 }
}
?>
Ryham Ali Maher Abouelnour
  • 11
  • 6
  • 1
    You may be better off just executing an INSERT statement rather than creating a procedure. You also should read about prepared statements for safety. – Nigel Ren Aug 04 '21 at 07:22
  • it is for an internal use only, no serious threats that exist – Ryham Ali Maher Abouelnour Aug 04 '21 at 07:29
  • That's what they all say. Don't trust _any_ inputs from _anywhere_ ... Don't assume your perimeter security cannot be breached or that internal staff are 100% trustworthy or cannot be duped by phishing etc. Also, prepared statements will protect against other more basic problems such as quite marks in the input data which would break the SQL syntax. – ADyson Aug 04 '21 at 07:37
  • Anyway you are not checking for errors in all cases - instead of all the nested `if`s which don't all have error handling, see here for a simpler way, just letting mysqli throw errors naturally: https://stackoverflow.com/a/22662582/5947043 – ADyson Aug 04 '21 at 07:45
  • Yes I was switching all errors and warnings off, now I have turned it on again and it gives notices with undefined variables for all columns and also for variable connect. – Ryham Ali Maher Abouelnour Aug 04 '21 at 08:03
  • Yes I am considering using prepared stmt rather also, and I know that creating a procedure is not the most safe way for handling forms – Ryham Ali Maher Abouelnour Aug 04 '21 at 08:05
  • The stored procedure isn't the unsafe part, it's the lack of prepared statement and parameters regardless of the query type). You can parameterise the inputs to a procedure. And you don't need to re-declare the procedure every time, just define it in the database and define some input arguments to it. Then call it from PHP and pass the arguments in as parameters. But yeah you don't _need_ a stored procedure here, you could just execute two separate statements. You can wrap them in a transaction if you need the operation to be atomic. – ADyson Aug 04 '21 at 08:56
  • Anyway it makes total sense that the variables are not defined because you're trying to use them in the SQL string **before** you've read them from the CSV! Therefore they don't exist yet. If you use a prepared statement you can prepare it once before the loop, and then re-bind the parameters and execute it repeatedly within the loop. But that won't work if you're just hard-coding the values into the SQL like you're doing now – ADyson Aug 04 '21 at 08:58
  • Thank you for detailed information. I am only a humble small experience in code writing, not that expert (: , however I am interested also to read more about wrapping up stmts in a single transaction. Guess this will provide an immediate solution for issue that I am facing, afterwards I will be focusing on prepared stmt for preventing injection. Thank you again so much! – Ryham Ali Maher Abouelnour Aug 04 '21 at 10:17
  • No the prepared statement is more likely to be the solution - as I've explained already - than the transaction, so please do that first – ADyson Aug 04 '21 at 12:07
  • @ADyson I have managed to do this task thru a combination of prepared statement and a predefined procedure with the update queries that I want to apply. It works but it seems that number of update statements are not executed by mysql server or being skipped without giving any errors. Is there any way to work this around so all statements in my stored procedure not get skipped by mysql? – Ryham Ali Maher Abouelnour Aug 05 '21 at 08:28
  • Did you implement the change so that mysqli will throw errors when a query fails, as per my earlier comment (https://stackoverflow.com/questions/68646982/trying-to-upload-a-csv-file-to-mysql-db-using-php-in-a-form-thru-a-procedure-but?noredirect=1#comment121318429_68646982)? – ADyson Aug 05 '21 at 09:21
  • @ADyson yes, moreover I tried executing each query and they work, just putting them out of the procedure. I have enabled error logs also, have encorporated try and catch, signal and resignal with no hope to find out what causes statements not to execute only inside of the stored proedure. – Ryham Ali Maher Abouelnour Aug 05 '21 at 09:28

0 Answers0