Installing Transfer in ModelGlue:Unity

Today we will install the Transfer ORM inside our Contact-O-Matic application. To complete this tutorial, you should have the Contact-O-Matic installed and running. If you have not completed this step, please create the database described at the bottom of Series 6 and install the files at Series 10 download link. Test the application by manually adding several ContactTypes to the ContactType table in your database (I chose Co-Worker, Enemy, Friend). Then use the Contact Form in the ContactOMatic application to enter a few contacts.


Learning OO and have questions?

Taking the decision to learn OO programming can be a confusing route. With all the new and complicated taxonomy, variety of opinions and general annoyances of OO programming, where do you turn for advice?

Doesn't it seem that most OO discussions take place on the ModelGlue or MachII mailing list? While that is true, I would like to point out two resources that are truly gems. The #ColdFusion channel on Dalnel IRC is a great place to ask questions and get help. Staffed by a set of ColdFusion Experts, simply join the channel, introduce yourself and ask away...

The second resource is the CFCDev listserv. This listserv has been in operation since CF6 came out. The focus is on OO programming and spans across the various frameworks and methodologies across the ColdFusion landscape.

I confess to having read each one of the archived posts early on in my transition to OO programming. You can find the Archives for CFCDev as well as the Registration for CFCDev

Happy Programming!

So you want more tutorials?

I am going to continue the 'So You Wanna Create a Model Glue Application series. (Thanks Lola ;) For those just tuning in, the Contact-O-Matic is a simple example of a mini-application using ModelGlue and ColdSpring. The tutorials on the Contact-O-Matic go through the code line by line showing how to perform such tasks as:

  1. Set up the frameworks
  2. Build a View
  3. Place your CSS and JS files
  4. Create a form
  5. Validate a submitted form and persist the data
  6. Return success/error messages
  7. Resolve CFC dependancies with ColdSpring
  8. Create Instance Objects with factories
  9. Refactor, as needed

The Contact-O-Matic is not an example of a Best Practices- Enterprise application. This is due to the simple nature of the program. We simply have a list of contacts, a mechanism to add and edit contacts, and a way to remove contacts. As Object Models grow complex in complex applications, it is important to note that there is no perfect Object Model, only the least annoying set of tradeoffs. I want the code in the Contact-O-Matic to remain simple, and so it shall.

Our next move is to integrate Transfer ORM into our application. The next set of posts will cover installation and testing of the Transfer ORM Framework, the inclusion of another 'Contact-O-Feature' in our application as well as some Architectural Techniques.

Stored Procedure argument is too long for SQL Server

I have been maintaining an ad-hoc report lately. The report uses a stored procedure to perform Cross-Tab functions on a resultset. The stored procedure works by passing in an SQL string, which in my case, is a string of generated SQL. Yesterday, I added some additional functionality to the report and the stored procedure failed. After spending quite a while on diagnosing the problem, I found the maximum length of the input parameter was 8,000 characters. My report generated an SQL string of 12,000 characters.

view plain print about
1Before I continue, I want to note the error messages returned from the stored procedure were syntax errors. Executing the exact same SQL statement with QueryAnalyzer returned a proper resultset. Not once did I get a useful message such as, "Hey Jerko, your parameter is too long".

For this report, the SQL Statement was generated at runtime. I needed a way to shorten the SQL Statement and still preserve the flexibility of the runtime SQL generation. After talking this through with my friend and co-worker, Bassil Karam, he recommended I replace a portion of the SQL statement with a view. Once I did that, I was able to shorten up the generated SQL to fit inside the input parameter of the Stored Procedure and my report ran just fine.

As an aside, the project I am working on has approached the technical limits of SQL server quite a few times. For example, I remain shocked at the table row limit. There are a lot of things right about MS SQL Server, but there are still some weak areas. Maybe left over limitations from when SQL Server was MS Access? ( Ha Ha Ha )

CF8 PostgreSQL support is Enterprise only?

Update: This post is no longer true. The documentation has been updated to reflect the inclusion of PostgreSQL in the standard edition of ColdFusion. See the comments for Steven Erat's response.

While the specs are not fully complete, Damon Cooper has released the system requirements for the impending CF8 release. I thank the entire CF team and Adobe for their transparency.

From the document, it appears that support for several databases will be only for CF8 Enterprise edition. This is not completely out of the norm as the Enterprise edition of CF has had improved Oracle drivers for a long time. If anyone has ever had to purchase 3rd party Oracle drivers, (Merant, anyone?) they are expensive and certainly make sense in the Enterprise edition. What surprised me was PostgreSQL support made the Enterprise list, but not the Professional list.

The PostgreSQL JDBC drivers are available for download at no charge. The implementation is Type 4, pure java implementation. The license for the drivers (taken from the site:

view plain print about
1The PostgreSQL JDBC driver is distributed under the BSD license, same as the server. The simplest explanation of the licensing terms is that you can do whatever you want with the product and source code as long as you don't claim you wrote it or sue us.

While I completely understand, and embrace, inclusion of expensive licensed drivers, (such as Oracle) in CF Enterprise only, I find the decision to include PostgreSQL JDBC drivers in CF Enterprise very surprising.

The good news is, adding the PostgreSQL drivers to a CF Pro installation is very easy. I often consult the most excellent tutorial by Joachem Van Dieten for the JDBC JAR file installation and datasource configuration

As stated in the paper, the material is subject to change. Let's hope they do.

Var scope checker revised and has a new home.

Jim Collins added a file browser to the Var Scope Checker originally authored by Seth Petry-Johnson. The code is now on Google Code, since Seth's website is REALLY down now.

Get it while it's hot!

New Scorpio/CF8 Yahoo Pipe

I am still wading through all of the new Scorpio/CF8 information. There is a lot of it and in order to get a better handle on all the topics and postings, I created a new Yahoo Pipe. The full URL is:

I picked the top 8 blogs for Scorpio news based on an unscientific cruise through the ColdFusion Yahoo Pipe from Brian Rinaldi. There are blogs I have missed so if you know of a good resource for Scorpio/CF8 postings, please leave the feed URL in a comment.

I want to be strict on this one and keep a valuable signal to noise ratio. My own blog is not included in this feed yet, because I haven't posted anything significant on Scorpio/CF8.

So let us make this Scorpio specific please. If you have a Scorpio feed, please submit that one rather than your generic feed URL.

Scorpio Beta works with Apache 2.2.4.

Just a quick note to say I was able to get CF 8 working on a fresh install of Apache version 2.2.4.

A few quick observations:

  1. During the CF install, I got a nice message reminding me to stop ColdFusion MX 7 Search Server
  2. After CF finished, I had to run the Web Server Configuration Tool again for everything to work
  3. I also had to open Port 51800 on my local firewall. I suspect this is JNDI.

My initial impressions? The installation process handled the above issues gracefully. This doesn't feel much like beta software... Remind me to tell you about an experience I had with a SQL Studio Express CTP beta once.

Editors note: I suppose I will never be hired by microsoft if I keep making these sorts of comments

Anatomy of an SQL Injection Attack

Security is everyone's problem. It is important to be aware of issues that can foster security violations in software. Buffer Overflows, a common software security hole, arise from the length of input not being checked. When the input is larger than the memory allocated, the input data can spill over into unintended memory addresses. By appending a command with the correct offset, it is quite possible to push the command into memory space with high level privileges and execute.

In a buffer overflow attack, often the application accepting the input is running under reduced privileges. Because the input overflows the given memory address, it matters not that the input originated from a low privilege application, but rather the actual memory address where the command is stored and executed.

SQL injection is another type of attack and shares a common root with Buffer Overflow attacks. When input is not properly evaluated and filtered, bad things can happen. In an SQL injection attack, the attacker appends SQL statements to input. Here is a simple query:

view plain print about
1SELECT userID, username, password
2FROM Users
3WHERE UserID = 1

Here is an example of appending a command to an SQL statement.

view plain print about
1SELECT userID, username, password
2FROM Users
3WHERE UserID = 1; DROP Users;

In the last example, an SQL command to drop the users table was added. Imagine for a moment the URL to access a user profile. http://someserver/index.cfm?userID=1 The userID is appended to the URL and is undoubtedly passed to a query in the application that returns the profile associated with UserID 1.

To create an SQL Injection attack with the URL above, we could simply try the following URL: http://someserver/index.cfm?userID=1;drop users When the application substitutes the userID value of 1;drop users in the query, there are actually two statements to be executed. Firstly, the command to return the data from the users table associated with userID 1. Secondly, the command to drop the whole users table.

Pragmatically speaking, there would be little to gain by dropping the users table apart from vandalism. That being said, there are thousands of 5kr1pt k1dd135 whom would be delighted in dropping your users table for you and then bragging to their little wanker friends about how they trashed your server. Still, not much of a security risk? Let us try another angle.

Suppose for a moment a site that charged a lot of money for access to data. Users periodically purchased subscriptions and your organization was making millions. Inside the database was a users table with the field of 'ExpirationDate' representing the date the subscription would need renewal. Shall we form an attack to give us a 20 year subscription?

This is the SQL we wish to execute

view plain print about
1SELECT userID, username, password
2FROM Users
3WHERE UserID = 1; update Users set ExpirationDate = '5/5/2027';

Can you guess what the URL string would look like?

If you guessed: http://someserver/index.cfm?userID=1;update users set expirationdate = '5/5/2027'

Then you are close. We may need to massage the url a little, or find a text input to put our command if the spaces and quotes are not respected.

So we can bump our subscription up 20 years or so. What else can we do? Let us try to add a user.

This is the SQL we wish to execute

view plain print about
1SELECT userID, username, password
2FROM Users
3WHERE UserID = 1; insert into Users (username, password, expirationdate) values ('imahaxor','inyourbox','5/5/2027');

http://someserver/index.cfm?userID=1;insert into Users (username, password,expirationdate) values ('imahaxor','inyourbox','5/5/2027');

Now in place of just extending a subscription, the attacker has a new account that won't expire for a while yet. Not a pretty picture is it?

How can you defend against SQL injection attacks, you ask? There are some best practices you may follow to reduce your attack surface. Let us look at a few:

  1. Reduce the privileges given to the SQL user of your application. If the application never needs to insert into a particular table, then remove that privilege.
  2. Check your input values. If you are expecting a number, add val() around the value. In the case above, a simple val() statement turns a malicious statement into a simple 0. We all like 0, right?
  3. Use prepared statements. Adding cfqueryparam values to your dynamic query values adds great protection. Apart from escaping malicious characters, the prepared statement treats the value as a value, not a string of text to be executed by the SQL engine.

While software is extremely difficult to secure completely you can remove a giant risk by gaining understanding of SQL Injection Attacks and using the techniques above.

Below are some examples of a recent attempt to use SQL Injection on my blog. This attack was not very sophisticated, but could have disrupted the services of this blog.

view plain print about
1Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and 1=2 value exceeds MAXLENGTH setting 35..
This was a fingerprinting attack. If the attacker retrieved a page, then the input mechanism would allow sanitized input. If the attacker received an error message, then perhaps important information about the server configuration would be revealed.

view plain print about
1Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and char(124)+user+char(124)=0 value exceeds MAXLENGTH setting 35..
This string evaluates to FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and |user|=0.

view plain print about
1Invalid data FDEB2819-9F27-DDC8-3C7C7A4B29BC8149' and char(124)+user+char(124)=0 and '%'=' value exceeds MAXLENGTH setting 35..
This string evaluates to FDEB2819-9F27-DDC8-3C7C7A4B29BC8149 and |user|=0 and '%'='

A tool to generate Database Scripts from MySql, MS SQL, Oracle, Pervasive and PostgreSQL

While reading the latest on the very cool Trac-Fu project, by Russ Johnson, I was reminded about a tool I use to generate database scripts.

I am a fan of most all Database platforms. I have used MSSQL 7/2000/2005, UDB, Oracle 8/9i/10g, MySql 4/5 and PostgreSQL 7.3/8.1 in production. When working in multiple database platforms, there inevitably comes the need to port a database from one platform to another.

Rather than spend my days mapping and transforming a database schema, I use SQL Script Builder. SQL Script Builder is a free tool that generates SQL scripts of a database and the data for 5 officially supported platforms:

  1. MySql
  2. MS SQL
  3. Oracle
  4. Pervasive
  5. PostgreSQL

I have also used SQL Script Builder to port a non-trivial MS Access database to PostgreSQL, a job that would normally be very manual and annoying.

This tool will produce SQL Scripts that can be executed on a local or remote server. Very handy indeed when working with remote hosts.

The support is also very good. I personally asked for PostgreSQL support on February 24 2007. Support for the PostgreSQL database was added and the documentation updated on March 06 2007.

Depending on your database structure, there are some tasks you will have to do manually such as column constraints and Foreign Key relationships, but this is a small price to pay for the level of automation provided by SQL Script Builder.

A big Thank You goes to Dave and the rest of the team at SQL Script Builder for providing and supporting this great tool.

To read more about SQL Script Builder:



Dave wrote in to say that SQL Script Builder now has support for values that are NULL contained in INSERT statements. Thanks Dave!