mysql - 使用单引号更新数据库失败 Ruby on Rails

Question

我需要使用 ruby​​ scipt 用另一个数据库表的数据更新一个数据库表。如果字段中有单引号，则会引发错误。如何避免呢？

在下面的示例中，它无法插入 nish。

A_db: products table info:
     id    text
    ----------------
      1    hashh
      2    nish's

A_db = Mysql2::Client.new(
           :host => "xxx", 
           :username => "xxx",
           :database => "xxx",
           :password => "xxx")
B_db = Mysql2::Client.new(
           :host => "zzz", 
           :username => "xxx",
           :database => "xxx",
           :password => "xxx")

Adata = A_db.query("select * from products;")
Adata.each do |d|
   id= d['id']
   B_db.query("insert ignore into products(id, text) values('#{id}', '#{d['text']}')")
end

Answer 1

您可以使用Mysql2::Client.escape method .

但不要。相反，请使用准备好的语句。准备好的语句是逃避您的值并保护自己免受 SQL 注入(inject)攻击的最佳方式。

Mysql2 README 有一个准备语句的示例 in its Usage section ，我将在这里为后代复制它:

Prepared statements are supported, as well. In a prepared statement, use a ? in place of each value and then execute the statement to retrieve a result set. Pass your arguments to the execute method in the same number and order as the question marks in the statement.

statement = @client.prepare("SELECT * FROM users WHERE login_count = ?")
result1 = statement.execute(1)
result2 = statement.execute(2)

statement = @client.prepare("SELECT * FROM users WHERE last_login >= ? AND location LIKE ?")
result = statement.execute(1, "CA")

因此，您的查询将如下所示:

statement = B_db.prepare("INSERT IGNORE INTO products (id, text) VALUES (?, ?)")
statement.execute(d["id"], d["text"])