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.
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:
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 *Now if you tried to use that in a CF statement as a variable it would error out.
WHERE Title = 'I can\'t get no satisfaction'
SELECT *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:
WHERE Title = 'I can\''t get no satisfaction'
SELECT *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.
WHERE Title = 'I can\'' OR 1 = 1 -- t get no satisfaction'
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:
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:
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:
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".