MySQL error while uploading large CSV data set

Question

I've got the below code snippet which works fine when I try to upload a CSV file with relatively smaller (~500) entries. However when the entries get larger (I tried 20,000) it triggers the following error.

You have an error in your sql syntax check the manual that corresponds to your mysql server version for the right syntax to use near 'S FANCY-KNDY0046','1298','32321','QE4224','2')' at line 1

My INSERT into looks like this: (Haven't really thought of sanitizing the input here yet but your thoughts would come in handy too!)

if($_FILES["file"]["size"] > 0)
     {
        $file = fopen($filename, "r");
         while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
         {
           $sql = "INSERT into universe (`zone`, `area`, `sub`) values('$emapData[0]','$emapData[1]','$emapData[2]')";

$result = mysqli_query($conn, $sql);
                if(!$result )
                {
                    echo "ERROR:". mysqli_error($conn); // I included this to see the error message.
                    echo "<script type=\"text/javascript\">
                            alert(\"Invalid File:Please Upload CSV File.\");
                            window.location = \"index.php\"
                        </script>";
            }
         }
         fclose($file);
         echo "<script type=\"text/javascript\">
                    alert(\"CSV File has been successfully Imported.\");
                    window.location = \"index.php\"
                </script>"; 
        mysqli_close($conn); 
     }

Appreciate if someone can point me if I'm doing something wrong here! Thank you.

add your whole error message here `You have an error in your sql syntax check the manual that corresponds to your mysql server version for the right syntax to use near..` — RAUSHAN KUMAR, Sep 01 '18 at 09:22
Might there be special characters in the larger file that aren't escaped properly? — jrswgtr, Sep 01 '18 at 09:24
@RAUSHANKUMAR added the whole line as requested. What you see there is the actual data getting inserted. — Mohan Wijesena, Sep 01 '18 at 09:27
check the data in your csv file just before the `S FANCY-KNDY0046`, may be there is some special characters which causes terminating the query. possibaly a quote(') — RAUSHAN KUMAR, Sep 01 '18 at 09:30
If you started to use prepared statements and bind variables, you would most likely see this problem disappear as quotes etc will cause all sorts of issues. — Nigel Ren, Sep 01 '18 at 09:31

score 1 · Accepted Answer · answered Sep 01 '18 at 13:09

Quite simply you have a ' in the string that you are wrapping with '' and therefore the string is being broken early and causing a syntax error.

If you were using parameterised queries this would not have any effect on the query, and you would be protecting yourself from SQL Injection Attack

if($_FILES["file"]["size"] > 0) {
    $file = fopen($filename, "r");

    // prepare the query once and reuse lots of times
    // reduces load on the server as well as the database
    // only has to compile and optimize the query once
    $sql = "INSERT into universe (`zone`, `area`, `sub`) 
            VALUES(?,?,?)";
    $stmt = $conn->prepare($sql);

    while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE) {
        $stmt->bind_param('sss', $emapData[0], $emapData[1], $emapData[2]);

        $result = $stmt->execute();
        if(!$result ) {
            echo "ERROR:". $conn->error; // I included this to see the error message.
            echo "<script type=\"text/javascript\">
                  alert(\"Invalid File:Please Upload CSV File.\");
                  window.location = \"index.php\"
                  </script>";
        }
    }
    fclose($file);
    echo "<script type=\"text/javascript\">
          alert(\"CSV File has been successfully Imported.\");
          window.location = \"index.php\"
          </script>"; 
 }

Yes! that makes a lot of sense to query once and reuse multiple times. Thank you. This helps! — Mohan Wijesena, Sep 01 '18 at 13:33

MySQL error while uploading large CSV data set

1 Answers1