Skip to main content
P
pham_mn
Known Participant
January 4, 2022
Answered

Possible SQL Injection

  • January 4, 2022
  • 3 replies
  • 924 views

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

This topic has been closed for replies.
Correct answer BKBK
quote

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>

 

 

3 replies

Charlie Arehart
Community Expert
Charlie ArehartCommunity Expert
Community Expert
January 7, 2022

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.

/Charlie (troubleshooter, carehart. org)
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
January 9, 2022

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. 

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
January 4, 2022
quote

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.

 

 

P
pham_mnAuthor
Known Participant
January 4, 2022

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
Community Expert
BKBKCommunity Expert
Community Expert
January 4, 2022

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.

P
pham_mnAuthor
Known Participant
January 4, 2022

It's Oracle as back-end

Thank you

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices