James Titcumb

Say you have a table of information, and another table that has more information, there’s a clever way of getting data from, one, the other, or both. It’s nothing new really at all, more of a reference thing for me… Say you have two tables:

common_table:

uncommon_table:

Joining these is a standard operation, and if we wanted all records, that may or may not have a postcode, we’d:

SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id

And that would give us:

Note carefully the fact that the uncommon_table values in the second row are NULL. We can use this to distinguish which records have entries in the uncommon_table, and which don’t. Then we can form queries based on this, for example if you wanted a report of each user with their postcode, ignoring those without postcodes:

SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id WHERE u.id IS NOT NULL

And converseley, find those people who haven’t got a postcode entry:

SELECT * FROM common_table c LEFT JOIN uncommon_table u ON u.customer_id = c.id WHERE u.id IS NULL

Knowing this can be handy, and sometimes quite powerful.

I’ve always used fancy tools like PhpMyAdmin to create users in mysql etc. as I don’t normally remember the syntax.

Here it is:

CREATE USER 'username'@'hostname'
     IDENTIFIED BY 'password';
GRANT USAGE ON * . * TO 'username'@'hostname'
     IDENTIFIED BY 'password'
     WITH
          MAX_QUERIES_PER_HOUR 0
          MAX_CONNECTIONS_PER_HOUR 0
          MAX_UPDATES_PER_HOUR 0
          MAX_USER_CONNECTIONS 0 ;

For each database you want the user to access, do:

GRANT ALL PRIVILEGES ON `databasename` . * TO 'username'@'hostname';

This basically means: create a user, with password “password”, don’t allow them to access anything except USAGE. Then, allow them to do anything they want in database “databasename”.

At lunch today I had a look at AES (aka Rijndael) to tighten up security in my PHP and MySQL applications at home today. Although very slightly more complex than simple MD5 or SHA1 encryption of passwords, AES does provide the ability to decrypt passwords. As of yet (as far as I can tell), AES also has not been hacked yet, and uses a “key” to unlock or lock the password. In plain old MySQL this is pretty simple:

INSERT INTO table_name (username, password) VALUES('james',AES_ENCRYPT('some_password','some_key'));

Hey presto - that generates a nice encrypted password. The password field in the database must be a BLOB, unlike MD5 or SHA1 where it could be a VARCHAR(32) or a VARCHAR(40). To reverse the encrpytion in MySQL is just as simple:

SELECT username, AES_DECRYPT(password,'some_key') FROM table_name;

I expect this will be just as simple using PHP’s mcrypt cryptography, but I have not yet looked into this… add to my todo list!

So I started thinking a bit more last night and I’ve started various musings now about the structure of my new home server. I’ve decided it’s going to be virtual IP-based at least (although not 100% decided!), and I know that I’ll want the DNS server to be updated etc. I’m going to write the control panel in a similar fashion to ISPConfig, the util that I currently use, but I’m going to personalise it especialy for my system. Once it’s set up and running, then I can erase the old server, and set it up as a slave server. Somewhere between doing all that, I’m going to get the RAID array working with the hotswap bay I have, which hasn’t been used in yonks. I’ll also split as equally as possible the hard drives between the two, and setup some kind of rsync for the data, and also some kind of MySQL cloning. There’s a lot of prep work and planning to do to make sure I do this right, so I’m going to start planning this weekend.

Read the rest of this entry »

I’ve started working on a little secret project in PHP at home, just for fun really, but it might take off (but, like anything I do probably won’t go very far…). With it, I’ve created a static PHP class for connecting/querying a MySQL database. At the moment, it’s extremely extremely simple, but here it is anyway. I’ll update it when it becomes a bit more complex, but it’s here mainly as a resource for myself, but anyone is welcome to use it as long as they keep the notice in tact at the top!

DBManager static object