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

aColumn is NULL

  • January 24, 2022
  • 1 reply
  • 630 views

Hello:

I have the <cfquery... below

<cfquery name="theQuery" datasource="#application.dsn#">
SELECT position_key, position_name,inactive_date
FROM aTable
WHERE aColumn IS NULL
</cfquery>

The Oracle is backend.
My question that does the line "WHERE aColumn IS NULL" is possible for SQL Injection?

If so, how should I add <cfqueryparam>

I tried couple scenario:

This line WHERE aColumn IS <cfqueryparam null="true">  error: missing NULL keyword

This line WHERE aColumn = <cfqueryparam null="true"> : the result is diff from 

WHERE aColumn IS NULL

Any advice please.

Thank you very much

 

    This topic has been closed for replies.
    Correct answer Grae Desmond

    Anytime you use a variable inside a query tag it is safest to assume you are vulnerable to SQL injection and you should protect yourself from it.  Sure there are times you have complete control of the variable because it does not come in via the form or url scope but its better to get in the habit of parameterizing your queries and stay in it.  This way you are always safe and you may get the performance boost mentioned in that blog post earlier.

     

    For querying in cfscript I use queryExecute().  You can learn more about it here: https://cfdocs.org/queryexecute

     

    The code based on your example would become:

    <cfscript>

    queryResults = queryExecute(

         'SELECT * FROM aTable WHERE aColumn = :myVariable'.

        { myVariable={ value = aVariables, cfsqltype = "cf_sql_varchar" } },

        {datasource = your_datasource_here }

    );

    </cfscript>

     

    I'm writing this from memory so may not have the syntax perfect but you get the idea.  You would replace the cfsqltype with the right type and your datasouce name.

    </cfscript>

    1 reply

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

    You ask, "does the line "WHERE aColumn IS NULL" is possible for SQL Injection?"

     

    The answer is no. Sql injection (in cfml) is only possible when the sql statement refers to a cfml variable. There is none in that sql.

     

    More specifically, sql injection may be possible when in particular a variable in the sql is one whose value can somehow be manipulated by a threat actor. In that case, they  could try to embed additional sql in that variable value.

     

    For more, just search for sql injection, and the first few (non-ad) results (such as at Wikipedia and OWASP) have good introductions.

     

    Bottom line: that specific cfquey has no need of cfqueryparam. 

    /Charlie (troubleshooter, carehart. org)
      P
      pham_mnAuthor
      Known Participant
      January 25, 2022

      Thank you for your reply, and what about this please

       

      WHERE aColumnName IN(#aVariable#)?

      I think it should be changed to

      WHERE aColumnName IN (<cfqueryparam value =="#aVariable#" cfsqltype="cf_sql_varchar")  ??

      Thanks

      Grae Desmond
      Grae Desmond
      Inspiring
      January 25, 2022

      You are correct, it should be changed as you could end up with SQL injection there depending on where aVariable comes from.  You should also add list="true" to your cfqueryparam as this SQL is checking a list.  Ex. WHERE aColumnName IN (<cfqueryparam value =="#aVariable#" cfsqltype="cf_sql_varchar" list = "true" >). 

       

      Ideally you should use cfqueryparam everywhere to be safe.  Also its supposed to improve performance in some databases.  There is a good article on that here: https://coldfusion.adobe.com/2018/12/the-hidden-power-of-cfqueryparam/

       

      You can learn more about cfqueryparam here: https://cfdocs.org/cfqueryparam.

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