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#">

As I was explaining my latest application to another developer this morning, I realized that I was a serious framework junky on this one. I like frameworks, but I also like to scatter in my own personal touches to them based on the needs of the application. In this latest app, I am using the following.

• Model-Glue
• Transfer
• ColdSpring
• jQuery
• AjaxCFC
• Ext

I was actually amazed at myself when I realized this. I also found myself thinking that the saying “standing on the shoulders of giants” is appropriate in this situation. I created an absolutely amazing application, in less time than was anticipated, and with an slick, easy-to-use interface. If it wasn’t for the hard work of the Rineharts, Resigs, Corfields, Gondas, Mandels, and others of the world, my applications would still be grinding along on my homegrown frameworks that, while they work well, don’t have near the feature set of these libraries.

I’ll add voice to the thousands who say thank you for your hard work.

Powered by ScribeFire.