Decrypt data imported into FileMaker from MySql

Posted April 2nd, 2008 by tammy

I have chosen SmartPill as the method I will use to decrypt the credit card data that I am importing from MySql. I will then use SmartPill to encrypt the data immediately after processing the credit card payment. Our web developer used mcrypt to encrypt the credit card data being posted to MySql. My problem is that I have tried using his mcrypt functions in SmartPill and I get a blank result. I have tried using SmartPill's mcrypt functions on my previously encrypted data and I get jibberish. I did make sure and use the same 'key' phrase. Since I am completely unfamiliar with both, I need some guidance. Do I need to make sure the web developer is using the exact mycrypt syntax as SmartPill? I noticed the mcrypt commands were a little different. I also see SmartPill works great in the example but the already encrypted credit card data will be coming from MySql and I wonder if there is a problem with pulling the weird characters into FileMaker. Any suggestions would be much appreciated.

Decrypt data imported into FileMaker from MySql

On April 2nd, 2008 Micah Woods says:

Hello Tammy,

Can you get your web developer to supply you with the PHP code that he would use to do the decryption? The code he would use should be able to be executed by SmartPill and return the same results.

I would try to get some test data from your web developer to make sure that the value being stored in MySQL by the web application can be retrieved in FileMaker and that the values match. THEN you can run the decrypt function and know that you're working with the same data. This will help narrow troubleshooting down to the decrypt function itself because you know the data is the same.

Regards,
Micah

ebc versus cbc

On April 2nd, 2008 tammy says:

I did just determine that the developers used ebc instead of cbc so I am able to encrypt and get the same result but I still can't decrypt the data from mysql.

mcyrpt

On April 2nd, 2008 tammy says:

I cannot get the web developers code to work in SmartPill at all. I just get a blank field. It seems to work for them as it did post a value to mysql however when testing their encryption code in Filemaker/SmartPill I get no data returned at all. Why would the code work for the web and not in Smartpill?

mcyrpt

On April 2nd, 2008 Micah Woods says:

It's hard to say why it's not working without seeing the code. Is that something you can post? It's possible that the version of PHP being used by the web application is compiled differently and that could be the issue.

Regards,
Micah

mcyrpt code

On April 2nd, 2008 tammy says:

function encryptData($value){
$key = "top secret key";
$text = $value;
$iv_size = mcrypt_get_iv_size(MCRYPT_BLOWFISH, MCRYPT_MODE_ECB);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
$crypttext = mcrypt_encrypt(MCRYPT_BLOWFISH, $key, $text, MCRYPT_MODE_ECB, $iv);
return $crypttext;
}

function decryptData($value){
$key = "top secret key";
$crypttext = $value;
$iv_size = mcrypt_get_iv_size(MCRYPT_BLOWFISH, MCRYPT_MODE_ECB);
$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);
$decrypttext = mcrypt_decrypt(MCRYPT_BLOWFISH, $key, $crypttext, MCRYPT_MODE_ECB, $iv);
return trim($decrypttext);
}
?>

I seem to have an issue with the way the encrypted data from MySql comes into Filemaker also. It is being stored as a varchar field and a BLOB field although FileMaker can only read the varchar field. I can't decrypt it using Smartpill function. I am not sure how to solve this issue. It has to be encrypted when the order is received. The order is pulled into FileMaker for processing and we do not charge the card until we know the full value. At that point I need to be able to decrypt. I believe I read on the Tech Network board that someone else was doing it this way.

mcyrpt code

On April 3rd, 2008 Micah Woods says:

Tammy,

This code seems to be working correctly:

function encryptData($value){ $key = "top secret key"; $text = $value; $iv_size = mcrypt_get_iv_size(MCRYPT_BLOWFISH, MCRYPT_MODE_ECB); $iv = mcrypt_create_iv($iv_size, MCRYPT_RAND); $crypttext = mcrypt_encrypt(MCRYPT_BLOWFISH, $key, $text, MCRYPT_MODE_ECB, $iv); return $crypttext; }


function decryptData($value){

$key = "top secret key";

$crypttext = $value;

$iv_size = mcrypt_get_iv_size(MCRYPT_BLOWFISH, MCRYPT_MODE_ECB);

$iv = mcrypt_create_iv($iv_size, MCRYPT_RAND);

$decrypttext = mcrypt_decrypt(MCRYPT_BLOWFISH, $key, $crypttext, MCRYPT_MODE_ECB, $iv);

return trim($decrypttext);

}
$in = encryptData("secret text 123456789");
echo $in;
$out = decryptData($in);

echo "\n" . $out;

Returns:

Ú1Üpì½îÞ³õØjª¡Å@®³é
secret text 123456789

At this point, my guess is that you're not getting the correct value in FileMaker via ESS. Here are a few ideas:

1. Take a look at the actual data in MySQL using something like PHPMyAdmin or one of the many MySQL query tools. Compare the actual data in MySQL to the data you're getting in FileMaker.

2. If the data is not the same, this could be an issue with the encoding used in the MySQL database. I'm not sure how FileMaker handles this but it may read the encoding and do some sort of translation or make some sort of assumption.

3. You may want to try using SmartPill to query MySQL and see if you can get the correct value. You can reference the Testbed file that's included with the SmartPill download for MySQL examples.

Regards,
Micah

encrypted value

On April 3rd, 2008 tammy says:

The problem is with the value I am getting out of mysql. I have been using phpmyadmin and notice that their are slight character differences in the value stored versus the value created when encrypting using Smartpill in Filemaker. I have found a solution. I changed the web code to convert the value to hexadecimal before storing into mysql. I then import that value into Filemaker and use Smartpill to convert back to binary and then to decrypt. It is working well so I am going to do some more testing. Thanks for you help. I am going to go ahead and purchase Smartpill now.