ColdFusion Package Installation on a Mac for Sublime Text 3

I have become a full-time Sublime Text 3 user. {{ Insert Fanboi Gushing }}

Until today, however, I had only used ST3 in my work Windows environment. Getting the ColdFusion package to correctly install on my Mac, however, proved to be a an exercise in ID10T errors, i.e., I wasn't paying attention to what I was doing and tried to install the package within the Sublime Text.app directory structure itself. Wall...meet head.

Current CF users of Sublime undoubtedly know that the ColdFusion package is still in progress. Since it is FOSS, the package depends on the community to make updates in their free time. To keep the Sublime Text 2 package stable, work on the ST3 package is happening in a development branch.

Installation Process

To install the ColdFusion Sublime Text 3 package on a Mac, I used the following procedure:

  1. In a Terminal window, open the ST3 packages path:

    view plain print about
    1cd ~/Library/"Application Support"/"Sublime Text 3"/Packages

  2. Use git to clone the ColdFusion github repo:

    view plain print about
    1git clone https://github.com/SublimeText/ColdFusion
  3. Switch to the development branch:

    view plain print about
    1cd ColdFusion
    2git checkout development

  4. Switch to the September 17, 2013 build. The HEAD version has an issue that destabilizes the package. It may or may not actually load. If it does load, then you may have trouble closing a tag when you type a > character.

    view plain print about
    1git checkout fca7f71f6eda883fcaa0ba9297c2c194875a8ba7

  5. Restart Sublime Text and open a ColdFusion page or component to verify you have color coding.

Thank you to Brien Malone for pointing out the right revision to use!

Run Twice Safe SQL for Migration

I've had the pleasure of working in multi-tiered environments for much of my time as a developer. Code is written in development, moved to a test environment, or two or three, and then migrated to production with a release. Each environment has its own application and database servers.

To facilitate migration between these environments, all SQL is put into a task-specific script file, and committed to source control. Since it is possible for a script to be executed more than once per environment, given the need for bug fixes or feature creep, the SQL needs to be written to be run-twice safe.

Run-twice safe means I can safely run the same script in the same environment multiple times without entering duplicate data or reaching an exception due to already extant schema changes.

Since I work primarily with MS SQL Server, my example below is written in T-SQL. However, the concept should work with almost any RDBMS.

The keys to run-twice safe are:

  • Try/Catch
  • Transactions
  • Conditionals
  • Error checking

Try/Catch

The entire script is wrapped in a try/catch block to allow us to catch and gracefully handle any exceptions which may arise.

view plain print about
1BEGIN TRY
2 // do stuff
3END TRY
4BEGIN CATCH
5 // handle the error
6END CATCH

Transactions

The functional portion of the script is wrapped in a transaction block. If one part of the script fails, we want to roll the entire thing back. Since the failure is an exception, the catch block will handle the rollback.

view plain print about
1BEGIN TRY
2 BEGIN TRANSACTION
3 // do stuff
4 COMMIT TRANSACTION
5END TRY
6BEGIN CATCH
7 IF @@TRANCOUNT >
0
8 ROLLBACK TRANSACTION
9 // handle the error
10END CATCH

Checking to see if a transaction is open before attempting a rollback is a safety valve. If you are worried about multiple transactions being open, the rollback could be rewritten to be

view plain print about
1WHILE @@TRANCOUNT > 0
2 ROLLBACK TRANSACTION

Conditionals

Before adding, subtracting, or updating anything, whether data or schema-related, perform a check to see if that bit of work is needed. For example, before adding a new table, check to see if it already exists.

view plain print about
1IF NOT EXISTS (
2 SELECT table_name FROM information_schema.tables WHERE table_name = 'sometable'
3BEGIN
4 // create the table
5END

For schema changes, I heavily depend on the INFORMATION_SCHEMA views. It just makes my life that much easier. For data changes, I look to see if the value I want to change has already changed, or even exists.

view plain print about
1IF NOT EXISTS (
2 SELECT somecolumn FROM sometable WHERE somecolumn = 'new value'
3)
4BEGIN
5 // insert/update the value
6END

Error Checking

Finally, if an error does happen, then we need to remember to throw it back to the console, as the try/catch block will swallow it. Using a RAISERROR statement, for T-SQL, in the catch block ensures we know what went wrong.

Keeping Excel from Being Helpful with CSV Files

Microsoft Excel likes to automatically format data in an attempt to be more helpful and friendly to the user. As an application developer, I can appreciate that. Users like friendly and helpful...until it is no longer friendly and helpful.

I recently created a management interface to allow a user to import and export a CSV file to manage data. The CSV format was part of the requirements, and is not negotiable. The import and export are not hard to do, and is something I've done plenty of times before.

"Helpful" Excel

While functional testing a change, however, I noticed that some of the data was wacky in the export file when opened in Excel. Specifically, three columns which contain a number range, i.e., 4 - 5, were appearing as dates formatted like 4-May. Excel saw the data and, even though it knows it is dealing with a text file, proceeded to be helpful and provide a format for the data. Thank you, Excel. This is not a time to be "helpful."

Time for a Single Quote?

The usual trick for ensuring Excel treats data as text is to prepend a single quote to the cell data. However, this applies to an Excel file, not a CSV. Adding the single quote in a CSV displays the quote in the cell. Not good.

Prepending a Space

I found that adding a space to the front of the number range, so that the CSV column content appears as " 4 - 5" did the trick. When the CSV is opened in Excel, Excel recognizes it is dealing with text and displays the value as text.

The downside to prepending the space is that it appears in the Excel cell. When the user updates the data, they must remember to retain the space if they wish Excel to properly show the data again.

Web Application Security

Application security ought to be automatic for all developers, but too often it becomes secondary to meeting frantic deadlines and making the customer happy. After all, clients do not see security, but they do see that the submit button is 3 pixels too small.

Writing more-secure code is not hard. It just requires a bit of thought, and the use of standard functionality and libraries, regardless of the language used. No application is 100% secure since the security landscape changes too quickly. I believe we can write more-secure applications, which are designed to make it easier to adapt to new vulnerabilities and approaches to security.

To start with, listed below are web application security resources I consult often. This list may grow.

Resources

OWASP Top Ten Project
OWAP is the go-to spot for web application security. Their top 10 list of security issues was updated in 2013, from the previous 2010 version.
Jason Dean's Blog
Jason wrote a very good series of entries on application security.
Pete Freitag's Blog
Pete often writes about security topics related to ColdFusion.

Unit Test Slacker

It is crunch time. QA logs a critical defect and it ends up in your queue to fix. A few clicks, key presses, and browser reloads later, your fix is ready to commit for the nightly build. Happiness is fresh code. You even remembered to test out the change...or did you?

I admit, I can be a slacker when it comes to unit testing. Happy path all the way. But my freewheeling, cowboy testing day came to a halt when my code change impacted more than I thought. Yep, I did not fully test my couple lines of code, hitting all the possibilities to make 100% sure I did not break something.

The problem was simple: the front end sends the data to the server via a POST of JSON data. The form input value was formatted as an array, a generic way of dealing with the possibility of multiple responses. The server side code, however, expected a simple value. A quick arrayToList() and the form was humming along.

In my enthusiasm, I updated more than one line in the code to use the new (hackish) approach. In my drive to close out the ticket, I only tested the documented problem, ignoring the tests for the other extra lines. Come the morning, I had two new issues documenting related problems...problems I created.

Lesson learned: if you change it, test it.

Basic Web Application Security - Enforcing SSL

Many web applications deal with data considered to be confidential or sensitive in nature. Applications like this should use SSL to encrypt the traffic between the server and the user (you are using SSL...right?). Most web servers, however, are not configured to only accept a secure connection.

The programmatic method for redirecting an insecure request over http to one using https relies on checking the https variable in the CGI scope. If it is not set to on, then we know that a redirect is needed to enforce https.

The following code uses the tag to send a 301 HTTP status code, which tells the browser that the requested page is permanently relocated, followed by a Location header to refer the client to the correct URL, which uses https. The reset is used to throw away any content already generated to this point in the code so that it is not sent back to the client.

view plain print about
1<cfif cgi.https IS NOT "on">
2 <cfcontent reset="true">
3 <cfheader statuscode="301" statustext="Use SSL">
4 <cfheader name="Location" value="https://#cgi.http_host#">
5 <cfabort>
6</cfif>

The above example relies on the application to perform the SSL check. If you have control over your web server, you may be able to move the check up a level by using URL rewriting or a web server-specific setting.

Basic Web Application Security - SQL Injection

Web application security is growing to be an interest of mine, especially after going through a round of audits against the application that pays my salary. With those audits (almost) behind me, my intent is to write a series of articles detailing some of the common vulnerabilities found in web applications and how they can be resolved in ColdFusion.

First up, SQL injection.

[More]

Datasource-less Queries in ColdFusion Using JDBC

Is it possible to execute a query in ColdFusion without defining a datasource in ColdFusion administrator? Yes! The following is one method, I'm sure there are others, for executing a ColdFusion query without explicitly defining a ColdFusion datasource.

What We'll Need

  • ColdFusion Engine
  • Database Driver JAR

I will assume you already have a ColdFusion engine installed and running. I tested this code on Adobe CF 9.

For this example, I used the MySQL Connector/J driver to connect to a local MySQL database. Place the JAR file in your ColdFusion classpath.

Steps

Before we begin, a note of caution. Java is case sensitive, so when instantiating an object using a Java class, case matters!

Create a Datasource

The first step to creating a manual connection to the database is to create a datasource using the com.mysql.jdbc.jdbc2.optional.MysqlDataSource class, which is part of the MySQL Connector/J library.

view plain print about
1<cfset datasource = createObject( "java", "com.mysql.jdbc.jdbc2.optional.MysqlDataSource" )>

Create a Connection

Using the datasource, create a connection object, passing in the identity of user to connect as. The datasource class has a method, getConnection(), that makes creating a connection simple. Use the setter methods on the datasource to provide the login details for the user credentials and the name and port of the server to connect to.

Please, please, please do not use root! Create and use a user account with only the permissions it needs.

view plain print about
1<cfset datasource.setUser( "cfuser" )>
2<cfset datasource.setPassword( "12345" )>
3<cfset datasource.setServerName( "localhost" )>
4<cfset datasource.setPortNumber( 3306 )>
5
6<cfset connection = datasource.getConnection()>

Execute the Query

Now that we have a connection to the database, we can use JDBC to execute queries. First, we'll need to create a JDBC statement, which is a class that defines the query to execute. The connection object has a method that once again makes it easy:

view plain print about
1<cfset statement = connection.createStatement()>

With the statement object in hand, we can now write out the query to execute. The executeQuery() method on the statement class takes a string with the query definition. This method returns a JDBC resultset object.

view plain print about
1<cfset resultset = statement.executeQuery( "SELECT table_name FROM information_schema.tables ORDER BY table_name" )>

Convert to a ColdFusion Query

At this point, the query is represented in a JDBC resultset. Although we could use this object, a ColdFusion query object is much more useful and feature-rich. Converting the resultset to a query object requires using one of the internal classes in ColdFusion, coldfusion.sql.QueryTable, passing the resultset into the constructor:

view plain print about
1<cfset query = createObject( "java", "coldfusion.sql.QueryTable" ).init( resultset )>

Clean Up

The JDBC connection needs to be cleaned up when it is opened. Keeping it open may result in oddness and other memory-related issues.

view plain print about
1<cfset connection.close()>

Out of the Comfort Zone - A First Introduction to CFWheels

I've decided to step outside my comfort zone and try out a new framework, CFWheels. CFWheels describes itself as "an open source CFML framework inspired by Ruby on Rails." It advocates conventions over configuration, which seems to be the latest craze to hit CF frameworks (see ColdBox and FW/1 for two others).

Since I'm going outside my comfort zone, I think CFWheels is a good framework to explore since it supports several unfamiliar and/or uncomfortable areas for me:

  • Convention over configuration - I am a long time Fusebox user
  • ORM - I proudly admit to being a control freak when it comes to the data layer
  • URL rewriting - More than just pretty URLs?

Creating a New CFWheels Site

According to the documentation, setting up a new CFWheels application is rather straightforward. Download CFWheels, extract the zip file to the web root, and voila, a new CFWheels site.

For my first site, it was indeed this easy. I created a directory, extracted the CFWheels files into it, and I was done (almost...).

The Default Route

I wanted the site to be under a directory named "reading" After creating such a directory and adding in the CFWheels skeleton, however, I immediately hit an error message. Not a ColdFusion-generated error, but a rather friendly CFWheels error message indicating that a view could not be found:

After poking around the documentation and code a bit, I realized that the default route for a fresh skeleton application was set to look for a file named wheels.cfm under views/wheels. The config/routes.cfm template has the following lines of code to set up the default action:

view plain print about
1<!---
2    Here you can add routes to your application and edit the default one.
3    The default route is the one that will be called on your application's "home" page.
4--->

5<cfset addRoute(name="home", pattern="", controller="wheels", action="wheels")>

Translated, the addRoute() line tells CFWheels to look for a controller CFC named wheels in the controllers directory and a CFM template named wheels.cfm under a subdirectory named wheels in the views directory. (This conventions thing is rather handy.)

The error message I received pointed to a problem I introduced while setting up the site. I renamed the default "wheels" views directory to "reading," not realizing that I was breaking the default route.

The fix was simple, either restore the views/reading directory to views/wheels, or, and this is the option I chose, change the route to to point to the existing views/reading directory:

view plain print about
1<cfset addRoute(name="home", pattern="", controller="reading", action="wheels")>

Next Steps

So now that I have a happy CFWheels skeleton site, what next? The site I had in mind is a simple application to track what books I have read, so I need to take a step back and design the model and the screens, pardon me, views, that need to exist.

Once the basic design is set, I want to explore CRUD automation using CFWheel's ORM. I need to see if I can just let go and allow a framework to automate a good portion of the data layer.

OT: Windows 7 Maybe?

I use a Mac for my personal work and a Windows XP machine for gaming and a few PC-only apps. I never thought twice about upgrading Windows to Vista, but I am certainly thinking about going to Windows 7.

So what brought about the change of heart? I set up a new computer for my mom last night, a HP slimline...wow, is it shiny and kind of cute, which came with Windows 7 Home Premium on it. My first experience actually driving Windows 7, and I came away really liking the changes and polish added to the OS.

The graphics are nice and shiny, looks like they took a page from Apple's design book, the speed is there, and the features and improvements really look like someone cared about this release of Windows. The improvements in searching alone are a big plus in the pro column on the go-and-get-a-copy list.

Many of the features I like about OS X, the speed, the graphics, Finder and Spotlight, are pretty much all there on Windows 7. Just one question: will it run Civilization IV? ;-)

Just maybe a trip to Micro Center is in order this weekend...

More Entries