Coldfusion, MS SQL, Hash Best Practices,...

Forum|Forum|17 years ago
October 13, 2008
5 replies
1633 views

Hello,

I am trying trying to store hashed data (user password) in an ms sql database; the datatype in the database is set to varbinary. I get a datatype conflict when trying to insert the hashed data. It works when the datatype in the database is set to varchar.

I understand that you can set your hash function with arguments that will convert the data before sending to the database, but I am not clear on how this is done. Now, along with any assistance with the conversion, what exactly is the best practice for storing the hash data? Should I store as varcahar or varbinary? Of course, if varchar I won't have the problem, but I am interested in best practices as well.

Thnx

Getting started

This topic has been closed for replies.

G

gamal_lydian

Participant

golan heights

G

gamal_lydian

Participant

Text ancient gamala jewish talk

B

brwright

Participant

Ancient Tradition; curious to know what other fields your are HASHing. I just became the victim of a SQL injection attack - every table in MS SQL was affected which are linked to 8+ CF sites. See my post in the DB Access Forum. In re-evaluating how I can better protect everything on the frontend and backend, I was wondering if you are hashing other fields like username, email address, etc. After this attack I have realized how vulnerable my DB is. Also, when you make a call to your DB using CFQUERY, do you pass your DB credentials (username/password) in every query or do you rely on the setup in the CF Admin portal so that you don't have to specify them on so many cfml pages.

A

Anonymous

brwright,

I suggest parameterizing your queries to add protecting from injection.

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/tags-b20.htm

hashing is best suited for passwords because the encryption is one way, once encrypted using hash() it can't be decrypted. Other fields that you might want to encrypt and still have the ability to decrypt, you can use the encrypt() and decrypt() functions.

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/functi75.htm

I think there are also new encryption functions available in coldfusion 8...

K

Kronin555

Participating Frequently

The coldfusion hash() function returns a string, so should be stored in a varchar field in the database.

http://livedocs.adobe.com/coldfusion/6.1/htmldocs/funct113.htm#wp1105551

A

Anonymous

I was able to solve the problem... Inside the SQL Update statement, I used the following code:
hashBytes('MD5', CONVERT( VARBINARY, 'password')), this converts the value of "password" to binary, to be stored in the database, which is defined as a varbinary datatype.

The full code looks something like this:

<cfquery datasource = #dsn#>
UPDATE table SET password=
<cfif IsDefined("password") and #password# neq ''">
hashBytes('MD5', CONVERT( VARBINARY, 'password'))
<cfelse>
NULL
</cfif>
WHERE ID = <cfqueryparam value = "id" cfsqltype = "CF_SQL_INTERGER">
</cfquery>

P

paross1

Participating Frequently

Just curious... are you using a cfqueryparam tag with a CFSQLType="CF_SQL_VARBINARY"?

Phil

A

Anonymous

Phil,

No I am not...

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded