All this to find a stupid crossword game?!
-
I used to work for a company that ran an online portal for casual downloadable games. The site had been developed a few years previous by a young overseas "computer science student", and it showed in the code used to run it - your standard hacky, rambling, obtuse PHP.
One day I was asked to fix a problem with the search function. Say a visitor wanted to search for a crossword game smaller than 10 megs in size. I figured that the code would be the simplest, most naive thing imaginable, something like:
SELECT * from games WHERE filesize < 10 AND title LIKE '%crossword%';
Which I could live with. Maybe after I fixed the problem I would go back and fix the MySQL injection vulnerability, but that wouldn't be a big deal.
Then I actually looked at the search code...
Without going into all the gory details, here's most of the logic behind the search query that was run every time someone was looking for games.
$strwhere = getSql($category, $title);
$strsql = "SELECT title, imageicons, webportal, portal, buylink FROM(SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM cdrom_games" . $strwhere;
$strsql .= " UNION SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM portal2" . $strwhere;
$strsql .= " UNION SELECT title, imageicons, webportal, '' as portal, '' as buylink FROM portal3" . $strwhere;
$strsql .= " UNION SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM portal4" . $strwhere;
$strsql .= " UNION SELECT title, imageicons, webportal, '' as portal, buylink FROM portal5" . $strwhere;
if ($title != "")
$strsql .= " UNION SELECT title, imageicons, webportal, portal, '' as buylink FROM whatsnew WHERE title LIKE '%" . $title . "%'";
$strsql .= " UNION SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM portal6" . $strwhere . ")a GROUP BY title";
$offset = ($page - 1) * 22;
$strsql = $strsql . " ORDER By Title LIMIT " . $offset . ", 22;";
Before I went to find a gun to shoot myself, I just had to see what a final SQL query string from this monster looked like, so I echo'ed it out before it was passed to the DB:
SELECT title, imageicons, webportal, portal, buylink FROM(SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM cdrom_games WHERE (filesize<10 OR filesize IS NULL) AND LCASE(genre)<>'cdrom' AND title LIKE '%crossword%' UNION SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM portal2 WHERE (filesize<10 OR filesize IS NULL) AND LCASE(genre)<>'cdrom' AND title LIKE '%crossword%' UNION SELECT title, imageicons, webportal, '' as portal, '' as buylink FROM portal3 WHERE (filesize<10 OR filesize IS NULL) AND LCASE(genre)<>'cdrom' AND title LIKE '%crossword%' UNION SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM portal4 WHERE (filesize<10 OR filesize IS NULL) AND LCASE(genre)<>'cdrom' AND title LIKE '%crossword%' UNION SELECT title, imageicons, webportal, '' as portal, buylink FROM portal5 WHERE (filesize<10 OR filesize IS NULL) AND LCASE(genre)<>'cdrom' AND title LIKE '%crossword%' UNION SELECT title, imageicons, webportal, portal, '' as buylink FROM whatsnew WHERE title LIKE '%crossword%' UNION SELECT title, imageicons, '' as webportal, '' as portal, '' as buylink FROM portal6 WHERE (filesize<10 OR filesize IS NULL) AND LCASE(genre)<>'cdrom' AND title LIKE '%crossword%')a GROUP BY title ORDER By Title LIMIT 0, 22;
-
Look at the bright side... at least he used LIMIT as opposed to a loop in PHP.
-
I hate people picking on php, pick on the idiot who wrote that crap!
Seriously, they don't need a new PHP developer, they need a new database designer.
