MS SQL Server 2005 SQL Injection attack; preventative code for application.cfc -- what is your opinion?

Hi. As many of you know, my MS SQL Server 2005 database server is under SQL Injection attack.

My ISP, hosting.com, just sent me some code, which I attach, below.

It looks like I can simply add this code to the end of my application.cfc file, as an extra line of defense. The application.cfc file goes in to web root of every web site on my server that uses ColdFusion UPDATE, INSERT, or DELETE statements.

I'd like to get your opinion of it before I add it to any of the application.cfc files. Have you heard of, or seen this code before? Do you think it will be effective? Can I simply append the code to the end of an existing application.cfc, or does the code go in onApplicationStart or onRequestStart?

Thanks for any advice.

Eric
<cfsilent>
<!---
name:   _SQLPrev.cfm
desc:   Helps Prevent SQL Injection Attacks.
author: Justin D. Scott of GravityFree (jscott@gravityfree.com)
date:   November 7, 2005
update: August 7, 2008
notes:

DIRECTIONS
Include this in the Application.cfm/cfc file to help prevent SQL injection attacks.
Compatible with ColdFusion MX, contact me for a version that will run on CF5.
If you update this code to be more effective, please send a copy of the changes
back to me so they can be implemented more widely.

DISCLAIMER
Justin Scott and GravityFree make no representation about the suitability or
accuracy of software or data for any purpose, and makes no warranties, either
expressed or implied, including merchantability and fitness for a particular
purpose or that the use of these software or data will not infringe any third
party patents, copyrights, trademarks, or other rights. The software and data
are provided "as is".  Use at your own risk.

LICENSE
This code is hereby released into the public domain.
--->

<!--- E-Mail address for attack notifications --->
<cfparam name="request.errorEmail" default="programmers@yourdomain.com" />
<!--- On attack, TRUE to abort FALSE to redirect to rootURL --->
<cfparam name="request.errorAbort" default="FALSE" />
<!--- On attack, TRUE to notify via e-mail --->
<cfparam name="request.errorNotify" default="TRUE" />
<!--- On Attack, TRUE to log --->
<cfparam name="request.errorLog" default="FALSE" />
<!--- Redirection URL --->
<cfparam name="request.rootURL" default="/" />

<cfscript>
// Default to nothing.
variables._SQLPrev_Found = "";

// What are the SQL Keywords?
variables._SQLPrev_Keywords = structNew();

// Populate the structure.
structInsert(variables._SQLPrev_Keywords, "EXEC", "");
structInsert(variables._SQLPrev_Keywords, "ALTER", "");
structInsert(variables._SQLPrev_Keywords, "EXECUTE", "");
structInsert(variables._SQLPrev_Keywords, "PROC", "");
structInsert(variables._SQLPrev_Keywords, "ASC", "");
structInsert(variables._SQLPrev_Keywords, "FILE", "");
structInsert(variables._SQLPrev_Keywords, "PROCEDURE", "");
structInsert(variables._SQLPrev_Keywords, "AUTHORIZATION", "");
structInsert(variables._SQLPrev_Keywords, "BACKUP", "");
structInsert(variables._SQLPrev_Keywords, "RAISERROR", "");
structInsert(variables._SQLPrev_Keywords, "FOREIGN", "");
structInsert(variables._SQLPrev_Keywords, "FREETEXT", "");
structInsert(variables._SQLPrev_Keywords, "READTEXT", "");
structInsert(variables._SQLPrev_Keywords, "BREAK", "");
structInsert(variables._SQLPrev_Keywords, "FREETEXTTABLE", "");
structInsert(variables._SQLPrev_Keywords, "RECONFIGURE", "");
structInsert(variables._SQLPrev_Keywords, "BROWSE", "");
structInsert(variables._SQLPrev_Keywords, "REFERENCES", "");
structInsert(variables._SQLPrev_Keywords, "BULK", "");
structInsert(variables._SQLPrev_Keywords, "FULL", "");
structInsert(variables._SQLPrev_Keywords, "REPLICATION", "");
structInsert(variables._SQLPrev_Keywords, "FUNCTION", "");
structInsert(variables._SQLPrev_Keywords, "RESTORE", "");
structInsert(variables._SQLPrev_Keywords, "CASCADE", "");
structInsert(variables._SQLPrev_Keywords, "GOTO", "");
structInsert(variables._SQLPrev_Keywords, "RESTRICT", "");
structInsert(variables._SQLPrev_Keywords, "GRANT", "");
structInsert(variables._SQLPrev_Keywords, "RETURN", "");
structInsert(variables._SQLPrev_Keywords, "CHECK", "");
structInsert(variables._SQLPrev_Keywords, "GROUP", "");
structInsert(variables._SQLPrev_Keywords, "REVOKE", "");
structInsert(variables._SQLPrev_Keywords, "CHECKPOINT", "");
structInsert(variables._SQLPrev_Keywords, "HAVING", "");
structInsert(variables._SQLPrev_Keywords, "RIGHT", "");
structInsert(variables._SQLPrev_Keywords, "CLOSE", "");
structInsert(variables._SQLPrev_Keywords, "HOLDLOCK", "");
structInsert(variables._SQLPrev_Keywords, "ROLLBACK", "");
structInsert(variables._SQLPrev_Keywords, "CLUSTERED", "");
structInsert(variables._SQLPrev_Keywords, "IDENTITY", "");
structInsert(variables._SQLPrev_Keywords, "ROWCOUNT", "");
structInsert(variables._SQLPrev_Keywords, "COALESCE", "");
structInsert(variables._SQLPrev_Keywords, "IDENTITY_INSERT", "");
structInsert(variables._SQLPrev_Keywords, "ROWGUIDCOL", "");
structInsert(variables._SQLPrev_Keywords, "COLLATE", "");
structInsert(variables._SQLPrev_Keywords, "IDENTITYCOL", "");
structInsert(variables._SQLPrev_Keywords, "COLUMN", "");
structInsert(variables._SQLPrev_Keywords, "COMMIT", "");
structInsert(variables._SQLPrev_Keywords, "SCHEMA", "");
structInsert(variables._SQLPrev_Keywords, "COMPUTE", "");
structInsert(variables._SQLPrev_Keywords, "INDEX", "");
structInsert(variables._SQLPrev_Keywords, "SELECT", "");
structInsert(variables._SQLPrev_Keywords, "CONSTRAINT", "");
structInsert(variables._SQLPrev_Keywords, "INNER", "");
structInsert(variables._SQLPrev_Keywords, "SESSION_USER", "");
structInsert(variables._SQLPrev_Keywords, "CONTAINS", "");
structInsert(variables._SQLPrev_Keywords, "INSERT", "");
structInsert(variables._SQLPrev_Keywords, "SET", "");
structInsert(variables._SQLPrev_Keywords, "CONTAINSTABLE", "");
structInsert(variables._SQLPrev_Keywords, "INTERSECT", "");
structInsert(variables._SQLPrev_Keywords, "SETUSER", "");
structInsert(variables._SQLPrev_Keywords, "CONTINUE", "");
structInsert(variables._SQLPrev_Keywords, "INTO", "");
structInsert(variables._SQLPrev_Keywords, "SHUTDOWN", "");
structInsert(variables._SQLPrev_Keywords, "CONVERT", "");
structInsert(variables._SQLPrev_Keywords, "CREATE", "");
structInsert(variables._SQLPrev_Keywords, "JOIN", "");
structInsert(variables._SQLPrev_Keywords, "STATISTICS", "");
structInsert(variables._SQLPrev_Keywords, "CROSS", "");
structInsert(variables._SQLPrev_Keywords, "KEY", "");
structInsert(variables._SQLPrev_Keywords, "SYSTEM_USER", "");
structInsert(variables._SQLPrev_Keywords, "CURRENT", "");
structInsert(variables._SQLPrev_Keywords, "KILL", "");
structInsert(variables._SQLPrev_Keywords, "TABLE", "");
structInsert(variables._SQLPrev_Keywords, "CURRENT_DATE", "");
structInsert(variables._SQLPrev_Keywords, "LEFT", "");
structInsert(variables._SQLPrev_Keywords, "TEXTSIZE", "");
structInsert(variables._SQLPrev_Keywords, "CURRENT_TIME", "");
structInsert(variables._SQLPrev_Keywords, "LIKE", "");
structInsert(variables._SQLPrev_Keywords, "THEN", "");
structInsert(variables._SQLPrev_Keywords, "CURRENT_TIMESTAMP", "");
structInsert(variables._SQLPrev_Keywords, "LINENO", "");
structInsert(variables._SQLPrev_Keywords, "CURRENT_USER", "");
structInsert(variables._SQLPrev_Keywords, "LOAD", "");
structInsert(variables._SQLPrev_Keywords, "TOP", "");
structInsert(variables._SQLPrev_Keywords, "CURSOR", "");
structInsert(variables._SQLPrev_Keywords, "NATIONAL", "");
structInsert(variables._SQLPrev_Keywords, "TRAN", "");
structInsert(variables._SQLPrev_Keywords, "DATABASE", "");
structInsert(variables._SQLPrev_Keywords, "NOCHECK", "");
structInsert(variables._SQLPrev_Keywords, "TRANSACTION", "");
structInsert(variables._SQLPrev_Keywords, "DBCC", "");
structInsert(variables._SQLPrev_Keywords, "NONCLUSTERED", "");
structInsert(variables._SQLPrev_Keywords, "TRIGGER", "");
structInsert(variables._SQLPrev_Keywords, "DEALLOCATE", "");
structInsert(variables._SQLPrev_Keywords, "TRUNCATE", "");
structInsert(variables._SQLPrev_Keywords, "DECLARE", "");
structInsert(variables._SQLPrev_Keywords, "NULL", "");
structInsert(variables._SQLPrev_Keywords, "TSEQUAL", "");
structInsert(variables._SQLPrev_Keywords, "DEFAULT", "");
structInsert(variables._SQLPrev_Keywords, "NULLIF", "");
structInsert(variables._SQLPrev_Keywords, "UNION", "");
structInsert(variables._SQLPrev_Keywords, "DELETE", "");
structInsert(variables._SQLPrev_Keywords, "UNIQUE", "");
structInsert(variables._SQLPrev_Keywords, "DENY", "");
structInsert(variables._SQLPrev_Keywords, "OFF", "");
structInsert(variables._SQLPrev_Keywords, "UPDATE", "");
structInsert(variables._SQLPrev_Keywords, "DESC", "");
structInsert(variables._SQLPrev_Keywords, "OFFSETS", "");
structInsert(variables._SQLPrev_Keywords, "UPDATETEXT", "");
structInsert(variables._SQLPrev_Keywords, "DISK", "");
structInsert(variables._SQLPrev_Keywords, "USE", "");
structInsert(variables._SQLPrev_Keywords, "DISTINCT", "");
structInsert(variables._SQLPrev_Keywords, "OPEN", "");
structInsert(variables._SQLPrev_Keywords, "USER", "");
structInsert(variables._SQLPrev_Keywords, "DISTRIBUTED", "");
structInsert(variables._SQLPrev_Keywords, "OPENDATASOURCE", "");
structInsert(variables._SQLPrev_Keywords, "VALUES", "");
structInsert(variables._SQLPrev_Keywords, "DOUBLE", "");
structInsert(variables._SQLPrev_Keywords, "OPENQUERY", "");
structInsert(variables._SQLPrev_Keywords, "VARYING", "");
structInsert(variables._SQLPrev_Keywords, "DROP", "");
structInsert(variables._SQLPrev_Keywords, "OPENROWSET", "");
structInsert(variables._SQLPrev_Keywords, "VIEW", "");
structInsert(variables._SQLPrev_Keywords, "DUMMY", "");
structInsert(variables._SQLPrev_Keywords, "OPENXML", "");
structInsert(variables._SQLPrev_Keywords, "WAITFOR", "");
structInsert(variables._SQLPrev_Keywords, "DUMP", "");
structInsert(variables._SQLPrev_Keywords, "OPTION", "");
structInsert(variables._SQLPrev_Keywords, "WHEN", "");
structInsert(variables._SQLPrev_Keywords, "WHERE", "");
structInsert(variables._SQLPrev_Keywords, "END", "");
structInsert(variables._SQLPrev_Keywords, "ORDER", "");
structInsert(variables._SQLPrev_Keywords, "WHILE", "");
structInsert(variables._SQLPrev_Keywords, "ERRLVL", "");
structInsert(variables._SQLPrev_Keywords, "OUTER", "");
structInsert(variables._SQLPrev_Keywords, "WITH", "");
structInsert(variables._SQLPrev_Keywords, "ESCAPE", "");
structInsert(variables._SQLPrev_Keywords, "OVER", "");
structInsert(variables._SQLPrev_Keywords, "WRITETEXT", "");

// Now check through the URL variables for possible SQL attacks.
for (_SQLPrev_Index1 in URL) {
	// Bring in the URL value.
	variables._SQLPrev_Value = URL[_SQLPrev_Index1];
	// Find any of the keywords in this value.
	for (_SQLPrev_Index2 in variables._SQLPrev_Keywords) {
		if (findNoCase(_SQLPrev_Index2, variables._SQLPrev_Value) and find(";", variables._SQLPrev_Value)) {
			variables._SQLPrev_Found = "sql";
		}
	}
}

// Kill the temp struct with the SQL keywords.
structClear(variables._SQLPrev_Keywords);

</cfscript>

<!--- Did we find anything? --->
<cfif len(variables._SQLPrev_Found)>

	<!--- Log if requested --->
	<cfif request.errorLog>
		<cflog file="SQLInjectionAttack" application="no" text="#cgi.remote_addr#" />
	</cfif>

	<!--- E-Mail the error for tracking. --->
	<cfif request.errorNotify>
		<cfmail to="#request.errorEmail#" from="#request.errorEmail#" subject="SQL Injection Attempt" type="HTML">
			<p>Date: #now()#</p>
			<p>Site: #cgi.server_name#</p>
			<p>URL: #cgi.script_name#?#cgi.query_string#</p>
			<p>IP: #cgi.remote_addr#</p>
			<cfdump var="#url#" />
			<cfdump var="#variables#" />
		</cfmail>
	</cfif>

	<!--- Abort or redirect to home. --->
	<cfif request.ErrorAbort>
		<cfabort>
	<cfelse>
		<cflocation url="#request.rootURL#" addtoken="no" />
	</cfif>

</cfif>

</cfsilent>

Open in new window

LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
From your post, it is NOT clear whether you have updated your ACTUAL queries or not, but if you haven't, I suggest you use <CFQUERYPARAM> in your queries.

There quite a few links that explain it's usage/purpose:

http://kb2.adobe.com/cps/300/300b670e.html
http://coldfusion.sys-con.com/node/41712
http://www.petefreitag.com/item/677.cfm
0
Eric BourlandAuthor Commented:
hielo,

Yes indeed, I am using CFQUERYPARAM in all of my queries that involve UPDATE, INSERT, or DELETE. I don't use any other kinds of SQL statements.

I'm about to ask another question about steps I can take to review the "attack surface area" of my ColdFusion server. For one thing, in CF administrator, I have allowed only the SQL statements that my code requires: SELECT, UPDATE, INSERT, DELETE. Maybe there are other steps I can take. That topic is outside the scope of this current question however.

I am experimenting with the above _SQLPrev.cfm code. Looks like I can just place the code in application.cfc, before the closing </cfcomponent>. We will see how that works out!

Thanks for your help as always. Peace.

Eric
0
gdemariaCommented:
The code would be placed inside your onRequestStart  function, or better yet, you can create a function from that code and then call it from your onRequestStart function.

IMHO, the code lacks some intelligence, simply checking to see if all those keywords exist within your query string is a bit drastic.  There is another post on EE where the user's site shows a page not found seemingly randomly.   Turns out that the when his URL contains  city=castlerock (or something like that) the SQL injection trigger caused it to abort due to the part of the word CAST in CASTlerock.  

I think you should experiment with it at best, it seems to be over kill to me.  Almost all SQL injection using the CAST( function, so searching for that will cover the majority of the attacks.   From what I have seen the rest of the command is encoded in a URL format (thus the purpose of cast()) so you can't even see those other words in the command anyway !

If you know what you are dealing with, you will be better off.   Check your logs to see what the SQL attack look like.

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Gurpreet Singh RandhawaCEOCommented:
Well Eric!!

There are tons of ways sql injection attacks can be done!

1. You can use the Script i gave u in the previous question to save ur code insert/update/delete from them it is called as XSS Attach.

2. read this entry:

http://www.coldfusioncookbook.com/entry/36/How-can-I-prevent-SQL-injection-attacks?
0
Eric BourlandAuthor Commented:
gdemaria and myselfrandhawa,

Thanks for this. gdemaria, I get what you are saying about the problem with simply filtering the word CAST from a query. Valid queries containing cast* will be stopped.

>>>From what I have seen the rest of the command is encoded in a URL format (thus the purpose of cast()) so you can't even see those other words in the command anyway

Interesting point. I will take a closer look.

myselfrandhawa, I really appreciate your idea. The trouble is, I don't quite understand it. =) I am going to study your XSS Attach code and see what I can make of it. Is XSS Attach a separate file that I create? Does it go into application.cfc? Or does it go into my form action page? These are things I need to study and think about -- I am very grateful that you take time to offer me this information. I will work on it and make myself understand.

I welcome all suggestions to stop this SQL injection nonsense, so I can get back to work. I am not able to sleep or think because of this problem.

I will continue tomorrow. (I am on Chicago time, central standard time USA.)

Take care, thank you again, and have a good evening.

Eric
0
gdemariaCommented:
Eric, let's try to wrap this up.  

What is your current status?  Is the attack still creating new records in the database, even after the code you placed into your application.cfc file?

If yes, have you run the log analyzer to see where your vulnerabilities are?    If not, you should probably do that asap so you can directly address the area of weakness.

What code of myselfrandhawa's are you confused about?  His link above is just the same-old use cfqueryparam which you already know and are already implementing.   If you're referring to the code snippet of a previous question, that is just the same type of thing that you posted in your question.  Just another code segment to examine the URL.  

So, in summary, you have two solutions in front of you.  cfqueryparam and analyzing your url variables.   Analyze your log , put those two things in place and see if its over.  

We are trying to fight this in the dark, let's see the log results.

0
Gurpreet Singh RandhawaCEOCommented:
Eric Stopping XSS is quite Simple:

u define it as:

onRequestStart Method:

<cfset request.xssattack = "(-- )|(' )|(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE) |(CAST) |(DROP)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">

now have a check in the form as:

<cfif NOT RefindNocase("#request.xssattack#",form.firstname)>
<cfset error! Attack Found>
</cfif>

Simple enough
0
Gurpreet Singh RandhawaCEOCommented:
use cflog for writing all details in the log file may be after the insert
0
Eric BourlandAuthor Commented:
gdemaria,

I had log files turned off in IIS 6. I turned them on this morning, for the affected sites. By tomorrow I will have log files to analyze, and will report my data here. I agree, it's no good fighting in the dark.

I've learned a lot these past few days.

myselfrandhawa -- that makes a lot more sense. I am not thinking. Sorry. It's been a long time since I slept. I'm going to get some sleep then will return to this task tomorrow with a clear focus.

I really appreciate your help. I agree, it's way beyond time to wrap this up. It's driving me crazy.

Peace.

Eric
0
Eric BourlandAuthor Commented:
OK, I've almost got this.

I have modified my application.cfc (see attached code). The application.cfc resides in web root.

Also, I took one of my forms, as a test, and modified it per myselfrandhawa's note, above.

I also modified my page_edit_process.cfm to include:

<cfif NOT RefindNocase("#request.xssattack#",form.firstname)>
<cfset error! Attack Found>
</cfif>

.... code attached below. I believe I have done this correctly. I have read the notes about SQL Injection at these sites:

http://www.jasonbartholme.com/2-methods-to-help-prevent-sql-injections-with-coldfusion/
http://www.coldfusioncookbook.com/entry/36/How-can-I-prevent-SQL-injection-attacks
http://www.petefreitag.com/item/677.cfm

... I am not sure how the RefindNocase works here, but I see it must be similar to the ListFindNoCase function mention in the Pete Freitag page.

Does the code below (application.cfc, page_process_edit.cfm) look OK to you or have I missed something?

Thank you again. Hope you are well.

Eric



application.cfc:

<!--- set up application --->
<cfset this.name="this">
<cfset this.clientManagement="true">
<cfset this.sessionManagement="true">
<cfset this.setclientcookies="true">
<cfset this.sessiontimeout=CreateTimeSpan(0,0,20,0)>
<cfset this.applicationtimeout=CreateTimeSpan(0,0,60,0)>
<cfset this.clientstorage="cookie">
<cfset this.loginstorage="session">
<cfset this.scriptprotect="all">

<!--- set path to cfform.js --->
<cfparam name="Request.CFFORM_JS_Lib" type="string" default="http://76.12.181.86/CFIDE/scripts/cfform.js" />


<cffunction name="onRequestStart" returnType="any">   

<!--- if query_string contains cast(, then abort! --->						
<cfif cgi.query_string contains "cast(">
     <cfabort>
</cfif>
<!--- end abort cast --->


<!--- prevent xssattack --->
<cfset request.xssattack = "(-- )|(' )|(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE) |(CAST) |(DROP)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">
<!--- end xssattack --->

</cffunction>

<cffunction name="onApplicationStart" returnType="any">   
<cfset application.datasource = "ebwebwork">
</cffunction>




page_edit_process.cfm:

  <!--- Update values in database columns --->
      
<cfif NOT RefindNocase("#request.xssattack#",form.PageTitle)>
	<cfset error! Attack Found>
</cfif>

      
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="UpdatePage">
UPDATE cedarcreekbusinesssolutions
SET PageTitle=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageTitle#">
      ,PageContent=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageContent#">
      ,DateModified=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
WHERE PageID=<cfqueryparam value="#form.PageID#" cfsqltype="cf_sql_integer">
</cfquery>

<!--- When done go back to Admin Page --->
<cflocation url="/admin/managePages.cfm" />

Open in new window

0
Gurpreet Singh RandhawaCEOCommented:
u used this:

DateModified=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

always use:

DateModified=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(now())#">
 
as it automatically handles how the date is being stored in different databases

Cheers

All other good


do it like this:

<cfif NOT RefindNocase("#request.xssattack#",form.PageTitle)>
      <cfset s = "Error! Attack Found">
<cfelse>
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="UpdatePage">
UPDATE cedarcreekbusinesssolutions
SET PageTitle=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageTitle#">
      ,PageContent=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageContent#">
      ,DateModified=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
WHERE PageID=<cfqueryparam value="#form.PageID#" cfsqltype="cf_sql_integer">
</cfquery>

<!--- When done go back to Admin Page --->
<cflocation url="/admin/managePages.cfm" />
</cfif>

then where u carry ur process_page.cfm define this as:

<cfif isdefined('s')>
<cfoutput>#s#</cfoutput>
</cfif>

That will show you the Error if it encountered any XSS Attack in the pagetitle field..

Cheers
0
Eric BourlandAuthor Commented:
myselfrandhawa, this is extremely helpful, and it makes perfect sense to me.

<cfif NOT RefindNocase("#request.xssattack#",form.PageTitle)>

Does this RefindNoCase function protect only the column PageTitle? Is there an easy way to protect all columns in a table using this method?

Thank you very much.

Best from Eric
0
Eric BourlandAuthor Commented:
Also, I get this error in page_edit_process.cfm:

 Element XSSATTACK is undefined in REQUEST.
 
The error occurred in C:\websites\CedarCreekBizSolutions.com\admin\page_edit_process.cfm: line 3

1 :   <!--- Update values in database columns --->
2 :      
3 : <cfif NOT RefindNocase("#request.xssattack#",form.PageTitle)>
4 :       <cfset s = "Error! Attack Found">
5 : <cfelse>

Do I need to in use CFINVOKE to invoke the application.cfc in web root?
0
Gurpreet Singh RandhawaCEOCommented:
in ur application.cfc u used this:

<!--- if query_string contains cast(, then abort! --->                                    
<cfif cgi.query_string contains "cast(">
     <cfabort>
</cfif>
<!--- end abort cast --->


u see my request.xssattack it shows that CAST is already defined there, so cfabort halting it so remove and then try and see what u get
0
gdemariaCommented:
Eric, the code below runs a bunch of tests through the script you are planning on implementing.

The first 5 lines should all fail because they are used in SQL injection attacks
The rest of the lines should pass because they are acceptable titles that your use may enter

To run this.  Create a new coldfusion .cfm file, put the code in and open it in your browser.  See the Fail  and Pass flags for each line.

<cfset request.xssattack = "(-- )|(' )|(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE) |(CAST) |(DROP)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">

<cfset phrases = arrayNew(1)><cfset i = 1>
<cfset phrases[i] = "delete clients"> <cfset i = i + 1>
<cfset phrases[i] = "drop table clients"> <cfset i = i + 1>
<cfset phrases[i] = "DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44004500"> <cfset i = i + 1>
<cfset phrases[i] = "54%20AnD%20(cAsT(CoUnT(1)%20aS%20VaRcHaR(100))%2bChAr(94)%20fRoM%20[mAsTeR]..[sYsDaTaBaSeS])"> <cfset i = i + 1>
<cfset phrases[i] = "CAST(0x4445434C415245204054205641524348415228323535292C404320564"> <cfset i = i + 1>

<cfset phrases[i] = "Welders Union"> <cfset i = i + 1>
<cfset phrases[i] = "Welders' Best Job"> <cfset i = i + 1>
<cfset phrases[i] = "60 Minutes-- News Report"> <cfset i = i + 1>
<cfset phrases[i] = "25th Reunion"> <cfset i = i + 1>
<cfset phrases[i] = "Ulysis S. Grant Bibliography"> <cfset i = i + 1>
<cfset phrases[i] = "Smiths' Family Receipe"> <cfset i = i + 1>
<cfset phrases[i] = "Achondroplastic"> <cfset i = i + 1>
<cfset phrases[i] = "Simply Unrevoked"> <cfset i = i + 1>
<cfset phrases[i] = "Union Pacific Station"> <cfset i = i + 1>

<cfloop index="i" from="1" to="#arrayLen(phrases)#">
  <cfset aPhrase = phrases[i]>
  <cfoutput>Testing: #aPhrase#</cfoutput>
  <cfif RefindNocase(request.xssattack,aPhrase)>
     <span style="color:red;">FAIL</span>
  <cfelse>
     <span style="color:green;font-weight:bold;">Pass</span>
  </cfif>
  <br>
</cfloop>
<br>
Done.

Open in new window

0
gdemariaCommented:
Oh, by the way.

The code was backwards, to get it to work at all I had to remove the NOT

As it was written, this will pass all form field with injection phrases and reject those that are good

<cfif NOT RefindNocase("#request.xssattack#",form.PageTitle)>
       <cfset s = "Error! Attack Found">
<cfelse>
....


should be...

<cfif RefindNocase("#request.xssattack#",form.PageTitle)>
   attack found..


0
Gurpreet Singh RandhawaCEOCommented:
hehe ! my bad :)

thanks for the correction dude
0
Eric BourlandAuthor Commented:
gdemaria,

This is really cool:

http://www.cedarcreekbizsolutions.com/gdemaria_test.cfm

I see the xssattack function in action now.

I am thinking hard about the comments that you and myselfrandhawa have made. My goal is to develop a responsible, simple, safe application that updates a table in a database, for web site www.cedarcreekbizsolutions.com. I will use this application as an example for other, very similar applications.

So, I need to make edits to these pages:

application.cfc
page_edit_process.cfm (updates a database record)
page_process_insert.cfm (inserts a database record)

I am studying the notes supplied here to come up with the correct syntax for these pages. I'll report back here later. Thank you again.

Eric
0
Eric BourlandAuthor Commented:
Hi. I have corrected the code, and it works! Thank you very much, myselfrandhawa and gdemaria.

I am looking at the results of the test code that gdemaria sent:

http://www.cedarcreekbizsolutions.com/gdemaria_test.cfm

I see that lines 3 - 5 get a pass -- should I be concerned about that?
application.cfc:

<!--- set up application --->
<cfset this.name="this">
<cfset this.clientManagement="true">
<cfset this.sessionManagement="true">
<cfset this.setclientcookies="true">
<cfset this.sessiontimeout=CreateTimeSpan(0,0,20,0)>
<cfset this.applicationtimeout=CreateTimeSpan(0,0,60,0)>
<cfset this.clientstorage="cookie">
<cfset this.loginstorage="session">
<cfset this.scriptprotect="all">

<!--- set path to cfform.js --->
<cfparam name="Request.CFFORM_JS_Lib" type="string" default="http://76.12.181.86/CFIDE/scripts/cfform.js" />


<!--- begin function or method onRequestStart --->
<cffunction name="onRequestStart" returnType="any">   

<!--- prevent xssattack --->
<cfset request.xssattack = "(-- )|(' )|(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE) |(CAST) |(DROP)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">
<!--- end xssattack --->

<!--- if query_string contains cast(, then abort! --->						
<cfif cgi.query_string contains "cast(">
     <cfabort>
</cfif>
<!--- end abort cast --->

<!--- end function or method onRequestStart --->
</cffunction>

<cffunction name="onApplicationStart" returnType="any">   
<cfset application.datasource = "ebwebwork">
</cffunction>



page_edit_process.cfm:

  <!--- Update values in database columns --->
      
<cfif RefindNocase("#request.xssattack#",form.PageTitle)>
      <cfset s = "Error! Attack Found">
<cfelse>

<cfquery datasource="ebwebwork" dbname="ebwebwork" name="UpdatePage">
UPDATE cedarcreekbusinesssolutions
SET PageTitle=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageTitle#">
      ,PageContent=<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.PageContent#">
      ,DateModified=<cfqueryparam cfsqltype="cf_sql_timestamp" value="#createODBCDateTime(now())#">
WHERE PageID=<cfqueryparam value="#form.PageID#" cfsqltype="cf_sql_integer">
</cfquery>

</cfif>

<!--- When done go back to Admin Page --->
<cflocation url="/admin/managePages.cfm" />

Open in new window

0
gdemariaCommented:
> I see that lines 3 - 5 get a pass -- should I be concerned about that?

Yes, and you should be concerned that all the lines after that have failed !

My point was that the script doesn't work at all.  It rejects phrases that are perfectly fine and allows SQL injection attacks.  

If someone enters a page title of "Smiths' Family Recipe"  It will fail.   But it allows clear SQL attacks.


Also, what is the point of testing the form field for SQL injection immediately before using CFQUERYPARAM which does it as well?    Also, you are only testing one field, pageTitle and not pageDescription.


Sorry Eric, this has been a big waste of time.  In my opinion you should remove that code completely.   Leave the CAST( check in your application.cfc, use the cfqueryparam as you are already doing and then get back to your projects.

If you want more security I will provide you with a bit stronger script for your application.cfc
We can add checks for your form fields to prevent HMLT/javascript being inserted, but that's not SQL injection.



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric BourlandAuthor Commented:
http://www.cedarcreekbizsolutions.com/gdemaria_test.cfm

Sorry, in the code that gdemaria supplied, above, the lines 6 - 8 receive a PASS:

<cfset phrases[i] = "DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44004500"> <cfset i = i + 1>
<cfset phrases[i] = "54%20AnD%20(cAsT(CoUnT(1)%20aS%20VaRcHaR(100))%2bChAr(94)%20fRoM%20[mAsTeR]..[sYsDaTaBaSeS])"> <cfset i = i + 1>
<cfset phrases[i] = "CAST(0x4445434C415245204054205641524348415228323535292C404320564"> <cfset i = i + 1>


I do not see any output for these phrases:

<cfset phrases[i] = "delete clients"> <cfset i = i + 1>
<cfset phrases[i] = "drop table clients"> <cfset i = i + 1>

Just wondering if I should be concerned.

Thank you again for guiding me in this matter.

Eric B
0
Eric BourlandAuthor Commented:
>>>Sorry Eric, this has been a big waste of time.  In my opinion you should remove that code completely.   Leave the CAST( check in your application.cfc, use the cfqueryparam as you are already doing and then get back to your projects.

Got it. =)

I understand, and will do.

I needed this advice.

Thank you gdemaria.

myselfrandhawa: thank you as always for your expert help. I really value your input, every time. Peace.

Eric
0
Eric BourlandAuthor Commented:
Thank you, gdemaria and myselfrandhawa.
0
Gurpreet Singh RandhawaCEOCommented:
Ok Eric..

I suggest you read the bennadel Application.cfc Refrence which can guide a more detailed information how u can set up your variables
0
Eric BourlandAuthor Commented:
>>>bennadel Application.cfc Refrence

I will do that. I am always looking for additional reading material. Thank you for this suggestion.

Hope you're well.

Eric
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.