July 19, 2011 at 11:39 am
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
July 19, 2011 at 11:41 am
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
July 19, 2011 at 11:45 am
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/
July 19, 2011 at 11:50 am
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.
July 19, 2011 at 11:53 am
Yes. The stored procedure does require parameters.
July 19, 2011 at 11:56 am
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.
_______________________________________________________________
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/
July 19, 2011 at 11:58 am
Same table vs different table makes no real security difference.
Naming the column something other than "Password" just makes the database that much harder to maintain and develop against.
So long as the data is stored and accessed in a hashed format, should be fine. Unless laws or policies say otherwise, then follow those.
- 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply