My toughts and experience on protecting form submission from bots

So as I sit and write this I am fully aware of the potential backlash that this may (or may not) cause. After some serious consideration I obviously decided to write this. I first want to make a couple points perfectly clear. First, I am not an expert in the security field. My points are merely observations on some things I have recently seen. Also, I am in no way attempting to discredit or diminish the work done by others. I believe that people are well intended until, well, they aren't.


Duplicate columns in select query

I ran into this interesting issue this morning. I had this query to return some data.

view plain print about
2 SELECT col1, col2, col3, othertable.*
3 FROM firsttable
4 JOIN othertable ON =
5 where = #id#

I added the join to "othertable" table and just returned the other tables full content. I intended to shrink the column return once I figured out what I wanted to use.

For some reason in the return the "col1" column was always empty. I looked into the table's data and there was data there. So, after a bit of poking around I found the issue.

In the "otherTable" there was a column called "col1". This column had no data. For some reason ColdFusion was overwriting the data of the first column with data from the 2nd. I ran the same query outside of CF and the data was returned correctly. Each column still retained is own data.

As a quick test, you can use the code below. The output is very interesting. No matter what you change the data to in the first column it is always overwritten with the data from the 2nd.

view plain print about
1<cfset myQuery = QueryNew("Name, Name")>
2<cfset newRow = QueryAddRow(MyQuery, 2)>
3<cfset temp = QuerySetCell(myQuery, "Name", "Sample Data col 1", 1)>
4<cfset temp = QuerySetCell(myQuery, "Name", "Sample Data col 2", 1)>
5<CFDUMP var="#myQuery#">

Granted, I don't expect anyone to actually do this. You should always name our columns in your query returns and never return "*".

Till next time...


Unicode data and queries

I have been working on a part of an application that stores translation data. How it works is very simple. A user would go in and translate text from one language to another and save the translation. Sounds very simple right? Yea, that is what I thought too. That was actually until I tried to save the data.

What was happening was this. The data being saved in the database was showing up as "?????? ???????" when querying the data through SQL Management Studio. I then double checked to see if I was going crazy. I ran a select query via ColdFusion and the data came back in the same way. I found this quite strange considering the cfstoredproc debug output of the insert looked good.

After doing some digging I made some alterations. The alterations were based on the fact that I was using the wrong data type. First I changed the parameter data type in the stored procedure from varchar to nvarchar. I also changed the table data type to nvarchar. This should now allow me to insert and store the data correctly.

I then reran the inserts and I was still at the same place. The data was still getting inserted wrong but the debugging of the insert looked accurate.

I then did some more digging and found something else I needed to change. This next change was to alter the datasource in ColdFusion to enable Unicode data. Once this was done I reran my tests.

BINGO! JACKPOT! YOU WIN! The data was now saved and retrieved as expected. Finally I was done with this part of the app. It was then, in the beginning moments of when I was reveling in my victory, that my world came crashing down around me.