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: database, design, eclass, mysql, openeclass, security, sql injection, virtual machine, web application, website
7 Comments | 16,043 views
As a student, I’ve been a user of eClass for years. It is used widely at the University of Athens to manage courses. I think I can offer a possible explanation for the database setup in the software. EClass is a fork of an older version of Claroline. Claroline used to be capable capable of managing a single course per installation. eClass developers had to find a way to extend the software so it could support multiple courses under the same installation — and it seems that they didn’t have much time to work on that, thus the inelegant compromise they came up with. Again, this is just me speculating. I haven’t been exposed to the actual Open eClass codebase.
Another ripoff, this time from Claroline. What is wrong with the greek developing community they don’t seem to get something right or make something new. This reminds me of another “greek software” http://www.elxis.org/ a blatantly stolen joomla.
[…] This post was mentioned on Twitter by George K., argp. argp said: RT @kargig: Worst web application database design I’ve ever seen http://ff.im/-rttYp […]
1. Yeah, people do mess up and sometimes quite badly, especially when they learn things by themselves. However, going public before having a chat with them is a new big mess up.
2. I saw ‘Elxis’ for the first time today. As far as I understand, it follows what the GPL license dictates which makes it fine. You can download the source and all. What’s the problem then?
3. You have lots of trackback spam. You should clean it up.
I just wanted to notify the author that a comment I posted here earlier today was caught by the spam filter.
@Dinos: This is open source we are talking about, where forking is generally not seen as equivalent to ripping off and/or stealing. For eClass, it made sense to fork rather than start from scratch. I don’t see how this reflects badly on the greek developer scene.
@simos: I am not doing any kind of official review or bug report here. This software is supposedly “production quality”, I don’t even think that the design is under consideration any more, so “chatting” with them would have probably not changed anything. That’s the way the software is meant to work I guess. I also can’t see a way that this blog post can lead to a new big mess up as you say, it’s just some thoughts from a sysadmin point of view 🙂
oh and I’ve “fixed” trackbacks…thanks.
@Simos: For starters,since the code is supposed to be “production quality”, kargig does the right think by calling them out, I have seen more than enough bad software out there, where most time it falls into the pattern of “COPY-PASTE-COPY-PASTE-FIND_AND_REPLACE_CREDITS-RELEASE”
Kargig spotted the bad design, he can pretty much do whatever he wants with it, remember the good-ole-days of “Full Disclosure”? What kargig wrote is more than valid (since I do not see you calling his reasoning invalid), so why all the big fuss for “going public”?
My quick 0.02 Euro