import csv pdo msql

Question

nadiam 0 Posting Pro in Training

9 Years Ago

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!

csv import msql pdo php

2 Contributors
10 Replies
3K Views
1 Month Discussion Span
Latest Post 9 Years Ago Latest Post by cereal

All 10 Replies

cereal 1,524 Nearly a Senior Poster

9 Years Ago

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

cereal 1,524 Nearly a Senior Poster

9 Years Ago

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.

cereal 1,524 Nearly a Senior Poster

9 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

nadiam 0 Posting Pro in Training · Answer 1 · 2014-07-17T01:44:33+00:00

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.

nadiam 0 Posting Pro in Training · Answer 2 · 2014-07-17T10:03:22+00:00

nadiam 0 Posting Pro in Training

9 Years Ago

doesn't the data get sanitized during binding?

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 3 · 2014-07-17T10:27:58+00:00

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:

nadiam 0 Posting Pro in Training · Answer 4 · 2014-07-18T01:43:59+00:00

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

nadiam 0 Posting Pro in Training · Answer 5 · 2014-07-18T04:00:52+00:00

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

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 6 · 2014-07-18T10:39:02+00:00

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 0 Posting Pro in Training · Answer 7 · 2014-08-28T09:46:24+00:00

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!

import csv pdo msql

Recommended Answers Collapse Answers

All 10 Replies

Recommended Answers