Category Archives: Applications

Comment spam vs nofollow

More comment spam hitting us at the moment, but curiously the comments don’t seem to have URLs with them, so I’m not sure what the point is. They’re all purporting to be from non-English-speaking e-mail addresses, and many in broken English, with a generic compliment about how marvellous your web site is. Odd.

Meanwhile, Google have come up with a new <rel=”nofollow”> attribute for links to help fight comment spam. And they’ve got a bunch of blogging heavyweights to back it, too, including the MT/TypePad, Blogger (duh), MSN Spaces and the WordPress gang, which might well cover a good proportion of blogs running today.

Now, W3C ratification, anybody? Oh pah, who cares?

Excel to VB bug?

From my limited testing (I had to find a workaround pronto) seems to be some kind of bug when using VB6, ADO 2.7, ODBC text driver to read from an Excel spreadsheet. Recordsets with date fields come in with the dates encoded as integers. This is normally no problem, as you can use IsDate to check if it’s valid, then CDate to convert it to a date.

But what I’ve found is that IsDate has stopped working… that is, it’s stopped returning True for field values that can be converted to dates, eg CDate works. Whether this is something to do with the new year, or a new version of something on my machine, I haven’t yet figured out, but I ended up writing a wrapper IsDate function that just tries the CDate regardless, and returns a False (eg not a date) if it errors.

An initial dig in the MSKB found nothing about it, but I’ll do some more exhaustive digging and try to get a definite answer later. The answer being, I suspect, “Switch to VB.Net, you luddite!”

A few brief things

Some people aren’t so happy about Google suggest… certainly not Eric Rice, who gets his name listed with words like “child molestor”. Wouldn’t be delighted about that, myself. (via the G’Day World podcast)

New version for WordPress (minor fixes) (hopefully it fixes the thing where if you forget your password and need it mailed to you, it sends it in some incomprehensible encoding format that can’t be read… at least not on any web or Windows email client I have access to).

New version for Trillian (major new release). Haven’t had the chance to try it yet… no time Bellamy, no time.

Two Office snippets

I reckon it’s about time Word (and other Office products) figured out that those last few paragraph breaks at the ends of documents don’t need to be printed, especially if they cause an extra blank page to be wasted.

Yesterday I was trying to work out how to tell if an Excel cell has a value or a formula in it, so some conditional formatting could indicate if a value was calculated or entered by a human. Seems to be no built-in function to do it. But this seems to work, though I’m still trying to figure out how, precisely. The explanation at the bottom more-or-less explains it, but damn Excel 2003’s help, which has no index, doesn’t integrate its VBA help into VB6, seems to download everything (maybe it’s just the way I have it configured) and makes it near impossible to jump straight to the definition of GET and CELL.

Empty recordset from MDB queries via ADO

Every so often I’ll find the solution to an obscure issue, and wonder “why isn’t this in the MSKB? Why has nobody written an explanation I could find in Google?” Well through this site, now I can do something about that.

If you call an Access (Jet 4.0) query via ADO/MDAC, you may get empty recordsets back if the query uses parameters. There’s a bug that Microsoft reckons applies to RDO calls, but also appears to affect ADO/MDAC 2.7 in the same way.

To fix it you need to define the parameter size to an arbitrary amount before you set the parameter. Here’s some hopefully useful (to somebody, somewhere, some day) sample code…

(This is VB6. Dim everything beforehand of course, ‘cos everybody loves early-binding. What, you want to play with fire by using late-binding and no Option Explicit? Silly you.)

Set oCommand = New ADODB.Command
oCommand.ActiveConnection = gcDatabase.Connection
oCommand.CommandText = "select * from MyQuery"

Set oParam = New ADODB.Parameter
oParam.Size = 255 'Take this out and you get an empty recordset back, but no error. Wacky.
oParam.value = "your value"
oParam.Name = "your parameter name"
oParam.Type = adYourFieldType
oCommand.Parameters.Append oParam

Set oRS = New ADODB.Recordset
oRS.Open oCommand, adOpenForwardOnly, adLockReadOnly

(and close and destroy everything when you’re done, natch. You know what VB6’s garbage collection is like.)

WordPress siteurl/path bug

Today geekrant.org’s stylesheet was fading in and out of existance. Well, to be precise, the path to it got screwed up a bit, because somehow it thought it was in a directory called (deep breath):

http://www.geekrant.org/wp-login.php/wp-images/smilies/ wp-images/smilies/wp-images/smilies/wp-images/smilies/ wp-images/smilies/wp-images/smilies/wp-images/smilies/ wp-images/smilies/wp-images/smilies/wp-images/smilies/ wp-images/smilies/wp-images/smilies/wp-images/smilies/ wp-images/smilies/wp-images/smilies/wp-images/smilies/ wp-images/smilies/

rather than the much more succinct (and correct):

http://www.geekrant.org/

This appears to be caused by a bug in WordPress 1.21, where under some circumstances registered users go to login, and a particular browser/server configuration is present (looks like something to do with proxies) and it thinks the WordPress directory has moved, and tries to compensate. It’s detailed in the WordPress support forums, and if anybody’s having problems with it, the fix is to manually fix the siteurl setting in the wp_options table (it’s the first row) and to get into wp-login.php and comment out the two lines following

// If someone has moved WordPress let’s try to detect it

…because really, if someone’s moved it, they should have done it properly and updated the siteurl setting themselves.

See, not even WordPress is perfect. But it does have a strong user community, open source code that’s not too confusing to dabble in even for PHP-newbies like me, and a straightforward database structure holding all it’s stuff together. And that counts for a lot, I think.

Protect WordPress against comment spam

I was asked to go step-by-step through how to protect WordPress from the current rash of spam comment attacks, so here it is. It’s fairly easy to get them to go into the moderation queue, but it’s a pain having to continually clear it out.

The way the current attacks (hold ’em poker and so on) are working is to attack a file called wp-comments-post.php which does the grunt-work of posting comments into the database… if this isn’t there, they can’t do it.

So first rename wp-comments-post.php to something else. Doesn’t really matter what, as long as it doesn’t clask with anything else. eg xyz.php. (It’s not ever seen by users so it really could be called anything without confusing people, though you might want to avoid confusing yourself if you later can’t remember what it is.)

Then you need to edit the files that call xyz.php, which are:

  • wp-comments.php
  • wp-comments-popup.php
  • wp-comments-reply.php

Save all those files to your server, and make sure the original wp-comments-post.php file is deleted, and then you should be done. Post a comment yourself to make sure it works.

For now it seems to stop the spammers… no doubt in future they’ll figure out something more advanced (like scanning the <form> code to figure out the name of the post file), but it should stop them for a little while at least.

Service packs

Here’s Microsoft on why service packs are better than patches (as well as explaining their meanings for: Product family, Product, Version, Service pack, Patch).

They don’t really clarify Service Pack vs Service Release, claiming it’s the same thing, though at one stage it seemed that an SP is cumulative, whereas SRs often require you to install them consecutively to be up-to-date, eg Office 97 SR1, then SR2b.

This theory is broken with Visio 2000 SP2, which requires SR1 before you install it. Helpfully, SP2 is available for download, but SR1 isn’t! Brilliant! I have a vague feeling that vanilla Visio 2000 was never available for retail sale, but it’s certainly found its way into a few enterprises (such as where I work), so some people are bound to need SR1. But no. Obviously it was taking up too much valuable disk space on the Microsoft servers.

Visio and database creation

For quite a while I used Visio 2000 Enterprise Edition to design database schemas, and then have it create and update the tables. Admittedly the Visio 2000 interface is a little cumbersome for such things: it’s overzealous on its checking before you can update the database, and just try and delete a relationship without the sky falling on your head — it somehow thinks some kind of underlying link is still there, and if there’s anything wrong with it, it refuses to play ball. But when it behaves, it’s an excellent timesaver.

The other week I upgraded to Visio 2003 Professional Edition. Somewhere between 2000 and 2003 they’ve scrapped the Enterprise Edition, and although Microsoft don’t specify it in their literature comparing editions and versions, gone too is the database creation stuff. Apparently they’ve moved that functionality into Visual Studio Enterprise Architect — which Joel On Software describes as the super expensive “Enterprise Architect” edition at the top of the line that hardly anyone ever buys; it’s only there to make the other prices look reasonable by comparison. Great.

In Visio 2003 you can still draw database designs, or even generate them from existing databases, but there’s no way to create the DDL (SQL) for them, or update/create the databases themselves. I spent a couple of hours searching vainly through the Visio menus (the “Database” one is particularly deceptive) looking for such options, but couldn’t find them. I did find stuff pertaining to outputting a bunch of data describing my database diagram, but nothing would let me create the database I’d meticulously designed, or even print out a list of the fields and their types and sizes.

I have Visual Studio 2003, but for various reasons it’s the Professional Edition. So I couldn’t get my lovely design into the waiting and ready Oracle database. On the Visio 2003 Save As, it lets you choose “Visio 2002”. I wondered if by some fluke Visio 2000 would read a 2002 format. So I saved it, removed Visio 2003, installed Visio 2000 and tried to load it up.

Eureka, it worked. There was some further messing with it to get around a relationship on the database that was causing an error, and which I eventually decided I wanted to delete (an impossibility in Visio 2000 — see above) but eventually I got my DDL and indeed managed to create my glorious Oracle database.

But really, it shouldn’t be this hard.

Word options

Word 2003: Tools menuDumb things in Word 2003 that they should have fixed 3 versions ago, number 473: Not being able to get into the Options when you don’t have an open document.

Longer and longer URLs

The Age and SMH have joined News.com in embedding story names in their URLs.

So at The Age before it was
http://www.theage.com.au/articles/2004/11/01/1099262789668.html — now it’s
http://www.theage.com.au/news/National/TAB-locks-superglued/2004/11/02/1099262825340.html

News.com used to have stuff like
http://news.com.com/2100-1028_3-5435183.html — but now it’s
http://news.com.com/Fahrenheit+94711+expands+election-eve+pay-TV+airing/2100-1028_3-5435183.html

(Note how the slash in the News.com article screws-up the text embedded in the URL.)

It’s probably good for the search engines but it’s hopeless for passing URLs via email, as they now spill out over more than one line.

(If they want to maximise hits, what the Fairfax should prioritise is countering Google News’s opinion of The Age and SMH being subscriber only.)

PS. Thursday 8am. For those of us who want to quote SMH/Age URLs to people, you can still chop out all the embedded text, and replace it with “articles” so the example above becomes: http://www.theage.com.au/articles/2004/11/02/1099262825340.html