Can't write encrypted data

[dmg2206]

I'm trying to write a PHP script for encrypting data and saving it to a MySQL database. I've tested the script, and it can encrypt and decrypt just fine. However, when I actually try to post the data to the database, much of it gets truncated.

The SQL query that gets posted:

INSERT INTO `event_payments` (`id`, `ccnum`, `status`) VALUES (MD5(3), 'N¦0¤í?ñHŸY?\ZIJo¡9D%$~1t¿0øC?Bð|ž}OOÅ/›;ƒ¼ýî
˜–?›@Ám%AÆ¡¢', 'Submitted')

And what gets posted to the ccnum column:

N

The id and status columns are filled as they should be. Any ideas?

 

[jgurgen]

what is the type of field your trying to insert it into? Is it varchar(1)??

 

[jgurgen]

* varchar(16)

md5 i know returns a 16 byte string

 

[towerbase]

The encryption process might produce a byte that contains #0. I would guess that MySQL would truncate the string at this point.

So I think you need to define your ccnum column as

VARCHAR(16) BINARY

or possibly as a BLOB field.

However, I haven't tested this out.

Andrew
Hampshire, UK

 

[dmg2206]

It's not the MD5'ed string that's been giving me problems. It's the next string, the long one that begins with "N" and includes the unicode characters.

Anyway, I've played around with column types and collations and finally found something that will let me write the string to the database preserving it as is. I've changed the column to MEDIUMBLOB.

 ALTER TABLE `table_name` CHANGE `ccnum` `ccnum` MEDIUMBLOB NOT NULL