MySql insert binary data to db without errors

Question

I have a problem. When I do an insert like so in php:

sql = "INSERT INTO mytable (id, value)
VALUES ('sds83','".$EncryptedString."')";

When I run the following query it sometimes works and sometimes it doesn't. The problem is that sometimes the $EncryptedString contains characters like this: ')') which causes syntax errors. The $EncryptedString contains binary data, how can I go about this issue?

Answer 1

Escape your encrypted string

mysql-real-escape-string

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

See StripSlashes

Answer 2

Use PDO (or another database layer) that supports prepared statements.

When you use query parameters instead of executing raw SQL, you gain speed improvements (the database only has to plan and optimize for one query) and all the data you write to it's parameters are immediately and completely isolated from the query itself.

It's surprising how many people don't have this in place! Take the initiative and update your code.

Answer 3

You need to escape your $EncryptedString. Depending on the type of MySQL connection object/functions you are using, it could be like this:

$sql = "
    INSERT INTO mytable (id, value)
    VALUES ('sds83','" . mysql_real_escape_string($EncryptedString) . "')";