ColdFusion Muse

probably breaks lots of open source software that expects that style of escaping. But I suppose if you're running CF that's less of an issue, you probably aren't also running PHP and likely to install PHPBB/Wordpress/Gallery/etc.

So far as I can tell, for MS SQL, simply quoting everything, including numeric values, works to block malicious entries.

Coldfusion sees it as a string and escapes any quotes; SQL receives it, looks to see if it's an int, continues as normal if it is, rejects it if it's not (or if the field can contain normal characters, it will simply store the whole string as text without executing it. You do take a small performance hit from that though.

Obviously, using a cfqueryparam helps too and gives you a performance boost instead of reduction. And cfset myInt = int(myInt) can kick it out before it even gets to SQL.

The single quote escape issue may be the first time I've ever seen a good reason to praise MSSQL over its competitors... now if only they'd give us order by rand() and limit 20,30...

Hey Mark,

Thanks for the tip of the hat and I am glad I could be of help! Love your blog, btw.

My tip of the hat goes entirely to MySQL reference manual - most of my post on HoF was a verbatim copy from there. You can download it in PDF, HTML or CHM formats from mysql website. It is a true treasure trove of information on everything MySQL.

The only consequence of using NO_BACKSLASH_ESCAPES slq mode that I know of, is that when using string comparison with LIKE operator in queries, and seraching for a literal MySQL wildcard character (% or _) you must use and specify a different escape sequence in the format: expr LIKE pat [ESCAPE 'escape_char'].
For example: SELECT 'ColdFusion' LIKE 'ColdF|%' ESCAPE '|'

I may also add that NO_BACKSLASH_ESCAPES is available starting from MySQL 5.0.1

See you on the CF-Talk list!

Yah!
This works more than fine thanks guys God bless