Can you provide examples of secure raw queries in Laravel

Secure Raw Queries in Laravel

Laravel supports raw SQL queries through methods like `DB::raw()`, `selectRaw()`, `whereRaw()`, `orderByRaw()`, and `DB::statement()`. However, raw queries are inherently risky because Laravel cannot automatically protect them against SQL injection. To write secure raw queries, you must use parameter binding and avoid directly injecting user input into the query string.

1. Using Parameter Binding with Raw Methods

Instead of concatenating variables directly into raw SQL strings, use placeholders (`?`) and pass the actual values as bindings. This lets Laravel safely escape the inputs.

Example: Secure `whereRaw` query

php
$id = 5; // This could come from user input

$post = DB::table('posts')
    ->select('postTitle', 'postBody')
    ->whereRaw('id = ?', [$id])
    ->first();

Here, the `?` acts as a placeholder, and `$id` is passed as a parameter. Laravel will bind and escape `$id` properly, preventing SQL injection[1].

2. Validating Input Before Using in Raw Queries

Always validate user inputs before using them in queries. For example, if an ID should be numeric, validate it before running the query:

php
$validator = Validator::make(['id' => $id], [
    'id' => 'required|numeric'
]);

if ($validator->fails()) {
    abort(404);
} else {
    $post = DB::table('posts')
        ->select('postTitle', 'postBody')
        ->whereRaw('id = ?', [$id])
        ->first();
}

This ensures only valid data reaches the query[1].

3. Using `DB::statement` with Bindings

When running raw SQL statements (e.g., updates), use parameter binding instead of string concatenation:

php
$username = 'user123';
$newPassword = 'newpass';

DB::statement('UPDATE users SET password = ? WHERE username = ?', [$newPassword, $username]);

This prevents injection attacks that could otherwise allow malicious users to alter the query logic[1].

4. Using `DB::raw()` Safely in Query Builder

`DB::raw()` allows embedding raw expressions, but you must ensure no untrusted input is concatenated inside:

php
$categoryId = 10;

$results = DB::table('sub_category as sc')
    ->leftJoin('products as p', 'p.sub_cat_id', '=', 'sc.sub_cat_id')
    ->where('sc.category_id', '=', $categoryId)
    ->whereNotNull('p.sub_cat_id')
    ->select('p.*', 'sc.*', DB::raw('sc.sub_cat_id AS sub_cat_id'))
    ->groupBy('sc.sub_cat_id')
    ->get();

Here, `$categoryId` is safely passed via `where()` (which uses parameter binding internally), and `DB::raw()` is used only for static SQL parts[3].

Summary of Best Practices

- Avoid raw queries unless necessary.
- Use parameter binding with `?` placeholders and pass bindings as arrays.
- Validate all user inputs before using them in queries.
- Prefer Eloquent ORM or Laravel's query builder methods, which handle binding automatically.
- Never concatenate raw user input directly into raw SQL strings.
- When using `DB::statement()`, always use bindings, not string concatenation.

These practices ensure raw queries in Laravel are secure against SQL injection while allowing the flexibility raw SQL sometimes requires[1][3][5][6].

Citations:
[1] https://www.stackhawk.com/blog/sql-injection-prevention-laravel/
[2] https://fideloper.com/laravel-raw-queries
[3] https://stackoverflow.com/questions/40340481/secure-laravel-query-which-use-dbraw
[4] https://laracasts.com/discuss/channels/eloquent/raw-queries-and-sql-injection
[5] https://www.youtube.com/watch?v=99Yit7WitxY
[6] https://brightsec.com/blog/sql-injection-in-laravel-everything-you-need-to-know/
[7] https://escape.tech/blog/laravel-sql-injection-guide/
[8] https://stackoverflow.com/questions/33207450/what-is-advantage-of-using-laravel-query-builder-over-raw-queries