使用PDO通过PHP保存到mysql时，字符串会发生变异

Question

This is the sql:

INSERT INTO stepup (target, body) VALUES ('http://test.com/dev-ws/events', '{"username":"Unknown","verb":"answer","object":"http:\/\/localhost\/elgg\/answers\/view\/42954\/q1","context":{"course":"42902","phase":"1","widget_type":"questions","activity_id":"95c48","widget_title":"Wonder moment"},"originalrequest":{"value":{"description":"<p>\u041f\u0440\u043e\u0443\u0447\u0432\u0430\u043d\u0435<\/p>","question_id":42954}},"starttime":"2015-03-26 17:28:57 +0100","endtime":"2015-03-26 17:28:57 +0100"}')

then I do $conn->exec($sql);

but the actual content of body in the DB (mysql) is:

"{\"username\":\"Unknown\",\"verb\":\"answer\",\"object\":\"http://localhost/elgg/answers/view/42954/q1\",\"context\":{\"course\":\"42902\",\"phase\":\"1\",\"widget_type\":\"questions\",\"activity_id\":\"95c48\",\"widget_title\":\"Wonder moment\"},\"originalrequest\":{\"value\":{\"description\":\"<p>u041fu0440u043eu0443u0447u0432u0430u043du0435</p>\",\"question_id\":42954}},\"starttime\":\"2015-03-26 17:28:57 +0100\",\"endtime\":\"2015-03-26 17:28:57 +0100\"}"

So \u is replaced by u :(

What can I do.. I thought that by not "preparing" the SQL, this will stop happening...

Answer 1

It seems like you are injecting JSON content into an SQL string literal in a query. You probably have SQL-injection security holes, which is a worse problem than \u sequences going missing.

You should use parameterised queries to avoid having to think about escaping rules. For example in PDO:

$url= 'http://test.com/dev-ws/events';
$json= '{"username":"Unknown","verb":"answer","object":"http://localhost/elgg/answers/view/42954/q1","context":{"course":"42902","phase":"1","widget_type":"questions","activity_id":"95c48","widget_title":"Wonder moment"},"originalrequest":{"value":{"description":"<p>\u041f\u0440\u043e\u0443\u0447\u0432\u0430\u043d\u0435</p>","question_id":42954}},"starttime":"2015-03-26 17:28:57 +0100","endtime":"2015-03-26 17:28:57 +0100"}';

$q= $db->prepare('INSERT INTO stepup (target, body) VALUES (?, ?)');
$q->bindParam(1, $url, PDO::PARAM_STR);
$q->bindParam(2, $json, PDO::PARAM_STR);
$q->execute();