Data Binding Without Using Cfqueryparam
I am always rhapsodizing on the benefits of CFQUERYPARAM. But what if you needed to not use CFQUERYPARAM? Is it possible to get the benefits of the tag without actually needing to USE the tag? Why yes it is! In order to explain let's look under the covers of how an SQL statement is prepared when you use binding. How about a little lesson from Classis ASP?
A Classic Example
In classic ASP you must build a string containing SQL and pass it into a database connection. Consider this example:
<%
set conn = server.CreateObject("ADODB.Connection")
conn.open connectionsStr
set rsRelo = conn.Execute("Select name,email from Users where UserId=" & intUser)
%>
Note that this code uses the "Execute" method of the ADODB object. The execute method is analogous to using CFQUERY without cfqueryparam. It says, to the DB Server, "Here... see what you can make of this and get back to me." To bind parameters in ASP you use the function (oddly enough) "SQLBindParameter", as in this example taken from this excellent tutorial on optimizing ODBC (note - this is NOT ASP code - but it could be):
set conn = server.CreateObject("ADODB.Connection")
conn.open connectionsStr
set rsRelo = conn.Execute("Select name,email from Users where UserId=" & intUser)
%>
{
SQLINTEGER val = 10;
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_LONG, 0, 0, &val, 0, NULL);
SQLExecDirect(hstmt, "SELECT C1 FROM T1 WHERE C1 = ?", SQL_NTS);
}
In this case the database is informed as to the type and can readily access the execution plan if available in the cache (see "Why you should worry about your execution plan"). You will notice that the statement itself, "SELECT C1 FROM T1 WHERE C1 = ?", looks familiar. It's exactly like the output of the debug information when you are using CFQUERYPARAM. That should tell you how CFQUERYPARAM does it's thing.
SQLINTEGER val = 10;
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_INTEGER, SQL_C_LONG, 0, 0, &val, 0, NULL);
SQLExecDirect(hstmt, "SELECT C1 FROM T1 WHERE C1 = ?", SQL_NTS);
}
Under the Hood
You see when you use CFQUERYPARAM, the driver "prepares" the statement and creates temporary "stored procedure" for it. Let's say we have this code.
<cfquery name="getUser" datasource="#dsn#">
SELECT name, Email, userId
FROM users
WHERE username = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="joe">
AND pin = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="5555">
</cfquery>
What actually get's passed to the db server? Something like this:
SELECT name, Email, userId
FROM users
WHERE username = <cfqueryparam cfsqltype="CF_SQL_CHAR" value="joe">
AND pin = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="5555">
</cfquery>
<cfquery name="getUser" datasource="#dsn#">
DECLARE @param1 varchar(50)
DECLARE @param2 int = 5555
set @param1 = 'joe'
set @param2 = 5555
SELECT name, Email, userId
FROM users
WHERE username = @param1
AND pin = @param2
</cfquery>
The code above is runnable as well - and it has the same benefits as cfqueryparam. In other words, the 2 queries perform the same function and both bind the data and data types. You could use either of them and get the benefits of data binding. In the words of Hugh Neutron, "Now you gotta admit that's pretty neat!".
DECLARE @param1 varchar(50)
DECLARE @param2 int = 5555
set @param1 = 'joe'
set @param2 = 5555
SELECT name, Email, userId
FROM users
WHERE username = @param1
AND pin = @param2
</cfquery>
Why would you want to use the latter code and not the former? Well, sometimes it is convenient to build a "query string" and pass it into the cfquery tag. Not often, but there are cases where the construction of the query is super-dynamic, like when the columns or the where clause are pulled from a db - or when the table isn't known in advance. In those special circumstances having a way to do data binding could be an important tool for you.
What do you lose? CFQUERYPARAM does have the benefit of escaping special characters and scrubbing the data for you. I can imagine there is an obscure way to implement an SQL injection attack on the latter code, that would not be effective when using cfqueryparam (though any attempt is far more likely to simply produce errors). Still, I like to think of this technique as one more arrow in my developer's quiver.
14 Comments
-
Well one advantage to this method is that you can now cache the query using cachedwithin, you can't cache queries using cfqueryparam.
Though I would use this method as a last resort, only if you need to. I also like to avoid creating highly database specific sql code if there is a way I can do it that will work on all db servers. -
this looks like SQL Server -- what db's have you tried this against? I'm curious to know if the syntax is consistent across other popular db's like oracle, mysql and (regretably) access. -
I should also have mentioned that this also will allow you to see the values of the parameters if a database error occurs. With cfqueryparam, you can only see the values of the parameters in the debug output if the query executes without error.
-
While I hate the cfqueryparam syntax, I think you still have the sql injection problem doing it this way. For example, if @param2 is set with a cf variable above, the variable could have something like this in it:
5555;select * from users
or worse
5555;drop table users
For some reason, I like using a UDF better. -
Ike - it is MSSQL. The drivers all support a "prepare" syntax - so yes there is way to do this in Oracle and (probably) Access - but I don't have any samples at the moment. I have some oracle SP's and I'll experiment and see what I come up with.
Mike, you are right about the problem with the syntax - I had thought that you had to put all the "DECLARE" syntax at the beginning (like dimensioning variables), but that is not the case. You CAN mix and match DECLARE and other statements, so the "all sql" method does leave you vulnerable to SQL Injection attack. You would have to "scrub" the variables for injection code. Ray's CFLIB.ORG has a couple UDFs that do a decent job of that, so it wouldn't be a brain teaser. -
Ike, if you use SeeFusion, it shows you which queries were run recently, and which parameters were passed to it. Worth a try: www.seefusion.com -
The @param2 declaration should prevent SQL injections of varchar data into @param2, same as cfqueryparam. -
Actually - if you pass in a semi-colon and unscrubbed SQL... like "1; truncate table user;" - it will indeed run it. This is because there is not "declaration" section - like there is in Oracle. You can "declare" and do other SQL tasks in the same section - or even on the same line.
Mike's example above works - I tested it. -
You also loose the fact that by using cfqueryparam you are using Pepared statements. By using the above code instead of cfqueryparam the DB has to create an execution plan on each request. If you use cfqueryparam the execution plan is reused over and over. -
Adam - do you have a way of testing that? I took it that just binding variables was enough to get cache hits on the db server. You are saying that the "all sql" way will not correctly prepare the statement to generate an execution plan hit?
If you look at the ASP tutorial I referenced in the post (http://developer.mimer.com/howto/howto_26.htm) he seems to indicate that binding WILL result in a cache hit - no? -
I might be missing something but this statement to me is saying you have to explicitely prepare your statements. "SQL statements that are executed more than once in an application should be prepared separately with SQLPrepare the first time and then executed with SQLExecute."
Which is how queries with cfqueryparam work. If you look at your SQL profiler or look at queries being executed on a SQL server you will see a bunch of sq_ExecuteSQL (Select .....) this is how cfqueryparam works on subesquent calls you will also find that on the first run it calls sp_prepare (if I recall). This is atleast how it used to worked...I am unfortunetly looking at a SQL server working with CF5 :(. In MX it may depend on the JDBC driver you are using. Under the covers I would assume java is doing an SQLprepare() then SQLexecute() and how those are implemented is up to the driver.
I don't think just declaring the data type ahead of time is enough to guarantee a cached execution plan. That’s not to say subsequent calls will not be faster, they are most of the time; SQL server does do a lot of caching automagically, it also stores hard hit record sets in memory. -
Adam - ok, thanks for the info! I suppose then that the only real performance advantage with using the "all-sql" approach is the ability to use caching... good to know. Thanks!
-
I'm trying to get the safety of cfqueryparam but also utilize query caching in CF7. I came across a solution and was looking for feedback.
Basically I do a initial query:
select <cfqueryparam value="#arguments.foo#"> as foo
from dual;
Then I use those scrubbed values in the query that I want to cache. Note that the initial query execution time is usually 0ms so there's no real performance loss.
Sites to Watch
Aggregators
RSS
