Railo vs ColdFusion cfqueryparam and SQL HashBtytes


While testing an application against Adobe ColdFusion vs Railo 4.2.1; everything went quite well except for a simple piece of inline SQL for an a legacy appication with a bit HashBytes encryption.

Nothing too fancy there, just comparing Hashed String with an inputted string, like so:

 .... where hashedkey =HashBytes('SHA1', <cfqueryparam cfsqltype="cf_sql_varchar" value="#variables.unhashedkey#">)


Except...

The input of HashBytes is a binary. Adobe CF, created the hashedkey (elsewhere) with a cfqueryparam type of  cf_sql_varchar but not cast/ converted as a binary.

There was no cf_sql_nvarchar which was added in CF10,  

Railo came back with a different results here running this code on each environment:
<cfquery name="qryInteresting" datasource="datasource">
select hashbytes('SHA1', 'poodle') nocfqueryparam
, hashbytes('SHA1', cast('poodle' as varchar(50) ) ) nocfqueryparamCastVarchar
, hashbytes('SHA1', <cfqueryparam cfsqltype="cf_sql_varchar" value="poodle">) cfqueryparam
, hashbytes('SHA1', cast('poodle' as binary(20) ) ) castasbinary
</cfquery>

<cfoutput>
Without CFQUERYPARAM# toBase64(toString(charsetEncode( qryInteresting.nocfqueryparam, "utf-8")))#<br>
With CFQUERYPARAM: # toBase64(toString(charsetEncode( qryInteresting.cfqueryparam, "utf-8")))#<br>
Cast as Binary: # toBase64(toString(charsetEncode( qryInteresting.castasbinary, "utf-8")))#<br>
</cfoutput>


 Adobe CF (9)
Without CFQUERYPARAM77+977+9D++/vSJz77+9bu+/vUXvv71oEEHvv73vv73vv71lLg0=
With nocfqueryparamCastVarchar: 77+977+9D++/vSJz77+9bu+/vUXvv71oEEHvv73vv73vv71lLg0=
With CFQUERYPARAM: 77+977+9D++/vSJz77+9bu+/vUXvv71oEEHvv73vv73vv71lLg0=
Cast as Binary: 77+977+9KGjvv70e77+9azFi77+9agnvv71277+977+9
Railo:
Without CFQUERYPARAM77+977+9D++/vSJz77+9bu+/vUXvv71oEEHvv73vv73vv71lLg0=
With nocfqueryparamCastVarchar: 77+977+9D++/vSJz77+9bu+/vUXvv71oEEHvv73vv73vv71lLg0=
With CFQUERYPARAM: 77+9MX01Du+/ve+/vWcKK++/ve+/ve+/ve+/vWZJ77+9URQr
Cast as Binary: 77+977+9KGjvv70e77+9azFi77+9agnvv71277+977+9


So the only on that comes back different is   hashbytes('SHA1', <cfqueryparam cfsqltype="cf_sql_varchar" value="poodle">) which was the part of the code! Interesting, the workaround is there in Railo to make cast the column as so, but I couldn't figure out why the difference:

 hashbytes('SHA1', cast(<cfqueryparam cfsqltype="cf_sql_varchar" value="poodle"> as varchar(50) )

PS poodle is clearly not the encypted sting, just the very first thing that came into my head when writing the explanation.

Comments

Post a Comment

Popular posts from this blog

Global SQL Procedure, System Objects and sp_ms_marksystemobject

You may come across a need to have a database of utils full of generic procedures which work on indvidual databases (say client databases). You can't pass the database name into the procedure as a parameter and say "use @dbname" in the procedure and dynamic sql sucks. One workaround is to create the procedure in the master database and then mark it as a system object. eg use [master] create procedure sp_doThis // note the sp_ prefix is required begin // etc etc end go exec sp_ms_marksystemobject 'sp_doThis' // second note, this procedure is undocumented, so I wouldn't be relying on this for life or death. use [myotherdb] exec sp_doThis go All done!
Read more

cf_sql_timestamp vs cf_sql_date vs getdate()

If there's one thing I don't like it is people confusing a date with a timestamp, and how a lazy bit of development can ruin a load of data. Here is a simple query inserting data to a timestamp I found in a system where all the created dates were truncated because of the incorrect syntax. Bad one which was in use: insert into testdate values (<cfqueryparam cfsqltype="cf_sql_date" value="#createodbcdatetime(now())#">); Result :  2015-09-29 00:00:00.000 Best One (easiest to read) insert into testdate values(getdate() ); Result : 2015-09-29 10:10:09.880 Not great (no binding) insert into testdate values(#createodbcdatetime(now())#); Result :  2015-09-29 10:14:44.000 What the bad one should have been insert into testdate values(<cfqueryparam cfsqltype=" cf_sql_timestamp " value="#createodbcdatetime(now())#">)
Read more

Lucee 4.5.2 cfpdfparam difference with Adobe ColdFusion

Suppose the following on Lucee 4.5.2: <cfset x = 1> <cfdocument format="pdf" pagetype="A4" name="myVar#x#"> <cfdocumentsection> Hi there </cfdocumentsection> </cfdocument> <cfpdf action="merge" destination="RAM:///myPDF.pdf" overwrite="yes">     <cfpdfparam source="#evaluate("myVar#x#")#" /> </cfpdf> <cfcontent file="RAM:///myPDF.pdf" type="application/pdf"> then to check it also runs on Adobe Cf; you'd actually end up with: "ByteArray objects cannot be converted to strings."   The original code for Adobe CF ran with the paramater like so:  <cfpdfparam source="myVar#x#" /> Both sets of documentation list source defined as follows: "Source PDF file to merge. You can specify a PDF variable, a cfdocument variable, or the pathname to a file." This would indicate it is just...
Read more