Has ColdFusion MX taken care of SQL injection ?

[yaf23]

Has ColdFusion MX taken care of SQL injection ? The reason is that when I
tried to test my own application without using <cfqueryparam ...> it seems like
when I test with a form field it adds extra quotes automatically in the query
at runtime to handle this. If this is so do we need to use <cfqueryparam ...>
.. Also is it the same for URL variables as well. If Cold Fusion is still
vulerable can someone post me some test lines so I can try them. Ihave tried
just about everything I know about SQL injection and have reached the end of
the road. Thanks

[Ben Pate]

The short answer: Every system is vulnerable to SQL injection, one way or
another. There will never be a computer language that can PREVENT you from
shooting yourself in the foot. The long answer: It looks like you're testing
all of the right things, and you should be pretty safe. It is highly
recommended by most experts for both of SQL injection, as well as performance
reasons. So, unless you really NEED to put parameters inline in the SQL,
switch back to <cfqueryparam> and you're just about guaranteed that you're safe
from this attack. In addition, you're describing on another anti-sql-injection
feature of CF. Within a <cfquery> block, ColdFusion will automatically escape
single quotes in the value of any variable that you include. So, when
FirstName='O'Reilly', putting #FirstName# inside a <cfquery> block will
actually send O''Reilly to the database server. This default behavior can
cause a problem in some instances, so CF includes the PreserveSingleQuotes()
function that lets you manually override it. It also works slightly
differently when you have a function such as #Left(FirstName, 10)# -- so it's a
good idea to test these, too. Benjamin Pate P.S. Security is always relative.
For example, are you locking out login attempts after 6 tries, or can I run a
dictionary attack against your user database until I find a username/password
combination? It always comes down to how much work, and dilligence you put
into your code. That common sense goes for any language.

[Stefan K.]

As mentioned:
When outputting variables inside a cfQuery, CF escapes all single-quotes
within the value of the var.

I'd therefore say that SQL-injection is taken care of in CF !

The only way around this is specific use of #PreserveSingleQuotes(myVariable)#
=> shooting yourself in the foot ;-) if you do it without real purpose

There are some bugs in this replacement function:
In #Form[myVar]#, CF will escape single-quotes in the value of myVar (the name
of the form-var), NOT in the value of the actual form-var (don't really know
the behaviour in newest CF-version, MX still had that bug).

BTW, we've never seen a use for cfQueryParam and prefeer to write our complete
SQLs ourselves with the correct datatypes.

p.s.
Single-quotes in variable-values is the only kind of SQL-injection that I know
of, anyone know another?

[Dross.2]

Originally posted by: Stefan K. BTW, we've never seen a use for cfQueryParam
and prefeer to write our complete SQLs ourselves with the correct datatypes.
Stefan, You need to look at what cfqueryparam actually does. The advantages in
sending a prepared statement to the database rather than a plain string of SQL
are numerous, especially if you are on a database that supports bind variables
(like MSSQL and Oracle). It's basically all the advantages of stored procs
without having to actually write stored procs.

[Stefan K.]

@Dross2

Thank you for the hint.
But writing stored procedures really is no problem, same with local vars.
We just prefeer it this way so we see and know what's going on.

Stefan K.