3

Having followed some tips on escaping apostrophes I am getting an unexpected combination of escape characters in the resulting sql statement. The following rails 4 active record statement is run against 5.5.42-MariaDB:

    User.where(["surname LIKE ?", "%#{params[:search]}%"])

Where 

    params[:search] =  "O'Keefe"

A .to_sql generates

     SELECT * FROM users WHERE surname LIKE '%O\\'Keefe%'

MySQL/MariaBD expects an apostrophe to be escaped as two single apostrophes '' , or with a single backslash \' so this results in a syntax error. I am looking for help to understand why two backslashes \\' are appearing, and for a solution that will maintain protection against SQL injection.

UPDATE After further investigation following suggestions below, it appears as though the console .to_sql output SELECT * FROM users WHERE surname LIKE '%O\\'Keefe%' is not what is passed onto MySQL. It failed for me 'cos I simply copied the statement into a mysql console to test execution. There is some black magic on route to the database that converts the double backslash \\' into a valid mysql escape sequence.

So problem 1/2 solved

    User.where(["surname LIKE ?", "%#{params[:search]}%"])

is valid syntax that correctly auto-escapes the user input string. But can anyone shed any light on the reason for the generation of the double backslash and how it is modified on its way to database execution?

SimonH
  • 31
  • 3
  • http://stackoverflow.com/questions/9596652/how-to-escape-apostrophe-in-mysql read this, it can be helpful – Legendary Mar 18 '15 at 11:02
  • Thanks, I had already read this, but it didn't explain why the array substitution and escaping was adding the two backslashes. – SimonH Mar 18 '15 at 16:03

1 Answers1

2

Try this:

User.where(["surname LIKE ?", "%#{params[:search].gsub("'", "''")}%"])

http://dev.mysql.com/doc/refman/5.0/en/string-literals.html#character-escape-sequences

AlexLarra
  • 841
  • 5
  • 18
  • params[:search] is "O'Keefe" params[:search].gsub("'", "\\'") returns "OKeefeKeefe" now I'm confused ?? Thanks for the link, that is the same page I referred to initially regarding valid escape characters. – SimonH Mar 18 '15 at 15:59
  • Thank you [user957087](http://stackoverflow.com/users/957087/user957087), I didn't expect that behaviour. In the link passed by [Legendary](http://stackoverflow.com/users/4003349/legendary) says that maybe this could work params[:search].gsub("'", "''") – AlexLarra Mar 18 '15 at 16:18
  • Thanks [Alex](http://stackoverflow.com/users/2988753/alexlarra) in my trials any additional escape characters added by gsub are further escaped by the array parameter substitution, which doubles up the escape characters. The end goal has moved slightly ...... see my update above. Thankyou. – SimonH Mar 18 '15 at 16:32
  • Rails will automatically escape single quote if you use '?' way. – Ghazi May 08 '18 at 07:47