Backslash in MySQL INSERT statement - Ruby on Rails

Question

I'm trying to write a SQL INSERT statement (deliberately not using ActiveRecord), to eventually insert a large number of rows in one go.

inserts.push "('#{x}', #{p}, '#{day.strftime("%Y-%m-%d")}', #{student.id}, '#{created_at}', '#{created_at}', '#{session}')"

sql = "INSERT INTO attendance_marks (mark_code, present, date_recorded, student_id, created_at, updated_at, sess) VALUES #{inserts.join(", ")}"
conn =  ActiveRecord::Base.connection
conn.execute sql

But I get an error because the value of x is "\". How do I get around this and successfully insert the backslash character into the MySQL DB?

It feels like it should be simple, and it probably is, but I've got to my wit's end trying to work it out.

Error message:

>> Attendance.parse_attendance_string Student.find(1), "\\", DateTime.new(2012,9,1)
Student Load (0.7ms)  SELECT `students`.* FROM `students` WHERE `students`.`id` = 1 LIMIT 1
(0.4ms)  INSERT INTO attendance_marks (mark_code, present, date_recorded, student_id, created_at, updated_at, sess) VALUES ('\', 1, '2012-09-01', 1, '2012-08-03 15:58:19', '2012-08-03 15:58:19', 'AM')
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2012-09-01', 1, '2012-08-03 15:58:19', '2012-08-03 15:58:19', 'AM')' at line 1: INSERT INTO attendance_marks (mark_code, present, date_recorded, student_id, created_at, updated_at, sess) VALUES ('\', 1, '2012-09-01', 1, '2012-08-03 15:58:19', '2012-08-03 15:58:19', 'AM')
from /Library/Ruby/Gems/1.8/gems/activerecord-3.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `query'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `execute'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `log'
from /Library/Ruby/Gems/1.8/gems/activesupport-3.2.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.2.0/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.2.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:233:in `execute'
from /Library/Ruby/Gems/1.8/gems/activerecord-3.2.0/lib/active_record/connection_adapters/mysql2_adapter.rb:214:in `execute'
from /Users/mike.campbell/projects/cpoms_messy/app/models/attendance.rb:76:in `parse_attendance_string'
from (irb):115
>>

Duplicated question: http://stackoverflow.com/questions/4696040/escaping-values-in-rails-similar-to-mysql-real-escape-string — denis.peplin, Aug 03 '12 at 15:30

score 1 · Accepted Answer · answered Aug 03 '12 at 15:05

Rather than sanitize the inputs, deal with backslashes, and join the query all on your own, I would recommend checking out ActiveRecord import. It provides a Gem that lets you semantically create bulk inserts using this syntax:

books = []
10.times do |i| 
  books << Book.new(:name => "book #{i}")
end
Book.import books

Backslash in MySQL INSERT statement - Ruby on Rails

1 Answers1