CODEWITHSUNDEEP
phpcsvinsert
Irene Ling
Irene Ling

Reputation: 1971

Insert csv data into database with single quote in data

if (($handle = fopen($source_file, "r")) !== FALSE) {
    $columns = fgetcsv($handle, $max_line_length, ",");
    foreach ($columns as &$column) {
        $column = str_replace(".","",$column);
    }
    while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
        while(count($data) < count($columns)) {
            array_push($data, NULL);
        }
        $c = count($data);
        for($i = 0; $i < $c; $i++) {
            $data[$i] = "'{$data[$i]}'";
        }

        $sql[] = '(' . implode(',', $data) . ", '" . $_POST['custgroup'] . "'," . $_POST['user_id'] . ')';
    }

 $db = new PDO("mysql:host=localhost;dbname=test;","root",""); 

 $insert = $db->prepare("INSERT INTO $target_table (". implode(',', $columns) .',custgroup,user_id) VALUES ' .implode(',', $sql));
 $insert->execute();

I have a script like this which will insert csv data into database according to header,and 2 extra datas which is group and user id. It works well , but when my name in csv file is Sher's Aria then it will have error.I know it's because of the symbol single quote in the name, ' , so what can I do to solve this problem so that any name with single quote can be inserted as well?

Thank you.

Edit:

function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) {

if($source_file != '')
  {

    if (($handle = fopen($source_file, "r")) !== FALSE) {
     $columns = fgetcsv($handle, $max_line_length, ",");
     $esc_columns = array();
         foreach ($columns as &$column) {
            $column = str_replace(".","",$column);
            $esc_columns[] = escapeSqlName($column);
          }

      $esc_columns[] = escapeSqlName('custgroup');
      $esc_columns[] = escapeSqlName('user_id');

      $sqlsmttempl = 'INSERT INTO %s (%s) VALUES (%s)';
      $sqlsmt = sprintf($sqlstmttempl,
      escapeSqlName($target_table), 
      implode(',', $esc_columns), 
      implode(',',array_fill(0, count($esc_columns), '?')) // the parameter placeholders
 );

$db = new PDO("mysql:host=localhost;dbname=test;","root",""); 

$insert = $db->prepare($sqlsmt);  

while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
    while(count($data) < count($columns)) {
        $data[] = NULL;
    }
    $data[] = $_POST['custgroup'];
    $data[] = $_POST['user_id'];


 if($insert->execute($data))
 {
   header("Location:customer-search.php");
 }
else
{
echo "no";
 }

}

 fclose($handle);
   }
 }


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

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

csv_file_to_mysql_table($file,$table);
}

Upvotes: 0

Views: 2655

Answers (4)

Francis Avila
Francis Avila

Reputation: 31641

You are already using PDO, so you should make PDO handle your escaping for you with your prepared statement. The code below does that.

Note that you still have a possible security issue because you assign table and column names dynamically. The code below attempts to make sure that won't result in any SQL injections, but you should still be cautious.

But you won't have any problems with unescaped input to the VALUES() part, and the insertions should be much faster since you prepare the statement only once.

function escapeSqlName($name, $quotechar='`') {
        // This is to escape column names. This $quotechar ONLY WORKS WITH MYSQL
        // ANSI syntax is to use $quotechar='"' and double them where-ever " is in the table name.
        return $quotechar.str_replace($quotechar, $quotechar.$quotechar, $name).$quotechar;
}

if (($handle = fopen($source_file, "r")) !== FALSE) {
    $columns = fgetcsv($handle, $max_line_length, ",");
    $esc_columns = array();
    foreach ($columns as &$column) {
        $column = str_replace(".","",$column);
        $esc_columns[] = escapeSqlName($column);
    }
    // your two extra columns
    $esc_columns[] = escapeSqlName('custgroup');
    $esc_columns[] = escapeSqlName('user_id');

    $sqlsmttempl = 'INSERT INTO %s (%s) VALUES (%s)';
    $sqlsmt = sprintf($sqlsmttempl,
        escapeSqlName($target_table), // the escaped table name
        implode(',', $esc_columns), // the escaped column names
        implode(',',array_fill(0, count($esc_columns), '?')) // the parameter placeholders
    );
    // $sqlsmt should now look like 'INSERT INTO `thetable` (`col1`,`col2`,...) VALUES (?,?,...)';

    $db = new PDO("mysql:host=localhost;dbname=test;","root",""); 

    $insert = $db->prepare($sqlsmt);  // prepare statement ONCE, execute with new values MULTIPLE TIMES

    while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
        while(count($data) < count($columns)) {
            $data[] = NULL;
        }
        // your two extra values
        $data[] = $_POST['custgroup'];
        $data[] = $_POST['user_id'];

        $insert->execute($data); // does data escaping for you.
    }
}

Upvotes: 2

KP-
KP-

Reputation: 11

Use

mysql_real_escape_string

for each entry in the csv file before inserting the value in the array.

so, replace 

$data[$i] = "'{$data[$i]}'";

with

$data[$i] = "'".mysql_real_escape_string($data[$i])."'";

Upvotes: -1

VolkerK
VolkerK

Reputation: 96159

One benefit (or maybe even purpose) of prepared statments is the separation of the actual statement and its parameters (the paylod data).
Instead of building a string that contains the paylod data you should bind those parameters via PDOStatement::bindParam or PDOStatement::bindValue.

Upvotes: 1

Treffynnon
Treffynnon

Reputation: 21563

CSV escaping

Surround your fields with quotes to contain the apostrophes in a CSV file. For example in your CSV:

"title", "name"
"Mr", "O'Hara"

Database escaping

You should look at escaping your data using PDO as @VolkerK suggests.

Upvotes: -1

Related Questions