How to sanitize raw SQL in a Ruby script

Question

I'm trying to write a script that connects with a database using Sequel.

If I have a SQL query like this:

record_values = csv_row.to_h.values.join(', ')

sql_query = "INSERT INTO table (#{ COLUMN_NAMES.join(', ') }) VALUES (#{ record_values })"

and the array record_values is dangerous.

How can I sanitize it?

I tried to sanitize using

ActiveRecord.sanitize_sql_array(sql_query)

but I kept getting the error

NoMethodError: undefined method 'sanitize_sql_array' for ActiveRecord:Module

Since you mention `ActiveRecord` – is this a Rails question? — Stefan, Jul 12 '19 at 06:59
@Stefan I tried to use its sanitization methods but failed. I think they require me to have a connection but I'm using sequel gem to connect to database — Henry Yang, Jul 12 '19 at 07:16
I don't know Sequel, but they have a [Security Guide](https://github.com/jeremyevans/sequel/blob/master/doc/security.rdoc) containing several examples. — Stefan, Jul 12 '19 at 07:31
You are generating a direct SQL statement, but using Sequel? That's wasting a huge amount of capability that Sequel brings to the table, especially when trying to guard against injection attacks. I'd recommend learning to use the gem and its language rather than just use it to connect only. It's not hard to learn, and the documentation is very good, and the author is extremely responsive. — the Tin Man, Jul 12 '19 at 23:54

score 4 · Accepted Answer · answered Jul 12 '19 at 08:27

I don't know Sequel, but did you try standard insert method?

connection = Sequel.connect('...')
table_name = connection.from(:table_name)
# OR
# table_name = DB.from(:table_name)
# table_name = DB[:table_name]
table_name.insert(csv_row.to_h)

It's more reliable I believe, because you avoid difference between COLUMN_NAMES and record_values.

How to sanitize raw SQL in a Ruby script

1 Answers1