markifornia Posted May 1, 2012 Share Posted May 1, 2012 I understand this is a very simplified example given in the book as there only consists of few records. encode table - contains the id and card number aes_salt - contains just the salt On page 387, the instructions for decrypting a stored credit card number shown below: SELECT @salt:salt FROM aes_salt; SELECT id, AES_DECRYPT(card_number, @salt) AS cc FROM encode; This is assuming we are working from just the books example, but what if there were two salt values and two credit card numbers? I am assuming the way to assign each salt with each credit card between two tables is to use a foreign key? How would you go about running the SELECT query above using multiple records? For example if I had multiple credit card numbers from the encode table and multiple salts in the aes_salt table? Would the salt table be already pre populated with salt values?? Here is my taking a stab at it. Assuming my theory is correct about the foriegn key, aes_salt will have an id column which is an fk of encode. Step 1: SELECT @salt:=salt FROM aes_salt; INSERT INTO encode (card_number) VALUES (AES_ENCRYPT(123456, @salt)); Step 2: SELECT @salt:salt FROM aes_salt WHERE id=2'; SELECT id, AES_DECRYPT(card_number, @salt) AS cc FROM encode; it just seems as though there needs to be a foreign key and a WHERE clause to make this work with multiple records. Any advice appreciated. Thanks, Mark Link to comment Share on other sites More sharing options...
Edward Posted May 2, 2012 Share Posted May 2, 2012 Hey buddy, i would help you if i could on this but at the moment Ive had to go back for a rerun of the SQL chapters as i need to brush up on it myself. Link to comment Share on other sites More sharing options...
Antonio Conte Posted May 2, 2012 Share Posted May 2, 2012 Yes, you do need to perform multiple inserts and a JOIN query here. If each salt is unique, you need to insert it into a salt table with the user_id/email/something unique as the foreign key. INSERT INTO salt ( user_id, salt ) Values(1, 'unique-salt-for-user-id-1'); INSERT INTO user ( user_id, card_number) VALUES ( 1, AES_ENCRYPT('the_card_number', 'unique-salt-for-user-id-1') ); When you need the salt again, you need to select the salt too: SELECT user.user_id , AES_DECRYPT(user.card_number, salt.salt) as card_number FROM user as user INNER JOIN salt as salt ON ( user.user_id = salt.user_id) LIMIT 1 That's the general idea. 1 Link to comment Share on other sites More sharing options...
Larry Posted May 2, 2012 Share Posted May 2, 2012 Or really, in that case, you'd just store the salt in the same table as the database being salted. That way, you don't have to do a JOIN. Or, you could just use another part of the stored record as the salt, which has the same effect. 1 Link to comment Share on other sites More sharing options...
markifornia Posted May 3, 2012 Author Share Posted May 3, 2012 Yes, you do need to perform multiple inserts and a JOIN query here. If each salt is unique, you need to insert it into a salt table with the user_id/email/something unique as the foreign key. INSERT INTO salt ( user_id, salt ) Values(1, 'unique-salt-for-user-id-1'); INSERT INTO user ( user_id, card_number) VALUES ( 1, AES_ENCRYPT('the_card_number', 'unique-salt-for-user-id-1') ); When you need the salt again, you need to select the salt too: SELECT user.user_id , AES_DECRYPT(user.card_number, salt.salt) as card_number FROM user as user INNER JOIN salt as salt ON ( user.user_id = salt.user_id) LIMIT 1 That's the general idea. get the general idea here, the foriegn key is what would associate the records between the tables. great example you've given. Or really, in that case, you'd just store the salt in the same table as the database being salted. That way, you don't have to do a JOIN. Or, you could just use another part of the stored record as the salt, which has the same effect. thanks larry, this is absolutely genius. didn't occur to me to use just about any part of the data as the salt within the same table. brilliant. Link to comment Share on other sites More sharing options...
Edward Posted May 3, 2012 Share Posted May 3, 2012 It's actually worth taking a look at some of the well know eCommerce (virtue mart, magento, zen cart, os commerce, presta shop) and message boards (vbulletin, phpbb) setups, you can see in phpmyadmin how all their databases are setup and also the view the mysql datatype's they are using. That way you can can possibly get some tips from them on making your databases. Ive seen vbulletin have the salts inside the users table, the same as Larry is mentioning here. Link to comment Share on other sites More sharing options...
markifornia Posted May 3, 2012 Author Share Posted May 3, 2012 It's actually worth taking a look at some of the well know eCommerce (virtue mart, magento, zen cart, os commerce, presta shop) and message boards (vbulletin, phpbb) setups, you can see in phpmyadmin how all their databases are setup and also the view the mysql datatype's they are using. That way you can can possibly get some tips from them on making your databases. Ive seen vbulletin have the salts inside the users table, the same as Larry is mentioning here. Good advice, I have worked with zencart extensively though I hate working management systems, I hate upgrades!!!! But forced to do so for clients. I'd be curious to see what these web apps are using to salt data. Link to comment Share on other sites More sharing options...
Edward Posted May 4, 2012 Share Posted May 4, 2012 Yes, but these are open source solutions, so everyone can see their techniques and lessens the security. For our own web application's is that what we really want, to be like everyone else in the market, or or would it not better to take a few steps back and consider how we can customize our own security solution, leaving a few question marks floating above foreheads. I don't know if you saw my post the other day about what is the point of having 10 locks on the door if the keys are under the doormat, allow me to elaborate, its a bit like that, the hardest part for is where and how to hide the keys. Link to comment Share on other sites More sharing options...
Larry Posted May 4, 2012 Share Posted May 4, 2012 It's a common misconception that the salt should be a secret. Or absolutely protected. Really, the salt just needs to be unique (or unique-ish) for each record. Link to comment Share on other sites More sharing options...
markifornia Posted May 4, 2012 Author Share Posted May 4, 2012 It's a common misconception that the salt should be a secret. Or absolutely protected. Really, the salt just needs to be unique (or unique-ish) for each record. got it larry. i was thinking of generating a unique set of strings via php functions which could be the salt Link to comment Share on other sites More sharing options...
vr_driver Posted July 30, 2012 Share Posted July 30, 2012 Hi Larry, I've been starting to think about this topic recently, (with all the recent hacking and stuff) and although I'm no expect in this field, I was a little surprised to read about storing the salt in the database. If a hacker stole the database, they'd be able to find the salt right there, and work their magic... As ignoramous at this, would it be better to use the password as a salt, such as this? $password = "banana" $salt = sha1(md5($password)); $password = md5($password.$salt); I was looking at this page for the example: http://pbeblog.wordpress.com/2008/02/12/secure-hashes-in-php-using-salt/ I don't know how other people use the salt, but I would want to embed it in the files, or store it either... I thought about using values that were constant, or specifically related to the user, eg, the username, an id number etc, but all these could still "in theory" change, and effect the salting of a password. So the only thing I could think of, was make salt, based on the password... What are your thoughts? I don't know what's in your 4th book, and maybe this has been addressed, or is a common question, I don't know. Just thought I'd ask some pros. Thanks, -Steve Link to comment Share on other sites More sharing options...
Larry Posted July 30, 2012 Share Posted July 30, 2012 There are a lot of misconceptions about salts, including the idea that they have to be super secretive. Salts should be unique, and they help prevent brute force attacks. The beginning of this article helps explain this: http://blog.ircmaxell.com/2012/04/properly-salting-passwords-case-against.html In terms of your proposals, to start, if a hacker gets your entire database, the game is over. That's the WORST thing that can happen. In fact, in that case, the security of the passwords is probably irrelevant, as the hacker has all the users's other information, which is probably more important. It's like worrying about the security of information in your safe after someone has stolen your safe. Doesn't really matter at that point. Second, you shouldn't use a version of the password for the salt. Partly because you'd be trying to secure something with itself, and second because passwords won't be unique. In terms of what would be unique in a database that could be used: ID column (primary key), username, email address. The PK should never change. If it's possible for the username or email address to change, you'd just update the stored password as part of that process. Link to comment Share on other sites More sharing options...
vr_driver Posted July 31, 2012 Share Posted July 31, 2012 Hi Larry, Thanks for your honest answers. I'll go and have a look at that article, and I appreciate your feedback on this. Cheers, Steve Link to comment Share on other sites More sharing options...
markifornia Posted August 3, 2012 Author Share Posted August 3, 2012 $password = "banana" $salt = sha1(md5($password)); $password = md5($password.$salt); Steve, I am also curious about the comparison between SHA1 vs salted passwords. In your example above, I've never seen the password pass to two of the functions md5 and sha1, is that common? Would SHA1 suffice, or would the extra md5 function add more encrypted security? I am not clear though as to which one is more secure though, salted passwords more secure than just passing the SHA1 function? Which one to use? Link to comment Share on other sites More sharing options...
Larry Posted August 3, 2012 Share Posted August 3, 2012 First, salting is always more secure than not salting. With a salt, the same password used by two people (which can easily happen with popular sites) is stored with two different values. Second, SHA1() is more secure than MD5(), although I think both have been cracked by now. I don't think there's any benefit to applying MD5() and then SHA1(), in fact it's probably the case, I believe, that doing so makes it less secure. Link to comment Share on other sites More sharing options...
markifornia Posted August 4, 2012 Author Share Posted August 4, 2012 Thanks Larry, I don't have the book with me at the moment so I was unable to check if your chapter on salting also includes passing the same data to a SHA1 function as well. Is it possible to salt and pass the SHA1 function to the data? Does this make the data more secure? If applying both methods simultaneously (for a lack of a better word) makes things "more" secure, then that is the approach that should be ideally used. Link to comment Share on other sites More sharing options...
Larry Posted August 6, 2012 Share Posted August 6, 2012 I don't understand what you mean in your question. Specifically I don't know what you mean about passing things to data. Link to comment Share on other sites More sharing options...
Recommended Posts