Caleb scored his first intership at a small, family-owned print-shop. Much to his surprise, the day before he started, their primary web-developer left for a bigger, more lucrative job. His predecssor was an experienced programmer, but came at solving problems in his own unique way. This meant no comments, no functions, no classes, SQL injection vulnerabilities everywere, and cryptic 500-character one-liners stuffed into the value attribute of an input tag.

Caleb spent his first day just trying to get the code running on his dev machine. On the second day, he sat down with a more experienced co-worker to try and understand some of the queries. For example, there was one query that needed to return product details sorted in some meaningful fashion- like by name. Weirdly, though, the page wasn’t sorting them by name, except when it was- no one who used the product search understood the sort order.

Cabel dug in, expecting to see some variation on this: 

SELECT `id`, `name`, `description`
FROM products
WHERE `name` LIKE '%{$keyword}% '
ORDER BY `name`;

Instead, he saw this: 

# $keyword is a PHP variable that was interpolated into the concatenated PHP string.
# It is the search term the user entered, preserved here for clarity.
SELECT T1.product.id AS product_id, T1.slug, T1.name, description, details, T2.media_id, url, T1.order
FROM (
  SELECT product.id AS product_id, slug, name, description, product.details, text
  CASE
    WHEN name LIKE '$keyword' THEN 0
    WHEN name LIKE '$keyword%' THEN 1
    WHEN name LIKE '%$keyword' THEN 2
    WHEN name LIKE '%$keyword%' THEN 3
    WHEN product.details LIKE '%$keyword%' THEN 4
    WHEN description LIKE '%$keyword% ' THEN 5
    WHEN text LIKE '$keyword' THEN 6
    WHEN text LIKE '$keyword%' THEN 7
    WHEN text LIKE '%$keyword' THEN 8
    WHEN text LIKE '%$keyword%' THEN 9
  ELSE 10 END
  AS `order`
  FROM `product_option`, `product`
  WHERE `product_option`.`product_id` = `product`.`id` AND product.parent_id=0 AND (
    name LIKE '%$keyword%' OR
    description LIKE '%$keyword%' OR
    product.details LIKE '%$keyword%' OR text LIKE '%$keyword%'
  )
  ORDER BY `order`, name
) AS T1 LEFT JOIN (
  SELECT * FROM product_media WHERE product_media.order = 1) AS
  T2 ON T1.product_id=T2.product_id LEFT JOIN media ON T2.media_id=media_id
)

He sat down with a more experienced developer, trying to understand what on Earth this was supposed to do. In the end, they couldn’t figure it out, so they just replaced it with the straightforward ORDER BY name, and left some TODO comments confessing they don’t know what they just replaced.

Caleb’s boss stopped by after they released this change, complimenting him on how much better the product search page worked.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!