Raw SQL when needed in Ruby on Rails - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is raw SQL in Rails?

Raw SQL means writing plain SQL queries directly instead of using Rails' ActiveRecord methods. It lets you run custom database commands when needed.

beginner

Why might you use raw SQL in a Rails app?

You use raw SQL when ActiveRecord can't express a complex query easily or when you want better performance by writing optimized SQL.

intermediate

How do you safely include user input in raw SQL queries in Rails?

Use parameter binding with question marks or named placeholders to avoid SQL injection. For example: `User.where("name = ?", user_name)`.

intermediate

What method lets you run raw SQL directly in Rails models?

You can use `ActiveRecord::Base.connection.execute(sql)` to run raw SQL commands directly on the database.

beginner

What is a risk of using raw SQL without care?

If you insert user input directly into raw SQL, it can cause SQL injection attacks, which let attackers access or damage your data.

Which Rails method safely inserts user input into raw SQL queries?

A String concatenation with + operator

B Using question marks (?) as placeholders

C Directly inserting variables inside strings

D Using puts to print SQL

Using question marks (?) or named placeholders safely binds parameters and prevents SQL injection.

What does `ActiveRecord::Base.connection.execute(sql)` do?

A Converts SQL to Ruby code

B Creates a new ActiveRecord model

C Validates SQL syntax only

D Runs raw SQL directly on the database

This method runs the given raw SQL string directly on the database.

When should you prefer raw SQL over ActiveRecord methods?

A When queries are too complex or need optimization

B For all simple queries

C To avoid learning ActiveRecord

D When you want to write less code

Raw SQL is best for complex queries or performance tuning not easily done with ActiveRecord.

What is a major security risk of careless raw SQL use?

A Slow app startup

B Syntax errors

C SQL injection attacks

D Memory leaks

Inserting user input directly into SQL can allow attackers to run harmful commands.

Which is NOT a benefit of using raw SQL in Rails?

A Easier to write than ActiveRecord always

B More control over queries

C Better performance in some cases

D Ability to use database-specific features

Raw SQL is not always easier; ActiveRecord is simpler for most common queries.

Explain when and why you would use raw SQL in a Rails application.

Think about complex queries and security.

Describe how to safely include user input in raw SQL queries in Rails.

Focus on security best practices.

Practice

1. What is the main reason to use raw SQL in a Rails application?

easy

A. To write database queries that Rails helpers cannot easily express

B. To avoid using ActiveRecord models completely

C. To speed up Rails server startup time

D. To automatically generate database migrations

Solution

1. Step 1: Understand Rails helpers limitations

   Rails helpers cover many common queries but sometimes complex queries need direct SQL.

2. Step 2: Identify why raw SQL is used

   Raw SQL is used when Rails helpers cannot express the needed query efficiently or at all.

3. Final Answer:

   To write database queries that Rails helpers cannot easily express -> Option A

4. Quick Check:

   Raw SQL = complex queries [OK]

Hint: Use raw SQL when Rails queries are too limited [OK]

Common Mistakes:

• Thinking raw SQL replaces ActiveRecord entirely
• Believing raw SQL speeds up server startup
• Confusing raw SQL with migration generation

2. Which Rails method is used to run a raw SELECT SQL query and get model objects back?

easy

A. Model.connection.execute(sql_query)

B. Model.find_by_sql(sql_query)

C. Model.where(sql_query)

D. Model.raw_query(sql_query)

Solution

1. Step 1: Identify method for raw SELECT returning models

   Rails provides find_by_sql to run raw SELECT queries returning model instances.

2. Step 2: Differentiate from execute method

   connection.execute runs SQL but returns raw results, not model objects.

3. Final Answer:

   Model.find_by_sql(sql_query) -> Option B

4. Quick Check:

   Raw SELECT with models = find_by_sql [OK]

Hint: Use find_by_sql for raw SELECT queries returning models [OK]

Common Mistakes:

• Using connection.execute for SELECT expecting models
• Confusing where with raw SQL execution
• Assuming raw_query is a valid method

3. Given the code:

users = User.find_by_sql("SELECT * FROM users WHERE age > 30")
puts users.first.name

What will this code do?

medium

A. Return an array of user IDs only

B. Raise a syntax error due to invalid SQL

C. Print the total count of users older than 30

D. Print the name of the first user older than 30

Solution

1. Step 1: Understand find_by_sql behavior

   find_by_sql runs the raw SELECT and returns model objects matching the query.

2. Step 2: Analyze the query and output

   The query selects users with age > 30, and users.first.name prints the first matching user's name.

3. Final Answer:

   Print the name of the first user older than 30 -> Option D

4. Quick Check:

   find_by_sql returns models = prints first name [OK]

Hint: find_by_sql returns model objects, so you can access attributes [OK]

Common Mistakes:

• Expecting raw SQL to return only IDs
• Thinking it raises syntax error without reason
• Confusing output with count instead of first record

4. What is wrong with this code snippet?

result = ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE name = 'Alice'")
puts result.name

medium

A. The result is a raw result set, not a model, so .name is undefined

B. The SQL query syntax is incorrect

C. ActiveRecord::Base.connection.execute cannot run SELECT queries

D. The connection object is not accessible this way

Solution

1. Step 1: Understand connection.execute return type

   connection.execute returns a raw database result, not model objects.

2. Step 2: Identify why .name fails

   Raw result sets do not have model attributes like .name; you must extract data manually.

3. Final Answer:

   The result is a raw result set, not a model, so .name is undefined -> Option A

4. Quick Check:

   connection.execute returns raw results, no model methods [OK]

Hint: Use find_by_sql for models; execute returns raw results [OK]

Common Mistakes:

• Assuming execute returns model objects
• Thinking SQL syntax is wrong
• Believing connection is inaccessible

5. You want to update all users' status to 'active' using raw SQL in Rails. Which code snippet correctly does this?

hard

A. User.find_by_sql("UPDATE users SET status = 'active'")

B. User.where("status = 'active'").update_all

C. ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'")

D. User.connection.execute("SELECT * FROM users WHERE status = 'active'")

Solution

1. Step 1: Identify method for non-SELECT raw SQL

   For UPDATE queries, use connection.execute to run raw SQL commands.

2. Step 2: Check each option's correctness

   ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'") uses connection.execute with UPDATE SQL correctly. User.find_by_sql("UPDATE users SET status = 'active'") misuses find_by_sql which is for SELECT. User.where("status = 'active'").update_all uses ActiveRecord but not raw SQL. User.connection.execute("SELECT * FROM users WHERE status = 'active'") runs SELECT, not UPDATE.

3. Final Answer:

   ActiveRecord::Base.connection.execute("UPDATE users SET status = 'active'") -> Option C

4. Quick Check:

   Raw UPDATE = connection.execute [OK]

Hint: Use connection.execute for UPDATE, INSERT, DELETE raw SQL [OK]

Common Mistakes:

• Using find_by_sql for UPDATE queries
• Confusing ActiveRecord methods with raw SQL
• Running SELECT instead of UPDATE