Quick Tip: Rails Escaping Database Values and Storing/Inserting with Multi-Insert SQL Query

In this quick tip we’ll see how to escape values in Rails before passing them on to an SQL query (preventing injection attacks) and then also look into how to do multi-inserts (at the DB level). Let’s first see how to escape values to prevent SQL injections in Rails:

pry(main)> Model.connection.quote("hel'lo")
=> "'hel\\'lo'"
pry(main)> Model.sanitize("hel'lo")
=> "'hel\\'lo'"

These methods are inherited from ActiveRecord::Base, since that’s what your Model will extend. So you can also do this:

What's the one thing every developer wants? More screens! Enhance your coding experience with an external monitor to increase screen real estate.

pry(main)> ActiveRecord::Base.connection.quote("hel'lo")
=> "'hel\\'lo'"
pry(main)> ActiveRecord::Base.sanitize("hel'lo")
=> "'hel\\'lo'"

You generally don’t escape manually, unless you’re executing raw SQL queries via ActiveRecord::Base.connection.execute('sql query'). Most of the times we end up using ActiveRecord’s query interface for a more object oriented paradigm which by default prevents SQL injection. Quick examples:

Model.where('col = ?', col_val)
Model.where(col: col_val)

Got it! Next, let’s see how multi-inserts can be done in Rails. By multi-insert (performant with high volume inserts) what I mean is this:

# Separate insert queries
INSERT INTO table (col1, col2) VALUES (val1, val2)
INSERT INTO table (col1, col2) VALUES (val3, val4)

# Multi-inserts
INSERT INTO table (col1, col2) VALUES (val1, val2), (val3, val4)

In Rails, we can pass an array to Model.create() like this:

Model.create([ { col1: val1, col2: val2 }, { col1: val3, col2: val4 } ])

But this will do separate INSERT queries under the hood. I couldn’t find any query interface in ActiveRecord itself that’d facilitate multiple SQL inserts at a shot. So seems like we’ll have to resort to raw SQL which should be passed to Model.connection.execute(raw_sql). But the problem with raw SQL query is escaping the input values. Now running through our values (looping over them) and then escaping them with sanitize or quote might seem like a clumsy approach. There’s another way though, which is much elegant.

sql_parts = ["INSERT INTO table (col1, col2) VALUES (?, ?), (?, ?)", val1, val2, val3, val4] # Using placeholders
sql = Model.send(:sanitize_sql_array, sql_parts) # Get the SQL query to execute
Model.connection.execute(sql) # Pass the raw SQL query for execution

and we’re done! The solution is to use sanitize_sql_array.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Author: Rishabh

Rishabh is a full stack web and mobile developer from India. Follow me on Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *