Stripping Unwanted Referrers from Refer Database

If you use Dean Allen‘s wonderful Refer PHP script to track incoming referrers as I do, you may come across some unwanted referrers that cannot be blocked from within refer.php.

I was specifically having issues with a cached Google reference to an entry on that, in a previous life, had a popup photo of my friend Samantha. Google had ‘samantha.jpg’ cached, but their incoming references to the photo are, somehow, borked. Unfortunately, Google still treats this as a valid incoming referral, probably because the photo itself hasn’t been taken down.

There must be 15 or 20 people a day who hit that image through, and well, I was getting annoyed. So I made myself take the time to learn how to write a shell script to call a .sql script. What follows is the result of that.

.sql Script to Strip Unwanted Referrers

The .sql script is pretty straightforward: connect to the database, run a nice DELETE FROM command, then quit.

CONNECT [database];
DELETE FROM refer WHERE page = '[unwanted referrer]';

Per my convention, items in [brackets] should be replaced by the end-user of the script.

If I had multiple pages that I wanted stripped, I’d just have multiple DELETE FROM commands.

Shell Script to Run the .sql Script

#!/bin/csh -f

# Log in to MySQL and run striprefer.sql.

mysql -hlocalhost -u[user] -p[password] < striprefer.sql

N.B.: This assumes that striprefer.sql and the attendant shell script—which I’ve named striprefer—are in the same directory. If they are not, you will need to reference that from the user root (e.g., ./sql-scripts/striprefer.sql).

You can’t have the shell script run the SQL commands, unfortunately. I found that out the hard way.

How I Use ./striprefer

I use a cron job to run this every hour. That’s probably overkill, but as I said, I’m using these scripts to stop an annoyance, so I’ll deal with the attendant overhead.