Solved

What's the best way to store passwords in mysql?

Posted on 2013-05-14
5
492 Views
Last Modified: 2013-05-15
I'm storing all the domains, usernames, and passwords in to a database so we can look customer account up online.  I cannot use md5 because the passwords need to be in plain text to view them.  Right now I'm storing them base64 with salt.  Can anyone give me ideas that might make them more secure and still allow me to "decode" them to display?
0
Comment
Question by:nickinthooz
[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
5 Comments
 
LVL 17

Accepted Solution

by:
OmniUnlimited earned 500 total points
ID: 39166740
There is no way your data will be secure in your database unless you encrypt it.  Why can't you use md5?  You can easily set up encryption and decryption functions like the following examples.  Just use the decryption function when you need to inspect the data from the database:

function encrypt($key, $input) 
{ 
return base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, md5($key), $input, MCRYPT_MODE_CBC, md5(md5($key)))); 
} 

function decrypt($key, $input) 
{ 
return rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, md5($key), base64_decode($input), MCRYPT_MODE_CBC, md5(md5($key))), "\0"); 
}

Open in new window

0
 
LVL 6

Author Comment

by:nickinthooz
ID: 39166759
I have to display the passwords to my staff.  Md5 is one way encryption.
0
 
LVL 17

Expert Comment

by:OmniUnlimited
ID: 39166789
As you can see from my code, there is an encryption process that mixes md5 coding with base64 and still allows you to decrypt using the proper md5 key.
0
 
LVL 34

Expert Comment

by:Slick812
ID: 39166875
greetings   nickinthooz, , the security factors and password storage in database are not straight forward and simple. you can successfully Encrypt and decrypt from a TABLE, the encryption can be important, but is worthless if the access to the encryption methods are not protected from the UN-authorized. In MySQL you can use it's AES encrypt as
AES_ENCRYPT('VALUE here', "passWD7890123456") and select as -
'SELECT calories, AES_DECRYPT(icon, "passWD7890123456") as iconDe,  lock1 FROM lowcal'

PHP MCRYPT has some effective encryptions

class macCBC {

function __construct($base64 = false){
$this->base64=$base64;
$this->Error=new encError;
$this->algor='twofish';// 'serpent' 'rijndael-128' 'saferplus' 'loki97'
}

public function encrypt($Plain, $Key){
if((!is_string($Key))||(!isset($Key{15}))){$this->Error->set(10,'Key String length less than SIXTEEN');return false;}
$sLen=strlen($Plain);
if($sLen<4){$this->Error->set(20,'Plain String length less than FOUR');return false;}
if($this->Error->Number)$this->Error->set();
$Key=str_pad($Key,32,chr(8).chr(219).'nH!`'.chr(244).'>;0');
$ivRand=mcrypt_create_iv(12,MCRYPT_RAND);
$chop=substr($ivRand,6,4);
$check=hash_hmac('crc32b',$Plain,$chop,true);
$check^=$ivRand{10}.$ivRand{3}.$ivRand{5}.$ivRand{9};
$ivRand.=$check;
$chop=15-($sLen%16);
$sLen=ord($ivRand{11});
$check=ord($ivRand{7})&15;
$chop^=$check;
$ivRand{11}=chr($chop|($sLen&240));
$chop=$ivRand{13};
$ivRand{13}=$ivRand{2};
$ivRand{2}=$chop;
$check=str_split($Key,16);
$check[0]^=$ivRand;
$check[1]^=$ivRand;
$Key=$check[1].$check[0];
$Plain=mcrypt_encrypt($this->algor,$Key,$Plain,'cbc',$ivRand);
if($Plain==''){$this->Error->set(30,'MCRYPT function FAILED');return false;}
$Key=strrev(substr($ivRand,7));
$ivRand=substr($ivRand,0,7);
$Plain=$Key.$Plain.$ivRand;
if($this->base64){$Plain=base64_encode($Plain);$Plain=str_rot13($Plain);return $Plain;}
return $Plain;
}

public function decrypt($Input, $Key){
if((!is_string($Key))||(!isset($Key{15}))){$this->Error->set(100,'Key String length less than SIXTEEN');return false;}
$sLen=strlen($Input);
if($this->base64){if(($sLen<44)||($sLen%4!=0)){$this->Error->set(300,'Incorrect Base64 length of Input string');return false;}
if(preg_match('/[^a-zA-Z0-9\+\=\/]/',$Input)!=0){$this->Error->set(301,'Incorrect Base64 characters in Input string');return false;}
$Input=str_rot13($Input);$Input=base64_decode($Input);$sLen=strlen($Input);}
if(($sLen<32)||($sLen%16!=0)){$this->Error->set(200,'Input string length is NOT Block Size');return false;}
if($this->Error->Number)$this->Error->set();
$Key=str_pad($Key,32,chr(8).chr(219).'nH!`'.chr(244).'>;0');
$ivRand=substr($Input,$sLen-7);
$chop=strrev(substr($Input,0,9));
$ivRand.=$chop;
$Input=substr($Input,9,-7);
$sLen-=17;
$check=str_split($Key,16);
$check[0]^=$ivRand;
$check[1]^=$ivRand;
$Key=$check[1].$check[0];
$Input=mcrypt_decrypt($this->algor,$Key,$Input,'cbc', $ivRand);
if($Input==''){$this->Error->set(500,'MCRYPT function FAILED');return false;}
$chop=ord($ivRand{11});
$chop&=15;
$check=ord($ivRand{7})&15;
$chop^=$check;
if($chop!=15)$Input=substr($Input,0,$sLen-$chop);
$chop=$ivRand{13};
$ivRand{13}=$ivRand{2};
$ivRand{2}=$chop;
$check=substr($ivRand,12);
$check^=$ivRand{10}.$ivRand{3}.$ivRand{5}.$ivRand{9};
$chop=substr($ivRand ,6,4);
$Key=hash_hmac('crc32b',$Input,$chop,true);
if($check!=$Key){$this->Error->set(400,'FAILED to Decrypt correctly; INVALID MAC');return false;}
return $Input;
}

public function RandKeyText($length=32, $less=true){$out='$key = ';$rand=0;$apos=false;
for($i=0;$i<$length;++$i){if(($less)&&($i&1))$rand=mt_rand(32,127);else$rand=mt_rand(0,255);
	if (($rand>31)&($rand<127)){if(!$apos)$out .='\'';$apos=true;if(($rand==39)|($rand==92))$out .='\\';$out .=chr($rand);}
	else{if($apos)$out .='\'.';$apos=false;$out .='chr('.$rand.').';}}
if($apos)$out .= '\';';else $out[strlen($out)-1]=';';
return htmlspecialchars($out);}

} // end of class macCBC

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39167923
I cannot use md5 because the passwords need to be in plain text to view them.
Security is a continuum, not an absolute.  If you are storing bowling scores, medical records, financial data or nuclear launch codes you choose different security measures.  By having the passwords exist in plain text (anywhere) so they can be viewed by others you're saying, in effect, "I trust everyone who comes in contact with my application, and I am certain that they will never abuse my complete and total trust, and if I am wrong and they abuse my complete and total trust, nothing bad will happen to me or anyone else."

If you're OK with that concept, why bother to create a reversible password obfuscation process at all?  Why not just store the passwords in clear text?

If the value of the information you're storing is negligible, any encryption scheme at all -- even a reversible encryption -- will be adequate to the task of obscuring the passwords.  If the value is great, then your security requirements go up, and storing a password that can be decoded is risky.

I favor the use of md5().  I do not accept the idea that md5() is insecure and easily cracked.  And I will buy anyone a beer who can show me the password that is encoded by this md5 string: e0f1299ed629d3c8826e2dd2be4780cf

So with that said, if you want to create a reversible password, here is a minimally-invasive and easy way to do it.
http://php.net/manual/en/function.str-rot13.php

Here is a step up:
http://www.laprbass.com/RAY_scramble_text.php
<?php // RAY_scramble_text.php
error_reporting(E_ALL);
echo '<pre>';

Class Scramble
{
    protected $clear = '$%-., 0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz';
    protected $scram = 'fjAEokIOzU2q13_h5w794p@6s8B.gPdFVmDTcSZe%r,lGKuCyJxHiQLt-RMa$NvW Ynb0X';

    public function __construct($num = 0)
    {
        if ($num <  1) $num = 0;
        if ($num >= strlen($this->scram)) $num = 0;
        if ($num)
        {
            $scr_1 = substr($this->scram, 0, $num);
            $scr_2 = substr($this->scram, $num);
            $this->scram = $scr_2 . $scr_1;
        }

        $this->endex = array_combine(str_split($this->clear), str_split($this->scram));
        $this->dedex = array_flip($this->endex);
    }

    public function encode($word)
    {
        return strtr($word, $this->clear, $this->scram);
    }

    public function decode($word)
    {
        return strtr($word, $this->scram, $this->clear);
    }

}


// USE CASE WITH A FIXED SCRAMBLE CODE
$obj = new Scramble(13);
$pwd = 'Supercalifragilisticexpialidocious';
$scr = $obj->encode($pwd);
$new = $obj->decode($scr);

// SHOW THE WORK PRODUCT
var_dump($pwd, $scr, $new);

Open in new window

And here is an encryption-based example.
http://www.laprbass.com/RAY_encrypt_decrypt.php
<?php // RAY_encrypt_decrypt.php
error_reporting(E_ALL);

// MAN PAGE: http://php.net/manual/en/ref.mcrypt.php

class Encryption
{
    protected $key;
    protected $eot;
    protected $ivs;
    protected $iv;

    public function __construct($key='quay', $eot='___EOT')
    {
        // SET KEY, DELIMITER, INITIALIZATION VECTOR - MUST BE KNOWN TO BOTH PARTS OF THE ALGORITHM
        $this->key = $key;
        $this->eot = $eot;
        $this->ivs = mcrypt_get_iv_size(MCRYPT_RIJNDAEL_128, MCRYPT_MODE_ECB);
        $this->iv  = mcrypt_create_iv($this->ivs);
    }

    public function encrypt($text)
    {
        // APPEND END OF TEXT DELIMITER
        $text .= $this->eot;

        // ENCRYPT THE DATA
        $data = mcrypt_encrypt(MCRYPT_RIJNDAEL_128, $this->key, $text, MCRYPT_MODE_ECB, $this->iv);

        // MAKE IT base64() STRING SAFE FOR STORAGE AND TRANSMISSION
        return base64_encode($data);
    }

    public function decrypt($text)
    {
        // DECODE THE DATA INTO THE BINARY ENCRYPTED STRING
        $text = base64_decode($text);

        // DECRYPT THE STRING
        $data = mcrypt_decrypt(MCRYPT_RIJNDAEL_128, $this->key, $text, MCRYPT_MODE_ECB, $this->iv);

        // REMOVE END OF TEXT DELIMITER
        $data = explode($this->eot, $data);
        return $data[0];
    }
}

// INSTANTIATE THE CLASS
$c = new Encryption();

// INITIALIZE VARS FOR LATER USE IN THE HTML FORM
$encoded = '';
$decoded = '';

// IF ANYTHING WAS POSTED SHOW THE DATA
if (!empty($_POST["clearstring"]))
{
    $encoded = $c->encrypt($_POST["clearstring"]);
    echo "<br/>{$_POST["clearstring"]} YIELDS ENCODED ";
    var_dump($encoded);
}

if (!empty($_POST["cryptstring"]))
{
    $decoded = $c->decrypt($_POST["cryptstring"]);
    echo "<br/>{$_POST["cryptstring"]} YIELDS DECODED ";
    var_dump($decoded);
}

$form = <<<FORM
<form method="post">
<input name="clearstring" value="$decoded" />
<input type="submit" value="ENCRYPT" />
<br/>
<input name="cryptstring" value="$encoded" />
<input type="submit" value="DECRYPT" />
</form>
FORM;

echo $form;

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

751 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