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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ancient Tradition - 13 Oct 2008 20:00 GMT

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

Reply to this Message

paross1 - 13 Oct 2008 21:54 GMT

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

Phil

Reply to this Message

Ancient Tradition - 13 Oct 2008 22:50 GMT

Phil,

No I am not...

Reply to this Message

Kronin555 - 13 Oct 2008 23:11 GMT

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

Reply to this Message

Ancient Tradition - 15 Oct 2008 17:37 GMT

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>

Reply to this Message

brwright - 15 Oct 2008 16:06 GMT

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.

Reply to this Message

Ancient Tradition - 15 Oct 2008 17:59 GMT

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

Reply to this Message

gamal lydian - 20 Oct 2008 20:58 GMT

http://william gamal mudd ancient gamala jewish talk

Reply to this Message

gamal lydian - 20 Oct 2008 21:00 GMT

golan heights

Reply to this Message

williamgamalmudd1 - 26 May 2009 15:15 GMT

keep
networking

Reply to this Message

Webmaster Forum / ColdFusion / Getting Started / May 2009

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