Raw expressions in Laravel - Deep Dive

Overview - Raw expressions

What is it?

Raw expressions in Laravel let you write database queries exactly as you want, without Laravel changing or escaping them. They are pieces of SQL code you insert directly into your query builder or Eloquent ORM. This helps when you need special SQL commands or functions that Laravel doesn't support by default. Raw expressions give you full control over the SQL sent to the database.

Why it matters

Without raw expressions, you would be limited to only the SQL that Laravel's query builder or ORM can create. This means complex queries or special database functions would be hard or impossible to write. Raw expressions solve this by letting you add custom SQL safely, so your app can do exactly what the database supports. This flexibility is crucial for real-world apps that need advanced queries.

Where it fits

Before learning raw expressions, you should understand Laravel's query builder and Eloquent ORM basics. After mastering raw expressions, you can explore advanced database topics like query optimization, database transactions, and database-specific features.

Mental Model

Core Idea

Raw expressions are like writing your own exact instructions to the database, bypassing Laravel's automatic query building.

Think of it like...

Imagine ordering a pizza: Laravel's query builder is like choosing toppings from a menu, but raw expressions let you write a special recipe card with exactly what you want, even if it's not on the menu.

┌─────────────────────────────┐
│ Laravel Query Builder        │
│  ┌───────────────────────┐  │
│  │ Automatic SQL creation │  │
│  └──────────┬────────────┘  │
│             │               │
│   ┌─────────▼─────────┐     │
│   │ Raw Expressions    │     │
│   │ (Custom SQL code)  │     │
│   └───────────────────┘     │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Laravel Query Builder

Concept: Learn how Laravel builds SQL queries automatically using its query builder.

Laravel's query builder lets you write PHP code to create SQL queries without writing SQL yourself. For example, to get users older than 18, you write: DB::table('users')->where('age', '>', 18)->get(); Laravel converts this to SQL behind the scenes.

Result

You get a list of users older than 18 without writing SQL manually.

Knowing how Laravel builds queries helps you see where raw expressions fit when you need more control.

2

Foundation

What Are Raw Expressions?

Concept: Raw expressions let you insert exact SQL snippets into Laravel queries.

Sometimes you need SQL that Laravel can't build automatically, like database functions or complex conditions. Raw expressions let you write SQL directly: DB::table('users')->select(DB::raw('COUNT(*) as user_count'))->get(); This runs the SQL COUNT function exactly as you write it.

Result

Laravel runs your exact SQL snippet inside the query.

Raw expressions give you power to use any SQL feature, bypassing Laravel's automatic query builder.

3

Intermediate

Using Raw Expressions in Where Clauses

🤔Before reading on: do you think you can use raw SQL inside a where condition safely with Laravel? Commit to yes or no.

Concept: You can use raw expressions inside where clauses to write complex conditions Laravel can't build.

For example, to filter users where a custom SQL condition applies: DB::table('users')->whereRaw('DATE(created_at) = CURDATE()')->get(); This runs the raw SQL condition inside the WHERE clause.

Result

You get users created today using a raw SQL date function.

Knowing you can put raw SQL inside where clauses lets you handle complex filters not supported by Laravel.

4

Intermediate

Binding Parameters with Raw Expressions

🤔Before reading on: do you think raw expressions automatically protect against SQL injection? Commit to yes or no.

Concept: You can safely insert user data into raw expressions using bindings to avoid SQL injection.

Instead of inserting variables directly, use bindings: DB::table('users')->whereRaw('age > ?', [18])->get(); Laravel safely inserts 18 into the query, preventing injection.

Result

Query runs safely with user data inserted correctly.

Understanding bindings with raw expressions prevents security risks while using custom SQL.

5

Intermediate

Raw Expressions in Select and Order By

Concept: Raw expressions can be used in select lists and order clauses for advanced queries.

You can select computed columns or order by custom SQL: DB::table('users')->select('name', DB::raw('LENGTH(name) as name_length'))->orderByRaw('name_length DESC')->get(); This selects user names and their length, ordering by length descending.

Result

You get users ordered by the length of their names.

Using raw expressions in select and order lets you do advanced sorting and computed columns.

6

Advanced

Raw Expressions in Eloquent ORM

🤔Before reading on: do you think raw expressions can be used with Eloquent models as easily as with query builder? Commit to yes or no.

Concept: Raw expressions work inside Eloquent queries to add custom SQL parts while keeping ORM features.

For example, to add a raw select in Eloquent: User::select('name', DB::raw('UPPER(name) as upper_name'))->get(); You still get User model objects with an extra attribute 'upper_name'.

Result

Eloquent returns models with custom computed attributes from raw SQL.

Knowing raw expressions work with Eloquent lets you combine ORM convenience with SQL power.

7

Expert

Risks and Best Practices with Raw Expressions

🤔Before reading on: do you think using raw expressions carelessly can cause bugs or security issues? Commit to yes or no.

Concept: Raw expressions bypass Laravel's protections, so you must use them carefully with bindings and validation.

Raw SQL can cause SQL injection if user input is inserted directly. Also, raw expressions can break database portability if you use database-specific SQL. Always use parameter bindings and test queries carefully. Example risky code: DB::table('users')->whereRaw("name = '$userInput'")->get(); Better: DB::table('users')->whereRaw('name = ?', [$userInput])->get();

Result

Safe queries prevent injection and maintain app stability.

Understanding the risks of raw expressions helps you avoid security bugs and maintain clean, portable code.

Under the Hood

Laravel's query builder normally escapes and formats all parts of a query to prevent SQL injection and syntax errors. When you use raw expressions, Laravel inserts your SQL snippet directly into the final query string without modification. This means the raw SQL is sent as-is to the database engine. Laravel still allows parameter bindings to safely insert user data into raw expressions, but it does not parse or modify the raw SQL itself.

Why designed this way?

Raw expressions were designed to give developers full control over SQL when Laravel's abstraction is insufficient. The tradeoff is that Laravel trusts the developer to write safe and correct SQL. This design balances ease of use with flexibility, allowing Laravel to cover most cases automatically but still support advanced queries when needed.

┌─────────────────────────────┐
│ Laravel Query Builder        │
│  ┌───────────────────────┐  │
│  │ Normal Query Parts     │  │
│  │ (escaped, formatted)  │  │
│  └──────────┬────────────┘  │
│             │               │
│   ┌─────────▼─────────┐     │
│   │ Raw Expressions    │     │
│   │ (inserted as-is)  │     │
│   └─────────┬─────────┘     │
│             │               │
│  ┌──────────▼──────────┐    │
│  │ Final SQL Query      │    │
│  │ (sent to database)   │    │
│  └─────────────────────┘    │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do raw expressions automatically protect against SQL injection? Commit to yes or no.

Common Belief: Raw expressions are safe by default because Laravel handles all queries securely.

Reality: Raw expressions bypass Laravel's automatic escaping, so if you insert user input directly, it can cause SQL injection.

Why it matters: Ignoring this can lead to serious security vulnerabilities that hackers can exploit.

Quick: Can raw expressions be used anywhere in Laravel queries? Commit to yes or no.

Common Belief: You can use raw expressions in any part of a Laravel query without restrictions.

Reality: Raw expressions are supported in many places but not everywhere; some query builder methods do not accept raw expressions directly.

Why it matters: Trying to use raw expressions in unsupported places can cause errors or unexpected behavior.

Quick: Does using raw expressions make your code database-independent? Commit to yes or no.

Common Belief: Raw expressions keep your queries portable across different database systems.

Reality: Raw SQL often uses database-specific syntax, which can break portability if you switch databases.

Why it matters: Relying heavily on raw expressions can lock your app to one database vendor, making future changes costly.

Quick: Do raw expressions always improve query performance? Commit to yes or no.

Common Belief: Using raw expressions always makes queries faster because you write optimized SQL.

Reality: Raw expressions can improve performance if used well, but poorly written raw SQL can hurt performance or cause bugs.

Why it matters: Assuming raw SQL is always better can lead to inefficient queries and harder-to-maintain code.

Expert Zone

1

Raw expressions can be combined with Laravel's parameter bindings to maintain security while writing complex SQL.

2

Using raw expressions inside Eloquent scopes allows reusable custom SQL logic integrated with models.

3

Excessive use of raw expressions can make code harder to read and maintain, so balance is key.

When NOT to use

Avoid raw expressions when Laravel's query builder or Eloquent can express the query clearly and safely. Use raw SQL only for features or optimizations not supported by Laravel. For complex reporting or analytics, consider database views or stored procedures instead.

Production Patterns

In production, raw expressions are often used for database-specific functions like JSON queries, spatial data, or full-text search. They are also used to optimize queries with custom SQL snippets or to call database functions not supported by Laravel's abstraction.

Connections

SQL Injection

Raw expressions relate directly because they can introduce injection risks if misused.

Understanding raw expressions deepens awareness of how SQL injection happens and how to prevent it with parameter binding.

Database Abstraction Layers

Raw expressions are an escape hatch from abstraction layers when they are insufficient.

Knowing when and how to bypass abstraction layers helps balance developer productivity with full control.

Natural Language Commands

Both raw expressions and natural language commands involve translating human intent into precise instructions.

Seeing raw expressions as precise instructions helps appreciate the challenge of converting flexible input into exact commands.

Common Pitfalls

#1 Inserting user input directly into raw SQL causing SQL injection.

Wrong approach:

DB::table('users')->whereRaw("name = '$userInput'")->get();

Correct approach:

DB::table('users')->whereRaw('name = ?', [$userInput])->get();

Root cause:

Misunderstanding that raw expressions do not automatically escape input.

#2 Using raw expressions for simple queries that Laravel can build safely.

Wrong approach:

DB::table('users')->whereRaw('age > 18')->get();

Correct approach:

DB::table('users')->where('age', '>', 18)->get();

Root cause:

Not realizing Laravel's query builder already handles common queries safely and clearly.

#3 Writing database-specific SQL in raw expressions without considering portability.

Wrong approach:

DB::table('users')->whereRaw('STR_TO_DATE(created_at, "%Y-%m-%d") = CURDATE()')->get();

Correct approach:

Use Laravel's date functions or database-agnostic queries when possible.

Root cause:

Ignoring that raw SQL can lock code to one database vendor.

Key Takeaways

Raw expressions let you write exact SQL inside Laravel queries when needed.

They bypass Laravel's automatic query building and escaping, so use them carefully.

Always use parameter bindings with raw expressions to prevent SQL injection.

Raw expressions work with both query builder and Eloquent ORM for flexibility.

Overusing raw expressions can hurt code readability and portability, so balance is important.