How to Override SQL Sanitization in Coldfusion

How to override SQL sanitization in ColdFusion

ColdFusion does not escape all single quotes, but only those that arrive in the query through variable interpolation. This is the offender:

SELECT #Arguments.strSelectAttributes#

This is usually a helpful thing and a small line of defense against SQL injection attacks. So rule number one is (here and everywhere else): Don't build your SQL string from variables.

If you positively have to use variables to build an SQL string, despite all the possible negative effects, use the PreserveSingleQuotes() function:

SELECT #PreserveSingleQuotes(Arguments.strSelectAttributes)#

This function stops ColdFusion from auto-escaping the single quotes.

And any other function call does the same thing, by the way. Try:

SELECT #LCase(Arguments.strSelectAttributes)#

which means that PreserveSingleQuotes() is really just a no-op that turns a string into a function result, preventing the automatic variable interpolation routine from happening.

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.

executing cfquery in cfc with parameters received via AJAX

Give this a try:

<cfquery name="query" datasource="phonebook">
    SELECT '#select#'
    FROM '#from#'
    WHERE '#where#'
    ORDER BY '#orderBy#'
</cfquery>

As I recall from my research, putting the quotes around the variables tells coldfusion that it should process those values in a sql-like manner. That is, it won't escape the quotes you are feeding it (or, more accurately, it will intelligently escape them).

Additionally, in my personal experience, it also thwarts SQL injection (to which your code is currently wide open). There are actually coldfusion prepared statements that exist, but I think this is a shorthand method of constructing them, that still prevents injection. If anyone conclusively knows otherwise, and can provide an example of injection even using this technique, please do correct me.

Edit: Looking at your code again, this might work, or it might not. With my personal experience, I've been just passing in variable values, not constructing the query itself. If you can, try to do something like

<cfquery name="query" datasource="phonebook">
    SELECT *
    FROM mytable
    WHERE ID > '#minID#'
    ORDER BY ID DESC
</cfquery>

In this instance, you're merely passing the value you're comparing to, rather than actually building the entire WHERE clause.

Edit: I cannot in good conscience leave this code up here when I am not fully confident it is not vulnerable to SQL injection. The proper way to execute the second query I posted (the one where only the WHERE clause has a variable) is as follows:

<cfquery name="query" datasource="phonebook">
    SELECT *
    FROM mytable
    WHERE ID > <cfqueryparam value="#minID#" CFSQLType="CF_SQL_INTEGER">
    ORDER BY ID DESC
</cfquery>

You can read more about cfqueryparam here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

cf_sql_longvarchar cut off saving a variable from coldfusion to sql 2008

Usually a combination of CLOB and an Long Text Buffer solve the problem.

I use the example below only for queries that I know have to move lots of data. I don't use it universally because then JVM memory issues can come up.

Sample Image

How do you protect css id

If using ColdFusion to generate the variable name, you could use the "variablise" method of the Inflector CFC. It will convert any string into a safe underscore-separated list that can be used as a ColdFusion variable name. (Inflector is based on the Ruby on Rails ActiveSupport::Inflector class.)

https://github.com/timblair/coldfusion-inflector

<cffunction name="variablise" access="public" returntype="string" output="no" hint="Converts a string to a variable name, e.g. CamelCase becomes camel_case, 'big CSSDogThing' becomes big_css_dog_thing etc.">
    <cfargument name="string" type="string" required="yes" hint="The string to variablise">
    <cfset arguments.string = replace(trim(rereplace(arguments.string, "([^[:alnum:]_-]+)", " ", "ALL")), " ", "-", "ALL")>
    <cfset arguments.string = rereplace(arguments.string, "([A-Z]+)([A-Z][a-z])", "\1_\2", "ALL")>
    <cfset arguments.string = rereplace(arguments.string, "([a-z\d])([A-Z])", "\1_\2", "ALL")>
    <cfreturn lcase(replace(arguments.string, "-", "_", "ALL"))>
</cffunction>

ColdFusion Security

I use a modified portcullis, and filter all incoming var scopes (URL,FORM,COOKIE) onRequestStart.
http://portcullis.riaforge.org/

Slow query with cfqueryparam searching on indexed column containing hashes

Your issue may be related to VARCHAR vs NVARCHAR. These 2 links may help
Querying MS SQL Server G/UUIDs from ColdFusion and
nvarchar vs. varchar in SQL Server, BEWARE

What might be happening is there is a setting in ColdFusion administrator if cfqueryparam sends varchars as unicode or not. If that setting does not match the column setting (in your case, if that setting is enabled) then MS SQL will not use that index.

Things to watch out for in ColdFusion 9 with CF-ORM

entity init() method must not have required argument(s), otherwise EntityNew() and other CF-ORM actions will break. You may want to use a Factory to create the entity, and enforce the required arguments there.
A bug regarding this limitation has been filed in the Adobe Bugbase.
ORMReload() with ormsettings.dbcreate = "drop create" might not drop all tables for you. CF9 Cumulative Hot Fix 1 improves this a little bit, but you might want to drop the tables in DB yourself.
type="date" (default to use ormtype="date"), will only store date but not time. If you want to persisted time as well, use ormtype="timestamp"
type="string" will default to varchar(255)
type="numeric" will default to float, not int. Use ormtype="int" if needed.
if fieldtype="id" and generator is set to some generator, ormtype will default to int.
type="string" length="10" will use varchar(10), not char(10)
ormtype="char" length="10" will use char(1) still. Use sqltype="char(10)" if you really need to.
type="boolean" use tinyint by default, use sqltype="bit" if you need to.
should use inverse=true in a bi-directional relationship, usually in "one-to-many" side.
do NOT use inverse="true" in uni-directional relationship! The relationship might not be persisted at all!
If you use MS-SQL, you cannot have more than 1 entity with one-to-one property set to Null, because Null is considered as an unique value in an index. Good idea to make column not null. (or use linktable)
EntityLoad("entity", 1, true) works, but EntityLoadByPK("entity", 1) is cleaner!
EntityLoad(), EntityLoadByPK(), and ORMExecuteQuery with unique=true, will return null if entity is not found. Use isNull() to check before you use the returned value.
ORMExecuteQuery will return empty array if no entity is found by default.
don't forget to use singularname property in "one-to-many" / "many-to-many" for nicer looking generated functions (e.g. addDog(Dog dog) vs addDogs(Dog dogs) .)
<cfdump> will load all the lazy-load properties. Alternatively you may try <cfdump var="#entityToQuery([entity])#"> or set top=1 to dump efficiently.
entity stored in Session scope will be disconnected with its Hibernate session scope, and lazy load property will not be loaded. To restore the hibernate session scope, use entityLoadByExample() or entitySave(entity).
cascade="all-delete-orphan" usually make more sense for "one-to-many" or "many-to-many" relationship. Hibernate sets null then delete, so make sure the column is nullable. Test and see if that's your desire behaviour.
set required="true" whenever notnull="true", more readable for others browsing the CFC with CFCExplorer
EntityNew('Y') is slightly more efficient than new com.X.Y if the entity is to be persisted later according to some Adobe engineer.
relationship with an inherited entity may break sometimes due to an unfixed Hibernate bug, use linktable as a workaround.
structKeyColumn cannot be the PK of the target entity.
bi-directional many-to-many cannot use struct
When adding new entity to struct, structKeyColumn is ignored when CF persists the parent entity.
If you access the one-to-many / many-to-many array or struct directly, make sure the corresponding array/struct exists before use. Generated addX()/hasX()/removeX() are safe to use anytime.
at postInsert(), the entity hibernate session is no longer available, so setting property at postInsert() will be silently ignore, or Session is Closed exception will be thrown.
after entity is loaded by entityLoad() or HQL from DB, the changes will be automatically persisted even if EntitySave() is not called.
transaction with CF-ORM is implemented in a way that it starts a new session and close when it's done.
inside the event (i.e. preLoad() / postInsert()), assigning to variables might throw Java exception about types. Use JavaCast() to work around the bug.

UPDATE

CF9.0.1+: use <cfquery dbtype="hql">, easier to do cfqueryparam, and debug output actually shows you the binded values.

How to Override SQL Sanitization in Coldfusion