In an ideal world, everyone is using qb or quick, and you really don’t know what a bind variable is. Before you discovered this ideal world, maybe you were using queryExecute and were executing queries like this one.

var q =queryExecute("Select * from users where userId = #url.UserId#");

This kind of code ( don’t do this in production ! ) is wide open for sql injection attacks. This post is not about sql injection, so we assume you were already so smart to use queryparams, so something similar to this.

var q = queryExecute(
  sql="Select * from users where userId = :myId",
  params =  { myId: { value = rc.UserId, cfsqlType = "integer" }
)

In your params you can specify a value, a cfsqlType and a lot more, like maxLength, null values or a separator for a list. Your cfsqlType brings you some more security, although you should still validate your input, and it brings performance advantages. If your database field is an integer, performance will be best if you specifye cfsqlType = “integer” instead of the more generic “numeric”. So now we are using cfqueryparams in our SQL, or more generic: bind variables. This was a very short primer on query params, and if you want to squeeze out every bit of performance and security out of it, I would strongly advise to study your query params a bit more. But this post is about qb, not about query params, so?

When qb enters the stage, things get a little bit easier. qb is a fluent query builder which is supposed to make querying more fun. Please visit the qb manual if you’ve not heard about this nice library before, otherwise read on. The above statement in qb would be written as:

query = wirebox.getInstance('QueryBuilder@qb');
q = query
  .from( "users" )
  .where( "ID", rc.UserId )  
  .get();

For inserts and updates code even looks simpler, e.g.

query = wirebox.getInstance('QueryBuilder@qb');
q = query
  .from( "users" )
  .where( "ID", rc.UserId )  
  .update( 
    {
      "name" = "myName",
      "phone" = "06 12345678",
      "isSuperUser" = true,
      "dateLastUpdated" = now()
    }
  );

Ok, so where are my query params or bind variables? In this qb code, I am just inserting some values both for selecting ( .where( "ID", rc.UserId ) ) and updating/inserting. So is this safe? And what is qb doing behind the scenes?

qb converts my code to queryExecute statements, and converts every single value I am feeding to the library in a bind param. So if I insert name it detects this is a string and creates a bind param { value = "myName", cfsqlType = "varchar" }. qb is doing some very smart guessing, and often you will be fine if you don’t specify a cfsqltype yourself. But in other scenario’s qb can’t be so smart, because it doesn’t know HOW to detect the correct type. So let’s tell qb how to bind those values with the following values:

query = wirebox.getInstance('QueryBuilder@qb');
q = query
  .from( "users" )
  .where( "ID", { value=rc.UserId, cfsqlType="integer" )  
  .update( 
    {
      "name" = { value="myName", cfsqlType="varchar" ), 
      "phone" = { value="06 12345678", cfsqlType="varchar" ),
      "isSuperUser" = { value=true, cfsqlType="tinyint" ),
      "dateLastUpdated" = { value=now(), cfsqlType="timestamp" ),
    }
  );

So now I am specifying every single parameter, and this way qb knows exactly what to do. So should we specify these params or can we just use the defaults? There’s one field wich will immediately fail in SQL server if I don’t specify a cfsqlType. isSuperUser is a boolean, and sql server has no booleans, it stores your boolean in a tinyInt. We have to specify this tinyInt or your query will fail. For other fields it is less obvious if you should specify your queryparams, so let’s see how qb detects it’s types.

  • first qb will detect if your value is an array, so you can do queries with lists of values.
  • next qb returns CF_SQL_NUMERIC if it can detect your value as a numeric data type
  • next qb returns CF_SQL_TIMESTAMP if it detects a date. It does so by using the cfml function IsDate().
  • if all other detection fails it returns a CF_SQL_VARCHAR.

If you want to see how it is done, here is a link to the inferSqlType function in github, where the datatype detection is done.

So in many cases (but not always) you can assume it will detect your strings correctly, if you omit the cfsqltype. Read on!

Detecting dates

Date detection is done with the IsDate() function. But there is a problem with that. Isdate is very greedy. Even if I am not entering dates, such as my dutch cell phone number 06 12345678 Lucee will still think it is a date. ACF is more forgiving and tells me it is a string (yes, it is!). So if I don’t specify the varchar in "phone" = { value="06 12345678", cfsqlType="varchar" )my code will break because Lucee will try to insert some out of range date (june 1th 12345678) in my database. In both Lucee AND ACF it is not hard to detect real datetime variables, but if we force real datetime detection other valid strings will fail as a date. So I better specify the varchar here. Don’t trust your IsDate detection, there are way too many strings which are incorrectly recognized as a date! The nasty thing here: if people know you are using qb, you can crash almost any page which has a string query param by entering some fake date like in my example.

Detecting numeric values

Lucee and ACF2018 do a nice job on detecting numeric values. Real datatype of a variable can be detected with myvar.getClass().name so as long as a numeric value is really numeric in your favourite cf engine you are fine. When using ACF2016 or lower, your numeric value will be a string. In that case you have to specify the correct datatyp yourself.

But there’s more. Recently we had some discussion on the boxteam slack about qb performance with querying some serious amount of date. It appeared the query in qb was FOUR times faster when specifying the correct sqltype instead of the more generic “NUMERIC”, which was autodetected. So for maximum performance it is wise to specify your sqltype in your qb queries.

Conclusion

Date detection is not very reliable. Your dates will probably be detected as dates, but sometimes your strings will be converted to dates as well. I don’t like that, and because it is easy to prevent if you always enter real datetime vars, I wrote a pull request for that. Since other people still want to enter plain strings for dates, this will certainly not be the default behaviour yet, but just an optional setting.
Detection of numbers also has it’s issues. If you are using integers 98 out of 100 times, why should we specify sqlType=”integer” this 98 times instead of specifying “numeric” or “double” two times. ACF 2018 does a decent job on detection of integers and big integers, so there’s room for improvement here. Maybe add a setting for number detection so you can decide what’s your preferred return type.

This brings me to the most important point. You can ALWAYS override your sqltype. Datatype detection will not be touched if you specify sqltype yourself. So by doing this for numeric values and a very occasional string, you will avoid some problems. You could also take the same route as for cfqueryparam: specify everything, and you know what you are doing.