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?

Do not store passwords in plain text. You should hash your passwords using a secure salted hash (not MD5) — SLaks
– SLaks, Commented Aug 25, 2011 at 17:20
@Daniel: Nobody says the database structure must be changed, you can store a hash or a cleartext password in a VARCHAR, to the DB that's all the same. But storing a hash is smarter and much more secure (or, put differently: storing cleartext passwords is incredibly dumb and dangerous). How long is the password field? — Tomalak
– Tomalak, Commented Aug 25, 2011 at 17:47
.. And not to sound unsympathetic, but developers still share responsibility for the security of information they handle whether it is their database or not. — Leigh
– Leigh, Commented Aug 25, 2011 at 17:53
I'm just writing a proposal for rewriting this application, while I might have to reword it, 'storing cleartext passwords is incredibly dumb and dangerous' is good fodder :) — Daniel
– Daniel, Commented Aug 25, 2011 at 17:59

Tomalak · Accepted Answer · 2011-08-25 18:22:49Z

11

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.

edited Aug 25, 2011 at 18:22

answered Aug 25, 2011 at 17:30

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Leigh Over a year ago

+1 For the tip about functions. Learn something new every day.

Chris Brickhouse · Accepted Answer · 2011-08-26 15:14:00Z

-1

An even better way to go would be to use stored procedures for everything.

answered Aug 26, 2011 at 15:14

Chris Brickhouse

6485 silver badges15 bronze badges

2 Comments

user619882 Over a year ago

Stored Procedures have their uses, but not for everything. Queries and Stored Procedures should be used appropriately.

Chris Brickhouse Over a year ago

when building a coldfusion application, raw SQL literally has no place. it executes more slowly than stored procs and is open to injection if you use params without protection.

Collectives™ on Stack Overflow

Methods for preventing SQL Injection in ColdFusion

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related