Insert php variable with latin characters into mysql db

Question

I'm pretty sure, a common problem but am looking for the best practice solution.

I have a legacy database, for which I have written a script to migrate the data.

In my script I have the following:

$select = $dbh->query("SELECT CollectionID, CollectionCode, Name FROM $db_local.collection");
  while ($collection_row = $select1->fetch (PDO::FETCH_ASSOC)) {
    $collection_id = $collection_row['CollectionID'];
    $collection_code = !empty($collection_row['CollectionCode']) ? "'{$collection_row['CollectionCode']}'" : "NULL";
    $collection_name = !empty($collection_row['Name']) ? "'{$collection_row['Name']}'" : "NULL";

    $dbh->exec("INSERT INTO $db_remote.cdrs_collectiononline (
        collection_id, 
        collection_code, 
        collection_name
        ) VALUES (
        $collection_id, 
        $collection_code, 
        $collection_name
        )");
}

This is all fine until I run across this row:

| CollectionID | CollectionCode | Name                                |
+--------------+----------------+-------------------------------------+
|         1032 | MHNG           | Muséum d'Histoire Naturelle, Genève |

If I do this directly in mysql (note double quotes):

INSERT INTO cdrs_collectiononline (collection_id, collection_code, collection_name) VALUES ('1032', 'MHNG', "Muséum d'Histoire Naturelle, Genève")

It works. What is the best way to deal with the possibility of these latin characters using mysql pdo?

Any help much appreciated.

score 1 · Accepted Answer · answered May 29 '12 at 22:21

1

try :

$collection_name = addslashes(mysql_escape_string($collection_name));

or :

$dbh->exec("INSERT INTO $db_remote.cdrs_collectiononline (
        collection_id, 
        collection_code, 
        collection_name
        ) VALUES (
        '".$collection_id."', 
        '".$collection_code."', 
        '".$collection_name."'
        )");

answered May 29 '12 at 22:21

mgraph

15,238
4
41
75

Thankyou, although `$collection_name = mysql_escape_string($collection_name);` was sufficient. Thanks again. – Darwin Tech May 29 '12 at 23:06

score 0 · Answer 2 · answered Feb 04 '14 at 14:59

0

Little bit late but it can help somebody else !

It would be better to use the pdo prepare and bindParam : http://fr2.php.net/manual/fr/pdostatement.bindparam.php

It'll escape the character according to the type you choosed.

answered Feb 04 '14 at 14:59

Christophe Ferreboeuf

1,048
14
26

Insert php variable with latin characters into mysql db

2 Answers2