SQL query to automate switching WordPress from a development to a live server

Posted on October 1, 2010

One of the more tedious things about WordPress is the way that it hardcodes complete fully qualified urls into the database, which makes quickly switching from a development server to a live server an irritation. It only takes a few SQL queries to sort it out, but I’m forever forgetting then exactly. The SQL below lets you define a few constants that then get feed into the UPDATE scripts. I usually develop initially on a local web server with a .dev TLD, so with this little script I just have to set 3 SQL variables, run it and job done ready to export the database to the live server. Which is nice.

START TRANSACTION;

# CONFIGURE THESE VARIABLES FIRST
SET @DOMAIN_NAME = "the-domain-in-question";
SET @DEV_SERVER_SUFFIX = ".dev";
SET @LIVE_SERVER_SUFFIX = ".com";

# CONSTANTS (IF YOUR DEV SERVER ISN'T  MAPPED BY SUFFIX, CONFIGURE AS APPROPRIATE)
SET @WEB_SERVER_SUFFIX = "http://www.";
SET @DEV_SERVER = CONCAT ( @DOMAIN_NAME, @DEV_SERVER_SUFFIX );
SET @LIVE_SERVER = CONCAT ( @DOMAIN_NAME, @LIVE_SERVER_SUFFIX );
SET @WEB_DEV_SERVER = CONCAT ( @WEB_SERVER_SUFFIX, @DOMAIN_NAME, @DEV_SERVER_SUFFIX );
SET @WEB_LIVE_SERVER = CONCAT ( @WEB_SERVER_SUFFIX, @DOMAIN_NAME, @LIVE_SERVER_SUFFIX );

# UPDATE THE SERVER NAME
UPDATE `wp_options` SET `option_value` = @WEB_LIVE_SERVER WHERE `option_value` = @WEB_DEV_SERVER;

#UPDATE THE GUID urls
UPDATE wp_posts SET guid = REPLACE(guid, @WEB_DEV_SERVER, @WEB_LIVE_SERVER);

#UPDATE and urls referenced in the post content
UPDATE wp_posts SET post_content = REPLACE(post_content, @DEV_SERVER, @LIVE_SERVER)
WHERE post_content LIKE (
CONCAT( "%",@DEV_SERVER, "%")
);

COMMIT;
Posted in Web Development, Wordpress | Tagged , , , | Comments Off on SQL query to automate switching WordPress from a development to a live server

Why OS X sucks as a web development platform

Posted on October 1, 2010

So I thought it was time to end the external summer that is the Apple love in here at Sweet-Apple, and be a little bit controversial.

As a whole, OS X makes an excellent web development platform – it’s got some great unique applications like Textmate and Coda, plus the usual platform agnostic suspects such as DreamWeaver, Eclipse and Netbeans. But writing code is only half of the equation. You also have to able to execute and test your code. And for this freelance web developer sitting in rainy Bradford on Avon, that means one thing. Apache and PHP.

For more years than I care to mention, I’ve built my own LAMP stack on OS X, downloading, installing and configuring Apache, MYSQL, PHP and Webmin. Every time there’s a major OS “point” release from 10.3, 10.4, 10.5 and 10.6 I’ve had to rebuild that stack, because what Apple supply out of the box leaves much to desired.

Snow Leopard comes with PHP5.3 and Apache2 preinstalled, which is great. Unfortuantely they are missing a number of vital PHP extensions for your average LAMP developer. Firstly, no XDebug. I know a lot of people are still printing debug code to screen, and I’m guilty myself, but sometimes you just need to be able to fire up a debugger and step through your code. Secondly, no mcrypt. I use Magento for eCommerce development and it requires mcrypt to run. Thirdly there’s no APC or memcache. Now I know that PHP6 will have APC rolled into the core, but for now I want to be able to get some real performance benchmarks when testing application performance, and APC can help boost even the most complex application. Lastly, no ImageMagick. I prefer it to GD as ImageMagick seems to handle large images much more gracefully.

And then we get to the really big one. OS X doesn’t really work well when you use the Case-sensitive HFS+ file system. Some critical applications like Creative Suite don’t even install! When is this important? Because every darn webserver out on the internet is Case-senstive. Many is the time I’ve moved files onto the live web server and found a few niggly problems because of uppercase/lowercase mismatches.

So here’s a little plea to Apple. Next time you’re planning a “point” release to 10.7, how about having a little think about anyone doing web development. If you can give us a build that out of the box has everything needed for LAMP development, you’ll bring over a lot of converts currently toiling away on Windows boxes…

Posted in Apple, Magento, Web Development | Tagged , , , , | 2 Comments

30% off dirt cheap Airport Express, Airport Extreme and Time Capsule

Posted on September 28, 2010

Currently the Apple Refurb Store is selling a large number of Apple wireless devices at very big discounts. So if you want to be able to stream music to your hi-fi through an Airport Express, or wirelessly backup multiple Macs via Time Machine to a Time Capsule, it’s a good time to get one at a huge discount.

  1. Airport Express – £55 inc VAT and delivery. Save 32%
  2. Airport Extreme with Gigabit ethernet – £79 inc VAT and delivery. Save 35%
  3. Time Capsule 1GB first generation – £159 inc VAT and delivery. Save 50%
  4. Refurbished Time Capsule – 1TB current generation – £169 inc VAT and delivery. Save 27%

I’ve always felt that Apple charge too high a premium for their wireless devices, unless you need one of their very specific features. The Airport Express features a USB printer server and crucially an audio out port to let you stream music from your computer to your hi-fi. The Airport Extreme I always felt was the weakest part of the lineup, as it’s little more than a bog standard wireless router, but it does have the USB print and disk sharing capabilities (which can be very handy when combined with “Back to My Mac” feature of .Mac to let you access your files remotely). The Time Capsule if just cool and very handy if you run multi Macs in your home or office. But remember, it should just be used as a backup device, as I’m not comfortable with people using it as a Fire Share as well. They tend to forget that the Time Capsule isn’t itself backed up anywhere.

Need advice on extending wireless network, backup strategies for OS X, or remote access to your files? Why not get in touch?

Posted in Apple, Refurbished | Tagged , , | Comments Off on 30% off dirt cheap Airport Express, Airport Extreme and Time Capsule

Where to get help when WordPress gets hacked or you can’t edit content

Posted on September 27, 2010

I’ve just got off the phone from a customer in Bath who’s suddenly become unable to edit their WordPress powered website and found my details via Google (it’s nice to know when some of your Search Engine Optimisation goals are starting to generate meaningful traffic). I expect the articles on WordPress security peaked their interest…

Like any good web developer, my first port of call was a quick dive into the html source code. And sure enough I found the dreaded meta generator tag announcing to the world their site was running an outdated version of WordPress. Now there are plenty of other ways of finding insecure installations of WordPress, and I’ve never been a big fan of the “Security through obscurity” school of thought, but it doesn’t help to announce the fact you aren’t maintaining your site as assiduously as you should. WordPress should disable this feature by default IMHO. Scanning further down the html source I saw hundreds of links injected underneath the closing </html> tag, wrapped in some css to be hidden from human viewers. So it’s clear, the site’s been hacked, but what should you do now?

The first thing to do is get an immediate dump of both the database and the ftp server contents the site is hosted on. Once you have that in place, it’s time to get a little forensic. A little Googling for the symptoms you are experiencing never hurts. The next step is to try to sanitise the files from the FTP server. However this gets tricky if you don’t have a pristine copy of the Theme files that were used to create the site. If you do, you can use a tool like diff to check for anomalies between the source files and the files from the hacked website.

Then I’d start to look through the database for external links or other such unusual content in the wp_posts table. You can often achieve this more quickly if you have a text editor and a mysql dump file. If the database has been hacked, you’ve got to be meticulous in cleaning it out. Remember that the hacker may well have either created a new login user, so don’t forget to delete all the users and create new ones, with new passwords.

I’d then install that database on a local web server that isn’t accessible from the internet and setup a clean installation of WordPress on a local webserver. Run the WordPress update wizard. Then download clean versions of all the plugins that have been used in your site and install them.

Next step is to install the custom theme and thoroughly check it for problems with the patched version of WordPress. Harden the theme and installation as much as you see fit.

The last step is to reset all your FTP, MYSQL and WordPress passwords and run a thorough check on your computer for viruses, trojans or other malware.  With WordPress it’s most likely they bruteforced their way in, but you could have a keystroke or ftp logger sending your details to some nefarious hackers…

Finally, once you’re happy that you have as secure an install as possible, upload everything and push it live. Then monitor the site carefully for a few days or weeks. Look in the server logs for visits to unusual pages or strange querystrings in urls.

WordPress is unfortunately a target for hackers, simply because it provides such a large attack surface, with millions of installs and thousands of inexperienced users. If you keep youR installation patched you should avoid most problems, unless you’re really unlucky and get hit during a “zero-day” attack.

If your business or website depends on WordPress and has been hacked, but you don’t understand how to sanitise it, why not give us a call and we can help you with your WordPress security issues.

Posted in Web Development, Wordpress | Tagged , , , | Comments Off on Where to get help when WordPress gets hacked or you can’t edit content

Search Engine Optimisation is more than keyword stuffing

Posted on September 21, 2010

I’ve recently started preparing a Search Engine Optimisation report for a client in Bath,and have been trawling through the mass of information that this entails. Whilst there are a huge and changeable number of factors that can affect your website’s position in the search engines results, one thing that really won’t help, whatever you have been told, is stuffing keywords into every available nook and cranny. Screaming <insert your selected keyword here /> repeatedly is more likely to get your pages penalised than praised. Not only that, but the copy will then be a dreadful read to any human that gets eyeballs on it.

Choose your keywords carefully, use them sparingly in 3 or 4 word phrases, by all means get them in your title elements and link copy, but don’t overdo it.

Posted in Search Engine Optimization, Web Development | Tagged , , | Comments Off on Search Engine Optimisation is more than keyword stuffing