Issue #151 invalid

User input as values for the 'IN' operator

Anonymous created an issue

Hey! Is there a way to securely use user input as values for the 'IN' operator, like using the '$' when putting user input in query parameters?

A simple example to demonstrate my situation:

    list_of_ids = [1,2,3,4]       # THIS IS USER INPUT!

    query = <<-EOQ
      SELECT 
        *
      FROM 
        my_table
      WHERE
        id IN (#{list_of_ids.join(',')}) 
    EOQ

    connection.execute_query(query)

Thanks! Tomer

Comments (2)

  1. Michael Granger repo owner

    Yes, you'd check user input to ensure it matches what you expect.

    list_of_ids = []
    request.parameters[:ids].each do |idstr|
        idstr =~ /^(\d+)$/ or raise ArgumentError, "malformed parameter"
        list_of_ids << $1
    end
    
    query = <<-EOQ
    SELECT  *
    FROM  my_table
    WHERE id IN (#{list_of_ids.join(',')}) 
    EOQ
    
    connection.exec( query )
    

    Web applications should be doing similar checks for all untrusted user input, really, even input you plan to use in queries with placeholders.

    Also, in the future, you should post questions like this to the ruby-pg Google Group instead of opening a ticket. You're more likely to get an answer quickly, and from a broader set of people.

  2. Log in to comment