A couple of days ago I found a pretty cool jQuery plugin for doing two select boxes called picklists. I started using it but immediately noticed one feature was missing from it, when the list is loaded it doesn't load "selected" items into the second list. First I tried to hack some code to move the stuff and although I got it working I decided that wasn't really the right solution. Anyway I've add the setting "preload". When its true items that are selected at the time picklist plugin is loaded are added to the selected list so now its a pretty cool tool for .net/cf/php/etc. When the setting is false it does the same as it did before (the selected items list is blank). Anyway enjoy! ;P

 jquery.picklists.js (4.75 kb)

Recently a old client I use to do some consulting for called me up because their in-house development team was having problems stopping SQL Injection. I did the polite thing and attempted to explain how it could be fixed. They then asked me what it would cost for me to fix their cold fusion and SQL Server code. I sent them over a rate sheet and I have yet to hear back that was about 3-4 weeks ago. Guess they didn't like the price ;P

Anyway fast forward one of my friends was browsing my sites (.NET) and ran across an error in which it appeared Npgsql stated there were no more connections in the pool (woops forgot to turn on custom errors). Anyway I decided to see if this was happening alot so I opened up event viewer. What did I find someone was trying to attack my site with the same type of script. So I ran a few quick tests to confirm that my stored proc's are not vunerable. Then i got to thinking even if they were I implement at least a dual password system where one account (read-only) is used for select queries and another (read-write) is used for everything else so I figured I am pretty safe. Never mind that the script is targeting SQL Server and my projects are running on PostgreSQL. Needless to say I decided to let it go since who ever is attempting to attack me is just spinning their wheels and maybe as long as they are going after me they wont find someone who is vunerable.

In any case I find the whole situation mildly amusing.



I cannot stress enough sanatize your inputs and consider setting up different user accounts so that in case you forget to to protect something you limit what an attacker can do.

So earlier today I was asked to update this site I do maintenance on to filter out products from displaying more than once per page. Normally this is no problem because I would just do it in the query using a group by or sub query if I had to. In this particular case doing it in the query would have required substantial re-work of a query that is already quite complex. Basically products can exist in many categories and this is a report that is grouped by category. The client decided they did not want to see the product more than once per page regardless of if it existed in more than one category. So I started to look at what it would take to write something in cold fusion. After looking on line for a few minutes I noticed that I could not find a simple function that would suit my needs and prevent me from having to do something like this in the future. So I opened up CF-Eclipse (I am totally spoiled on visual studio now, if I had to do CF more often I think I would go back and get Home site 5.5+ again) and began to write a simple function. I decided the function should be able to dynamically rebuild the query results regardless of the column names and filter out multiple values on a particular column.

First I realized I needed an easy way to find out if a value already existed in an array. Unfortunately Cold Fusion lacks a IndexOf method for searching arrays so that would be step 1.

<cffunction name="IndexOf">
    <cfargument name="arr" type="Array">
    <cfargument name="val" type="String">
    <cfif ArrayLen(arr) gt 0>
        <cfloop from="1" to="#ArrayLen(arr)#" index="i">
            <cfif arr[i] is val>
                <cfreturn i>
            </cfif>
        </cfloop>
    </cfif>
    <cfreturn 0>
</cffunction>

I created another function that would dynamically recreate the recordset but filter on a column named productid.

<cffunction name="UniqueQuery">
    <cfargument name="query" type="query">
    <cfset columns = query.ColumnList>
    <cfset myQuery = QueryNew(columns)>
    <cfset items = ArrayNew(1)>
    <cfloop query="query">
        <cfif IndexOf(items, productid) is 0>
            <cfset temp = ArrayAppend(items, productid)>
            <cfset newRow = QueryAddRow(myQuery)>
            <cfloop list="#columns#" index="i">
                <cfset temp = QuerySetCell(myQuery, i, evaluate(i), myquery.recordcount)>
            </cfloop>
        </cfif>
    </cfloop>
    <cfreturn myQuery>
</cffunction>

Now this is all well in good and works for what I want but with a little tweaking it could be better and more portable.

<cffunction name="UniqueQuery">
    <cfargument name="query" type="query">
    <cfargument name="filter" type="string">
    <cfset columns = query.ColumnList>
    <cfset myQuery = QueryNew(columns)>
    <cfset items = ArrayNew(1)>
    <cfloop query="query">
        <cfif IndexOf(items, evaluate(filter)) is 0>
            <cfset temp = ArrayAppend(items, productid)>
            <cfset newRow = QueryAddRow(myQuery)>
            <cfloop list="#columns#" index="i">
                <cfset temp = QuerySetCell(myQuery, i, evaluate(i), myquery.recordcount)>
            </cfloop>
        </cfif>
    </cfloop>
    <cfreturn myQuery>
</cffunction>

Keep in mind that only the first two snippets have been tested but I don't see why the third would not work. I know this isn't hard or really that complicated but maybe this will help someone out so they don't have start from scratch.