Welcome to the second installment of the “case study in bad design”-series, where I talk about generally horrible design in code, security or user experience. Todays subject is the PHP Generator for MySQL software by SQL Maestro (whose website will present you with a self-signed certificate for *.magicshoes.net if you try to access it via SSL, so you at least have to give them credit for creativity in that area).
PHP Generator for MySQL is a software that allows non-programmers to create web-frontends to their MySQL-Databases. It does a comparatively good job and provides some decent options, although the UI is somewhat cluttered and unintuitive, and the error reporting in places nonexistant. I was required to use it (as opposed to writing something myself) during my last employment with an institute at my university.
The story begins in July 2012, when I noticed that the code generated by PHP Generator had multiple vulnerabilities to Cross-Site scripting, allowing me to steal the login cookie (which, for good measure, contained the password in clear text, even if it was stored as a hash in the database). I cursed, wrote up some proof-of-concept code and reported the vulnerability to the devs.
A few weeks later, a new version of PHP Generator was released, fixing one of the two Cross-Site scripting holes I reported. They never responded to my mail and never fixed the second Cross-Site scripting hole. So, a year almost to the day later, I sent a follow-up mail, reminding them about the holes I reported, reporting another hole and setting a deadline of two weeks, after which I would apply for a CVE and publish the vulnerability. That got their attention and they responded within a day and got a new build out a few days later, fixing the vulnerabilities (and refusing to credit me in the changelog for reporting these issues, but hey, whatever).
A few days ago, I took another pass at the code and found another vulnerability (HTML stored in the database would be evaluated when displayed on the website), complaining that they were now using unsalted hashes of the password for authentication in the cookies (instead of session IDs completely unrelated to the password, which would be a better practice) and, after past experiences, setting a deadline of a week for a reply. Once again, they replied within a day.
Appearently, evaluating HTML from the database was a feature and not a bug. A feature that was on by default and could be disabled on a “per-input” basis. Whoever thought that was a good idea? Every “feature” that opens up the possibility for a security hole as big as stored XSS should either be completely removed or be off by default, to be enabled manually and with a big message box warning about the security implications. To make matters worse, the state of this setting seems to not be saved in the project file, leading to compatibility problems if the default value was changed (and I have no idea how they would make this state persist over restarts of the program if they save the setting nowhere…).
As for the proper session management, they claim to be working on something. They also may add salted hashes, but have not fully committed on that, citing possible compatibility issues.
They closed their mail with a statement that blew my mind:
By the way, we have never received any security related complaints from other PHP Generator users, so probably there is no real threat.
I’m not going to comment further on this statement, as anyone with at least a rudimentary understanding of security should be able to see what is wrong with this.
PHP Generator for MySQL starts at $99 for a single, non-commercial license without upgrades. I would think that you could expect more interest in the security of their customers for that.
I wished I had read this post before dishing out $149, It would have save this money and a lot of time wasted trying to make this product work.
I didn’t get to the security problems as it only took a couple of days to realize how BAD the performance of the generated code is. For one single page, the code repeats the same query 13 times, yup 13 times the same query, this is the code taken directly from the tool, and this is not even the query for the actual table, which is repeated twice.
So imagine you have a users table with a decent number of rows and for every page you request, your DB is hit 13 times. It’s like putting wooden wheels on your brand new car, you wouldn’t do that, would you?
Funny thing their answer is the same as the one you got:
” we have never received any performance-related complaint”
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
SELECT `reg_users`.`login` AS `login`, `reg_users`.`id` AS `id` FROM `reg_users` WHERE CAST(`reg_users`.`login` AS CHAR) = ‘root’
They can answer a simple question after 40 hours but when I email them about a XSS I found for the latest update, the replies stopped. Ran into a few issues and questions, no replies.
Does anyone here/anywhere know of an end-user forum that focuses on the products of SQL Maestro?
I’ve been an infrequent user for several years and have found them to be more responsive than some who have posted here before this.
What I’m interested in is other users who can/will compare experiences using their product(s) to be able to partially side-step a response I’ve received which is “RTFM”.
Their release 17.10 of the PHP Generator for MySQL seems to have “upped” their game relative to security, controls, etc. but I can’t speak to the XSS/cross-site stuff!