03/10/2010

Worst web application database design I’ve ever seen

Lately I was given a task of moving some websites/webservices from real boxes to some VMs. Most of the sites were Joomla! applications so moving the installation was quite easy, tar files, check configuration.php for db username/pass/etc and dump the database on the old server and then copy these to the VM. Restore files, import database, minor path changes to configuration.php… that’s about it.

But then it was time to move an “eclass” application. Specifically it was an installation of Open eClass, a web based e-learning software created by Greek Academic Network. So I copied the files, found the configuration file with database credentials, dumped the db and moved it to the VM. The site came up but it was not functioning properly. Course material was missing from the website, but I could certainly see the files on the file system. I dumped the database again and re-imported it. Nothing, the site refused to work as expected. I went back to the original machine and shut down mysql to start it with “–skip-grant-tables” since I didn’t have the root mysql password. MySQL came up, I logged in as root and I typed: “show databases;”

Oh the horror!!!!
I couldn’t believe my eyes…in front of me there were more than 200 databases with the names of courses of the e-elearning platform! I shut down mysqld and restarted it normally. Then I logged in as the “eclass” user and issued the following:
show grants for eclass@localhost;
The output:

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'eclass'@'localhost' IDENTIFIED BY PASSWORD 'XX' | 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES ON `eclassdb`.* TO 'eclass'@'localhost'  |

I immediately started thinking that someone had _really_ fucked up the installation. I went to Open eClass website and tried to search for documentation on installation instructions. I downloaded a pdf and I read between the installation instructions:

A “username” and a “password” for MySQL with database creation rights.

.
Okie..let’s translate that to simple english, it needs a ‘root’ mysql account renamed to something else.

I am not a web developer, I do not even consider myself a developer, but this setup makes no sense for me. Who and why decided that it would be a good idea to have a web application’s mysql user being able to create new databases ? Is this application only to be installed on a machine of its own ? If so, it’s such a waste of resources. I can understand the complexity and the extra time that a well designed and correctly normalized database requires, but this isn’t an excuse when creating software to be distributed and widely used by lots of people, especially universities. I can’t judge the application, it actually looks quite useful, but it’s setup certainly has design problems that need to be solved.

And finally, what “if” there is some security hole in the application (sql injections anyone?) and a malicious user starts dropping databases other than the ones belonging to eclass ? Who’s to blame for that ?

My advice to anyone running this application is to have it as isolated as possible from the rest of his infrastructure. Possibly in a virtual machine of its own. And there should be a warning about it on the website.

P.S. Looking at the credits, it seems that I know in person some of its developers, and that makes it ever harder to blog about what I faced. I’ll certainly ask them about this web application the next time I meet them though.

* Filed by kargig at 11:05 under Internet,Linux
Tags: , , , , , , , , ,
* 7 Comments | 16,070 views