tag:blogger.com,1999:blog-18337119.post115619075799964815..comments2024-01-22T07:09:30.574-05:00Comments on MySQL Consulting and NoSQL Consulting: MySQL DBA: Storing Passwords in MySQLUnknownnoreply@blogger.comBlogger44125tag:blogger.com,1999:blog-18337119.post-17982172604147215492011-05-13T13:23:52.598-04:002011-05-13T13:23:52.598-04:00Rafael: any system where an insider can reverse en...Rafael: any system where an insider can reverse engineer the password is unacceptable. In your proposal, an insider can take a stored md5 and can get the cleartext password, could he/she not?<br /><br />I think a better idea would be actually encrypt the md5 of timestamp with the password and store that.<br /><br />create table secure_passwords (password blob,salt char(40));<br /><br />// '4bf371a084fc186e6b7913dcffc8e975' is the md5(current_timestamp())<br /><br />insert into secure_passwords values (AES_ENCRYPT('4bf371a084fc186e6b7913dcffc8e975','mySecurePassword'),'4bf371a084fc186e6b7913dcffc8e975');<br /><br />// When user logs in, try to decrypt using what they provided. If it is correct it will succeed.<br />select true from secure_passwords where AES_DECRYPT(password,'mySecurePassword') = salt;<br /><br />// This will fail<br />select true from secure_passwords where AES_DECRYPT(password,'wrongPassword') = salt;Chandranoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-3915312414763495722011-04-30T06:05:17.349-04:002011-04-30T06:05:17.349-04:00when a user is creating a new account, and you try...when a user is creating a new account, and you try to use the browser's javascript to encrypt their typed password before sending it to your script, how can you use regular expressions on the server side to ensure their submitted password is a minimum length, contains at least 1 uppercase alphacharacter, and contains at least 1 special character (that you provide a list of valid special chars @#$) in your script?<br /><br />if it's md5 hashed at the browser before being submitted, their is no way to unhash it at the server to check for these conditions using a regex, is there?<br /><br />i know you could us an SSL connection during the signup phase, but i'm wondering about the first question. thanksAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-48169904186570605262010-12-28T23:22:48.485-05:002010-12-28T23:22:48.485-05:00I think a good way to use AES is to use both usern...I think a good way to use AES is to use both username and password as key by concatenating both.<br /><br />Like this:<br /><br />AES_ENCRYPT(password, CONCAT(username, password))Robnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-87230011500466927992010-12-28T23:15:47.354-05:002010-12-28T23:15:47.354-05:00@Rafael Beckel
which means that the key to decry...@Rafael Beckel <br /><br />which means that the key to decrypting all the passwords are also stored in your database. Not secure.Robnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-52180723785907323352010-11-28T18:05:16.904-05:002010-11-28T18:05:16.904-05:00I works and I have it in production.
I don't ...I works and I have it in production.<br /><br />I don't store the original timestamp itself, but its MD5 Hash. ;)Rafael Beckelhttps://www.blogger.com/profile/01830105071166080182noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-55637995179276801612010-08-21T23:49:31.082-04:002010-08-21T23:49:31.082-04:00@Rafael Beckel
That won't work. Your timestamp...@Rafael Beckel<br />That won't work. Your timestamp is gonna be different everytime, which makes your key_str different everytime, so you cannot possibly check against the original AES encrypted password you stored, 'cos for that, you need to have that original timestamp that you encrypted the password with.jfranconoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-77669050608183402162010-05-04T05:29:16.422-04:002010-05-04T05:29:16.422-04:00just reading up on this by chance, and had a few n...just reading up on this by chance, and had a few notes to all your suggestions, especially the ones who have commented on using AES with either MD5/SHA with current timestamp. Could they pray explain, how they will authenticate after the password has been stored, since the current timestamp has changed. Storing the timestamp would be downright foolish. I really wish MySQL would come with a 512 SHA, instead of forcing us to recompile as then we would be forced to redistribute MySQL to clients, and users will lose out on upgrades.Kartikhttps://www.blogger.com/profile/17926142631483844575noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-56099853851624125862010-04-23T09:12:53.280-04:002010-04-23T09:12:53.280-04:00nice md5 description. helped a lot. thxnice md5 description. helped a lot. thxFrankhttp://www.drebbin.denoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-59642709223319185542010-01-04T06:50:55.849-05:002010-01-04T06:50:55.849-05:00@Rafael: Then there is no way to decrypt the passw...@Rafael: Then there is no way to decrypt the password, and also no way to check this password when the users gives it for authentication, which is the whole point of passwords, isn't it? :)<br /><br />@Moe: it is very well possible that AES can be cracked if the cracker knows the password and the key are equal. With the key and the password equal it is no longer AES encryption. <br /><br />However, I'm not a specialist in encryption, so I don't know the details of AES. However, I did some course on encryption so I have some general idea. At least I know that encryption is tricky stuff: specialist study for years on some encryption algorithm to make sure it is secure if used in a certain way. So such an encryption algorithm is implemented with recommendations how to use it. As soon as you start using it differently (like in AES_ENCRYPT (MD5 ('mypass'), SHA1 ('mypass')) ) these security guarantees are no longer valid, and it will probably be very hard to prove it is secure. <br /><br />In this specific case, there is some relation between MD5 ('mypass') and SHA1 ('mypass') so the AES is no longer AES. As soon as your cracker knows you store passwords like this, he/she may find an exploit. <br /><br />The bottomline is: I think you should encryption algorithms the way they were meant to be used. If AES_ENCRYPT (MD5 ('mypass'), SHA1 ('mypass')) ) was so secure, some encryption genius would probably have realized that already :). There's no point in constructing your own encryption layers on top of existing, it's only a risk of creating a security leak.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-26212120809418559772009-11-18T23:26:47.893-05:002009-11-18T23:26:47.893-05:00I once read a thesis explaining that nested encryp...I once read a thesis explaining that nested encryption actually reduces the strength of the encryption and makes it easier to hack. It suggested that a secret 'salt' (http://en.wikipedia.org/wiki/Salt_(cryptography)) be used instead. Wish I could remember where I read that. But I'd seriously research it before wasting CPU cycles only to make it weaker.TheChadnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-76337453880378555902009-10-21T20:13:04.810-04:002009-10-21T20:13:04.810-04:00I know this post is three years old, but there are...I know this post is three years old, but there are a couple of things that ought to be pointed out, especially for the noobs who are looking for advice.<br /><br />AES is not a suitable way of encrypting passwords, since it's two way. The only reason we encrypt password in the DB in the first place is to make sure the passwords are safe, if someone gets unauthorized access to it. And since you need the secret key to encrypt a password, we can assume that the hacker has access to it, if he got into the DB. And at that point we could just have stored the passwords in plaintext to begin with.<br /><br />MD5 and SHA1 are vulnerable to dictionary to reverse lookup attacks, ie you have a list of common words and their hashes which enables you to reverse lookup hashes easily. The way to solve that problem is to salt the hash, something you don't mention at all in the post. Salting means you add a random string to the password string, which makes it less likely that the word exists in a reverse lookup database. So if you're password is mike, and your salt is csidubcsiudbcsdiucb you store sha1('mikecsidubcsiudbcsdiucb') this way you can easily check the password by comparing sha1(password || 'csidubcsiudbcsdiucb') with the stored hash, but you can't recover the password easily. That's how you keep passwords safe.nitro2k01https://www.blogger.com/profile/07178371574984665746noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-19747387108058347122009-10-08T07:12:05.799-04:002009-10-08T07:12:05.799-04:00A good post, solved a lot of doubts. Thank You.A good post, solved a lot of doubts. Thank You.sjmachhttps://www.blogger.com/profile/13128759308353994017noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-34447062522531903732009-09-29T10:21:21.277-04:002009-09-29T10:21:21.277-04:00Sounds to me that we can use AES_ENCRYPT('myte...Sounds to me that we can use AES_ENCRYPT('mytext', 'mypassword') to make an <b>one way password encryption</b>. Is this right?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-54017075952071039472009-09-12T02:56:09.830-04:002009-09-12T02:56:09.830-04:00@Rafael - While at first glance your suggestion ap...@Rafael - While at first glance your suggestion appears to produce a one-way AES encryption solution, if you use MD5(current timestamp) for your key, it is still possible to break. Think of record creation timestamps within log files, for example. If you have access to a log entry that tells you when the record was created, you have the key, and therefore can use AES_DECRYPT to reverse the encrypted field.<br /><br />I like Matei's idea for AES encryption of passwords - but wouldn't use it for fields that are accessed too frequently as there may be a performance hit with all the encryption and decryption for comparison/verification purposes. For passwords, it's generally a one-time hit with a session flag set to indicate that the user is authenticated when accessing subsequent pages of the site that are secured. Just my two-cents'. :-)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-8168245125887667402009-09-11T18:36:32.982-04:002009-09-11T18:36:32.982-04:00If using SHA1 for storing the password, the output...If using SHA1 for storing the password, the output of SHA1 hash is always exactly 40 characters in length (regardless of the plaintext password length). So instead of:<br />password VARCHAR(40)<br />use:<br />password CHAR(40)<br />Thus saving the 1-byte overhead used to store VARCHAR (see <a href="http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html" rel="nofollow">MySql docs</a>). Same tip applies to MD5 (but with 32 instead of 40, of course).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-36118031016278194882009-08-27T09:38:22.701-04:002009-08-27T09:38:22.701-04:00I recently needed to use both functions to encrypt...I recently needed to use both functions to encrypt and decrypt passwords.<br /><br />We have a client who uses a Webservice to register costumers on their PostgreSQL database. That webservice requires that I send a plain text password to it.<br /><br />However, I needed to store this password for 3 days in our local MySQL database while the costumer didn't click in the confirmation link sent to his e-mail.<br /><br />I did this way: in the moment the client send the form with his password, I generate a MD5 hash of the current timestamp multiplied for a random value between -5 and 5. Then I save his password on our MySQL database using AES_ENCRYPT("password",random hash) and I send him the confirmation link with this random hash as a parameter.<br /><br />When he clicks on this link, my code picks that hash and uses AES_DECRYPT(password_field,random hash) to recover the plain text format of his password and send it to the webservice provided by our client.<br /><br />Then, I delete the register of that costumer from our database.Rafael Beckelhttps://www.blogger.com/profile/01830105071166080182noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-79513273464291140832009-08-20T15:23:07.067-04:002009-08-20T15:23:07.067-04:00What about...
AES_ENCRYPT( 'password' , m...What about...<br /><br />AES_ENCRYPT( 'password' , md5(current_timestamp()) ) ?Rafael Beckelhttps://www.blogger.com/profile/01830105071166080182noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-6916361475926971492009-07-15T09:43:46.084-04:002009-07-15T09:43:46.084-04:00Great info, i was finding a way to increase the se...Great info, i was finding a way to increase the security and here it is, all discussed already. :)<br /><br />I will go with @Matei, using sha1 with md5 combination, and additionaly using the different field to make it reversable.<br /><br />Reversing encryption can come in handy when you will be encrypting the client details.Najm Abideenhttps://www.blogger.com/profile/08491871753714461665noreply@blogger.comtag:blogger.com,1999:blog-18337119.post-22163697554497125632009-06-20T12:17:05.912-04:002009-06-20T12:17:05.912-04:00Thank you! Your examples are very clean and helpfu...Thank you! Your examples are very clean and helpful. Best blog about Mysql I've seen yetAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-35143957376155631132009-06-08T20:54:39.556-04:002009-06-08T20:54:39.556-04:00HELP!!!
i am trying to use aes_encrypt but can...HELP!!!<br /><br />i am trying to use aes_encrypt but can't! do i need a particular config setting? eg. ssl etc. if so where and how do i set it?<br /><br />i just want to be able to start using some sort of encryption.<br /><br />thanks.alnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-76094704209131667362008-12-26T05:59:00.000-05:002008-12-26T05:59:00.000-05:00Wow, this was very usefull indeed. Everything is c...Wow, this was very usefull indeed. Everything is clear, there were no parts I didn't get, thanks for the info, I most definately am going to use this!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-25617147896595675542008-12-09T11:11:00.000-05:002008-12-09T11:11:00.000-05:00What the hell is the point in reversible encryptio...What the hell is the point in reversible encryption for passwords, thats just stupid. Keys to the castle ?!?<BR/><BR/>Why not just use SHA512<BR/><BR/>Not reversible, no known exploits.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-13009526183012098032008-11-18T03:37:00.000-05:002008-11-18T03:37:00.000-05:00Hi,I was wondering, can I change a vBulletin/Wordp...Hi,<BR/><BR/>I was wondering, can I change a vBulletin/Wordpress PHP software to use AES encryption rather than others?<BR/><BR/>regards,Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-65561170829618834342008-11-13T14:26:00.000-05:002008-11-13T14:26:00.000-05:00@AdamYou might as well use MD5 or SHA1 then cause ...@Adam<BR/><BR/>You might as well use MD5 or SHA1 then cause you won't be able to get the password back or decrypt it anyway.<BR/><BR/>Unless there's some reverse decryption of the secret word you can use?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-18337119.post-48342484050866187592008-10-31T15:50:00.000-04:002008-10-31T15:50:00.000-04:00When storing passwords using AES, you should actua...When storing passwords using AES, you should actually use a known phrase as the plaintext value, and the user's password as the key. The AES function is resistant to known plaintext attack, and so even if everyone knows the plaintext and the encrypted output, you'll have to break AES before you can recover the key. <BR/><BR/>Since the key in this case is the user's password, it will be protected by AES. For the plaintext, I'd recommend something unique to the user (such as their username) so that it'll be harder to tell if two users have the same password.<BR/><BR/>This also solves the problem of storing the output of AES() since the length of the output is dependent on the length of the text, which you now control.Unknownhttps://www.blogger.com/profile/15859563908190038261noreply@blogger.com