Solved

Encode mysql entry

Posted on 2010-09-04
3
476 Views
Last Modified: 2013-12-13
Hi,

I use Dreamweaver and I like using their mysql recordsets (I know i should learn proper mysql but this is easier).

I want to encrypt all the information that is to be submited however I can't seem to figure it out. Can someone show me how to encode all the entries in the code below without changing too much of the code below?

It needs to be two-way encode. I think I need to use AES_ENCRYPT but willing to take any suggestion.

Thanks!

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
  $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
  $insertSQL = sprintf("INSERT INTO creditcards (bookingid, cardholdername, address1, address2, town, postcode, cardnumber, validfrommonth, validfromyear, expirymonth, expiryyear, issuenumber, cvc, cardtype, dateadded, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['bookingid'], "int"),
                       GetSQLValueString($_POST['cardholdername'], "text"),
                       GetSQLValueString($_POST['address1'], "text"),
                       GetSQLValueString($_POST['address2'], "text"),
                       GetSQLValueString($_POST['town'], "text"),
                       GetSQLValueString($_POST['postcode'], "text"),
                       GetSQLValueString($_POST['cardnumber'], "text"),
                       GetSQLValueString($_POST['validfrommonth'], "text"),
                       GetSQLValueString($_POST['validfromyear'], "text"),
                       GetSQLValueString($_POST['expirymonth'], "text"),
                       GetSQLValueString($_POST['expiryyear'], "text"),
                       GetSQLValueString($_POST['issue'], "text"),
                       GetSQLValueString($_POST['cvc'], "text"),
                       GetSQLValueString($_POST['cardtype'], "text"),
                       GetSQLValueString($_POST['dateadded'], "date"),
                       GetSQLValueString($_POST['status'], "int"));

  mysql_select_db($database_bookedy, $bookedy);
  $Result1 = mysql_query($insertSQL, $bookedy) or die(mysql_error());

  $insertGoTo = "step4.php?id=" . $row_booking['bookingid'] . "";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
?>

Open in new window

0
Comment
Question by:bookedy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Assisted Solution

by:onemadeye
onemadeye earned 250 total points
ID: 33605830
'AES_ENCRYPT' and 'AES_DECRYPT' functions are MySQL functions as you know ...
and below is another solution you might want to try it out ..
it's playing around with SHA1 hash and base64.

Check it out ...

<?php

function rc4($key, $data)
{
	/* TAKEN FROM: http://nz2.php.net/manual/en/ref.mcrypt.php#87274 */
    // Store the vectors "S" has calculated
    static $SC;
    // Function to swaps values of the vector "S"
    $swap = create_function('&$v1, &$v2', '
        $v1 = $v1 ^ $v2;
        $v2 = $v1 ^ $v2;
        $v1 = $v1 ^ $v2;
    ');
    $ikey = crc32($key);
    if (!isset($SC[$ikey])) {
        // Make the vector "S", basead in the key
        $S    = range(0, 255);
        $j    = 0;
        $n    = strlen($key);
        for ($i = 0; $i < 255; $i++) {
            $char  = ord($key{$i % $n});
            $j     = ($j + $S[$i] + $char) % 256;
            $swap($S[$i], $S[$j]);
        }
        $SC[$ikey] = $S;
    } else {
        $S = $SC[$ikey];
    }
    // Crypt/decrypt the data
    $n    = strlen($data);
    $data = str_split($data, 1);
    $i    = $j = 0;
    for ($m = 0; $m < $n; $m++) {
        $i        = ($i + 1) % 256;
        $j        = ($j + $S[$i]) % 256;
        $swap($S[$i], $S[$j]);
        $char     = ord($data[$m]);
        $char     = $S[($S[$i] + $S[$j]) % 256] ^ $char;
        $data[$m] = chr($char);
    }
    return implode('', $data);
}

// Assign a random value for RC4 encryption
$random_value = 'f^jAE]okIOzU[2&q1{3"5w_794p@6s8?BgP>dFV=m D<TcS%Ze@lGK/uCy.Jx)HiQ!#$&;Lt-R}Ma,NvW+Ynb*0X';

// String to encrypted
$string = 'HeLLo woRld!';

echo '<b>String to encrypt:</b> '.$string;

echo '<br /><br />';

// ENCRYPTION PHASE
$is_enc = rc4($random_value,$string); // Output: K<3lð…Í·‚0
// add base64 encryption to ensure safe input to MySQL
$is_enc_b64 = base64_encode($is_enc); // Output: SzwRM2zwhc23go0w
echo '<b>ENCrypted string:</b> '.$is_enc_b64; 

echo '<br /><br />';

// DECRYPTION PHASE
$is_enc_b64 = base64_decode($is_enc_b64); // Output: SzwRM2zwhc23go0w
$is_dec = rc4($random_value,$is_enc_b64); // Output: HeLLo woRld!
echo '<b>DECrypted string:</b> '.$is_dec; 

?>

Open in new window

0
 

Author Comment

by:bookedy
ID: 33606305
Hi!

I seem to have been able to get the information encrypted - now my problem is the decrypting.

I have the below script which i believe works as i have tested it in mysql however when i try to echo the result, I just get nothing.

mysql_select_db($database_bookedy, $bookedy);
$query_ccinfo = sprintf("SELECT creditid, bookingid, cardholdername, address1, address2, town, postcode, country, AES_DECRYPT(cardnumber,'passwordprotect') AS unencrypted, validfrommonth, validfromyear, expirymonth, expiryyear, issuenumber, cvc, cardtype, dateadded, datetobedeleted, status, adminnotes FROM creditcards WHERE creditid = '15'");

<?php echo $row_ccinfo['cardnumber']; ?>

I think the error is simply coming from the echo. If i echo any of the other information, it works fine. Any ideas?

Thanks!
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 33606424
@bookedy:

This is a TERRIBLE idea.  Don't do it for anything you do not ABSOLUTELY have to encrypt.  The more you obscure, the more you will be confounded in your efforts to debug your code and provide tech support.

That said, let's look at this:

mysql_select_db($database_bookedy, $bookedy);
$query_ccinfo = sprintf("SELECT creditid, bookingid, cardholdername, address1, address2, town, postcode, country, AES_DECRYPT(cardnumber,'passwordprotect') AS unencrypted, validfrommonth, validfromyear, expirymonth, expiryyear, issuenumber, cvc, cardtype, dateadded, datetobedeleted, status, adminnotes FROM creditcards WHERE creditid = '15'");

<?php echo $row_ccinfo['cardnumber']; ?>

What is in $row_ccinfo?  Where did it get populated?  Can you please print out the entire contents of this array using var_dump() and post it here?  Thanks.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question