mysql - 通过ruby脚本防止SQL注入(inject)

Question

我创建了以下 ruby​​ 脚本，该脚本登录到 mysql 数据库并根据用户输入的用户名和密码返回订单信息。我的问题是如何防止 sql 注入(inject)？我知道它目前的编写方式很容易受到攻击，但我对 ruby​​ 很陌生，不知道如何防止这种情况发生。

 #!/usr/bin/ruby

 #Import mysql module
  require "mysql"

  begin

   #Establish connection to mysql database as the operator user.
   connection = Mysql.real_connect("localhost", "operator", "", "rainforest")
   #Allow Multi line statements
   connection.set_server_option(Mysql::OPTION_MULTI_STATEMENTS_ON)

   #Prompt user for username
   puts "Please Enter Your Customer Username:"
   #Get username entered and store to variable
   username = gets.chomp  

   #Prompt user for password
   puts "Please Enter Your Customer Password"
   #Get password entered and store to variable
   password = gets.chomp

   #Specify SQL query that returns order if user entered data matches data held in customer table
   customerQuery = connection.query("SELECT O.order_ID, O.date_ordered, C.customer_name, P.product_name
   FROM orders As O
   INNER JOIN customer As C ON O.customer_ID=C.customer_ID
   INNER JOIN product As P ON O.product_ID=P.product_ID
   WHERE C.customer_name = '" + name + "' AND C.customer_password = '" + password + "'")

   #If query returns a row then user has entered correct login details
   if customerQuery.num_rows > 0 then

   #tell user they have successfully logged in
   puts "User Successfully Authenticated: Hello " + username + ". Here are your orders:     \n**********"

   #Print all row data containing users order details to screen
   while row = customerQuery.fetch_row do

    puts row
    puts "**********"       
   end
   else
   #if no rows return, user has entered incorrect details, inform them of this by printing to screen
   puts "User Authentication Unsuccessful:Incorrect Username or Password, Please Try     Again" 
   end
   #close connection to database
   connection.close
   end

Answer 1

使用准备好的语句而不是字符串连接/插值:

p = connection.prepare(%q{
    select o.order_id, o.date_ordered, c.customer_name, p.product_name
    from orders as o
    join customer as c on o.customer_id = c.customer_id
    join product  as p on o.product_id  = p.product_id
    where c.customer_name     = ?
      and c.customer_password = ?
})
customerQuery = p.execute(name, password)
if customerQuery.num_rows > 0
    customerQuery.each do |row|
        #...
    end
else
    #...
end

如果出于某种奇怪的原因您绝对必须使用字符串插值，那么请使用connection.quote:

customerQuery = connection.query(%Q{
    select o.order_id, o.date_ordered, c.customer_name, p.product_name
    from orders as o
    join customer as c on o.customer_id = c.customer_id
    join product  as p on o.product_id  = p.product_id
    where c.customer_name     = '#{connection.quote(name)}'
      and c.customer_password = '#{connection.quote(password)}'
})

但实际上，除非别无选择，否则不要这样做。在这种情况下，您不必为此使用字符串操作。