CODEWITHSUNDEEP
mysqlencodingcollationmcrypt
rgbflawed
rgbflawed

Reputation: 2147

mysql encoding encrypted text

I'm currently attempting to switch from my shared inmotionhosting account (have received AWEFUL service lately) to an Amazon EC2 server that I've set up. I'm having trouble with getting the encryption function working in the EC2 account.

In my PHP code, all text gets encrypted by mcrypt before being put into the SQL. I have deduced that those mcrypt characters are responsible for all my queries throwing errors. (I know it's because of encoding issues, but Google searches on the subject aren't very clear on where I need to focus my attention.)

A more simplified way of explaining the problem. On my new hosting account this SQL query doesn't work:

UPDATE mydatabase.clients SET firstname='\'å».”é¶Q' WHERE id_client=65

But this does

UPDATE mydatabase.clients SET firstname='Test' WHERE id_client=65

So that tells me the mcrypt function is using characters that the SQL database doesn't understand and thus the queries aren't working.

Some other info for you...

When I run "SHOW VARIABLES LIKE 'character_set_%'" on the working database I get this:

Variable_name              Value
character_set_client       utf8
character_set_connection   utf8
character_set_database     latin1
character_set_filesystem   binary
character_set_results      utf8
character_set_server       latin1
character_set_system       utf8

When I do that on the nonworking database I get:

Variable_name              Value
character_set_client       utf8
character_set_connection   utf8
character_set_database     utf8
character_set_filesystem   binary
character_set_results      utf8
character_set_server       utf8
character_set_system       utf8

I saw the difference in character_set_database and ran this line of code:

ALTER DATABASE mydatabase DEFAULT CHARACTER SET latin1

It successfully changed the character_set_database to "latin1" to match the other, but didn't solve the problem.

Finally, all my columns in my tables are using the Collation "latin1_swedish_ci"

Any help you could give would be very very appreciated!

Upvotes: 0

Views: 676

Answers (1)

visionviper
visionviper

Reputation: 11

Store your encrypted strings as binary (or a similar) type. Also make sure you are escaping the encrypted string. Both are important parts to doing this right!

I've been working with MySQL and Mcrypt and I store my encrypted data and initialization vectors as binary and I escape all of these strings before they get put in a query. Works like a charm.

Upvotes: 1

Related Questions