mysql query in laravel suddenly not working

Question

I have a laravel app that's been working for 2.5 years on a hosted site. Today an auto complete field started generating an exception on Connection.php

> SQLSTATE[42S22]: Column not found: 1054 Unknown column
> 'venues.details' in 'order clause'

The query is:

SELECT *
FROM
  (SELECT `venues`.*,
          `a`.`street_number`,
          `a`.`longitude`,
          `a`.`latitude`,
          `p`.`code` AS `postal_code`,
          `s`.`name` AS `street`,
          `c`.`name` AS `city`,
          `st`.`name` AS `state`,
          `co`.`name` AS `country`,
          `co`.`sortname` AS `sortname`,
          `t`.`name` AS `timeZone`,
          `t`.`zone` AS `timeZoneName`,

          CONCAT(street_number, ' ', s.name, ' ', c.name, ', ', st.name, ', ', co.name) COLLATE utf8_bin AS address,
          CONCAT(venues.name, ' ', street_number, ' ', s.name, ', ', c.name, ', ', st.name, ', ', co.name) COLLATE utf8_bin AS details
   FROM `venues`
   INNER JOIN `addresses` AS `a` ON `a`.`id` = `address_id`
   INNER JOIN `streets` AS `s` ON `s`.`id` = `a`.`street_id`
   INNER JOIN `postalcodes` AS `p` ON `p`.`id` = `a`.`postalcode_id`
   INNER JOIN `cities` AS `c` ON `c`.`id` = `p`.`city_id`
   INNER JOIN `states` AS `st` ON `st`.`id` = `c`.`state_id`
   INNER JOIN `countries` AS `co` ON `co`.`id` = `st`.`country_id`
   INNER JOIN `timezones` AS `t` ON `t`.`id` = `a`.`timezone_id`
   WHERE `venues`.`deleted_at` IS NULL) AS venues
ORDER BY venues.details
LIMIT 15
OFFSET 0)

If I run that query on the mysql console it works as I expect. I haven't change the (laravel) app or the database in over a year. The hosting site say nothing has changed.

I managed to get it working by commenting out the sort:

$collate = ''; //, $collate = ' COLLATE utf8_bin ASC'
                $results = $builder
                        //->orderByRaw($column . $collate)
                        ->paginate();

But I can't think why it fails but manually running the query works (and that it's worked fine for 2+ years).

Show also `venues` schema. Does it have `details` column? – Styx Nov 12 '19 at 08:07 — Styx, Nov 12 '19 at 08:07
Re-read the query. “details” is a computed column. – pfa Nov 12 '19 at 14:04 — pfa, Nov 12 '19 at 14:04

Matt Borja · Answer 1 · 2019-11-14T07:35:34.390

1

Something esoteric like this sometimes points to an underlying driver issue or a breaking change introduce into the environment following OS patching. In the past, I've found an update to cURL break a PHP web application during a yum update for a client and had to rollback that individual package.

Also, currently you have:

SELECT *
FROM
  (SELECT `venues`.*, ... FROM `venues` INNER JOIN ...) AS venues
ORDER BY venues.details
LIMIT 15
OFFSET 0

You might also consider updating the SQL to use a different alias name for your subquery to eliminate any possibility of "clobbering" with your table name:

SELECT *
FROM
  (SELECT `venues`.*, ... FROM `venues` INNER JOIN ...) AS result
ORDER BY result.details
LIMIT 15
OFFSET 0

Update: You might also look into your usage with orderBy. This seems to be related: Laravel OrderByRaw column not found.

Also, aside auditing the environment for recent PHP package updates, etc. I would try to audit the actual [user-supplied?] input this is failing on and get a dump of the raw SQL (i.e. How Do I Get the Query Builder to Output Its Raw SQL Query as a String?) before or as it gets executed to identify possible SQLi that may also be messing up your query.

edited Nov 14 '19 at 07:35

answered Nov 12 '19 at 06:09

Matt Borja

1,509
1
17
38

The queries are generated by laravel components. I’m sure what you’re saying is doable, I’m just very rusty with that technology (I no longer work in php for work). – pfa Nov 12 '19 at 14:03
@pfa I hear ya. Troubleshooting something like this would almost be better being able to look at more of the application for better context to see how your database entities (i.e. models and relationships) are set up. I wouldn't mind taking a look at it off-thread if it came down to it. – Matt Borja Nov 12 '19 at 14:18
Are you savvy with laravel? – pfa Nov 12 '19 at 14:38
@Matt Your suggestion will add _new_ error even to valid and working query, isn't that obvious? – Styx Nov 12 '19 at 18:10
@pfa I have used Laravel in the past for a couple personal production-style projects with support for middleware, authentication (i.e. Passport), etc. but it's been a while. I find it to share a lot of similarities, though, with other MVC frameworks I've also used before including Rails and esp. these days ASP.NET MVC (C#) for my line of work. All of these come with their preferred ORMs so it sort of becomes a repeat after a while. – Matt Borja Nov 13 '19 at 05:38
@Styx Sorry, I'm still not sure what exactly you're referring to. If you're referring to the ellipses, that's intentional; it's for condensing code to focus on the point I'm making about aliases. If you're referring to the trailing closing parenthesis that was in the OP, I've removed that. I don't think any of this warrants a downvote because I'm actually attempting to provide a tangible solution with scenario context and examples, but honestly, I don't really care about votes at this point either. – Matt Borja Nov 13 '19 at 05:42
@Matt I'm sorry if I haven't been clear about that. Your advice to rename alias to `result` while still using `venues.details` in `ORDER BY` clause is an error. This renaming will produce error even for valid queries. You can try it yourself: open `mysql` database and run query: `SELECT * FROM (SELECT 1 AS title FROM db) AS result ORDER BY db.title` — that will produce error. – Styx Nov 13 '19 at 07:22
@Styx That makes sense. I've updated the ORDER BY clause to use the correct alias on the outside of the subquery instead. Does that look better? – Matt Borja Nov 14 '19 at 06:23
@Matt Yes, much better, though I'm not sure whether this will help. OP is unresponsive though. I've removed downvote. – Styx Nov 14 '19 at 07:06
@Styx Yeah, quite possible this is more ORM/user-input related. I've also updated my answer in consideration of those as well. – Matt Borja Nov 14 '19 at 07:36

mysql query in laravel suddenly not working

1 Answers1