Ruby中User.update函数报SQLite3::SQLException: no such column: COLIN错误排查

错误根源 #

你遇到的SQLite3::SQLException: no such column: COLIN错误，本质是字符串值没被正确转义，SQL把它当成了列名。

看你的update方法代码：

def self.update(user_id, attribute, value)
  DBConnection.execute(<<-SQL, user_id, attribute, value)
      UPDATE
          users
      SET
          #{attribute} = #{value}
      WHERE
          id = #{user_id}
  SQL
end 

当调用User.update(1, :firstname, 'COLIN')时，插值后的SQL语句会变成：

UPDATE users SET firstname = COLIN WHERE id = 1

这里的COLIN没有被单引号包裹，数据库会误以为这是一个列名，但你的users表中不存在名为COLIN的列，因此抛出错误。

另外这段代码还有两个严重问题：

• 明明在execute方法里传了参数，却没用到占位符?绑定，完全浪费了参数绑定的安全特性，全程用字符串插值。
• 字符串插值会带来SQL注入风险，如果传入恶意内容，能直接篡改数据库数据。

修复代码 #

修改update方法，正确使用占位符绑定值，同时先校验列名的合法性：

def self.update(user_id, attribute, value)
  # 先验证列名是否合法，防止恶意注入
  valid_columns = ['firstname', 'lastname', 'age', 'password', 'email']
  unless valid_columns.include?(attribute.to_s)
    raise ArgumentError, "非法列名: #{attribute}"
  end

  DBConnection.execute(<<-SQL, value, user_id)
      UPDATE
          users
      SET
          #{attribute} = ?
      WHERE
          id = ?
  SQL
end 

修复后，调用User.update(1, :firstname, 'COLIN')生成的SQL会是：

UPDATE users SET firstname = 'COLIN' WHERE id = 1

数据库会把COLIN当成字符串值处理，不会再报错。

顺带提一句，你的create方法也存在同样的SQL注入问题，建议改成参数绑定的写法：

def self.create(user_info)
  DBConnection.execute(<<-SQL, 
    user_info[:firstname], user_info[:lastname],
    user_info[:age], user_info[:password], user_info[:email]
  )
    INSERT INTO
      users (firstname, lastname, age, password, email)
    VALUES
      (?, ?, ?, ?, ?)
  SQL
  DBConnection.last_insert_row_id
end

内容的提问来源于stack exchange，提问作者Colin Jackson