How to write Secure Raw SQL in Laravel

Beyond the ORM: Safe Advanced Expressions with selectRaw and DB::raw

While Laravel Eloquent and Query Builder provide expressive methods for performing almost any standard SQL operation, there are times when you need to execute native database functions. For instance, you might need to calculate a distance using the Haversine formula or aggregate several fields using COALESCE() and CASE WHEN expressions or simply sort the results where null values appear at the end using particular database syntax.

Laravel allows you to execute such requests safely using the raw expression feature available in several methods, namely selectRaw(), whereRaw(), havingRaw(), and orderByRaw(). However, one should be very careful while trying to execute raw queries. The main reason is that people often attempt to use raw expressions by concatenating user-supplied values in the query string, accidentally creating potential SQL injection points.

In this tutorial, you will learn how to perform such operations safely using Parameterized Queries and bind user-supplied values using PDO bindings.

Why Do We Need Raw Expressions?

Query builder scopes cannot cover all possible vendor-specific SQL functions that can be used in MySQL, PostgreSQL, SQLite, etc. For example, you may need to:

Calculating geographical distance between latitude and longitude coordinates.

Complex conditional arithmetic (e.g., dynamic tax or commission calculations based on status).

Database-specific date formatting functions like MySQL's DATE_FORMAT() or Postgres's TO_CHAR().

Custom ordering rules (e.g., custom priority sorting or placing NULL values last).

Laravel's raw methods allow you to inject raw SQL fragments directly into specific clauses of your query without having to abandon Eloquent altogether.

The Danger: SQL Injection via String Concatenation

Before looking at the secure approach, let's look at the classic security mistake developers make when using raw methods:

// Highly vulnerable to SQL Injection!
$status = $request->input('status');
$orders = Order::whereRaw("status = '" . $status . "'")->get();

If an attacker sends ' OR 1=1 -- in the status parameter, the resulting query bypasses all filtering logic and dumps every row in your database table.

The golden rule of SQL security in Laravel is simple: Never concatenate or interpolate variables directly inside raw expressions. Always use PDO parameter bindings.

1. Secure selectRaw() with Parameter Bindings

The selectRaw() method accepts two arguments:

The raw SQL string containing positional question mark placeholders (?).

An array of bound values that PDO safely escapes and binds before executing the query.

Real-World Example: Dynamic Discount Calculation

Suppose you want to compute an item's discounted price based on a user-provided promotional percentage, while ensuring the percentage cannot break out of the SQL expression:

namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Request;

class ProductController extends Controller
{
    public function index(Request $request)
    {
        $discountRate = (float) $request->input('discount_rate', 10);

        $products = Product::query()
            ->select('id', 'name', 'price')
            ->selectRaw(
                'ROUND(price - (price * (? / 100)), 2) as discounted_price',
                [$discountRate] // Bound securely via PDO
            )
            ->where('is_active', true)
            ->get();

        return response()->json($products);
    }
}

Because $discountRate is passed in the second parameter array, PDO treats it strictly as a literal value—making SQL injection impossible.

2. Calculating Distances with Native Functions (Haversine Formula)

Geospatial calculations are one of the most common reasons developers turn to selectRaw() and havingRaw(). Here is how to find locations within a given radius using the Haversine formula securely:

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;

class Store extends Model
{
    // Scope a query to find stores within a given radius (in km).
    public function scopeNearLocation(Builder $query, float $latitude, float $longitude, float $radiusInKm = 50): Builder
    {
        $haversine = '(6371 * acos(cos(radians(?)) * cos(radians(latitude)) * cos(radians(longitude) - radians(?)) + sin(radians(?)) * sin(radians(latitude))))';

        return $query
            ->select('id', 'name', 'latitude', 'longitude')
            ->selectRaw("{$haversine} AS distance", [$latitude, $longitude, $latitude])
            ->havingRaw('distance <= ?', [$radiusInKm])
            ->orderBy('distance', 'asc');
    }
}

Notice how every single dynamic user coordinate is bound using ? placeholders:

// Controller call
$stores = Store::nearLocation(22.3072, 73.1812, 25)->get();

3. Advanced Ordering with orderByRaw()

Standard orderBy('column', 'desc') works well for basic sorting, but real-world requirements often demand custom ordering rules.

Custom Status Prioritization (FIELD / CASE)

Suppose you want to sort support tickets so that urgent tickets always appear first, followed by open, and lastly resolved:

use App\Models\Ticket;

$tickets = Ticket::query()
    ->orderByRaw(
        "CASE 
            WHEN status = ? THEN 1 
            WHEN status = ? THEN 2 
            WHEN status = ? THEN 3 
            ELSE 4 
        END",
        ['urgent', 'open', 'resolved']
    )
    ->latest('created_at')
    ->get();

Pushing NULL Values to the Bottom

By default in MySQL, ORDER BY ASC places NULL values at the very top. To push NULL values to the bottom regardless of sort order, use orderByRaw():

// Pushes null published_at dates to the end
$articles = Article::query()
    ->orderByRaw('published_at IS NULL, published_at DESC')
    ->paginate(15);

4. Protecting Column Names: Whitelisting Dynamic Inputs

Parameter binding protects data values (strings, numbers, dates), but PDO cannot bind SQL identifiers like column names or table names.

Say you want to let users sort the table by choosing a column from a drop-down list, passing the column name as the sort field to an order clause is dangerous:

// PDO cannot bind identifiers via '?'
$column = $request->input('sort_by');
$query->orderByRaw("? DESC", [$column]); // Will NOT work as expected in SQL

To safely handle dynamic column sorting, always use an explicit whitelist:

$allowedColumns = [
    'name'       => 'name',
    'price'      => 'price',
    'created_at' => 'created_at',
];

$sortBy = $allowedColumns[$request->input('sort_by')] ?? 'created_at';
$direction = strtolower($request->input('direction')) === 'asc' ? 'asc' : 'desc';

$products = Product::orderBy($sortBy, $direction)->paginate(20);

Raw Methods Cheat Sheet

Method	Use Case	Binding Syntax
`selectRaw()`	Calculated columns, math functions, date formatting	`->selectRaw('PRICE * ? as total', [$multiplier])`
`whereRaw()`	Complex full-text search, regex, multi-column math	`->whereRaw('DATEDIFF(now(), created_at) > ?', [$days])`
`havingRaw()`	Filtering aggregate results (e.g., `SUM`, `COUNT`)	`->havingRaw('COUNT(*) > ?', [$minCount])`
`orderByRaw()`	Custom priority ordering, handling null placement	`->orderByRaw('FIELD(status, ?, ?) ASC', ['active', 'pending'])`

Important Things to Remember

📌DB::raw() vs selectRaw(): while using ->select(DB::raw('...')) is acceptable, this approach lacks the integrated bindings parameter array. It is preferable to utilize direct methods such as selectRaw($query, $bindings) or whereRaw($query, $bindings)

🔒Cross-database portability: raw SQL statements bind your code to a particular database engine (e. g. MySQL functions like IFNULL() vs Postgres's COALESCE()). If you are considering switching engines or running in-memory SQLite tests, consider using ANSI-standard SQL functions.

⚡Index awareness: wrapping indexed columns inside raw SQL functions (whereRaw('YEAR(created_at) = ?', [2026])) will prevent the database engine from using standard B-tree column indexes, leading to full table scans. Prefer range queries: whereBetween('created_at', [$start, $end])

Conclusion

Raw expressions in Laravel give you power to execute complicated raw SQL statements, making the use of database's full power possible. Using Laravel's query builder makes it easy to write secure queries by separating SQL string and user input, and using PDO parameter binding and column whitelisting.

Thank you for reading this article 😊

For any query, do not hesitate to comment 💬