CODEWITHSUNDEEP
phpmysqlunicodepdo
davidhq
davidhq

Reputation: 4760

String gets mutated when saving to mysql via PHP using PDO

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...

Upvotes: 0

Views: 74

Answers (3)

bobince
bobince

Reputation: 536409

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();

Upvotes: 1

gowthamnvv
gowthamnvv

Reputation: 307

You have to use PDO->quote().

Visit http://php.net/manual/en/pdo.quote.php for more details

You might also want to read about NO_BACKSLASH_ESCAPES sql mode of MySQL

Upvotes: 1

Wahyu Kodar
Wahyu Kodar

Reputation: 674

in postgre:

\uxxxx, \Uxxxxxxxx (x = 0 - 9, A - F)   16 or 32-bit hexadecimal Unicode character value

please check: String Constants with C-style Escapes

Solve: you can use \ or regexp_replace(c, '[^\]\(u\d{4})', '\\\1', 'g');

Upvotes: 1

Related Questions