A Scripted Query Param & Whitespace Gotcha

I discovered this one a while back, but forgot to write a post on it. Did you realize that formatting queries could affect the execution of scripted queries? Consider the following function:

view plain print about
1/**
2 *    FUNCTION login
3 *    A function to validate a user login, and return a struct of user details
4 *
5 *    @access public
6 *    @returnType struct
7 *    @output true
8 */

9function login(required struct formScope) {
10    var retVal = {"success"=true,"message"="","data"=""};
11    var sql = "SELECT     u.userID,
12                        u.username,
13                        u.password,
14                        u.dateCreated,
15                        u.lastUpdated
16                FROM    users u
17                WHERE     u.username = :username
18                AND        u.password = :password
19                AND        u.isActive = 1";
20    var q = new Query(datasource = VARIABLES.instance.dsn,sql = sql);
21    q.addParam(name = "username", value = ARGUMENTS.formScope.username, cfsqltype = "cf_sql_varchar");
22    q.addParam(name = "password", value = ARGUMENTS.formScope.password, cfsqltype = "cf_sql_varchar");
23
24    try {
25        retVal.data = LOCAL.q.execute().getResult();
26        // Check for no recordCount, and throw a 'no records' exception
27        if(!retVal.data.recordCount){
28            throw(type="MH-Custom",errorCode="001",message="The user " & ARGUMENTS.formScope.username & " could not be authenticated. Please check your credentials and try again.");
29        }
30    } catch (any excpt) {
31        retVal.success = false;
32        if(excpt.type eq "MH-Custom"){
33            retVal.message = excpt.message;
34        } else {
35            // TODO: Add admin notification in here somewhere
36            retVal.message = "There was a problem executing this request, and our administrators have been notified";
37            WriteDump(var=VARIABLES.instance,label="instance");
38        }
39        if(StructKeyExists(excpt,"errorCode") AND Len(excpt.errorCode)){
40         retVal["errorCode"] = excpt.errorCode;
41        }
42    }
43    return retVal;
44}

It's a pretty basic function, with a query to check submitted form fields against the database. Right? So, why would it error? "Error? What error?" Yes, it errors. Here's the code for a basic call, along with a dump to output that to the page:

view plain print about
1<cfscript>
2    REQUEST.testObj = CreateObject("component","com.multihome.core.Security").init(DSN='multihome');
3    REQUEST.test = REQUEST.testObj.login({username='admin',password='admin'});
4    WriteDump(var=REQUEST.test);
5
</cfscript>

Dumping that result shows you the error coming through:

CFDump 1

So, to get at the root of this I had to comment out all of my try/catch work:

CFDump 2

Whoops! Forgot my onError handler. OK, I'll comment that out. Here we go! Now we get to the meat of it (the dump was the same, but sometimes you just want to see the raw error):

view plain print about
1Error Executing Database Query
2
3Parameter 'username AND u.password' not found in the list of parameters specified
4
5SQL: SELECT u.userID, u.username, u.password, u.dateCreated, u.lastUpdated FROM users u WHERE u.username = :username AND u.password = :password AND u.isActive = 1
6
7The error occurred in C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\CustomTags\com\adobe\coldfusion\query.cfc: line 108
8Called from C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\CustomTags\com\adobe\coldfusion\query.cfc: line 137
9Called from C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\CustomTags\com\adobe\coldfusion\query.cfc: line 472
10Called from C:\JRun4\servers\cfusion\cfusion-ear\cfusion-war\WEB-INF\cfusion\CustomTags\com\adobe\coldfusion\query.cfc: line 605
11Called from C:\Inetpub\com\multihome\core\Security.cfc: line 68
12Called from C:\Inetpub\wwwroot\multihome\index.cfm: line 21
13Called from C:\Inetpub\wwwroot\multihome\application.cfc: line 228

Did you get anything out of that? Neither did I. The first thing I did was go review the scripted 'new Query()' documentation on the Adobe site. That didn't help. According to the documentation, everything appears to be fine. The next thing I did was look at other examples out there. Yep, still good. Next, I started comparing to other instances of code that I know to work. Wait a minute....Look at this:

SQL code with whitespace characters

See anything odd? Yes, I show whitespace characters in my editor (ColdFusion Builder). Pretty easy to do. Just go to your preferences and change it: go to Window | Preferences | General | Editors | Text Editors, and select Show whitespace characters. What you see in this picture are tabs, spaces, and End of Line markers. "OK, so what?" Well, here's where it gets strange. Let's take our original query, and put it all on a single line:

view plain print about
1var sql = "SELECT u.userID, u.username, u.password, u.dateCreated, u.lastUpdated FROM users u WHERE u.username = :username AND u.password = :password AND u.isActive = 1 ";
2var q = new Query(datasource = VARIABLES.instance.dsn,sql = sql);
3q.addParam(name = "username", value = ARGUMENTS.formScope.username, cfsqltype = "cf_sql_varchar");
4q.addParam(name = "password", value = ARGUMENTS.formScope.password, cfsqltype = "cf_sql_varchar");

If you run this, all is well:

CFDump 3

As you can see, everything works fine now. But, when I format my SQL for readability again, I again get the error. When I went back, and looked at examples that worked (in my editor) I discovered that lines following lines with params were directly preceded with one or more spaces. So, just to test, I added a single space right before those lines that followed lines referencing params:

view plain print about
1var sql = "SELECT     u.userID,
2                    u.username,
3                    u.password,
4                    u.dateCreated,
5                    u.lastUpdated
6            FROM    users u
7            WHERE    u.username = :username
8             AND    u.password = :password
9             AND    u.isActive = 1";
Code with adjust whitespace

This took care of it. My error went away, and my query executed properly, and my query was still formatted for readability. After another round of the great Tabs vs Spaces debate at work I had to change my editor's default preferences back, which is what caused/highlighted this issue. (I told you guys we needed to stick with 4 spaces ;) Maybe it's a bug in the SQL parser, or there's a method to the madness, but adding that single space before those lines is all that's required to get back on track.

MSOC Part 8: onRequest Event Handlers

Some of the most powerful and versatile ColdFusion application event handlers are often the most ignored: the onRequest event handlers. Not sure why these so often seem to get ignored, but I can't name for you how many times, and how many apps, I've started working on that weren't using any of these methods in any capacity, and needed to.

Like the onApplication and onSession before it, the onRequest event handlers include methods that fire immediately before (onRequestStart) and after (onRequestEnd) the page request. Also like the scopes of those handlers, the onRequest handlers allow access to the REQUEST scope variables without having to lock access to those variables. Those are pretty standard, and not much to figure out. But the onRequest handlers also include two additional handlers: onRequest, and onCFCRequest. These two are a bit more confusing to understand "why?", so we'll go a little more in depth.

[More]

ColdFusion 9 Hotfix 2 Released

Adobe has released the ColdFusion 9.0.1 Hotfix 2, available on the update page. This is a cumulative hotfix, containing fixes for security issues, items around ORM, resolution to questions of JSON serialization, integration bits for Exchange, and much more.

Install has some quirks. It's not just a simple 'upload the file' bit, so you'll want to pay careful attention to the instructions, and backup affected files in advance. This will get so much easier with the next version of ColdFusion, but for now it's worthwhile to jump through the hoops. It is a 'cumulative' hotfix, and word from those in the know say that it is safe to skip over the CHF 1 install, if you haven't done it already, as all it's changes are within this hotfix as advertised.

Legacy Code and Some Modern Browsers

Working on some legacy code the other day, and came across one that was driving me nuts. I had a form that was part of a tabbed interface, and the form would not submit in Firefox or Chrome. Finally, after some trial and error, I was on a specific tab when I hit submit, and saw the following:

Unable to display content. Adobe Flash is required.

Did you see that? Some kind of form validation. It was odd though, because I didn't see that popup from any other tab, nor did it shift focus to the tab with that field (though focus was on that field). So, I went searching.

After quite a bit of time I found out something even more odd. There was no form validation on that field. Zilch. Nada. What the...? Now I was really stumped.

So, I started looking at the code of the form itself, specifically at that field. Here's what I found:

view plain print about
1<input type="text" name="somefield" required="No">

Like I said, some legacy code. Have you figured it out yet? Here's the deal. This form used to be a cfform, and the original developer had added the required attribute on a cfinput (unnecessarily). At some point, the form was switched over to a standard form, with it's own validation, and the cfinput was switched quickly to an input, without removing the attribute. No big deal, right?

Well, it wasn't a big deal, for a very long time. But, the browsers are updating. They're slowly implementing changes to support html5. And, guess what. There are changes to the input elements for html5. Now you have a required attribute? It will automatically validate that field, with a default message, and stop form processing if the field is empty. Never mind that you've implemented the required attribute incorrectly for html5, it'll still validate it regardless.

Removing the required attribute, which was no longer necessary in our application, resolved the issue.

How 'bout that gotcha?