Prevention of SQL injection via ruby script

Question

i have created the following ruby script that logs into a mysql database and returns order information based on what username and password the user has entered. my question is how would i go about preventing sql injections? i know that the way it is written at the moment leaves it wide open to attacks but i am new to ruby and am not sure how to go about preventing this.

 #!/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

Use a prepared statement instead of string concatenation/interpolation:

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

If you absolutely must use string interpolation for some bizarre reason, then use 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)}'
})

But really, don't do this unless you have no other choice. And in this case, you don't have to use string manipulation for this.