Rebellious Database Programming
Muse Reader Brian Asks:
Do you know of any way to SQL inject the following if the backend is MSSQL Server
select *
from table
where username = '#FORM.username#'
</cfquery>
Occasionally someone asks me this question about CFQUERYPARAM. "Must I use it here or there? In a boat? With a goat?" Yes Sam-I-Am you should make it a habit to use it everywhere. It should be a common part of your best practice guidelines. There are even reasons to use it that go beyond security. Do a quick search for CFQUERYPARAM on this blog and you will find all sorts of information about why to use it and the very rare exceptions (FYI in case you missed the tone here, there is rarely a good reason not to use it).
As for your specific question, I can think of no way to inject the query above. If you moved the query to a MySQL server you might run afoul of the alternate way of escaping single quotes, but on an MSSQL server the query above is safe as far as I know. Just remember, right now some clever hacker in Elbonia is experimenting with ancient character sets, time travel, and a dead cat which he swings over his head while chanting "...one ring to rule them all..." - all in an effort to try and crack into a query like the one above. So I reiterate, there is no way as far as I know. It's what I don't know that keeps me up at night. You really should just use the tag as a matter of course and stop looking for places to not use it. Let me illustrate with a little story my Dad used to tell me.
Foreign Intelligence
In the 1930's a man immigrated to America from a fascist country. He needed cash so he went to the bank to cash a check. Walking up to the teller the man presented his check and his ID and said "I vant cash pleaze" (if you hear an German accent it will help the story).
The teller looked at the check and noticed that the man had failed to sign it. "Sir, you must sign this check in order to get your money." the teller said in a friendly way.
The man, who was leery of being swindled in his unfamiliar new home, said quite forcefully, "I don't sign nutting!" The teller protested to the man that he simply couldn't cash the check unless the man chose to sign it. Finally the man said, "I don't need you... I go to other bank."
Leaving in a huff and crossing the street the man entered another bank and went up to the teller. Presenting his ID and check he said as before, "I vant cash pleaze."
Like the previous teller the man examined the check and said immediately, "You have to sign this check before I can give you the cash".
The man stiffened and reacted again saying, "I don't sign nuttin!" The teller looked at the man intently, then reached over the counter and grabbed him by the hair on the back of his head. Holding it firmly he banged the man's head on the counter two or three times. The man shook his head as if to clear it, then without a word he picked up a pen, signed the check and walked out with his cash.
The man immediately crossed the street to the first bank and, waiving his cash in the teller's face he crowed, "See... they give me cash."
The puzzled teller looked at him and asked accusingly, "But... you had to sign the check, right?"
Without missing a beat the man said, "Yes, but dey esplain it to me over der!"
Virtual "Esplanation"
So Brian, and all of you Muse readers out there who are looking for exceptions to the always-use-cfqueryparam rule. Try to sense what I am doing right now. I'm reaching through the screen and out into the Internet... to grab you by the hair and bang your head a few times on the table of whatever coffee shop you are currently frequenting. Hopefully that will esplain it to you. Now just use the dang tag will ya!
7 Comments
-
I don't have an instance MSSQL to test on, but wouldn't the following be enough to perform a sql injection attack?
<cfset form.username = "something'; Drop table;--">
When the inner contents of the <cfquery> tag are evaluated, the query inside then becomes:
SELECT *
FROM table
WHERE username = 'something'; Drop table; --' -
@Jalpino,
No... CF will automatically escape (double up) the single quote taht you put into your form.username... so the final string would be:
where username = 'something''; drop table; --'
and that would be treated (in total) like a string. This autoescaping of single quotes protects most MSSQL character code but it can be circumvented in MySQL when the server is configured to allow for backslash escaping. Do a search of this blog for 'mysql' and 'injection' and you will find a post on that method. -
Thanks Mark, will look through your blog for more info on MySQL. Like you had mentioned in the post, its better to err on the safe side and just use the tag (<cfqueryparam>)
-
Another easy way to get yourself hacked with a query not too much unlike this would be something like this
<cfset myquery = "select * from table where username = '#form.username#'" />
<cfquery ...>#preserveSingleQuotes(myquery)#</cfquery>
The automatic escaping of single quotes was something that was added to the CFML language in the early days before the CFQUERYPARAM tag was added and it made it a lot easier to write most queries... and then there was that occasional query where for one reason or another you had a string of flat out SQL and escaping the single-quotes caused problems, so they had to give you a way to suppress the automatic escaping. But it's kind of a double-edged sword, you have to be really careful when you choose to use preserveSingleQuotes() for anything at all.
But if you use any of the ORM systems for ColdFusion (any at all), whether it's DataFaucet that I developed, Transfer or Reactor, those tools will add all the CFQUERYPARAM tags for you so you don't have to worry about that too much. You still need to exercise caution when using an ORM of course, because you can always do some boneheaded thing like allow a user to enter a table name in a form:
<cfset myQuery = datasource.getSelect(form.table,"*").execute() />
And then BLAMMO, you've given the user a freebie for SQL injection... Actually wait, NO! DataFaucet protects you against that also! ;) But seriously, there are a couple of security do's and dont's even with DataFaucet. -
@Ike,
Thanks for the great comment. I cover some of the nuances of using a string builder method in a prevoius post:
http://www.coldfusionmuse.com/index.cfm/2008/7/21/...
But your point on ORM is well taken. -
The man's brain had obviously become dislodged. Happens more often than you think. Sometimes a swift blow to the base of the skull is all that is needed to solve many of life's problems.
Sites to Watch
Aggregators
RSS
