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.
