Methods for preventing SQL Injection in ColdFusion

Question

I'm wondering if the # symbol is enough.

This is a part of the sql command that I'm using

WHERE login='#FORM.login#' AND password COLLATE Latin1_General_CS_AS = '#FORM.password#'

I'm trying to test it with user names such as ' OR 1=1 and variants of it, but even though it's not working I don't want to have a false sense of security.

I've read that using <cfqueryparam> can prevent this form of attack, are there any other ways?

Answer 1

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.