Just figured to share this.

Adobe flaw puts PCs at risk

You can download the patch here.

So say you did run into a comma delimited field, but this time with IDs. To make things worth, there is a second comma delimited field that contains data related to the first field in the same order.

E.g.
Field1 (ids): 1,3,5,8,10
Field2 (prices): 10,44,18,40,115

Your task: you need to first the price for ID number 5.

Ok, so now in you coldfusion code you could query the entire table, loop through the records, add an extra column, and user querysetcell to match all prices for the specific ID. Of course, the ID might be in different positions for every row.

If definitely defeats the definition of relational database, but I have seen this time after time and it is extremely handy to have the solution already written by the time you need it.

Solution: Let’s combine the UDF ListGetAt with the ListFind that I wrote in my previous blog.

The ListFind will return the position for the ID in every row while ListGetAt returns the token at the specified position. It works just like it would in Coldfusion, but you can apply it to an entire recordset.

For example, let’s find the price for ID number 5 in the previous example

Select dbo.ListGetAt(field2,dbo.ListFind(field1,5,’,’),’,’) as thisPrice from table

Download the SQL function here.

Some time we run into bad database design were a single fields contain a comma delimited list of pointers or attributes; sometime we even do it ourselves to kick out some quick database… for example in a typical Coldfusion security framework you may have a database field with a comma delimited list of roles in the users table.

e.g.
uname: rob; roles: admin, dbadmin, user; datestamp: 6/13/2005

So, you may query the DB with a bunch of like operators which may or may not be accurate. You cannot query the DB with like ‘%admin%’ because it will return true if you’re an admin or dbadmin.

Of course you can use like ‘admin,%’ or ‘%,admin,%’ or ‘%,admin’ or ‘admin’… but that’s too much!

Here’s where my small SQL UDF’s come handy. I replicated the ListFind coldfusion function as a SQL function.

You can now query from the users table

select * from users where dbo.ListFind(roles,’admin’,’,’) > 0.

The third parameter works just like in CF so you can specify other characters such as pipe ‘|’ as a delimited if needed.

Download the SQL function here.

Shared hosting companies usually block cfexecute and cfregistry at the sandbox level; but what about cfobject or the createobject function? Coldfusion 5.0 used to have the license serial number in the Server scope. Macromedia immediately recognized the security flaw and removed it in the MX versions. Yet, undocumented coldfusion such as the ServiceFactory object allows anyone to get a hold of the serial number. Perhaps, try to execute the following code:

I am CTO for iChameleon Group. I have a bachelor in Computer Science and Engineering from F.A.U, an M.B.A. with specialization in Entrepreneurship from Wayne Huizenga school of Business, and I am an Advanced Certified Coldfusion Developer.

iChameleon Group specializes in cutting edge Rich Internet Application development.

Welcome to my blog.

We’ll be discussing mostly Coldfusion MX, SQL Server, Flash Remoting, and Rich Internet Applications.

NOTE: This my personal blog, and the opinions and statements voiced here are my own.