Coldfusion Query - Injection Protection

ColdFusion Query - Injection Protection

doesn't CF already "magically" do this in CF query tag when you wrap evaluated variables in single quotes?

Yep, it'll convert ' to '' for you.

Now guess what SQL you get from this code:

<cfset value = "\'; DROP TABLE tableName -- " />

<cfquery>
    select * from tableName
    where fieldName = '#value#'
</cfquery>

The cfqueryparam tag works; using query params solves SQL injection.

Any custom written attempts at validating, sanitizing, or escaping (all separate things, btw) are, at best, only as good as the developer's knowledge of the database system the code is running against.

If the developer is unaware of other escape methods, or if the values are modified between validation/escaping and them being rendered into SQL, or even if the codebase is ported to another database system and seems to be fine, there's a chance of custom code breaking down.

When it comes to security, you don't want chances like that. So use cfqueryparam.

How do I prevent SQL injection with ColdFusion

Use a <cfqueryparam> tag for your id:

http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/tags-b20.htm

<cfquery name="rsRecord" datasource="DataSource">
    SELECT * FROM Table
    WHERE id = 
     <cfqueryparam value = "#url.id#"
        CFSQLType = "CF_SQL_INTEGER">
</cfquery>

Methods for preventing SQL Injection in ColdFusion

The way to go is <cfqueryparam>. It's simple, straight-forward, datatype-safe, can handle lists (for use with IN (...)) and can handle conditional NULLs. Plus you get a benefit out of it in loops - the query text itself is sent to the server only once, with each further loop iteration only parameter values are transferred.

You can use '#var#' and be relatively safe. In the context of a <cfquery> tag ColdFusion will expand the value of var with single quotes escaped, so there is some kind of automatic defense against SQL injection. But beware: This will — by design — not happen with function return values: For example, in '#Trim(var)#' single quotes won't be escaped. This is easily overlooked and therefore dangerous.

Also, it has a disadvantage when run in a loop: Since variable interpolation happens before the SQL is sent to the server, ColdFusion will generate a new query text with every iteration of a loop. This means more bytes over the wire and no query plan caching on the server, as every query text is different.

In short: Use <cfqueryparam> wherever you can:

WHERE
  login        = <cfqueryparam value="#FORM.login#" cfsqltype="CF_SQL_VARCHAR">
  AND password = <cfqueryparam value='#Hash(FORM.password, "SHA-512")#' cfsqltype="CF_SQL_VARCHAR">

Instead of a simple Hash(), you should indeed use a salted hash, as @SLaks pointed out in his comment.

Is this Coldfusion query SQL Injection proof?

That's safe by virtue of the fact that you're using <cfqueryparam>. That's what the tag does. It sends the value as text (or whatever the cfsqltype happens to be), not a command to be executed.

Prevent SQL injection without using cfqueryparam

I've been at three different companies that had to do a complete project freeze and assign all devs to update calls to queries and stored procedures. Some even had to hire contractors to just do that for a couple of months. You're going to have to bite the bullet and do them manually.

You might take this opportunity to remove a bunch of redundant queries, replace them with stored procedures and move them to CFCs. This would allow you reuse queries from a single source and reduce the overall amount of work you'll have to do.

You can put a Web Application Firewall (WAF) in place to handle some requests from the outside, but they're not 100%. Depending on your clients, the code has to protect from SQL Injection without the use of a WAF.

How do I prevent SQL injection with ColdFusion

Use a <cfqueryparam> tag for your id:

http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/tags-b20.htm

<cfquery name="rsRecord" datasource="DataSource">
    SELECT * FROM Table
    WHERE id = 
     <cfqueryparam value = "#url.id#"
        CFSQLType = "CF_SQL_INTEGER">
</cfquery>

form data into database: preventing SQL injection

You should wrap all form and url variables in cfqueryparam

Your query would look like this:

<cfquery name="InsRegistrant" datasource="#application.Datasource#" dbtype="odbc">
    INSERT INTO Schedule_Registrations(
         schedule_id,
         first_name,
         last_name,
         phone_number,
         email,
         guest,
         list_type,
         datetime_registered
     )
     VALUES(
         <cfqueryparam cfsqltype="cf_sql_integer" value="#url.schedule_id#">,
         <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.first_name#">,
         <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.last_name#">,
         <cfqueryparam cfsqltype="cf_sql_varchar" value="#CleanPhoneNumber#">,
         <cfqueryparam cfsqltype="cf_sql_varchar" value="#FORM.email#">,
         <cfqueryparam cfsqltype="cf_sql_integer" value="#attendee.guest#">,
         <!--- Values for list types 
            0 = NEVER USE Will cause many many problems
            1 = Main List
            2 = Waiting List --->                    
         <cfqueryparam cfsqltype="cf_sql_integer" value="#attendee.list_type#">,
         #createodbcdatetime(now())#
     )                
</cfquery>

I'm not sure I got all the data types correct, see the full documentation of cfqueryparam for all the data types.

Cfquery causing XSS and SQL Injection issues

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>

Trouble using cfqueryparam with sql query strings

You cannot use cfqueryparam for query strings. It can only be used for those things sql allows you to paramaterize (basically, just literals like strings or numbers). They can't be bound to keywords or field names, let alone complex clauses.

FWIW, this is a SQL limitation, not a ColdFusion issue.

---

Related Topics