Solved

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

Posted on 2013-05-14
5
469 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
5 Comments
 
LVL 17

Accepted Solution

by:
OmniUnlimited earned 500 total points
Comment Utility
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
Comment Utility
I have to display the passwords to my staff.  Md5 is one way encryption.
0
 
LVL 17

Expert Comment

by:OmniUnlimited
Comment Utility
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 33

Expert Comment

by:Slick812
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now