Possible SQL Injection

[pham_mn]

Hello,

Here is my query

<cfquery name="myQuery" datasource="myDataSource">

SELECT *

FROM Table

WHERE myDate BETWEEN ThisDate AND ThatDate

</cfQuery>

I wonder if I need to use <CFQUERYPARAM> at WHERE to avoid SQL Injection please?

Thanks for advice

Hung Pham

[pham_mn]

It's Oracle as back-end

Thank you

[BKBK]

Hello,

Here is my query

<cfquery name="myQuery" datasource="myDataSource">

SELECT *

FROM Table

WHERE myDate BETWEEN ThisDate AND ThatDate

</cfQuery>

I wonder if I need to use <CFQUERYPARAM> at WHERE to avoid SQL Injection please?

Hung Pham

By @pham_mn

Yes, definitely. You would then be doing something like

<cfset thisDate=createdate(2021,1,1)>
<cfset thatDate=createdate(2021,12,31)>

<cfquery name="myQuery" datasource="myDSN">

SELECT *

FROM myTable

WHERE myDate BETWEEN <CFQUERYPARAM VALUE="#thisDate#" CFSQLType="CF_SQL_Date"> 

AND <CFQUERYPARAM VALUE="#thatDate#" CFSQLType="CF_SQL_Date"> 

</cfQuery>
<!---
<cfdump var="#myQuery#" >
--->

Especially if you don't know what the input is or where it originates from. Anyway, it is best to make a habit of always using cfqueryparam in this way. Remember that even data from your own database might contain injection code. Some other innocuous process might have saved it beforehand.

Not only is cfqueryparam safer, it is more efficient too. It uses data-binding, improving performance.

[pham_mn]

Thank you very much for your codes.  And I have another question that relates to SQL Injections.  Well... I am new hired and assigned to add <CFQUERYPARAM> to about 1500 lines of code (from diff cfm and cfc files) that missing<CFQUERYPARAM>.  This could take weeks or even months to add and could cause some problems like typo or could be anything. 

I think I should test one file at a time, but I am new hire and don't know what page I should test

I wonder if you guys have any method to approach the issue

Thank you very much

[BKBK]

I would not try to automate this. Correcting 1500 lines of code is a small, at most medium-sized, project. So, for maximum accuracy, I would add the cfqueryparams the old-fashioned way: manually. That is because you have to know the CF_SQL of each input. Also, it follows from this that I suggest that you test one page or function call at a time.

[pham_mn]

Thank you so very much for your time and advice.

[pham_mn]

hello,

I am not sure  it would work because just like myDate: thisDate and thatDate are column name of myTable.  They are not input data.

The code below gives me an error

<cfquery name="myQuery" datasource="myDataSource">
SELECT *
FROM Table
WHERE sysDate BETWEEN <cfqueryparam value ="thisDate" cfsqltype="CF_SQL_DATE"> AND
<cfqueryparam value ="thatDate" cfsqltype="CF_SQL_DATE">
<cfquery>

OR

<cfquery name="myQuery" datasource="myDataSource">
SELECT *
FROM Table
WHERE sysDate BETWEEN <cfqueryparam value ="#thisDate#" cfsqltype="CF_SQL_DATE"> AND
<cfqueryparam value ="#thatDate#" cfsqltype="CF_SQL_DATE">
<cfquery>

[pham_mn]

It should be SysDate instead of myDate

Thank you

[pham_mn]

This is orginal codes, and it works fine

<cfquery name="qryFacAreaList" datasource="#application.dsn#">
SELECT *
FROM  AREA_DIM
WHERE 1 = <cfqueryparam value="1" cfsqltype="CF_SQL_NUMERIC">
AND SYSDATE BETWEEN ACTIVE_DATE AND INACTIVE_DATE
ORDER BY area_name
</cfquery>

[BKBK · Accepted solution]

hello,

I am not sure  it would work because just like myDate: thisDate and thatDate are column name of myTable.  They are not input data.

The code below gives me an error

<cfquery name="myQuery" datasource="myDataSource">
SELECT *
FROM Table
WHERE sysDate BETWEEN <cfqueryparam value ="thisDate" cfsqltype="CF_SQL_DATE"> AND
<cfqueryparam value ="thatDate" cfsqltype="CF_SQL_DATE">
<cfquery>

OR

<cfquery name="myQuery" datasource="myDataSource">
SELECT *
FROM Table
WHERE sysDate BETWEEN <cfqueryparam value ="#thisDate#" cfsqltype="CF_SQL_DATE"> AND
<cfqueryparam value ="#thatDate#" cfsqltype="CF_SQL_DATE">
<cfquery>

By @pham_mn

cfqueryparam is for input data. If thisDate and thatDate are column names representing date values, and you trust the values, then you require

<cfquery name="myQuery" datasource="myDataSource">
SELECT *
FROM Table
WHERE sysDate BETWEEN thisDate AND thatDate
<cfquery>

[pham_mn]

cfqueryparam is for input data is the key word.  Thank you so much

[Charlie Arehart]

Hung Pham, you asked about automating this task (of finding and fixing potential sql injection coding issues). BKBK argued against it, saying yours was a small enough task to do it manually, which he even encouraged.

But I think it's appropriate to mention that there are at least TWO tools that can help with this very process of finding and fixing such CFML security coding issues like sql injection:

• One is the ColdFusion Security Code Analyzer, which has been included in CFBuilder since its 2016 version, which is designed to work with CF Enterprise (also since CF2016) or any CF edition (Enterprise, Standard, or Developer) since CF2021. If you google that phrase you will find resources from Adobe and others with more on it (implementing it, and using it). Since CFBuilder has a free 60-day trial and this can work with the free CF 2021 Developer edition (whether you run CF2021 in prod or not), this is a free solution to the problem of using a tool to help find security coding issues.
• The other is Fixinator, a commercial tool from Pete Freitag and Foundeo. It's a commercial tool but it has some advantages and differences compared to the first tool (for example, Fixinator doesn't rely on CFBuilder at all, nor the RDS feature for connecting CFBuilder to a CF instance). For more, see fixinator.app.

Hope that's helpful.

[BKBK]

I do encourage the use of tools to assist in a task. I take that for granted. Tools help speed up our work, for sure. For example, you can use the tools that Charlie mentions or the search facilities in a text editor or IDE to locate the places where cfqueryparam may be required. 

However, as far as I know, these tools are not yet fully machine-intelligent. For example, a tool might not be able to tell a decimal from a float, a numeric from a double or a varchar from an nvarchar.

My suggestion remains. If your project is relatively small, you should - even if you use a tool - manually check each cfqueryparam that you add.