Rob's Blog

I’m sure I’m not alone in creating administration pages for websites that under development, and relying on the fact the URL is unknown to keep them private (until the site is launched of course).

I currently have a large project under development, which has a number of administration functions that are executed by visting a certain URL (in this case refreshing product information and wiping cache tables). I got a call from the customer telling me that the cache kept disapearing without him running the function, and I knew I hadn’t.

After checking my code, and looking at the data to check if the data really was missing, I decided to check the Apache access logs.

A quick grep told me that the admin URL had been accessed, not by me or the customer, but by Alexa! This had caused the cache to be wiped (correctly as it turns out, since that was what it was meant to do).

I have a firefox plugin on my main office PC that tells me the Alexa ratiung of any sites I visit. Clearly they also spider any URLs they don’t know about!

I quickly added password protection to the admin pages, and will make sure it’s the first thing I do in future. I don’t think (I hope) that they are publicising these private URLs, but visiting them is bad enough. I will of course be removing the Alexa plugin when I get back to the office.

One for the geeks I’m afraid.

I’m developing a site with quite a complicated menu system getting details using equally complicated database queries. In various places in the menu, I needed to find out whether to display a particular sub-menu. To do this I had some SQL to check whether any products existed at that particular point.

The SQL to do this took quite a while to execute (in one case over 30 seconds), which is obviously too long to wait to load a page.

I realised that my SQL statements were selected all appropriate products, where as they in fact only had to check whether any existed.

I could have just added a LIMIT to just return the first row, but if my statement had any ordering etc this wouldn’t help. I don’t want the first row (I don’t care about the data), just to know whether there is one. I knew there must be a better way.

I discovered EXISTS, which although isn’t documented very much, seems to do exactly what I want very simply. The solution is to surround my existing SELECT statement as follows:

SELECT 1 FROM DUAL WHERE EXISTS ( [SELECT statement here] )

That’s it. I can plug any SELECT statement I like in, and it tells me (very quickly) whether any results would be returned. A very useful trick I’m sure I’ll use in future.