Figured this one out by sheer, dumb luck.

<cfscript>
driverManager = createObject("java","java.sql.DriverManager");

conn = driverManager.getConnection("jdbc:macromedia:oracle://111.11.11.111:1521;SID=mySID;serverName=111.11.11.111;user=**********;password=**********");

stmt = conn.createStatement();
recordSet = stmt.ExecuteQuery("select * from table order by 1 desc");
results = createObject("java", "coldfusion.sql.QueryTable").init(recordSet);
</cfscript>

<cfdump var="#results#">

The trick was to add the username and password as properties of the connection string rather than having them inline.

This fails

driverManager.getConnection("jdbc:macromedia:oracle:username/password/111.11.11.111:1521;SID=mySID;serverName=111.11.11.111;")

An old client of mine recently contacted me with a problem. Their home page, several sub-pages, and the administrative section weren’t displaying properly and functionality was broken. Turns out, they were a victim of the recent swarm of SQL injection attacks.

After pulling the old code out of my repository, I discovered that I’d used CFQUERYPARAM (I won’t iterate the hundreds of articles you can find by yourself stating, “USE CFQUERYPARAM, DUMMY!!”) in all of my business logic code, so I couldn’t quite figure out what was going on. While perusing the web for other ideas, I ran across Jason Bartholme’s blog entry about preventing SQL injection attacks. He has a simple bit of code that he put in onRequestStart() so check if the keyword DECLARE was in the URL.

<cfif not structIsEmpty(URL) >
   <cfloop list="#StructKeyList(URL)#" index="i">
      <cfif URL[i] CONTAINS "4445434C415245">
         <cfmail to="me@mysite.com" from="them@theirsite.com" subject="SQL Injection Attempt" type="html">
            <cfdump var="#URL#">
            <cfdump var="#CGI#">
         </cfmail>
         <cfabort>
      </cfif>
   </cfloop>
</cfif>

This worked like a charm and allowed me to see what exactly these jerkoffs were doing. Turned out I had left CFQUERYPARAM out of some queries I was running for the Contact Us page and a few product display pages. Once I added the tags, the attacks stopped.

If you’re seeing SQL injection attacks, just add the code to application.cfm or the onRequestStart() function of application.cfc to see if this is the method the attackers are using and what they are doing.

Thanks Jason!

I just discovered, about 4 days ago, that you can format Visio database diagrams in the IDEF1X format. While IDEF1X has many shortcomings, I have to say I find it much easier to understand the relationships and constraints at a quick view in this format rather than the standard format.

Granted I’m still running the 2001 version of Visio for Enterprise Architects, so I’m sure that recent releases have more formats in which to view an object model. However, for the time being, I’m having fun with opening all my old schemas and changing their format.

To get your model in this format, you choose the following menu option:
Database > Options > Document…

Then in the General tab, select IDEF1X as the symbol set.

Ok, I just learned how to do levels and partitioning in an Oracle query today, and I have to say it’s pretty cool. Basically, I have a view that returns groups of data; i.e. multiple rows with matching “key” attributes but different non-prime attributes.

Simplistic example:

------------------------------------------------------
3786       01/26/2008      1        3      0578
------------------------------------------------------
3786       01/27/2008      1        3      0579
------------------------------------------------------
3786       01/28/2008      1        3      0581
------------------------------------------------------
3787       01/29/2008      1        3      0587
------------------------------------------------------
3787       01/30/2008      1        3      0591
------------------------------------------------------

I was wracking my brain trying to figure out how, with one query, I could properly aggregate this data into one resultset. Using OVER PARTITION BY, SYS_CONNECT_BY_PATH, and the LEVEL Pseudocolumn, I came up with a pretty slick resultset that groups certain columns from multiple rows into one parent row.

SELECT column1,
   ltrim(column2, '<br/>') column2,
   column3,
   column4,
   ltrim(column5, '<br/>') effective_date
FROM (SELECT row_number() over(PARTITION BY column1 ORDER BY column1, ord_level DESC) aggregateRow,
        column1,
        column2,
        column3,
        column4,
        column5
      FROM (SELECT column1,
                  column3,
                  column4,
                  LEVEL ord_level,
                  sys_connect_by_path(column2, '<br/>') column2,
                  sys_connect_by_path(column5, '<br/>') column5
           FROM (SELECT column1,
                       column2,
                       column3,
                       column4,
                       column5
                       row_number() over(PARTITION BY column1 ORDER BY column1, mapped_facility_no) dataSet
                   FROM   database_table
                   ORDER  BY column1, column3) x_alias
            CONNECT BY column1 = PRIOR column1
            AND dataSet - 1 = PRIOR dataSet
           )
    )
WHERE  aggregateRow = 1
ORDER  BY column1

Resultset:

------------------------------------------------------
          01/26/2008                     0578
3786      01/27/2008      1       3      0579
          01/28/2008                     0581
------------------------------------------------------
3787      01/29/2008      1       3      0587
          01/30/2008                     0591
------------------------------------------------------

I was trying to call an Oracle function from ColdFusion today and was just receiving error, after error, after error… well you get the point. Long story short, I couldn’t successfully get a response since there is no binding between ColdFusion and the Oracle BOOLEAN type, and the function was returning BOOLEAN. So I changes the response to 0/1 and it works.

Just something to keep in mind.

<cfquery name="myQueryName" datasource="myDS">
    select myPackage.myFunction(myArg1, myArg2) response from dual
</cfquery>
<cfdump var="#myQueryName.response#">