Hi Thanks for all the replies. For your information, we are going to store passwords using SHA256. I think I will go with the four additional column approach as I proposed (in the current table) - since this need is a PCI compliancy security requirement. I can then pull all the data with one query.
I don't envisage that we will need to record the last 20 passwords as a example in the future - so if I need to expand in the future it should not be too involved. Cheers Neil On Tue, Jan 19, 2010 at 1:11 AM, Carlos Proal <carlos.pr...@gmail.com>wrote: > On 1/18/2010 6:52 PM, Colin Streicher wrote: > >> On January 18, 2010 01:34:15 pm Tompkins Neil wrote: >> >> >>> Hi >>> >>> I'm in the process of designing a login system to a secure web page using >>> MySQL. One of the features is we need to record and ensure that the user >>> password is different from any of the last four passwords he/she has >>> used. >>> I was thinking of create four fields called Password1, Password2, >>> Password3 and Password4 to record the old passwords. >>> >>> Is this a preferred method - or does anyone else have any recommendations >>> ? >>> >>> Thanks, >>> Neil >>> >>> >>> >> I'm not an awesome database designer, most of what I do is code related >> stuff, >> I think what I would do for this is 1. hash the password( sha256/512 >> whatever) >> and then 2. store the hash in a string with delimiters. In that way, you >> solve >> 2 problems. >> You can store as many as you want to because you can just check hashes to >> make >> sure it isn't the same, and second, you aren't storing passwords in plain- >> text, which is a personal pet peeve. >> >> >> > > Neil, > As others appointed, having another table with old passwords is a good > "design" solution, and can allow you to have more than 4 passwords on your > history. But in fact your solution is the best solution for performance and > is called "denormalization", this solution gives good performance because in > 1 read you get all the passwords but has the limitation of be "fixed" to > only 4 passwords (which is not so bad because you can add new columns as > needed, you will never have 20 history passwords anyway, do you ?). > So, thats the trade, design vs performance, you should pick the best for > you. > > The solution proposed by Colin is another way to do it but, from the good > design perspective is NOT a good solution, is what its called a "multivalued > attribute" and all those should be avoided. But again, is up to you. > > Carlos > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com > >
