How to properly escape CSV file before import in SQL using PHP

Question

I have to build reporting website in which SQL data will be imported using exported csv file from another system.

I built the code but I cannot proceed it, to import in SQL, I just have message QUERY FAILED, not error from SQL or connection with database.

Could someone review my code and write me where is my mistake? I user escape on data because i have come data like... "John's company":

<?php

if(isset($_POST['import'])) {

$filename = $_FILES['file']['tmp_name'];

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

    while (($data = fgetcsv($file, 10000, ",")) !== FALSE ) {
        $data = array_map('mysqli_real_escape_string', $data);
        $query = "INSERT INTO purchasing ";
        $query .= "(
            purchase_req, 
            pr_date,  
            pr_item,  
            pr_created_by,  
            po_created_by,  
            purch_group,  
            purchase_ord,  
            po_date,  
            po_item,  
            po_state,  
            vendor_numb,  
            vendor_name,  
            mat_desc,  
            gl_acc,  
            cost_cent,  
            po_qty,  
            po_del_date,  
            po_delivered_qty,  
            po_to_be_del
        ) ";
        $query .= "VALUES ( 
            '" . $data[0] . "',  
            '" . $data[1] . "', 
            '" . $data[2] . "',  
            '" . $data[3] . "',  
            '" . $data[4] . "',  
            '" . $data[5] . "',  
            '" . $data[6] . "',  
            '" . $data[7] . "',  
            '" . $data[8] . "',  
            '" . $data[9] . "',  
            '" . $data[10] . "',  
            '" . $data[11] . "',  
            '" . $data[12] . "',  
            '" . $data[13] . "',  
            '" . $data[14] . "',  
            '" . $data[15] . "',  
            '" . $data[16] . "',  
            '" . $data[17] . "',  
            '" . $data[18] . "' 
        ) ";

        $send_to_database = mysqli_query($conn, $query);

        if(!$send_to_database) {
            die("QUERY FAILED <br>" . mysqli_error($conn));
        } else {
            header("Location: index.php");
        }
    }
    fclose($file);
}
}

?>

/// UPDATED CODE AFTER SUGGESTIONS

<?php

if(isset($_POST['import'])) {

$filename = $_FILES['file']['tmp_name'];

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

    $conn = new mysqli("localhost", "####", "####", "####");

    if($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

$stmt = $mysqli->prepare("INSERT INTO purchasing (purchase_req, pr_date, pr_item, pr_created_by, po_created_by, purch_group, purchase_ord, po_date, po_item, po_state, vendor_numb, vendor_name, mat_desc, gl_acc, cost_cent, po_qty, po_del_date, po_delivered_qty, po_to_be_del) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

    fgetcsv($file, 10000, ",");
    while(($data = fgetcsv($file, 10000, ",")) !== FALSE) {
        $data = fgetcsv($file);

        $stmt->bind_param("sssssssssssssssssss", $data[0], $data[1], $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $data[8], $data[9], $data[10], $data[11], $data[12], $data[13], $data[14], $data[15], $data[16], $data[17], $data[18]);

        $stmt->execute();
    }
    fclose($file); 
}
}

?>

With the second case, i print the array but again i can't send it to SQL...

Don't escape it, instead prepare it. Right now you are wide open to SQL injection from the file, It can happen that way easily. In fact this error . i user escape on data because i have come data like... "John's company" is doing sql Injection, which is why your query is broken. Your query is something like this ..."VALUES ('John's company', ...) see how the apos completes the first single quote VALUES ('John' [s company'], ...) then you have s company' just chilling there. — ArtisticPhoenix
– ArtisticPhoenix, Commented Mar 2, 2019 at 21:26
That is because you are injecting a ' into your SQL, you can avoid all this and more by simply using a prepared statement.... I would post some code, but there is probably a billion examples on the web how to do it. — ArtisticPhoenix
– ArtisticPhoenix, Commented Mar 2, 2019 at 21:31
What do you mean when you say "prepare it" i understand your point that i have problem because of data type in my csv, but how can i prevent it? How can i proceed this data in SQL? Could you please wrote my one line of code? — Martin M
– Martin M, Commented Mar 2, 2019 at 21:32
php.net/manual/en/mysqli.prepare.php Prepares the SQL query, and returns a statement handle to be used for further operations on the statement. The query must consist of a single SQL statement. The parameter markers must be bound to application variables using mysqli_stmt_bind_param() and/or mysqli_stmt_bind_result() before executing the statement or fetching rows. Basically instead of concatinating the data into the sql, you use a place holder, then send the data later. VALUES ( ?, ?, ?, ?) — ArtisticPhoenix
– ArtisticPhoenix, Commented Mar 2, 2019 at 21:33
as you wont have any single quotes in your sql VALUES ( ?, ?, ?, ?) then you don't have to worry about escaping them, because the DB knows that this is the SQL, and this is the DATA when you prepare it. Could you please wrote my one line of code? - No, just Google "MySqli Prepared statements" or such. — ArtisticPhoenix
– ArtisticPhoenix, Commented Mar 2, 2019 at 21:36

Bill Karwin · Accepted Answer · 2019-03-03 00:25:51Z

1

With your revised code, you're using prepare and bound parameters but you're missing out on one of the benefits of prepared statements: you can prepare once and execute many times with different values.

By the way, when you bind, you need a control string with as many characters as the number of parameters. In this case, you need a string of length 19.

$stmt = $mysqli->prepare("INSERT INTO purchasing (purchase_req, pr_date, pr_item, 
  pr_created_by, po_created_by, purch_group, purchase_ord, po_date, po_item, po_state, 
  vendor_numb, vendor_name, mat_desc, gl_acc, cost_cent, po_qty, po_del_date, 
  po_delivered_qty, po_to_be_del) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

while(($data = fgetcsv($file, 10000, ",")) !== FALSE) {
    $data = fgetcsv($file);

    $stmt->bind_param("sssssssssssssssssss", $data[0], $data[1], $data[2], $data[3], $data[4], $data[5], 
     $data[6], $data[7], $data[8], $data[9], $data[10], $data[11], $data[12],
     $data[13], $data[14], $data[15], $data[16], $data[17], $data[18]);

    $stmt->execute();
}

That's a little bit more efficient because MySQL doesn't have to re-parse the SQL statement for every row of your CSV input.

Tip: I prefer using PDO instead of mysqli, because it has a nicer interface for using prepared statements. You don't have to bind any variables. PDO does it for you if you pass the array of data as an argument to execute():

$stmt = $pdo->prepare("INSERT INTO purchasing (purchase_req, pr_date, pr_item, 
  pr_created_by, po_created_by, purch_group, purchase_ord, po_date, po_item, po_state, 
  vendor_numb, vendor_name, mat_desc, gl_acc, cost_cent, po_qty, po_del_date, 
  po_delivered_qty, po_to_be_del) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

while(($data = fgetcsv($file, 10000, ",")) !== FALSE) {
    $data = fgetcsv($file);
    $stmt->execute($data);
}

I am frequently puzzled why more PHP developers don't use PDO.

PDO got an undeserved reputation as being something exotic, because it wasn't included in default PHP installations way back in the PHP 5.0 days. But ever since PHP 5.1, PDO has been part of the official PHP distribution. It's strange that developers avoid it, even 15 years later!

One thing you need to get into the habit of doing is checking for errors. This is true in PHP and other languages too.

Mysqli functions return FALSE if there's an error. Then you should log the error so you can use that for troubleshooting.

Like this:

$stmt = $mysqli->prepare(...);
if ($stmt === false) {
    error_log($mysqli->error);
    die("Internal error");
}

$ok = $stmt->execute();
if ($ok === false) {
    error_log($stmt->error);
    die("Internal error");
}

You need to check for errors after every call to prepare() or execute(). It's a pain, but otherwise how will you know what went wrong?

Keep a window open tailing your http error log while you are developing and testing code, so you can see the errors if they are logged.

It's a good practice to output a generic error message to the user, because you don't want to reveal too much about your code to the user. But log more technical information to the error log so you can troubleshoot it yourself.

edited Mar 3, 2019 at 0:25

answered Mar 2, 2019 at 23:29

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martin M Over a year ago

I am new in PHP so i am still learning, but i will read about PDO also, thanks for your suggestion! By the way, i change the code, but it still not work, i have no idea where is my mistake, i have server error 500 now... code updated

Martin M Over a year ago

"PHP Fatal error: Call to a member function prepare() on null in" my upload.php file... line 16... this line contain my $stmt = $mysqli->prepare line of code

Bill Karwin Over a year ago

Sorry, your connection is in a variable called $conn. Your second code sample has both $conn and $mysqli. You only need to use one of those, whichever was the variable for the connection that you made with mysqli_connect(). It doesn't matter what the variable is named, you just have to use the one that you made for the connection.

Bill Karwin Over a year ago

And my example of $pdo->prepare() is meant to assume that one had previously created a PDO connection and stored it in a variable $pdo. Creating a PDO connection is documented here: php.net/manual/en/pdo.construct.php Again, the name of the variable is up to you.

Martin M Over a year ago

It cannot be too easy... i have to learn much more :) Thank you a lot!

GMB · Accepted Answer · 2019-03-02 22:57:26Z

Why not simply use the MySQL LOAD DATA INFILE syntax to load the file at once, with a single SQL query?

This MySQL buit-in is an efficient method to load a CSV file, while preventing from SQL injection and handling quoting issues. It also avoids opening and looping through the file with php. As per MySQL documentation:

When loading a table from a text file, use LOAD DATA. This is usually 20 times faster than using INSERT statements.

This should as simple as:

LOAD DATA INFILE '$filename'
    INTO TABLE purchasing(
        purchase_req, 
        pr_date, pr_item, 
        pr_created_by, 
        po_created_by, 
        purch_group, 
        purchase_ord, 
        po_date, 
        po_item, 
        po_state, 
        vendor_numb, 
        vendor_name, 
        mat_desc, 
        gl_acc, 
        cost_cent, 
        po_qty, 
        po_del_date, 
        po_delivered_qty, 
        po_to_be_del
    )
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' -- or maybe '\n'?
;

NB : the OPTIONALLY ENCLOSED BY '"' option should handle field values like "John's company". There are many more useful options to LOAD DATA INFILE, that are described in the above linked documentation.

Collectives™ on Stack Overflow

How to properly escape CSV file before import in SQL using PHP

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related