Decrypting certain fields from a mysql database using PHP

Question

I have this PHP script

 <?php
//assume this is the key, declared as variable $cipherKey in the file cipherkey.php.
include ('cipherkey.php')
class Cipher {
    private $passKey;
    private $iv;

    function __construct( $inputKey ) {
        $this->passKey = hash( 'sha256', $inputKey, true );
        $this->iv = mcrypt_create_iv( 32 );
    }

    function encryptThis( $inputText ) {
        $cipher = mcrypt_encrypt( MCRYPT_RIJNDAEL_256, $this->passKey,$inputText,  MCRYPT_MODE_ECB, $this->iv );
        $encrypted = base64_encode( $cipher );
        return $encrypted;
    }

    function decryptThis( $inputText ) {
        $decipher = mcrypt_decrypt( MCRYPT_RIJNDAEL_256, $this->passKey, base64_decode( $inputText ), MCRYPT_MODE_ECB, $this->iv );
        $decrypted = trim( $decipher );
        return $decrypted;
    }
}

?>

This script is used to encrypt certain fields in a mysql database like this;

if( isset( $prescRequester, $patientName, $patientDOB, $contactPhone, $medType1, medType1_dose, $medType1_freq, $pharmacyName, $pharmacyPhone ) ) {
$prep = $db->prepare(
    "INSERT INTO renal_prescRequest(
        date,
        prescRequester,
        patientRelationship,
        patientName,
        patientDOB,
        contactPhone,
        contactEmail,
        physician,
        medProvider,
        medType1,
        medType1_dose,
        medType1_freq,
        medType2,
        medType2_dose,
        medType2_freq,
        medType3,
        medType3_dose,
        medType3_freq,
        ninetyDaySupply,
        pharmacyName,
        pharmacyPhone,
        comments
    ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
    ");
$prep->bind_param(
        'ssssssssssssssssssssss',
        $date,
        $cipher->encryptThis( $prescRequester ),
        $cipher->encryptThis( $patientRelationship ),
        $cipher->encryptThis( $patientName ),
        $cipher->encryptThis( $patientDOB ),
        $cipher->encryptThis( $contactPhone ),
        $cipher->encryptThis( $contactEmail ),
        $physician,
        $medProvider,
        $cipher->encryptThis( $medType1 ),
        $medType1_dose,
        $medType1_freq,
        $cipher->encryptThis( $medType2 ),
        $medType2_dose,
        $medType2_freq,
        $cipher->encryptThis( $medType3 ),
        $medType3_dose,
        $medType3_freq,
        $ninetyDaySupply,
        $pharmacyName,
        $pharmacyPhone,
        $comments
    );

$prep->execute();
$prep->close();

$db->close();

I am not this author of this code. But I am supposed to decrypt the encrypted fields. So I did something like this ;

  $cipher = new Cipher ( $cipherKey );
  $id = $_GET['id'];

  $query = "SELECT * FROM renal_clinicalTrial WHERE id = '".$id."'";
      $result  = mysql_query($query);
     if(!$result){
    die("Unable to perform query". mysql_error());
}

while($row = mysql_fetch_array($result)){
  $firstname = $row[firstName];
  $lastname = $row[lastName];
  $address = $row[address];
  $city = $row[city];
  $state = $row[state];
  $zipcode = $row[zipcode];
  $email = $row[contactEmail];
  $phone = $row[contactPhone];
    $cipher->decryptThis($firstname);
    $cipher->decryptThis($lastname);
    $cipher->decryptThis($address);
    $cipher->decryptThis($city);
    $cipher->decryptThis($state);
    $cipher->decryptThis($zipcode);
    $cipher->decryptThis($email);
    $cipher->decryptThis($phone);

When i display the fields to the browser, I get the encrypted data instead of the decrypted data. Is there something I am overlooking here. Thanks!

**Warning:** you're using [a **deprecated** database API](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) yourself from. *This is especially frightening as you seem to use medical data*. Why don't you use the `mysqli_*` functions in the first code fragment? — Marcel Korpel, Dec 25 '13 at 10:53

score 1 · Answer 1 · answered Dec 25 '13 at 10:54

1

The Cipher decryptThis() method returns a value, so you need to assign that returned value

$firstname = $cipher->decryptThis($firstname);
.... etc

or modify the method to accept its argument by reference instead of by value (but not advised to retain consistency in the calls)

answered Dec 25 '13 at 10:54

Mark Baker

209,507
32
346
385

Thanks I have done this and there is a change in how the fields are displayed but the strings are returned in some weird characters.. like Ý™T0»ë ¹À;„•Ñ`0Ët'ŒP+ÑìFÏêž|.. Is there any corrections to be made. Thanks – Damilare Binutu Dec 25 '13 at 12:56
Looks like you need to convert the encoding – Anthony Dec 25 '13 at 13:38
I have added () to the head of the document it changes and renders something like �BJ��ba�f�T;�hŞ�I j�Z�2��#O... How can I correctly render the document? Thanks – Damilare Binutu Dec 25 '13 at 14:05
Just a few of the fields have been decrypted, other fields such as zipcode and phone still show encrypted data. Is there any way to work around this? – Damilare Binutu Dec 25 '13 at 19:59
Are those fields actually encrypted? – Mark Baker Dec 25 '13 at 23:21

Decrypting certain fields from a mysql database using PHP

1 Answers1