How to properly avoid regex in Active Record where condition

Question

I am using Active Record

v4.2.1

to query a database mySQL

using regex. I would like to create the following SQL:

SELECT `users`.* FROM `users` WHERE (email REGEXP '[@\.]gmail\.com')

However, I cannot find the correct way to generate SQL using Active Record.

User.where("email REGEXP ?", "[@.]gmail.com").to_sql

gives

"SELECT `users`.* FROM `users` WHERE (email REGEXP '[@.]gmail.com')"

User.where("email REGEXP ?", "[@\.]gmail\.com").to_sql

gives

"SELECT `users`.* FROM `users` WHERE (email REGEXP '[@.]gmail.com')"

User.where("email REGEXP ?", "[@\\.]gmail\\.com").to_sql

gives

"SELECT `users`.* FROM `users` WHERE (email REGEXP '[@\\\\.]gmail\\\\.com')"

How do I get Active Record to exit SQL correctly?

+3

Nick Q. May 14 '15 at 22:44

1 answer

infused · Accepted Answer · 2015-05-14T23:00:39+0000

When used, to_sql

escaping just looks wrong in the console, the last example sends it to MySQL:

SELECT `users`.* FROM `users` WHERE (email REGEXP '[@\\.]gmail\\.com')

You can confirm this by running the actual query in the console and checking the log file.