Category: mysql

Comma separated list of EU country codes

I’ve been doing some work with SQL this afternoon, and needed to query some data for EU countries. I couldn’t easily find a comma separated list of EU-28 2 digit country codes, so here is one for future reference:

'BE','BG', 'CZ', 'DK', 'DE', 'EE', 'IE', 'EL', 'ES', 'FR', 'HR', 'IT', 'CY', 'LV', 'LT', 'LU', 'HU', 'MT', 'NL', 'AT', 'PL', 'PT', 'RO', 'SI', 'SK', 'FI', 'SE', 'UK'

For VAT purposes, the United Kingdom is referred to by GB instead of UK, so you may need this list instead:

'BE','BG', 'CZ', 'DK', 'DE', 'EE', 'IE', 'EL', 'ES', 'FR', 'GB', 'HR', 'IT', 'CY', 'LV', 'LT', 'LU', 'HU', 'MT', 'NL', 'AT', 'PL', 'PT', 'RO', 'SI', 'SK', 'FI', 'SE'

This list was derived from the EU’s Interinstitutional style guide and UK Gov’s VAT EU country codes. These codes are a sub-set of ISO 3166-1 alpha-2 codes, with a few exceptions for UK and Greece.

How to stop Mysql ASCII tables / column separators from being lost when redirecting bash output

Today I needed to write a quick bash script to send a monthly report  to a colleague.  The report required running a few MySQL queries and then concatenating the output into a file,  and e-mailing it to them.  Normally when you run a MySQL query from the command line, the output is shown within a handy ASCII table.

# mysql --table -e "SELECT 1+1";
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

Unfortunately,  it seems that when you redirect the output to a file,  the ASCII formatting is lost…

# mysql -e "SELECT 1+1" > /tmp/test
# cat /tmp/test
1+1
2

It took me a while to find it, but the solution is really simple. Simply add the –table parameter to the MySQL command:

# mysql --table -e "SELECT 1+1" > /tmp/test
# cat /tmp/test
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

And that’s it! Hopefully this post will save someone else some time in the future.

MySQL one database versus multiple databases

When designing an application – should you put each customer in a separate database or keep them all in one large central database?

When it came to start work on our new Clear Books accounting software last year, we had to make a fundamental database design choice between using a single database for all clients, or having a separate database for each one.

Since there was going to be a fairly large amount of data for each client, we decided to go for the latter, and one year on – I am very happy with our decision.

Advantages

  • Easier to balance load. Since each client is in a separate database, it is relatively easy to move them between database servers.
  • Faster. One client’s tables being locked won’t affect another clients. Indexes are smaller. If MySQL has to do a row scan (let’s hope it doesn’t!), it’ll be faster.
  • MyISAM rsync backups will be faster.
  • Easier to clone a clients database for testing purposes (just simply copy the whole db, no need to write a special script to extract their data).

Disadvantages

  • Upgrading the database is more complicated, as you have to add new fields to each client database, rather than just once globally. If you are successful you could be dealing with thousands of databases, and so an upgrade script is definitely needed. However, this is also a blessing in disguise as it allows you to test upgrades on a small set of databases before rolling them out generally.
  • More complicated to implement in general.

I think the ultimate decision would come down to how much data your store for each client. If it’s a fair amount, then I think separate databases is worth the extra development effort.

PHP Web Application Security

Here are some tips to help you think more about security when developing a web app. 

  1. Buy a good book on the subject, such as Securing PHP Web Applications and implement what you learn in your code.
  2. Read through Web Application Security section on of the SANS Institute 2007 top 20 security risks. There are some useful tips on securing PHP in particular. 
    • Check PHP configuration settings:
      • Turn register_globals off, use super globals such as $_GET instead (from PHP 4.2.0 this is the default).
      • Turn allow_url_fopen off (unless you really need it).
      • Disable magic_quotes.
      • Configure open_basedir for each site to restrict access from PHP scripts to certain directories.
      • Consider running PHP with FastCGi instead of mod_php
    • Use best practices when developing:
      • ALWAYS validate user input! This is probably the most important point in the entire list. There are many nasty bots and spiders going round the web trying to break into your site, and the most common way in is through your web forms. There are various validation libraries out there to make your life easier  (e.g. PEAR Validate, Zend Filter Input)- use them!
      • Avoid SQL injections. If you validate user input correctly, then this should help you avoid SQL injection vulnerabilities. To be doubly safe you could use a database abstraction layer, that if used correctly with prepare statements, will automatically escape user input data. Check out PDO and Zend DB.
      • Avoid XSS attacks. An XSS attack is where malicious users are able to inject their own code in to pages on your site that may be viewed by other users. You could strip tags from user input, and encode html entities in any plain text being output.
      • Don’t transmit passwords and other secret information over plain text, submit to a secure URL.
      • Be careful when allowing uploads. Check the file types, and only allow files you expect. Resample uploaded images in case there is any hidden code inside.
      • Use sessions instead of cookies, unless you really need the persistence of a cookie. Sessions are temporary and keep everything except the session ID hidden from the user’s machine.
      • Peer review your code. Get another developer to look through it, two heads are better than one!
  3. Download the Wapiti and Grendel Scan web application vulnerability scanning tools and run them on your sites.

This is of course an overly simple list, and it can’t protect against things like logic flaws, but at least – if you were wondering where to start then I hope it will give you some useful inspiration!

A script to reset the MySQL root password

It’s a pain if you ever forget your MySQL root password. Fortunately it’s a fairly straightforward process to reset it, here’s how:

pkill -9 mysqld;
echo "UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;" > /tmp/reset-pass.sql
mysqld_safe --init-file=/tmp/reset-pass.sql &
sleep 10
pkill -9 mysqld;

A bash script to reset the mysql root password

To make the process easier, I’ve wrapped these commands up in a script and put it on our open source respository here:

mysql_reset_root_password.sh script

Update: This script has been improved with Andy’s suggestion in the comments,  which is a simpler and more secure method.

killall -15 mysqld
read -s -p 'Enter a new root password: ' MYSQL_ROOT_PASSWORD
echo "UPDATE mysql.user SET Password=PASSWORD('$MYSQL_ROOT_PASSWORD') WHERE User='root';" | mysqld --bootstrap

Getting Apache, PHP and MySQL to work on Leopard

Enabling PHP

If you have upgraded from Tiger, you may need to complete this step to get Leopard’s version of Apache to work with UserDir (otherwise ignore this bit):

  • Copy your old userdir configurations from /etc/httpd/users/ to /etc/apache2/users/

PHP is normally disabled by the default Apache configuration, so you will need to load up a text editor and then:

  • Uncomment LoadModule php5_module libexec/apache2/libphp5.so in /etc/apache2/httpd.conf

Once you’ve saved this, you’ll need to restart apache. The easiest way to do this is to go to System Preferences > Sharing and to turn Web sharing off then on again.

Installing MySQL

To install MySQL, just follow these steps:

  • Download the MySQL OS X disk image from a MySQL mirror
  • Mount the image, and install the main package, the startup item package, and then the preferences pane
  • Add the mysql bin folder to your path: 
    sudo echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bash_profile
  • If no /private/etc/php.ini exists, make a copy of php.ini.default
  • Update php.ini to point at the new MySQL socket mysql.default_socket = /private/tmp/mysql.sock – do this for both mysql and mysqli. 
    mysql.default_socket = /private/tmp/mysql.sock
mysqli.default_socket = /private/tmp/mysql.sock

Other tips if you having problems with .htaccess

Ensure the following directives are enabled (e.g. look in /etc/apache2/users/username.conf) :

Options FollowSymLinks
AllowOverride All

Hosting multiple blogs on a single WordPress installation

As you can probably tell from this blog, WordPress is my favourite blogging tool. I’ve been using it for a couple of years now, and during that time I’ve been really impressed by it. So when I was recently asked if a single copy of WordPress could be used to power several blogs, I was optimistic that it would be up to the job.

And it didn’t disappoint me…

There are a number of ways you can host multiple blogs with WordPress:

  • WordPress MU (multi-user) – This is system that powers all blogs at wordpress.com, Le Monde, Harvard Univeristy etc…
  • Batch management of blogs with WP-Create and WP-Upgrade – These scripts let you install multiple blogs in parallel, however each one would get it’s own installation.
  • Modifying wp-config.php to choose a different database per hostname. This uses the standard wordpress scripts.

Since I wanted to use a standard wordpress installation, and I didn’t want to install it multiple times, I chose the 3rd option. WordPress stores most of it’s configuration in it’s database, so all you need to do is modify wp-config.php to point at a different database depending on the hostname of the site being accessed:

// Ignore the www. part of a hostname
$host = eregi_replace('^www\.', '', $_SERVER['HTTP_HOST']);

switch ($host) {
        case 'site1.co.uk';
                $db = 'site1';
                break;
        case 'site2.co.uk';
                $db = 'site2';
                break;
        default:
                header("HTTP/1.0 404 Not Found");
                exit();
                break;
}

// ** MySQL settings ** //
define('DB_NAME', $db);    // The name of the database
define('DB_USER', 'user');     
define('DB_PASSWORD', 'password'); 
define('DB_HOST', 'hostname');

Simply add the existing database settings code with the code above, and then create a new empty database for each blog you want to host. You will need to run the install scripts for each blog, e.g. http://www.site1.co.uk/blog/wp-admin/install.php

References:

How to remove duplicate data with MySQL

Quite often I have found myself wanting to remove some duplicate data from a MySQL table without having to 1) write a script to de-dupe the table or 2) copy the data into a new table with unique indexes.

Well fortunately there is a quick (and dirty?) solution. Say for example you wanted to get rid of duplicate e-mail addresses in a table, then you could do the following:

 ALTER IGNORE TABLE customer ADD UNIQUE (email);

By using the IGNORE keyword, it changes the way ALTER TABLE works so that only the first row with a particular e-mail address is kept, further rows containing that e-mail address are simply dropped.