June 9, 2005 at 6:45 am
Hi,
I have run into a db that uses varbinary as the data type for a col that stores passwords in a user data table. On enquiry, I was told the password needed to be stored encrypted and should not show up as is, say during a SELECT. The data is encrypted at the app and sent to the db at storage time and decrypted on retrival. I was wondering if this is the right thing to do. Shouldn't it be a varchar instead. I am told it has been tested and the project is slated to go live soon.
Need some inputs from someone.
Thx.
Vis.
June 9, 2005 at 7:19 am
As long as the encryption/decryption works, I see nothing wrong with this.
June 10, 2005 at 1:52 am
Note: difference between varchar and varbinary;
Your encryption script can use special charactors which are not supported by varchar, like the return and tab charactors cause truncation of data string when used. So this means your encryption supports special charactors and is thus more powerful, possibly (128 byte) encryption.
Coach James
June 10, 2005 at 7:30 am
I have two suggestions for you from personal experience with passwords and databases.
1. You can store them either way in the DB but if it is a varchar they will need to watch out for characters like Coach James said that can cause not only the results to come back funky from the DB but also in the application that your development team is putting together. Using a string representation can get you around that as well as using binary fields. Either way works fine.
2. Passwords should not be encrypted and decrypted. I don't know how you are doing it on your system so I could be off base but if you are storing the password encrypted with a tripple DES key or some other cyper then the password can be figured out. A better approach is to use a one way hashing algorithm like SHA-1. Then when the password is stored there is no possible way to get the password from the stored data. When the application has to verify the password it uses the sam algorithm again on the value the user enters at run time and checks the result to what is stored on the DB.
I don't know your system so I could be off and it probably isn't your department but just something to keep in mind if you have any influence on it.
HTH,
Jim
June 10, 2005 at 9:57 pm
Hi All,
Thx a lot for the feedback. Just one more question for Jim. From what I have seen, mostly, all our applications use SHA-1 and this was just one of those one-off things that confused me. I am new to this place and new to encryption. Is it ok to use varchar if I were use SHA-1 or should I still use varbinary.
Once again,
Thx.
Vis.
June 13, 2005 at 7:42 am
We are using the image data type. SHA-1 hashes are always 20 bytes not that that matters with the image data type. If you use a varchar field then the size would have to be at least 40 bytes so you can store the hex representation of the binary data. Reason being the output from the SHA-1 algo can include a byte of data that has all 0's which would indicate the end of a string. Or we have seen single quotes which messed up our SQL statements to save the data.
So I guess the answer is if you can change the DB without a major issue then use an image field otherwise if it has to be char then you need to use a 40 byte char field and convert the binary data to an ascii representation of hex.
For example this would be the representation of abc123
0x6367C48DD193D56EA7B0BAAD25B19455E529F5EE
If you need more info email me @
remove the spaces: jberg @ net-link . net
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply