A Scripted Query Param & Whitespace Gotcha
Nov 14
posted by Cutter in
SQL, Adobe, ColdFusion, Development, CFScript
| 5 Comments
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:
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:
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:
So, to get at the root of this I had to comment out all of my try/catch work:
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):
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:
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:
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:
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:
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";
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.
. You can learn more about me in the 