使用DBO连接插入或更新带有json值的MySQL表失败

Question

Data is a json-encoded. It seems to always fail with errors as if the string has not been escaped properly even though I've tried using both quote() as the documentation suggests.. and also mysql_real_escape_string() but nothing seems to work :S

If I change $data to 'apa' for example instead of my json I get:

PDO::errorInfo(): Array ( [0] => 42000 [1] => 1064 [2] => You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\'apa\'')' at line 1 )

-

$data = json_encode($jsonStoreArr, JSON_HEX_APOS | JSON_HEX_QUOT);
$stmt = $pdo->prepare("INSERT INTO _mytablename (movie_id, cached_data) VALUES (:id, :data) ON DUPLICATE KEY UPDATE cached_data = values(:data)");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->bindParam(':data', $data, PDO::PARAM_STR);
$stmt->execute();   
if ($stmt->errorCode() !== '00000') {
     print_r($stmt->errorInfo());
}

Answer 1

I urlencoded urls in the json string and then simply did this (below), now it works!

$data = json_encode($jsonStoreArr);
$stmt = $pdo->prepare("INSERT INTO _mytablename (movie_id, cached_data) VALUES (:id, :data) ON DUPLICATE KEY UPDATE cached_data = :data");
$stmt->bindParam(':data', $data, PDO::PARAM_STR);