AES_ENCRYPT/DECRYPT Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jayr517
Forum Commoner
Posts: 26
Joined: Fri Aug 22, 2003 7:28 pm
Location: Boise, ID
Contact:

AES_ENCRYPT/DECRYPT Question

Post by jayr517 »

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!
tylerdurden
Forum Commoner
Posts: 66
Joined: Mon Jul 28, 2003 11:52 am
Location: Austria

Post by tylerdurden »

For me a simple

Code: Select all

SELECT *,AES_DECRYPT(user_password,'text') as user_pass FROM users
works.
jayr517
Forum Commoner
Posts: 26
Joined: Fri Aug 22, 2003 7:28 pm
Location: Boise, ID
Contact:

Post by jayr517 »

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.
ericsodt
Forum Commoner
Posts: 51
Joined: Fri Aug 22, 2003 12:12 pm
Location: VA

Post by ericsodt »

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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

It's inbuildt in MySQL.
http://www.mysql.com/doc/en/Miscellaneo ... tions.html (one or so pages down)
jayr517
Forum Commoner
Posts: 26
Joined: Fri Aug 22, 2003 7:28 pm
Location: Boise, ID
Contact:

Post by jayr517 »

JAM wrote: It's inbuildt in MySQL.
http://www.mysql.com/doc/en/Miscellaneo ... tions.html (one or so pages down)
What he said!

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???
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

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.)
jayr517
Forum Commoner
Posts: 26
Joined: Fri Aug 22, 2003 7:28 pm
Location: Boise, ID
Contact:

Post by jayr517 »

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.)
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????
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Example:

Code: Select all

<?php 
$secretpass = "ohOhohsosecret";
echo md5($secretpass);
?>
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? =)
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

technically, and md5 sum is not 'encrypted', it is just a hashed checksum made up in a unique way that makes it very difficult to find out what it was made of since it indicates nothing about the original size or content of the data it made a checksum from..
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Personally, to the human eye, id still call it 'encrypted'. But youre right.
cryptography -
The art of protecting information by transforming it (encrypting it) into an unreadable format, called cipher text.
Post Reply