Books vs. e-Books for DBA's

As most people still do I learned to read using books. WhooHoo!

Books are nice. Besides reading them they are also a nice decoration on your shelf. There is a brilliant TED talk by Chip Kidd on this subject.

But sometimes books have drawbacks. This is where I have to start the comparison with vinyl records (Yes, you're still reading a database oriented blog). Vinyl records look nice and are still being sold and yes I also still use them. The drawback is that car dealers start to look puzzeled if you ask them if your new multimedia system in your car is able to play your old Led Zeppelin records. The market for portable record players is small, and that's for a good reason.

The problem with books about databases is that they get old very soon. The MySQL 5.1 Cluster Certification Study Guide was printed by lulu.com which made it possible to quickly update the material. This made sure that the material wasn't outdated when you bought it.

I like to use books as reference material, but I tend to use Google more often and the books stay on the bookshelf and are getting old and dusty. One of the reasons for this is that taking books with me just for reference is not an option judging by the weight of it.

At Percona Live UK I got a voucher from O'reilly to get a free e-Book. So I chose 'SQL and Relational Theory'. I started to read it on my laptop with FBreader and on my iPhone using Stanza. Both my phone and laptop are not really made for reading. So I bought an Sony Reader, which is made for reading.

Reading 'SQL and Relational Theory' on the Sony Reader is nice. The only annoyance is that the examples are like this:

SELECT COUNT(*) | SELECT COUNT(col1)

FROM tbl1       | FROM tbl1

And with line wrapping it looks like this:

SELECT COUNT(*) | 

SELECT COUNT(col1)

FROM tbl1       | 

FROM tbl1

Which is not very readable.

The book is very theoretical as you might expect, but nonetheless it's a very good read.

The Sony Reader is not very suitable for reading whitepapers in PDF format  as most whitepapers are in A4 or Letter format which is too big for the device. Of course software like Calibre can covert some of those.

(Oracle, Percona, others… please also publish your whitepapers in a format more suitable for an eReader)

The device itself is very nice. The battery time and e-Ink display are good (especially if you compare them with an tablet).

Unfortunately it doesn't increase my reading speed and it doesn't give me more time to read.

I'm looking forward to read some other database books in e-Book format.  I think the next one on my list is High Performance MySQL.

I planned to publish this post when I finished reading SQL and Relation Theory, but I thought now might be a better time as O'Reilly has a discount for that book and other books by C.J. Date.

The Sony Reader runs a modified Android (yes it's possible to root it to play angry birds on it). It also has a webbrowser, but itsn't well suited for reading Planet MySQL or Planet MariaDB. Using the webbrowser to download the MP3 for the OurSQL Podcast and then playing it does work flawlessly. I tried to download the EPUB file for the MySQL Reference Manual, but that failed so I used USB for that.

SQL Injections, Again…

Last Friday the Dutch TV program Zembla aired part two of the "verzuimpolitie" series. The first part was mainly about how employers could access medical information about employees. There is a news article about the second part here (with google translate).

The second part is about the security of the IT system which is used to record medical information about employees. They give this information to the company to which the company they're working for is outsourcing everything related to workplace absenteeism.

After the first part of the series some viewer reported that the website contained SQL injections. The creators of the program verified this and tried to report it to VCD (The company which offers the software as a service). Then VCD called to police to remove them from the VCD office.


Then Zembla contacted the Radboud University and asked them to assist with this issue. The University verified the SQL Injection and confirmed that this was a serious security flaw. Then a VCD executive told Zembla that there wasn't a SQL Injection, someone just stole the passwords. This is strange because VCD reported to the University that they recorded a SQL Injection attack by the University.


The users of the VCD Humannet software were not informed. And when some of the companies using this SaaS service became aware of the security incident it took a lot of effort before the service was temporarily shutdown to prevent further harm.


This whole story reminded me of the situation around Comodo and DigiNotar. Comodo was hacked, stopped the issuing process, reported the issue and fixed it. Then DigiNotar was hacked, did not stop the issuing process. It also didn't report the issue. Then they became bankrupt.


The lessons learned for SQL Injections for DBA's and Application Developers:
1. Input validation. This is obvious.

2. Use prepared statements if possible.

3. Prepare for a security incident: make it easy to disable applications or parts of applications.
If all client companies are in the same database then it's very hard to shutdown the application for just one company. Using one database instance per client company might be a solution.


4. Use isolation
If there are 10 client companies and they all use different databases  as separation, then you should also use 10 application users with the correct permissions. Then a SQL injection for one customer won't affect other customers.


5. Use a database firewall.
This is not very common yet. You could use GreenSQL or McAfee (partly opensource). There are more solutions available, but these are at least partly opensource.

6. Use two factor authentication if dealing with sensitive data.
You don't have to buy expensive tokens. There are enough free or almost free solutions available. Yubikey is a possible solution.

7. Do not store passwords, store hashes.



8. Use encryption an function like AES_ENCRYPT() to encrypt sensitive data.
This could guard your data from 'curious' DBA's and other administrative users.
 Do not use a hardcoded password for this! Make sure that the AES_ENCRYPT doesn't end up in your binlogs, use a variable! And only use TLS secured connections. It might be better to encrypt the data in the application instead of in the database. It could even be possible to use client side encryption to encrypt the data in the browser.

9. Remove old authentication methods, login screens, etc.


The lessons learned for SQL Injections for management:
1. Security scans are mandatory. Companies like Madison Gurkha and Fox IT can offer this.

2. Don't only inclue your own services in security scans, but also the external services you use.

3. Make sure that there is a security breach notification requirement in the contracts for security sensitive services.

4. Make it easy to report security incidents.

5. Do shutdown the service if needed for security.

6. Do inform your customers about the security incident.

Backup your sandbox with XtraBackup

Today I tried to make incremental backups of a MariaDB instance in a MySQL sandbox with Percona XtraBackup.
I used the recently released XtraBackup 2.0. And of course there is documentation about making incremental backups. 

MySQL sandbox makes it easy to run many different MySQL versions on one machine. It does this by changing the port number, data directory, UNIX socket location and a whole lot more.

So I first started with a full backup and after that I used that backup as a base for the incremental backups. To do that I had to specify the port number which is 5522 and the username and password for the msandbox account. As MySQL uses a UNIX socket instead of a TCP connection if the hostname is localhost I specified 127.0.0.1 as hostname to force a TCP connection. That worked!

Then I created the incremental backup by using the --incremental option and the --incremental-basedir option to specify the location of the full backup. That also worked!

Then I tried to make a backup while putting some load on my database. I did use  "INSERT INTO test1(col1) SELECT col1 FROM test1" to do this.

The full and incremental backups still worked, or at least that's what the backup script told me. But the size of the incremental backups was quite small. And I noticed that the LSN was very small and not increasing. The xtrabackup_checkpoints file also told me that the backups where all for exactly the same LSN. As the LSN is only for InnoDB, I verified the table type for my test tables. And my test tables were in fact InnoDB. A "SHOW ENGINE INNODB STATUS\G" told me that the LSN was in fact increasing.

It turned out that XtraBackup was making backups of /var/lib/mysql instead of ~/sandboxes/msb_5_5_22-mariadb/data/. Adding "--defaults-file=~/sandboxes/msb_5_5_22-mariadb/my.sandbox.cnf" to the innobackupex command did correct this.

After specifying the correct config file I did try to make backups under load again. It failed due to the logfiles being too small. So I stopped the database, removed the ib_logfile's and started the database with a larger InnoDB logfile size.

Then It all worked flawlessly!

So you should make sure that your backup completes without errors AND that your backups is from the right database. Of course testing restores regularly would also detect this.

MySQL DoS

There is a nice demo of  MySQL Bug 13510739 on Eric Romang's blog

I've published this blog to make this content available on planet.mysql.com.