Rails SQL Example: Raw SQL Querying

When it comes to querying data in a Rails application, ActiveRecord provides a number of convenient methods for generating SQL queries. However, there may be situations where you need to execute raw SQL queries to interact with your database.

The connection Method

Rails provides a connection method on the ActiveRecord::Base class that returns a handle to the database connection. You can use this handle to execute raw SQL queries.

<% sql = "SELECT * FROM users WHERE age > 18" %>
<% result = ActiveRecord::Base.connection.execute(sql) %>
<% result.each do |row| %>
  <p><%= row["name"] %></p>
<% end %>

In the example above, we select all users from the database where their age is greater than 18 using raw SQL. The result of the query is then iterated over to display the names of each user.

Parameterized Queries

When using raw SQL queries, it's important to be aware of the potential for SQL injection attacks. To mitigate this risk, you should use parameterized queries.

<% sql = "SELECT * FROM users WHERE age > ? AND email = ?" %>
<% result = ActiveRecord::Base.connection.execute(sql, 18, "example@example.com") %>
<% result.each do |row| %>
  <p><%= row["name"] %></p>
<% end %>

In the example above, we use parameterized queries to select users where their age is greater than a specified value and their email matches a specified string. The values for the parameters are passed as additional arguments to the execute method.

Conclusion

Raw SQL querying can be a powerful tool when working with a Rails application. However, it's important to use caution and ensure that your queries are safe from SQL injection attacks. By using the connection method and parameterized queries, you can safely execute raw SQL queries in your Rails application.