CODEWITHSUNDEEP
mysqlsqlruby-on-rails
Qing
Qing

Reputation: 33

Escaping ':' and '?' in parameterized SQL queries in Rails

I want to know how to escape ? and : in SQL queries in general.

These are just arbitrary examples:

Post.find_by_sql ["SELECT title, author, content FROM post where author = ? AND RIGHT(content, 1) = '?'", @author]

(finding posts ends with ?)

Post.find_by_sql ["SELECT title, author, content FROM post where author = :author AND title LIKE 'Foo:bar%'", {author:@author}]

(finding posts starts with foo:bar)

I'm not asking how to escaping to prevent injection. I am asking when I am using parametrized queries, rails will see ? and :some_var as parameters. But what if I want to use ? and :stuff as part of the queries? How do I escape them so Rails will treat them as strings instead of trying to find a matching parameter?

I know a solution will be writing these two queries like:

Post.find_by_sql ["SELECT title, author, content FROM post where author = ? AND RIGHT(content, 1) = ?", @author, '?']

Post.find_by_sql ["SELECT title, author, content FROM post where author = :author AND title LIKE CONCAT(:name, '%')", {author:@author, name:'foo:bar'}]

But is there a more elegant way?

Upvotes: 2

Views: 400

Answers (1)

Philip Hallstrom
Philip Hallstrom

Reputation: 19879

See the quote method: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/Quoting.html#method-i-quote

I know you said "in general", but the above two queries can be written using ActiveRecord so you can avoid having to quote things yourself.

Upvotes: 1

Related Questions