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

[Guest]

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

[paross1]

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

Phil

[Guest]

Phil,

No I am not...

[Kronin555]

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

[Guest]

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>

[brwright]

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.

[Guest]

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...

[gamal_lydian]

Text ancient gamala jewish talk

[gamal_lydian]

golan heights