title: Escaping Strings For Ruby SQLite Insert
body: <p>I'm creating a Ruby script to import a tab-delimited text file of about 150k lines into SQLite. Here it is so far:</p>
  
  <pre><code>require 'sqlite3'
  
  file = File.new("/Users/michael/catalog.txt")
  string = []
  
  # Escape single quotes, remove newline, split on tabs, 
  # wrap each item in quotes, and join with commas
  def prepare_for_insert(s)
    s.gsub(/'/,"\\\\'").chomp.split(/\t/).map {|str| "'#{str}'"}.join(", ")
  end
  
  file.each_line do |line|
    string &lt;&lt; prepare_for_insert(line)
  end
  
  database = SQLite3::Database.new("/Users/michael/catalog.db")
  
  # Insert each string into the database
  string.each do |str|
    database.execute( "INSERT INTO CATALOG VALUES (#{str})")
  end
  </code></pre>
  
  <p>The script errors out on the first line containing a single quote in spite of the <code>gsub</code> to escape single quotes in my <code>prepare_for_insert</code> method:</p>
  
  <pre><code>/Users/michael/.rvm/gems/ruby-1.9.3-p0/gems/sqlite3-1.3.5/lib/sqlite3/database.rb:91:
  in `initialize': near "s": syntax error (SQLite3::SQLException)
  </code></pre>
  
  <p>It's erroring out on line 15. If I inspect that line with <code>puts string[14]</code>, I can see where it's showing the error near "s". It looks like this: <code>'Touch the Top of the World: A Blind Man\'s Journey to Climb Farther Than the Eye Can See'</code></p>
  
  <p>Looks like the single quote is escaped, so why am I still getting the error?</p>