CFQUERYPARAM and Oracle Databases

I’ve already shown in my old posts about the benefit of using cfqueryparam tag. Using this tag can prevent SQL inject and output data format what SQL like.

Generally, we use old-fashioned style like

<CFQUERY DATASOURCE=”DSN_NAME”>
SELECT username
FROM users
WHERE user_id=#SESSION.USER_ID#
</CFQUERY>

In modern style, we use cfqueryparam tag within cfquery tag like

 

<CFQUERY DATASOURCE=”DSN_NAME”>
SELECT username
FROM users
WHERE user_id=<cfqueryparam value=”#SESSION.USER_ID#” cfsqltype=”cf_sql_number”>
</CFQUERY>

 

It’s ok for passing variable is integer. If we want to pass string variable, we need to do following code. Unlike old-fashioned style, we don’t need to put single quote in front of and end of cfqueryparam tag.

 

<CFQUERY DATASOURCE=”DSN_NAME”>
SELECT username
FROM users
WHERE user_name=<cfqueryparam value=”#SESSION.USER_NAME#” cfsqltype=”cf_sql_varchar”>
</CFQUERY>

 

Buch..!! How about Like condition..?? Don’t worry. You can use as follow:

<CFQUERY DATASOURCE=”DSN_NAME”>
SELECT username
FROM users
WHERE user_name=<cfqueryparam value=”%#SESSION.USER_NAME#%” cfsqltype=”cf_sql_varchar”>
</CFQUERY>

cfqueryparam and cachedwithin

To prevent SQL Injection in Coldfusion, we should use <cfqueryparam> tag between <cfquery> tag. It’s good tag and it output the variable which MS.SQL like. But to get good performance of our website, we should use cachedwidthin attribute of cfquery tag.  If we use <cfqueryparam> tag in <cfquery>, error occur for sure and <cfquery> doesn’t allow <cfqueryparam> tag. So, how to prevent for SQL injection and how to get good performance for your site without using <cfqueryparam>. The answer is quite simple: we need put following coding at the top of your page.

<CFIF IsDefined(“id”) AND NOT IsNumeric(id)>
<cfabort showerror=”Invalid Query String”>
</CFIF>

And, also add following coding in <cfquery> tag,

WHERE ID = #Val(id)#

How? It’s easy though, isn’t it?