CODEWITHSUNDEEP
mysqlrubyruby-on-rails-3activerecordruby-on-rails-4
Roeland
Roeland

Reputation: 3858

Rails is escaping my query incorrectly

So I am trying to handle a fairly complex query within active record. I am using rails 4.0 and mysql. The problem is, my query requires single quotes in the WHERE statement.

This is what the sql statement (WHERE portion) needs to end up looking like:

WHERE 
   `location_hours`.`open_at` <=
     hour(CONVERT_TZ(now(), 'US/Mountain',`locations`.`time_zone`)) * 60 * 60 
AND 
   `location_hours`.`close_at` >= 
     hour(CONVERT_TZ(now(), 'US/Mountain', `locations`.`time_zone`)) * 60 * 60

The issue is with 'US/Mountain'. I have tried the following two ways to make this work:

1.

string = "hour(CONVERT_TZ(now(), 'US/Mountain', `locations`.`time_zone`)) * 60 * 60";
filtered = filtered.joins(:hours)
  .where("`location_hours`.`closed` = ?", false)
  .where("`location_hours`.`open_at` <= %s AND `location_hours`.`close_at` >= %s",
    string, string)

2.

filtered = filtered.joins(:hours)
      .where("`location_hours`.`day` = weekday(CONVERT_TZ(now(), \"US/Mountain\", `locations`.`time_zone`))")
      .where("`location_hours`.`closed` = ?", false)
      .where("`location_hours`.`open_at` <= hour(CONVERT_TZ(now(), \"US/Mountain\", `locations`.`time_zone`)) * 60 * 60 AND `location_hours`.`close_at` >= hour(CONVERT_TZ(now(), \"US/Mountain\", `locations`.`time_zone`)) * 60 * 60")

The sql the second option generates (copy,pasted from console) I can literally drop as a raw query into phpmyadmin, and I get results back.

For some reason Rails is doing something funny with my query. Any pointers or suggestions would be greatly appreciated!

Thanks!!

Upvotes: 0

Views: 179

Answers (1)

mu is too short
mu is too short

Reputation: 434665

The easiest way to get single quotes into your SQL is to just put them in there:

.where("location_hours.open_at <= hour(CONVERT_TZ(now(), 'US/Mountain', ...

If the timezone is in a variable then use a placeholder:

.where("location_hours.open_at <= hour(CONVERT_TZ(now(), :tz, ...", :tz => time_zone_string)

Keep in mind that an SQL snippet such as

string = "hour(CONVERT_TZ(now(), 'US/Mountain', `locations`.`time_zone`)) * 60 * 60";

is not an SQL string and should not be escaped as such. That means that sending it through a %s won't do the right thing, the %s will just make a mess of the quotes in the snippet.

BTW, you don't need to backtick-quote everything, only identifiers that are keywords, case sensitive, contain whitespace, etc. Backticking everything just makes an ugly mess.

Upvotes: 2

Related Questions