0

I'm using a PHP File to read values from CSV file and import it into a mysql database. Works fine, but now I have an error with a CSV file with an apostrophe in one field:

CSV-File: 

...
21.08.2018;21.08.2018;Peter Muller;Voucher;6 LED's for Kitchen Lamp;19,40;EUR;1000
...

The problem: there is an apostrophe in Text.

my PHP Insert statement: 

<?php    
...    
$sqlinsert  = "INSERT INTO `" . $tablename . "` "
                               ."(`BankID`, `Date`, `Valuta`, 
                                  `Topic`, `Type`, 
                                  `Text`, `Amount`, 
                                  `Currency`, `Balance`) "
                               ."VALUES "
                               .    "(" 
                               ."'$id', "           // BankID
                               ."'$csvdata[0]' , "  // Date
                               ."'$csvdata[1]' , "  // Valua
                               ."'$csvdata[2]' , "  // Topic
                               ."'$csvdata[3]' , "  // Type
                               ."'$csvdata[4]' , "  // Text
                               ."'$csvdata[5]' , "  // Amount
                               ."'$csvdata[6]' , "  // Currency
                               ."'$csvdata[7]'"     // Balance
                               .") "
                               ."ON DUPLICATE KEY UPDATE "
                               ."BankID      = VALUES(BankID), "
                               ."Text        = VALUES(Text); ";
            # echo "<b>SQL: </b> . $sqlinsert . <br>";
            $insCmd = $pdo->prepare($sqlinsert); 
            $insCmd->execute();

And the error message is: 

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

Since I'm new to PDO, how can I manipulate $csvdata[4] to handle a perfect insert? - Or is there a special PDO command to manage this?

Peter
  • 1,224
  • 3
  • 16
  • 28
  • 5
    Since you're using PDO, take advantage of [prepared statements](https://secure.php.net/manual/en/pdo.prepared-statements.php) and [bindparam](http://php.net/manual/en/pdostatement.bindparam.php) or [bindvalue](http://php.net/manual/en/pdostatement.bindvalue.php). **This will take care of any pesky quoting issues that may occur,** and help prevent SQL injection. – aynber Feb 04 '19 at 16:26
  • 3
    Use Prepared statements - https://stackoverflow.com/questions/1457131/php-pdo-prepared-statements – Nigel Ren Feb 04 '19 at 16:26
  • 3
    Possible duplicate of [PHP PDO prepared statements](https://stackoverflow.com/questions/1457131/php-pdo-prepared-statements) – Nico Haase Feb 04 '19 at 16:29
  • @aynber: added `bindparam` into my code and now it works without errors. Thank you. – Peter Feb 04 '19 at 18:29

0 Answers0