ColdFusion Muse

SQL Injection Using a Character Field

Ok, I admit it. Most of the examples of SQL injection that I give use a numeric field. Why? Because to inject using a character field requires manipulating single quotes. Since Coldfusion escapes single quotes automatically when using the cfquery tag these attacks are much more difficult to pull off. It may surprise you to know that your character fields can still be vulnerable and it is my belief that you should still use CFQueryparam. In fact, one of the attacks below can work even if you do use cfqueryparam. Check it out.

Alternate Escapes

This is a vulnerability that does not exist on every platform. MS SQL is not affected for example, but MySQL is affected. It has to do with alternate ways of escaping characters. You probably already know that single quotes are escaped by doubling. To use the string I can't get no satisfaction to select against a (grammatically poor) database table you would need to escape the apostrophe in can't to make it I can''t get no satisfaction. This would result in a successful select. In fact Coldfusion does this for you automatically. The following code works:

<Cfset song = "I can't get no satisfaction"/>
    <cfquery ...>
        SELECT     *
        FROM    IgnominiousHits
        WHERE    Title = '#song#'
    </cfquery>
It works because inside of CFQUERY coldfusion converts Can't to Cant''t.

But database platforms sometimes provide other ways of escaping characters. For example, MySQL allows you to escape a single quote either by doubling it or by using the backslash. As an example, here is a syntactically correct MySQL statement:

	SELECT 	*
FROM IgnominiousHits
WHERE Title = 'I can\'t get no satisfaction'
Now if you tried to use that in a CF statement as a variable it would error out.
<Cfset song = "I can\'t get no satisfaction"/>
    <cfquery ...>
        SELECT     *
        FROM    IgnominiousHits
        WHERE    Title = '#song#'
    </cfquery>
This code would produce something like:
	SELECT 	*
FROM IgnominiousHits
WHERE Title = 'I can\''t get no satisfaction'
You see how Coldfusion automatically doubles the quote? Look carefully. If the first quote mark is escaped by the backslash, that second quote mark (the one added by Coldfusion) actually terminates the string. That means anything that comes afterward is fair game for the hacker. For example, consider the following:
<Cfset song = "I can\' OR 1 = 1 -- t get no satisfaction"/>
    <cfquery ...>
        SELECT     *
        FROM    IgnominiousHits
        WHERE    Title = '#song#'
    </cfquery>
This would result in the following SQL statement:
	SELECT 	*
FROM IgnominiousHits
WHERE Title = 'I can\'' OR 1 = 1 -- t get no satisfaction'
The two dashes "comment out" the rest of the string literal leaving "or 1 = 1 as the tail end of the WHERE clause. Obviously this benign example would return all the hits in the IgnominiousHits table (it would be a bloodbath). Does Cfqueryparam solf this problem? Yes indeed it does. By binding the whole string to a variable and type cfqueryparam insures that the string will always be treated as a string and never read as anything else.

PreserveSingleQuotes() is Madness

In many Coldfusion applications throughout the web the use of Preservesinglequotes() is common. This is probably a legacy of folks coming to CF from ASP or JSP where concatenating a string together and passing it to an SQL execute function is standard practice. It looks something like this:

<cfset string = "SELECT * FROM users WHERE username = '#form.username#'"/>
    <cfquery name="getUser" datasource="myDSN">
        #preserveSingleQuotes(string)#    
    </cfquery>
This aptly named function keeps CF from escaping the quotes and creating a syntax error. In so doing it should be obvious that it also opens up the flood gates to injection. If form.Username contains the string Mkruger' OR username LIKE 'Admin%' it is going to be able to drill into the database. Although the use of preserveSingleQuotes is a fairly obvious vulnerability the next one is not so obvious.

Runtime Execution in Stored Procedures

Hang around long enough and you will hear folks talk about how wonderful stored procedures are for encapsulation and security. Stored procedures can be the bees knees to be sure but they are not a panacea. In fact, one particular use of stored procedures can bite you even if you are using cfstoredprocparam (the procedure equivilent of Cfqueryaparm) or cfqueryparam. Consider my simple example - this time using MS SQL:

<cfquery ...>
CREATE PROCEDURE usp_test
     (    @whereClause nvarchar(2000) )
WITH RECOMPILE
AS
SET NOCOUNT ON


DECLARE @STR nVARCHAR(4000)

SELECT @STR = 'SELECT * FROM stats WHERE ' + @whereClause

print @STR

EXEC sp_executesql @STR
</cfquery>
This procedure takes a where clause as a string and concatenates the entire statement for execution. Why would anyone want to do this? In my experience some shops are narrowly focused on using SPs for everything. In most cases it works fine but it is exceedingly difficult to do those fancy search forms with lots of different search parameters. The same task is really easy and maintainable using Coldfusion logic directly inside of a cfquery tag. For example:
<cfquery ...>
        SELECT     *
        FROM     users    
        WHERE    username = '#form.username#'
        <cfif isDefined('form.lastname') AND NOT isEmpty(form.lastname)>
            AND    lastName = '#form.lastname#'
        </cfif>
        <cfif isDefined('form.email' AND isValid('email',form.email)>
            AND email = '#form.email#'
        </cfif>
    </cfquery>

Please note that I did NOT use cfqueryparam in the above example only to save a little space. In production, Cfqueryparam would definitely be used. Code like that above is simple and visually understandable in CF. Not so much in a stored procedure where you have to account for nulls, empty strings and the like - and use coalesce and other less common SQL techniques. So ironically shops that want to use stored procedures for security and encapsulation end up passing whole or partial query statements as strings and converting them into executable SQL after they reache the SQL server. They do something like this:

<cfsavecontent variable="whereclause">
        username = '#form.username#'
        <cfif isDefined('form.lastname') AND NOT isEmpty(form.lastname)>
            AND    lastName = '#form.lastname#'
        </cfif>
        <cfif isDefined('form.email' AND isValid('email',form.email)>
            AND email = '#form.email#'
        </cfif>
    </cfsavecontent>
    <!--- run query --->
    <cfquery ...>
        usp_test (<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#whereclause#"/>)
    </cfquery>
Of course the string passed to the SQL server bound as a string. But then lo and behold it is treated as part of an executable SQL statement. This rather defeats the purpose of using Cfqueryparam in the first place.

Conclusion

So as you can see there is more to SQL injection than meets the eye. My final thought is a comment on how developers approach applications. Dozens of times a year my staff and I are dumbfounded as we release a product for testing to a client, and they promptly do something with it that we never thought of before (sometimes even unearthing errors or bugs). Developers get tunnel vision pretty easily - and the smart ones are sometimes the worst. In the words of Grenalda Smortensgrammer before he leapt into the chasm - "It's not what I know that troubles me, it's all the stuff I know that I don't know".

Comments
anon's Gravatar "one of the attacks below can work even if you do use cfqueryparam"

which one's that then?
# Posted By anon | 2/25/08 11:59 AM
Mark Kruger's Gravatar The runtime execution inside of a stored procedure. Since part of the executable SQL is contained in a string variable and then parsed on the DB Server, binding the var to a string type gives you no security agains SQL injection.
# Posted By Mark Kruger | 2/25/08 12:05 PM
Counter Strike's Gravatar Thanks so much for the Interesting article.
greetings, CS
# Posted By Counter Strike | 2/26/08 4:41 AM
Doug's Gravatar So, after reading this and other blogs, it appears that MSSQL is immune to injection via a character field. All the MSSQL/CF examples I see are of the type www.mysite.com?myid=123 where myid is an integer, not a character field.

Am I right in this or are there examples with MSSQL where myid could be charactgers? The reason I ask is that I've inherited a CF3-CF5 site with hundreds and hundreds of queries and zero cfqueryparam's and I want to quickly prioritize what needs to be immediately cfqueryparamed and what can wait for the second run.

Doug
# Posted By Doug | 12/31/08 7:56 PM
David's Gravatar How would i use cfqueryparam to escape double quotes? Can double quotes be used in sql injections?
# Posted By David | 2/25/09 5:18 PM
Mark Kruger's Gravatar Double quotes are not special characters in MSSQL but they can be so in Oracle or other platforms - although I believe you have to opt for them.

As far as escaping them, you don't need to do anything special with double quotes... in your cfqueryparam do the following:

value='my string with a " double quote " or two " in it' .../>

Of course if your double quote is inside of a variable you have no worries either.

cfset str = 'this is my " double " quote string'

cfqueryparam value="#str#" ..../>

single and double quotes are interchangeable when writing CF code, but you don't have to massage double quotes when sending a var to SQL.

I hope this helps.
# Posted By Mark Kruger | 2/25/09 5:28 PM
David's Gravatar Im using Access. I have some form processing on my site that pulls stuff from the db and users can add reviews to the db as well, but i havent used the cfqueryparam tag at all in my sql statements. I just did my server side validation by checking the length of the form variables and putting a maxlength on them, as well as client data validation. Does cfqueryparam escape sql characters to prevent sql injections?

Thanks!
# Posted By David | 2/25/09 5:43 PM
anseva's Gravatar nice. .Interesting article. .coldfusion server only add one quotes on quotes are odd, not on even one. .hmmmh. .
# Posted By anseva | 3/4/10 4:10 AM



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.