Fix SQL Injection (Legacy & Modern) in Roda

SQL Injection in Roda applications usually stems from improper use of the Sequel ORM or raw database connections. The shift from 'legacy' (string interpolation) to 'modern' (parameterized datasets) is the primary line of defense. In Roda, the routing tree handles the input, but how you pass that input to the DB determines your security posture. Avoid raw string fragments at all costs.

The Vulnerable Pattern

class App < Roda
  route do |r|
    r.on "search" do
      # VULNERABLE: Direct string interpolation into a raw SQL query
      # Payload example: /search?name=' OR '1'='1
      query = "SELECT * FROM items WHERE name = '#{r.params['name']}'"
      DB[query].all
    end
  end
end

The Secure Implementation

The vulnerable code uses Ruby's string interpolation to build a SQL statement, allowing an attacker to manipulate the query logic by injecting single quotes and SQL keywords. The secure version leverages Sequel's abstraction layer, which treats user input as a bound parameter. This ensures the database engine interprets the input strictly as data, not as executable code, effectively neutralizing SQL injection payloads.

class App < Roda
  route do |r|
    r.on "search" do
      # SECURE: Using Sequel's dataset API with hash-based filtering
      # This automatically uses parameterized queries
      DB[:items].where(name: r.params['name']).all
  # ALTERNATIVE (Raw SQL with placeholders):
  # DB['SELECT * FROM items WHERE name = ?', r.params['name']].all
end
end
end