import csv pdo msql

[nadiam]

hi, i have an import csv script but it does not get entered into the database. only "uploaded successfully" and the data is echoed but nothing in the database is something wrong with it? and is it sql injection safe?

<?php
if(isset($_SESSION['sess_user_id']))
{
    if (isset($_POST['ubmit'])) {

        require "connection.php";

        $session = $_SESSION['sess_user_id'];

        if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
            echo "<h1>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h1>";
            echo "<h2>Displaying contents:</h2>";
            readfile($_FILES['csv']['tmp_name']);
        }
        //Import uploaded file to Database
        $handle = fopen($_FILES['csv']['tmp_name'], "r");

        $import=$dbh->prepare("INSERT INTO contact1(
            user_id,
            salutation,
            fname,
            lname,
            dob,
            house,
            mobile,
            office,
            email,
            spouse_name) VALUES(?,?,?,?,?,?,?,?,?,?");

        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $import->execute($data);
        }

        fclose($handle);
    }
}
?>

TIA!

[cereal]

Hi, try to add errorInfo(), for example:

if (!$import) {
    echo "<p>Prepared statement error:</p>";
    echo "<pre>" .print_r($dbh->errorInfo(), true) . "</pre>";
}

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $import->execute($data);
    echo "<p>Execute error:</p>";
    echo "<pre>" .print_r($import->errorInfo(), true) . "</pre>";
    break;
}

The break will stop the loop after the first execution. If there are no errors, then remove break and check the full file. It could be an extra column or a non null column receiving null or an empty value, for example:

aaa, bbb, , ddd, eee

Docs: http://php.net/manual/en/pdo.errorinfo.php

[nadiam]

fixed it. thank you.

final:

<?php
if(isset($_SESSION['sess_user_id']))
{
    if (isset($_POST['ubmit'])) {

        require "connection.php";
        $session = $_SESSION['sess_user_id'];

        //upload file
        if (is_uploaded_file($_FILES['csv']['tmp_name'])) {
            echo "<h1>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h1>";
            echo "<h2>Displaying contents:</h2>";
            readfile($_FILES['csv']['tmp_name']);
        }
        //Import uploaded file to Database
        $handle = fopen($_FILES['csv']['tmp_name'], "r");

        $import=$dbh->prepare("INSERT INTO contact1(
                user_id,
                salutation,
                fname,
                lname,
                dob,
                house,
                mobile,
                office,
                email,
                spouse_name) VALUES(
                ?,?,?,?,?,?,?,?,?,?)");
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            $import->bindParam(1, $session, PDO::PARAM_INT);
            $import->bindParam(2, $data[0], PDO::PARAM_STR);
            $import->bindParam(3, $data[1], PDO::PARAM_STR);
            $import->bindParam(4, $data[2], PDO::PARAM_STR);
            $import->bindParam(5, $data[3], PDO::PARAM_STR);
            $import->bindParam(6, $data[4], PDO::PARAM_STR);
            $import->bindParam(7, $data[5], PDO::PARAM_STR);
            $import->bindParam(8, $data[6], PDO::PARAM_STR);
            $import->bindParam(9, $data[7], PDO::PARAM_STR);
            $import->bindParam(10, $data[8], PDO::PARAM_STR);
            $import->execute();
        }

        fclose($handle);
    }
}
?>

though i do hope its correct way.

[cereal]

Yes, that's the correct and safe method to avoid SQL injections, but you always need to sanitize data because some javascript could be submitted:

...
aaa4,bbb4,,ddd4,eee4
aaa5,<script>alert('hello');</script>,ccc5,ddd5,eee5
aaa6,bbb6,ccc6,ddd6,eee6
...

and when you load it into a page it will execute.

[nadiam]

doesn't the data get sanitized during binding?

[cereal]

No PDO::PARAM_STR for example:

$import->bindParam(2, $data[0], PDO::PARAM_STR);

is a constant that will check only if the input is a string, instead of an integer, null or a boolean, but it doesn't remove HTML or Javascript.

Prepared statements are used to prevent SQL injections, but it does nothing to remove scripts, in your loop you can add filter_var() to sanitize the array, for example:

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

    # removing empty spaces
    $data = array_map('trim', $data);

    # remove tags and encode special characters
    $data = array_map(function($row){
        return filter_var($row, FILTER_SANITIZE_STRING, FILTER_SANITIZE_FULL_SPECIAL_CHARS);
    }, $data);

    $import->bindParam(1, $session, PDO::PARAM_INT);
    $import->bindParam(2, $data[0], PDO::PARAM_STR);
    $import->bindParam(3, $data[1], PDO::PARAM_STR);
    $import->bindParam(4, $data[2], PDO::PARAM_STR);
    $import->bindParam(5, $data[3], PDO::PARAM_STR);
    $import->bindParam(6, $data[4], PDO::PARAM_STR);
    $import->bindParam(7, $data[5], PDO::PARAM_STR);
    $import->bindParam(8, $data[6], PDO::PARAM_STR);
    $import->bindParam(9, $data[7], PDO::PARAM_STR);
    $import->bindParam(10, $data[8], PDO::PARAM_STR);
    $import->execute();
}

Docs:

• http://php.net/manual/en/function.filter-var.php
• http://php.net/manual/en/filter.filters.sanitize.php
• http://php.net/manual/en/filter.examples.sanitization.php

[nadiam]

thanks cereal i will read up those docs u linked. I learned something today!

[nadiam]

how do i check if a csv file has headers then if it does have headers ignore them ?

[cereal]

If you're sure the first line is always an header then you can skip it:

$i = 0;
while()
{
    # skip condition
    if($i == 0) continue;

    # other code ...

    $i++;
}

otherwise you can check if it matches with a previously hardcoded value, for example, here the first line is an header:

column_1, column_2, column_3, column_4, column_5
aaa1,     bbb1,     ccc1,     ddd1,     eee1
aaa2,     bbb2,     ccc2,     ddd2,     eee2
aaa3,     bbb3,     ccc3,     ddd3,     eee3

And the script:

# value to match
$check = 'column_1';

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

    $data = array_map('trim', $data);
    $data = array_map(function($row)
        {
            return filter_var($row, FILTER_SANITIZE_STRING, FILTER_SANITIZE_FULL_SPECIAL_CHARS);
        }, $data);

    # if you match the value, then skip this loop
    if(in_array($check, $data)) continue;

    $import->bindParam(1, $data[0], PDO::PARAM_STR);
    $import->bindParam(2, $data[1], PDO::PARAM_STR);
    $import->bindParam(3, $data[2], PDO::PARAM_STR);
    $import->bindParam(4, $data[3], PDO::PARAM_STR);
    $import->bindParam(5, $data[4], PDO::PARAM_STR);
    $import->execute();
}

This will prevent the insert of repeated headers, in case of merged files.

[nadiam]

so this is my code now (have not added the ignore header part yet) and all i did was tweaked it a bit,as it is basically the same as my code before(see above) but its not working.

if(isset($_SESSION['sess_user_id']))
{
    if(isset($_POST['ubmit'])) {

        require "connection.php";
        $session = $_SESSION['sess_user_id'];

        //Import uploaded file to Database
        $handle = fopen($_FILES['csv']['tmp_name'], "r");

        $import=$dbh->prepare("INSERT INTO contact1(
                user_id,
                salutation,
                fname,
                mname,
                lname,
                dob,
                personalno,
                houseno,
                personalemail,               
                houseadd,
                officeadd,
                secretary,
                pano,
                officeno,
                faxno,            
                emailpa,
                emailwork) VALUES(
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            //remove the empty spaces
            $data = array_map('trim', $data);

            $data = array_map(function($row){
                return filter_var($row, FILTER_SANITIZE_STRING, FILTER_SANITIZE_FULL_SPECIAL_CHARS);
            }, $data);

            $import->bindParam(1, $session, PDO::PARAM_INT);
            $import->bindParam(2, $data[0], PDO::PARAM_STR);
            $import->bindParam(3, $data[1], PDO::PARAM_STR);
            $import->bindParam(4, $data[2], PDO::PARAM_STR);
            $import->bindParam(5, $data[3], PDO::PARAM_STR);
            $import->bindParam(6, $data[4], PDO::PARAM_STR);
            $import->bindParam(7, $data[5], PDO::PARAM_STR);
            $import->bindParam(8, $data[6], PDO::PARAM_STR);
            $import->bindParam(9, $data[7], PDO::PARAM_STR);
            $import->bindParam(10, $data[8], PDO::PARAM_STR);
            $import->bindParam(11, $data[9], PDO::PARAM_STR);
            $import->bindParam(12, $data[10], PDO::PARAM_STR);
            $import->bindParam(13, $data[11], PDO::PARAM_STR);
            $import->bindParam(14, $data[12], PDO::PARAM_STR);
            $import->bindParam(15, $data[13], PDO::PARAM_STR);
            $import->bindParam(16, $data[14], PDO::PARAM_STR);
            $import->bindParam(17, $data[15], PDO::PARAM_STR);
            $import->execute();
            if($import->rowCount() > 0)
            {
                echo "<script type='text/javascript'>
                    window.location.href = 'contact_data.php';
                </script>";
                echo "<h1>" . "File ". $_FILES['csv']['name'] ." uploaded successfully." . "</h1>";
            }
            else
            {
                echo "smtg wrong w query.";
            }
        }

        fclose($handle);
    }
}

"smtg wrong w query." is echoed when i try to import csv. Can someone help me out here please? TIA!

[cereal]

Add errorInfo() as suggested in my first post. From there you can see why the query fails.