You Give Love a Bad (Dot) Name

A month or so ago I had to throw together a script to help with some forensics, it strikes me that it might come in handy for other people, so I'm going to post it here. Just edit the first bit and provide it with your datasource name, and run it. You might need to adjust the timeout settings in ColdFusion Administrator — if your database user has access to a lot of stuff, it could take a very long time to run. 

It uses one of the new CF8 tags, CFDBinfo, that hasn't gotten much press. It also highlights something that I found a bit disturbing, though I guess it makes sense… even though you assign a database name to your DSN in CF, CF isn't locked down to that DSN, it can interact with any database on the server that the user you put in there can access.

This snippet of code is also pretty useful — it shows all your DSN settings at once:

<cfset sf = CreateObject("java", "coldfusion.server.ServiceFactory")>
<cfdump var="#sf.DataSourceService.getDatasources()#" expand="true">


With either of these, please exercise caution.. don't put them somewhere anyone but you can execute them, and don't leave them up there any longer than it takes for you to extract the data you need.

I keep this in a file on my desktop.. may as well put it here, too… maybe it'll help someone someday.

CRLF:
cfset crlf = #chr(13)#&#chr(10)#

Variable Variables:
cfset varvar = "MyVarName"
cfset "#varvar#" = "whatever"
#evaluate(varvar)# outputs "whatever", #varvar# outputs "MyVarName"

Existence/Type Test:
StructKeyExists() IsDefined() (isDefined fails on complex vars with[], use dot notation) IsArray() IsStruct() IsQuery() IsSimpleValue() IsNumeric() IsDate() IsBinary() IsBinary()

CFHTTP Connection Failure
Gzip bug — add this:
cfhttpparam type="Header" name="Accept-Encoding" value="deflate;q=0″
cfhttpparam type="Header" name="TE" value="deflate;q=0″
Also check for redirect="yes|no" — sometimes redirect needs to be set to no

Query Variables:
query_name.currentRow, query_name.columnList, query_name.RecordCount, result_name.sql

Query of Queries:
cfquery dbtype="query" name="foo" — select * from query_name

CFDirectory Performance:
listinfo="name" significantly improves performance, if you don't need other columns

CFQueryParam:
Null="#YesNoFormat(NOT LEN(MyVar))#" will make the value NULL instead of empty
List="yes" will internally CFParam all list values so you can do WHERE ID IN (cfqueryparam… list="yes") and get a valid list format. Add separator = '|' instead of delimiter='|' — for some reason this tag is different than all the other ones.
CF_SQL_TIMESTAMP — datetime, smalldatetime (use CreateODBCDateTime() for this)
CF_SQL_CHAR — char, nchar, unique identifier
CF_SQL_VARCHAR — varchar, nvarchar, sysname
CF_SQL_LONGVARCHAR — text, ntext
CF_SQL_NUMERIC — numeric (scale='2′ for 2 decimal places)
CF_SQL_INTEGER — int
CF_SQL_SMALLINT — smallint
CF_SQL_TINYINT — tinyint
CF_SQL_BIT — bit
CF_SQL_FLOAT — float
CF_SQL_DECIMAL — decimal, money, smallmoney (scale='2′ for 2 decimal places)
CF_SQL_BINARY — binary, timestamp

Merge Log Files (Not CF, but useful!)
copy *.log merged.log

CF Regex:
rereplace(foo, "[[:space:]]+", " ", "all") — turns all whitespace into single space
rereplace(foo, ".*?", "", "all") — strips all HTML tags (probably fails if they span multiple lines)

(updated July 16)