Can you provide an example of using parameterized queries in Laravel

Example Using Laravel Query Builder

The most common way to write parameterized queries in Laravel is by using the `where` method of the query builder. Laravel automatically binds the parameters for you:

php
$users = DB::table('users')
    ->where('name', $name)  // $name is safely bound as a parameter
    ->get();

Here, `$name` is the parameter value that Laravel binds securely, preventing SQL injection. You don't need to manually sanitize or escape it[3][8].

You can also specify operators explicitly:

php
$users = DB::table('users')
    ->where('votes', '>=', 100)
    ->get();

For more complex conditions involving raw SQL, you can use `whereRaw` with bindings:

php
$orders = DB::table('orders')
    ->whereRaw('price > IF(state = "TX", ?, 100)', [1.5])
    ->get();

In this case, the `?` placeholder is replaced by the value `1.5` safely[3][8].

Example Using Raw SQL with Named Bindings

If you prefer raw SQL, Laravel's `DB::select` method supports named bindings:

php
$sql = "
    SELECT o.id AS oid, o.name, o.fields
    FROM projects p
    JOIN tasks t ON t.project_id = p.id
    LEFT JOIN commands c ON c.task_id = t.id
    LEFT JOIN objects o ON o.id = c.object_id
    WHERE p.id = :pid
    GROUP BY o.id, o.name
    ORDER BY o.name
";

$objects = DB::select($sql, ['pid' => $projectId]);

Here, `:pid` is a named parameter bound to the value of `$projectId` securely[6].

Using Parameterized Queries in Query Builder with Bindings

If you want to avoid raw SQL and use the query builder for the above query, you can add a `where` clause that automatically binds parameters:

php
$objects = DB::table('projects as p')
    ->select('o.id as oid', 'o.name', 'o.fields')
    ->join('tasks as t', 't.project_id', '=', 'p.id')
    ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
    ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')
    ->where('p.id', $projectId)  // parameter bound here
    ->groupBy('o.id', 'o.name')
    ->orderBy('o.name')
    ->get();

Laravel automatically binds `$projectId` as a parameter in the generated SQL, ensuring safety against injection[6][3].

Summary

- Use `where(column, value)` or `where(column, operator, value)` for simple parameterized queries.
- Use `whereRaw` with bindings for complex raw SQL conditions.
- Use named bindings in raw SQL with `DB::select` for explicit parameter binding.
- Laravel handles the parameter binding automatically, so you don't need to sanitize inputs manually.
- Avoid allowing user input to specify column names or SQL keywords to prevent injection vulnerabilities[3][8].

This approach ensures your Laravel applications are secure and safe from SQL injection attacks while maintaining readable and maintainable code.

Citations:
[1] https://stackoverflow.com/questions/35960535/laravel-query-builder-binding-parameters-more-than-once
[2] https://laracasts.com/discuss/channels/eloquent/how-to-parameterized-a-query-using-like
[3] https://laravel.com/docs/12.x/queries
[4] https://stephenreescarter.net/why-parameterised-queries-are-important/
[5] https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html
[6] https://stackoverflow.com/questions/79541139/parameterized-query-in-laravel-query-builder
[7] https://escape.tech/blog/laravel-sql-injection-guide/
[8] https://larasec.substack.com/p/security-tip-parameterised-queries