CODEWITHSUNDEEP
ruby
Michael Gruber
Michael Gruber

Reputation: 601

Escaping single quotes in Ruby

so I'm making a little ruby script to parse a .csv and output a large MySQL query. The problem is some of the names I'm working with have single quotes, which messes up my SQL...

My attempt to gsub the quotes into escape quotes has failed, though I'm not sure why. But sure enough when I open my query.txt file a name like D'vinci would be just that, and not D\'vinci.

What am I doing wrong?

require 'csv'

filename = ARGV[0]
q = "INSERT INTO `table` (`username`, `password`, `firstname`, `lastname`, `email`) VALUES "

CSV.foreach(filename) do |row|
  last_name, first_name, email, id = row.each {|c| c.gsub "'", "\'"}
  q += "('#{id}', SHA1('password'), '#{first_name}', '#{last_name}', '#{email}'),\n"
end

q += ";"

File.open("query.txt", 'w').write(q)

Upvotes: 0

Views: 463

Answers (2)

sawa
sawa

Reputation: 168081

each returns the receiver. So, your gsub inside that block is doing nothing. Change it to map, and it shall be fine. Or, you can keep the each and use gsub! instead.

Upvotes: 1

tadman
tadman

Reputation: 211560

Do not write your own SQL escaping. Please, always use the methods provided by the appropriate database driver.

If you're using MySQL, either mysql or the newer mysql2 gem, there's an escape function that should handle this for you.

It's not entirely clear why you're writing SQL in the first place when most databases have some kind of import-from-file function. MySQL in particular has LOAD DATA INFILE which can read in many different formats if used correctly.

Upvotes: 3

Related Questions