AES_ENCRYPT/DECRYPT Question
Moderator: General Moderators
AES_ENCRYPT/DECRYPT Question
Hey guys...I can use aes_encrypt to encrypt and store mysql data fine in a db, but when I pull it back, aes_decrypt returns null. I don't know if I quite understand the parameter usage with aes_encrypt/decrypt.
Here's what I'm doing (fake data of course)...
INSERTING THE DATA
insert into password_t
values(3524,AES_ENCRYPT(@password, "text"))
GETTING THE DATA BACK
select @password:= AES_DECRYPT(user_password, "text")
from password_t
where user_id = 3524
I'm assuming that the first parameter to these functions is the value that you want to encrypt/decrypt, and that the second value is any random text that will be used as a key (and yes, I am actually using the word "text" in each call). Also, the first parameter in the call to AES_DECRYPT() above is the name of the password column in password_t.
Can someone tell me what I'm doing wrong...I know it's probably a rookie question, but I'm pretty new to PHP and encrypting data.
BTW, I already checked http://www.mysql.com/doc/en/Miscellaneo ... tions.html but I haven't been able to figure it out from what they have there...their explanation of aes_decrypt is a bit brief.
Thanks!
Here's what I'm doing (fake data of course)...
INSERTING THE DATA
insert into password_t
values(3524,AES_ENCRYPT(@password, "text"))
GETTING THE DATA BACK
select @password:= AES_DECRYPT(user_password, "text")
from password_t
where user_id = 3524
I'm assuming that the first parameter to these functions is the value that you want to encrypt/decrypt, and that the second value is any random text that will be used as a key (and yes, I am actually using the word "text" in each call). Also, the first parameter in the call to AES_DECRYPT() above is the name of the password column in password_t.
Can someone tell me what I'm doing wrong...I know it's probably a rookie question, but I'm pretty new to PHP and encrypting data.
BTW, I already checked http://www.mysql.com/doc/en/Miscellaneo ... tions.html but I haven't been able to figure it out from what they have there...their explanation of aes_decrypt is a bit brief.
Thanks!
-
tylerdurden
- Forum Commoner
- Posts: 66
- Joined: Mon Jul 28, 2003 11:52 am
- Location: Austria
For me a simple works.
Code: Select all
SELECT *,AES_DECRYPT(user_password,'text') as user_pass FROM usersthanks tylerdurden, that works...I also found another problem with my logic. The datatype of the table field used to stored the password, was a varchar(20). I think the encrypted value of my passwords was exceeding 20 characters, so it was being truncated. I changed the datatype to a blob and it works great now.
Thanks again.
Thanks again.
is AES_ENCRYPT and AES_DECRYPT a standard PHP function or is this your own algorithm?
jayr517 wrote:thanks tylerdurden, that works...I also found another problem with my logic. The datatype of the table field used to stored the password, was a varchar(20). I think the encrypted value of my passwords was exceeding 20 characters, so it was being truncated. I changed the datatype to a blob and it works great now.
Thanks again.
It's inbuildt in MySQL.
http://www.mysql.com/doc/en/Miscellaneo ... tions.html (one or so pages down)
http://www.mysql.com/doc/en/Miscellaneo ... tions.html (one or so pages down)
What he said!JAM wrote: It's inbuildt in MySQL.
http://www.mysql.com/doc/en/Miscellaneo ... tions.html (one or so pages down)
I've been wondering though...the "text" parameter (the second parameter sent to both functions) is really the password to encrypt/decrypt. Do you guys have any cool way to hide this password? Right now I have a Constants.php which stores global (static) variables that I use in all my scripts. I include this script whenever I want access to my own user defined global stuff. I have the password defined there instead of hard-coding it where ever I need it. Conceivably though, someone could hack my site, download the Constants.php and be able to see the password. They could then use the password to write a script that will pull the encrypted user passwords from my DB (assuming they know what DB and table, the data is stored). I know it seems a bit far-fetched, and someone would really have to be going after my site, but I thought I'd throw it out there to see what you guys think.
Anyone have a more secure method???
If youre worried about crackers, then storing the password in any file, in any database, in anyway, is unsafe.
Storing it md5()'ed in a database usually is good enough tho, because md5 is one-way only, so you cant reverse it to get the readable password... (And both PHP and MySQL can handle md5 as alg.)
Storing it md5()'ed in a database usually is good enough tho, because md5 is one-way only, so you cant reverse it to get the readable password... (And both PHP and MySQL can handle md5 as alg.)
I guess I don't understand the concept of one-way encrypting. When you say that MySQL and PHP can handle md5 as an alg, are you saying that I MD5 the key password once...when I insert it into the DB and after that I pull it out using regular select statements in MySQL and using it as a regular variable in PHP...basically treating it like any other variable????JAM wrote: Storing it md5()'ed in a database usually is good enough tho, because md5 is one-way only, so you cant reverse it to get the readable password... (And both PHP and MySQL can handle md5 as alg.)
Example:
Just as demonstrative purpose, "ohOhohsosecret" is here used as a variable. In real life, store the 32 char long md5'ed pass in the database (or file, or $var).
Say you later login to your page using a username and a password. If you md5 that password (using either php or sql), and compare it with the one stored in the database, you should get the same 32 char long match = accepted.
No match = not accepted.
You know the password in clear text. PHP/SQL takes care of the conversion. A cracker cant reverse it even if he/she gets the 32 char long encrypted string...
Did I make it easier? =)
Code: Select all
<?php
$secretpass = "ohOhohsosecret";
echo md5($secretpass);
?>Say you later login to your page using a username and a password. If you md5 that password (using either php or sql), and compare it with the one stored in the database, you should get the same 32 char long match = accepted.
No match = not accepted.
You know the password in clear text. PHP/SQL takes care of the conversion. A cracker cant reverse it even if he/she gets the 32 char long encrypted string...
Did I make it easier? =)