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.

SQL Tricks: What's an Upsert?

So, despite the debate on utilizing an Active Record (sometimes called Table Row) design pattern, there are times when it can be incredibly useful. Especially when dealing with simple forms that deal with a single record within a table (go figure). It's also really handy if you're utilizing something like The Illudium PU-36 Code Generator to auto-generate your data modeling. But, occasionally, you hit a glitch and need to rethink.

[More]

Nashville CFUG News: The Scorpio Tour

You may notice the new Flash banner above, retelling the exciting news that Ben Forta is coming to Nashville on May the 9th. With reports coming in after the first week of this tour, we are all getting pretty jazzed. Daily reports come in on new features, functions, capabilities that are going to blow the doors off the other guys. Truly great stuff, and we'll have quite a few really nice giveaways as well.

We were discussing this at last night's meeting. Which rocked! Andy Matthews (who sits next to me daily at work) did a great presentation on Cascading Style Sheets. I'm already very familiar with CSS, but Andy taught me one or two things last night. Very in depth, and well done on his first presentation effort. You can access the Adobe Connect preso from the link in the Archive section of the Nashville ColdFusion User Group website.

So, anyway. Scorpio is coming. It's weeks away now, so make sure to go to the UG site and signup. For those who might be unfamiliar (or slightly familiar), Ben is a terrific technical author, with books on ColdFusion, SQL, MySQL, and more, and is a senior technology evangelist for Adobe. Not to mention his alter ego, Scorpio Man.

Steve Bryant on DataMgr

So, I just got home from the monthly Nashville ColdFusion User Group meeting, and I have to say that I'm looking forward to watching the Breezo (Connecto?) again of Steve Bryant's presentation on DataMgr. This lightweight utility seems like it can actually do some heavy lifting with some of your basic database interaction, handling all of your basic CRUD and a whole lot more. Has some very nice, built-in functionality for handling things like automatic data truncation when inserting to a column of a smaller size (but only if you ask it to), rewriting a batch of records to reflect new sort orders (like when re-ordering display orders), and quite a few other things.

[More]

Beginner SQL Tricks: NULL Value Defaults

OK, I'm not a beginner with SQL. After 7 years of hammering away at web apps I've picked up a little bit here and there. But I also try to remember how difficult it was for me to wrap my head around certain things that didn't necessarily have to do with programming logic. Things like design, layout, and database calls.

[More]