reg expression in sql insertion attack

[ranger]

one of my client sites has be under attack for the last several months and seems like i have pretty much stopped it (i hope).

what i am trying to do now is find out their exact string, ip, time, etc.

i am searching for strings like update, datasource, select etc.

example:

<cfif (ListContainsNoCase(myfield, "select"))

OR (ListContainsNoCase(myfield, "update"))...

the problem is when i come across a word like selection, it sets off trigger.

i need a reg expression where select stands alone or with special characters on either side but passes if part of a word such as "reselet" or "selection"

tnx in advance

[Owainnorth]

Now I'm rubbish at Regex, but how about a simple one:

[^a-z]SELECT[^a-z]

Which should (untested) give you the word "select" with anything other than a letter before or after it.

[ranger]

tnx Owain. have tried in a test as:

<cfset myfield = " the selection is">

<cfif (ListContainsNoCase(myfield, "[^a-z]SELECT[^a-z]"))>
aaa
<cfelse>
bbb
</cfif>

and does not seem to be working. i have tried also variations on theme to no effect

[Dan_Bracuk]

Try it with ReFindNoCase.

[Owainnorth]

As Dan says, make sure you're not using a case sensitive search, these hackers can sometimes be inconsiderate and not keep to strict grammar rules

I've had a play with my Regex tester, this one definitely works:

\b(SELECT|INSERT|UPDATE)+\b

Word boundary, followed by at least one select, update or insert, then followed by a word boundary.

[TLC-IT]

Obviously, the right way (if you will...) to solve this kind of problem is:  <cfqueryparam>.

In other words...  never allow user-contributed text to appear, in any form or under any circumstances, within an SQL query that ColdFusion ever presents to the server.  The one and only way that such text should appear is:  as a parameter.

An SQL server will never interpret a query-parameter as possibly being part of the SQL string.  It will have already parsed the SQL text, already generated the execution plan, and be ready to execute it.  The string, no matter what it may contain, will only be interpreted as "character data."

Every "injection attack," no matter what flavor it is, always relies upon mis-interpretation and/or mis-handling of the data that is being "injected."  But there are always ways to prevent this.

It's unfortunate that the ColdFusion language has no notion of Perl's "taint mode," which actually flags individual data-values(!) as "potentially tainted" as they flow through the system.

[Gabriel_Landes]

TLC-IT's point is valid in that SQL injection attacks thrive on misinterpreted statements - you should use cfqueryparam religiously!

BUT, you do still need to be very careful with usersubmitted data - its danger is not just in carrying malicious SQL, but also in carrying malicious HTML/JS when it comes back out. This is why forums often use "BBcode" and reject any HTML coming from the user. If a user can get your system to store and send malicious javascript to other viewers, they can do a lot of harm that way, too. Check out the Cross-site Scripting article below for a good primer on the topic including some suggestions for preventing it.

http://en.wikipedia.org/wiki/Cross-site_scripting