Ruby SQLite插入的转义字符串 [英] Escaping Strings For Ruby SQLite Insert

问题描述

我正在创建一个Ruby脚本，将大约150k行的制表符分隔的文本文件导入SQLite.到目前为止，这里:

I'm creating a Ruby script to import a tab-delimited text file of about 150k lines into SQLite. Here it is so far:

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 << 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

尽管gsub会在我的prepare_for_insert方法中转义单引号，但脚本仍会在第一行包含单引号的地方出错:

The script errors out on the first line containing a single quote in spite of the gsub to escape single quotes in my prepare_for_insert method:

/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)

第15行出现错误.如果用puts string[14]检查该行，则可以看到在"s"附近显示错误的位置.看起来像这样:'Touch the Top of the World: A Blind Man\'s Journey to Climb Farther Than the Eye Can See'

It's erroring out on line 15. If I inspect that line with puts string[14], I can see where it's showing the error near "s". It looks like this: 'Touch the Top of the World: A Blind Man\'s Journey to Climb Farther Than the Eye Can See'

看起来单引号已转义，为什么我仍然收到错误消息?

Looks like the single quote is escaped, so why am I still getting the error?

推荐答案

根本不要那样做，字符串插值和SQL往往是一个不好的组合.改用准备好的语句，让驱动程序处理引号和转义符:

Don't do it like that at all, string interpolation and SQL tend to be a bad combination. Use a prepared statement instead and let the driver deal with quoting and escaping:

# Ditch the gsub in prepare_for_insert and...
db  = SQLite3::Database.new('/Users/michael/catalog.db')
ins = db.prepare('insert into catalog (column_name) values (?)')
string.each { |s| ins.execute(s) }

您应该将column_name替换为实际的列名；您不必在INSERT中指定列名，但无论如何都应该始终这样做.如果您需要插入更多列，请向ins.execute添加更多占位符和参数.

You should replace column_name with the real column name of course; you don't have to specify the column names in an INSERT but you should always do it anyway. If you need to insert more columns then add more placeholders and arguments to ins.execute.

使用 prepare 和

Using prepare and execute should be faster, safer, easier, and it won't make you feel like you're writing PHP in 1999.

此外，您应该使用标准CSV解析器进行解析用制表符分隔的文件，XSV格式处理起来并不有趣(实际上它们是彻头彻尾的邪恶)，并且与处理废话和边缘情况(而不是其他情况)相比，与时间相关的事情要好得多.

Also, you should use the standard CSV parser to parse your tab-separated files, XSV formats aren't much fun to deal with (they're downright evil in fact) and you have better things to do with your time than deal with their nonsense and edge cases and what not.

这篇关于Ruby SQLite插入的转义字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！