Is this a bug or a "security feature" ?

chazman113 wrote:
> I had a nagging bug in coldfusion where every once in a while when people
> entered text and I inserted it into an MS SQL database it would throw the error
> "Statement is not allowed." I got a clue when a customer put in the words drop
> and program in their description for something, when I took the words out it
> worked fine. I gave the datasource permission to grant, revoke, drop, ect ect
> in the advanced datasource options and poof, it works! So is this a bug or some
> kind of injection attack prevention? The text was actually formatted in an FCK
> editor and put within single quotes in an UPDATE statement.
>

It is not a bug, and you have definitely disabled a lot of security by
opening up your DSN like that. It helps to look at this how each of
various systems look at it.

On the ColdFusion end, it is just working with strings. The security
settings in the administrator are basically string searches. I.E.
Don't allow these problematic sub-strings in the SQL string. Anyone who
has worked with more then the most basic string manipulation understands
how convoluted it quickly becomes and how nigh impossible it is to make
up a foolproof rule.

Once the database receives the query string, it tokenizes what it
receives and starts processing the commands. If one just uses
<cfquery...> than everything is received as one long string and the
database must make it's best attempt to determine which parts of the
strings are commands and which parts are parameters and which parts are
data. It is relatively easy to confuse the database in this process so
that information meant to be data is processed as a command. This is
the essence of a SQL injection attack.

If one uses the <cfqueryparam...> tag or stored procedures, the
developer more explicitly defines which parts of the SQL is commands and
which part is data. Now the database knows what part of the SQL is data
and it does not even try to tokenize it for commands.

Thus the simpler and more secure solution to your original problem would
be to use <cfqueryparam...> for these fields that could potentially and
legitimately contain strings that could look like SQL commands so that
the database would know to not even look there. Generally its a best
practice to always use <cfqueryparam...> tags in your ColdFusion SQL.