how to escape a string before insert or update in Ruby

Question

In ruby ActiveRecord doesn't provide dynamic binding for update and insert sqls, of course i can use raw sql, but that need maintain connection, so i want to know if there is simpler way to escape update or insert sql before executing like code below:

ActiveRecord::Base.connection.insert(sql)

i think i can write code by gsub, but i know if there has been a ready method to do it.

Whicker

Could you explain in more detail why you can't use ActiveRecord's regular save? What do you mean by "provide dynamic binding for updates and insert"? Could you show us some code? I have a feeling that you're not using AR in the right way if you feel the need to write raw SQL. – Supporter

Answer 1

You could do this:

ActiveRecord::Base.send(:sanitize_sql,["select * from my_table where description='%s' and id='%s'","mal'formed", 55], "my_table")

Of course, this means that you have the params separately. Not sure if it will work otherwise, but try it out.

Belia

Thank you, however it seems that ActiveRecord::Base there is no 'send' method, and there is no sanitize_sql method either. – Whicker

Wha?! send has to exist! It's defined on Object! What's the exact error you are getting? Also, you might try using __send__ , – Belia

Answer 2

In Rails >= 3.2.5 the following works for me:

evil_input = '"\';%#{}\"foo'
ActiveRecord::Base.connection.quote(evil_input)
=> "'\"'';%\#{}\\\"foo'"

Ascidian

Very nice. It also adds ' to Strings and converts Hashes and Arrays to yaml, so I can throw away my own code ;) - Still works with Rails 3.2 – Detoxicate

Answer 3

It's not real clear what you are asking for because your title talks about escaping a string before insert or update, then in the tags you talk about SQL injection.

If you need to have ActiveRecord automatically encode/modify content before insertion or updating, have you checked ActiveRecord's Callbacks? The before_save callback will fire when you do an update or create. If you want to modify the content before it's to be stored, that's a good place to do it.

If you want to use SQL parameters instead of inserting the variables into your "update" or "insert" statements to avoid SQL injection, then use AR's variable bindings. Scroll down to the section on "Conditions".

Ambuscade

AR's variable bindings is only applied for :conditions instead of update or insert field values. – Whicker

@ywenbo, Please rewrite your question so it clearly states what you want to know. It doesn't make sense. – Ambuscade