What is the best way to store passwords

Question

Post reply

What is the best way to store passwords

MikeAngelastro-571287

SSCommitted

Points: 1576
More actions
July 19, 2011 at 11:39 am

#244249

Hi,

I performed a search in SSC but didn't find an answer.

I have a student table in a database that contains a password field. I am using the table to test if SQL Injection is possible so as to avoid it. One thing I am doing is storing the passwords encrypted in a field named 'Password'. I use a stored procedure with parameters for ID and password(encrypted of course). If you open the table in SQL Server Manager you can see the encryption result of the passwords. I have a few questions about what I have done here:

1. Should I change the name of the password column to something that is not obvious?

2. Should I create another table that would contain the encrypted passwords and use a join in the stored procedure?

3. Is there anything else I should be doing?

FYI, the encryption key is built into the utility used by the application I am testing with.

Any thoughts?

Mike

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'm not sure what the name of a column in a table has to do with preventing SQL injection. That's about access methods in your data access layer.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

I agree with Gus that the table itself is neither prone to or not prone to sql injection. It is all about how you access your data. You say your code is using a stored proc to authenticate your users. I would assume that means you are passing parameters to your stored proc. That is the first and probably the most solid defense against sql injection. It sounds like your table structure and data at rest are probably pretty reasonable.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

MikeAngelastro-571287 SSCommitted Points: 1576 More actions · Answer 3

It doesn't have anything to do with SQL Injection. It's just that, as I was testing for that, I was wondering about the password storage issue. In addition, it is often in the log-in process that the SQL Injection is attempted and that involves a password. Maybe I provided too much info.

I'm just wondering how best to design the tables to deal with passwords in the safest way.

MikeAngelastro-571287 SSCommitted Points: 1576 More actions · Answer 4

MikeAngelastro-571287

SSCommitted

Points: 1576

July 19, 2011 at 11:53 am

#1355069

Yes. The stored procedure does require parameters.

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

Sounds like you have done pretty much what you can. You have to store the hash somewhere and there really isn't much wrong with putting it in a password column. This of course all depends on the application and the regulations around password storage/retrieval if such regulations exist. Is this a banking application? Is it ecommerce? Are you processing stored credit card information? If it is just an application and you are trying to protect your user's confidential information as best as possible it sounds like you are pretty much good to go.