Skip to main content
A
Anonymous
October 13, 2008
Question

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

  • 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
This topic has been closed for replies.

5 replies

G
gamal_lydian
Participant
October 20, 2008
golan heights
G
gamal_lydian
Participant
October 20, 2008
Text ancient gamala jewish talk
B
brwright
Participant
October 15, 2008
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
October 15, 2008
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
October 13, 2008
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
October 15, 2008
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
October 13, 2008
Just curious... are you using a cfqueryparam tag with a CFSQLType="CF_SQL_VARBINARY"?

Phil
A
Anonymous
October 13, 2008
Phil,

No I am not...
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices