Raw Insert Query with ActiveRecord

These past days have taken me to a place that I need to learn more about executing a raw query, without relying on generated active record object (existing model classes).

Last week, I wrote a lib, that I think, required me to wrote it with custom or raw query. My research was filled with these kind of results

ActiveRecord::Base.connection.execute(...)

I used it actually. But, until I find it hard to produce a query string that free from SQL injection. Anyone knows how to do it?

I tried looking for it. Some showed using the object class execute the raw query along with the input parameters and some showed using other functions provided by ActiveRecord::Base. But, it still kept me in the dark.

Well, the only goal that I want to achieve is, to make an insert query with input parameters. And so far, Arel is the best answer for this.

What I did to achieve this was

> manager = Arel::InsertManager.new(ActiveRecord::Base)
> table = Arel::Table.new(:stations)
> manager.into(table)
> manager.insert([ [table[:user_id], 1], [table[:station_id], 1], [table[:label], 'this is a label'] ])
> manager.to_sql
#"INSERT INTO `stations` (`user_id`, `station_id`, `label`) VALUES (1, 1, 'adfasdfad')"

Well, I think the query string is now safe from SQL injection and it can be used against the execute function from ActiveRecord.

Author: Hafiz B

Raw Insert Query with ActiveRecord

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s