From: "D. Deryl Downey" Date: 2013-01-03T05:22:39+09:00 Subject: Re: Escaping SQL queries. This is a multi-part message in MIME format. --------------020707070306000909040301 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Could do something like this example: first = "T'Luth" User.where("first_name LIKE ?", first) # This is the parameterized call Matma mentioned. Under ActiveRecord, the 'first' parameter to where() will automatically be escaped for you. Under Rails, calling .html_safe will escape non-alphanumeric characters for you, explicitly, before being passed to the calling method, the .where() method such as in this example, even though AR will do it for you, implicitly. User.where("first_name LIKE ?", "#{first.html_safe}") # Explicit escaping The example AR .where() clause is the same regardless if your app is straight Ruby or Rails. The .html_safe call is a Rails-only method. bear in mind that you can .html_safe variables directly in an SQL query, *but*, one still should not get into the habit of directly interpolating in a query string. Its an unsafe habit to form. (Lord help you if you forget to call .html_safe and it contains something malicious) > Matma Rex > January 2, 2013 2:38 PM > *The* way would be to use a good ORM so that it does all this for you > (I personally recommend Sequel). > > The other good way would be to use parameterized queries. > > Apart from that, use whatever given SQL library gives you; unlike in, > say, PHP, there is more than one widely used one. > > Ken D'Ambrosio > January 2, 2013 2:35 PM > Hi, all. As someone whose last name has one of those dreaded > apostrophes, I'm acutely aware of how poorly things can go when SQL > queries don't handle apostrophes correctly. Googling this seems to > give me some indeterminate methods on handling it, and I was wondering > if there's One True Way to escape strings correctly in Ruby. > > Any pointers? > > Thanks! > > -Ken > -- D. Deryl Downey "The bug which you would fright me with I seek" - William Shakespeare - The Winter's Tale, Act III, Scene II - A court of Justice. --------------020707070306000909040301 Content-Type: multipart/related; boundary="------------080304070306090905050603" --------------080304070306090905050603 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Could do something like this example:

first = "T'Luth"
User.where("first_name LIKE ?", first) # This is the parameterized call Matma mentioned.

Under ActiveRecord, the 'first' parameter to where() will automatically be escaped for you.

Under Rails, calling .html_safe will escape non-alphanumeric characters for you, explicitly, before being passed to the calling method, the .where() method such as in this example, even though AR will do it for you, implicitly.

    User.where("first_name LIKE ?", "#{first.html_safe}") # Explicit escaping

The example AR .where() clause is the same regardless if your app is straight Ruby or Rails. The .html_safe call is a Rails-only method. bear in mind that you can .html_safe variables directly in an SQL query, *but*, one still should not get into the habit of directly interpolating in a query string. Its an unsafe habit to form. (Lord help you if you forget to call .html_safe and it contains something malicious)

January 2, 2013 2:38 PM
*The* way would be to use a good ORM so that it does all this for you (I personally recommend Sequel).

The other good way would be to use parameterized queries.

Apart from that, use whatever given SQL library gives you; unlike in, say, PHP, there is more than one widely used one.

January 2, 2013 2:35 PM
Hi, all.  As someone whose last name has one of those dreaded apostrophes, I'm acutely aware of how poorly things can go when SQL queries don't handle apostrophes correctly.  Googling this seems to give me some indeterminate methods on handling it, and I was wondering if there's One True Way to escape strings correctly in Ruby.

Any pointers?

Thanks!

-Ken


--
D. Deryl Downey
"The bug which you would fright me with I seek" - William Shakespeare - The Winter's Tale, Act III, Scene II - A court of Justice.


--------------080304070306090905050603 Content-Type: image/jpeg; x-apple-mail-type=stationery; name="compose-unknown-contact.jpg" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="compose-unknown-contact.jpg" /9j/4AAQSkZJRgABAQEARwBHAAD/2wBDAAEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEBAQEC AQEBAQEBAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/2wBDAQEBAQEBAQICAgICAgIC AgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgL/wAAR CAAZABkDAREAAhEBAxEB/8QAGAAAAwEBAAAAAAAAAAAAAAAABgcICQr/xAA0EAABAwMCAgUK BwAAAAAAAAACAQMEBQYRABITIQcUMUF2CBUXIjI2N0JRtVRWkZOV0dL/xAAYAQEAAwEAAAAA AAAAAAAAAAADAAEEAv/EACQRAAICAAQGAwAAAAAAAAAAAAABAhEDMrHREyExM0FxgfDx/9oA DAMBAAIRAxEAPwDuEt+gW/ULet6oVC3rfqNQqFv0OfPn1GhUqfOmzZtKZlS5UqZMaNwzNwiJ VIl7eXLCaZIGwBl3TY8epPx2+jy2ZNPjvkwc9uhW8j7nCPhvOsQliYIeS7cvCpp8o50qwrC4 v3lsNSDbdmTEhvs2tahxpfV3WnmbbozJEw/gwdadbYExVRXKEKoSdvJcaOSqxE7/AAiX0gXx +a69/JSf9alIlste0VzaNpeFrcT9KKymotyiaZ0KRCnzacoE7Kjzn4gi2KqUh3jqDHDHv4mR UfruTWlMzlVUKIVNp9GguEJnAh0+IZjyAiisgyRDnu5azS8miKqjOTVkKqS/psG37fo1Fbab eg25b8eZPeFJBBJSjMG5HjMeyihnaauZwe4OGiju13GAcpOwBeN+U8/IkGbsiS8b7ryogmbz hbyc9REROfZhERO5ETShjPtvpGqTUyLErytS4siSwx5x2tRH4hPOI0DkjZtaJtFxuVEbIUUi yeNujlBUJGbJN6nM/Cyf2Hf60YgjvKA+NPSP4gT7axpcPtr51YWJnYn9dnAQWl722p4ot37y zqnlfp6FrqbwawG8/9k= --------------080304070306090905050603-- --------------020707070306000909040301--