Cfquery causing XSS and SQL Injection issues

Question

I ran an application scan (IBM) recently and now I'm trying to fix the issues that came up. A good majority of my issues stem from a cfquery (see below). I'm trying to get it so that Stored XSS and SQL injections issues don't show up on my scans. Any help would be greatly appreciated, as this is my first time doing something of the sort.

Thanks!

<cfquery name="enter_question" datasource="#dsn#">
INSERT INTO xx_questions(q_id,
                      q_name,
                  q_narrative,
                  q_used,
                  q_type)
VALUES(               #variables.new_q_id#,
                      '#form.q_name#',
                  '#form.q_narrative#',
                  'n',
                  #form.q_type#)
</cfquery>

Answer 1

You need to use <cfqueryparam>. Check the documentation at: https://wikidocs.adobe.com/wiki/display/coldfusionen/cfqueryparam

Try something like this (you should change the CFSQLType to match whatever your DB columns are):

<cfquery name="enter_question" datasource="#dsn#">
    INSERT INTO xx_questions(q_id,
        q_name,
        q_narrative,
        q_used,
        q_type)
    VALUES(
        <cfqueryparam value="#variables.new_q_id#" CFSQLType="CF_SQL_INTEGER">,
        <cfqueryparam value="#form.q_name#" CFSQLType="CF_SQL_VARCHAR">,
        <cfqueryparam value="#form.q_narrative#" CFSQLType="CF_SQL_VARCHAR">,
        <cfqueryparam value="n" CFSQLType="CF_SQL_CHAR">,
        <cfqueryparam value="#form.q_type#" CFSQLType="CF_SQL_INTEGER">
        )
</cfquery>

Answer 2

Each query that contains user inputs should have cfqueryparam like so:

<cfquery name="enter_question" datasource="#dsn#">
INSERT INTO xx_questions(q_id,
                      q_name,
                  q_narrative,
                  q_used,
                  q_type)

VALUES(           
       <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#variables.new_q_id#"/>,
       <cfqueryparam cfsqltype="CF_SQL_CHAR"    value="#form.q_name#"/>,
       <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#form.q_narrative#"/>,
       <cfqueryparam cfsqltype="CF_SQL_CHAR" value="n"/>,
       <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#form.q_type#"/>)
</cfquery>

The cfsqltype will need to reflect the data type in the xx_questions table for the cooresponding column - but you get the idea.

Binding the variable to a type in this manner automatically protects you against SQLi injection. XSS injection is something else. That's when user input is output onto the page. If a user pushes something (say a script tag) into a column in the DB and then it is output onto the page you haven't really suffered from injection (they gave you a character column, you put in a character column, they did not change the nature of the query). But in a forum app or any other area where user input from one user is shown to another you have the potential for malicious code infections.

For that each of your character types needs to be vetted for malicious code. Using something like Foundeo as a Web application Firewall can do this. You can also protect your page at a server level using the CF admin or at an application level using the scriptprotect attribute of the cfapplication tag or props in the application.cfc.

You can also manually protect within your DB inserts using HTMLEditformat like so:

 <cfqueryparam cfsqltype="CF_SQL_CHAR" value="#htmleditformat(form.q_narrative)#"/>,

... or you can check each user input against a list of known allowed inputs (if the data is predictable). These techniques should be part of your "best practice" list if possible. :)