Run Raw Queries Securely In Laravel

Sometimes there might be a requirement to run raw queries in Laravel. In this article, you will learn how to run raw queries in Laravel and how to prevent SQL injection with it.

---

Prerequisites

Working Laravel application and zeal to learn :)

Here I will demo simple examples which must give you insights how to run the complex queries

---

Running RAW Queries Syntax

To run raw queries you use DB::select() function with the following syntax

\DB::select("
    /** Your Query */
");

---

Problem (SQL Injection)

You might have run the raw queries as follows.

Example: I want to fetch the posts which has author & published_on is greater than some date

$author         = 'Channaveer';
$publishedDate  = '2020-02-01';

$post = \DB::select("
            SELECT 
                id, title, body, author, published_on
            FROM
                posts
            WHERE
                published_on >= $publishedDate
                    and
                author = $author
        ");

Nothing wrong in the above query. Everything works fine.

What about security? Security plays a vital role in your application, even if you run the application for internal team purpose there might arise a requirement to publish it somewhere to access remotely.

Observe published_on >= $publishedDate & author = $author which is hardcoded, this is the real criminal which is prone to SQL Injection and exploiting your database.

---

Solution (Positional Bindings & Named Bindings)

Positional Bindings ( ? )

In positional bindings we will use ? as placeholders for values and then pass these values in the 2nd parameter as regular array and must follow the same sequence of positions.

NOTE: What I meant with same sequence of positions is in the following example published_on comes first in query so does the $publishedDate in 2nd parameter array and then author & $author respectively.

$author         = 'Channaveer';
$publishedDate  = '2020-02-01';

$post = \DB::select("
            SELECT 
                id, title, body, author, published_on
            FROM
                posts
            WHERE
                published_on >= ?
                    and
                author = ?
        ", [ $publishedDate, $author ]);

---

Named Binding ( : )

In named bindings we use : with name as placeholder. For example :publishedOn. Here no need to follow 1st and 2nd sequence order as earlier

$author     = 'Channaveer';
$publishedDate = '2020-02-01';
$post = \DB::select("
      SELECT 
        id, title, body, author, published_on
      FROM
        posts
      WHERE
        published_on >= :publishedDate
          and
        author = :author
    ", [ ":publishedDate" => $publishedDate, ":author" => $author ]);

This helps you to overcome the SQL Injections.

---

FUN PART

You can run your CRUD operations in \DB:select() function. But its highly not advised to do so. As Laravel has given DB:select DB::insert DB::update DB::delete DB::statement for it

---

CRUD OPERATIONS ( DB::select(), DB::update(), DB::insert(), DB::delete(), DB::statement() )

Tip: I usually prefer Named Bindings with :name as it will be more clear and maintainable in future too without much hassle

Fetch Details - DB::select()

To fetch any details from database you use this function as you saw earlier. This returns array of results.

$author     = 'Channaveer';
$publishedDate = '2020-02-01';
$post = \DB::select("
      SELECT 
        id, title, body, author, published_on
      FROM
        posts
      WHERE
        published_on >= :publishedDate
          and
        author = :author
    ", [ ":publishedDate" => $publishedDate, ":author" => $author ]);

---

Insert Details - DB::insert()

To insert in to your database table you use this function. This takes query in first parameter and values in second parameter

$post = \DB::insert("
            INSERT INTO 
                posts
                    (title, body, author, published_on)
            VALUES
                (:title, :body, :author, :published_on)
        ", [ 
                ":title"        => request('title'),
                ":body"         => request('body') , 
                ":author"       => session()->get('user_details')->id,
                ":published_on" => request('published_on')
        ]);

---

Update Details - DB::update()

To update the existing records use the following way. update returns number of rows affected.

$post = \DB::update("
        UPDATE 
            posts
        SET
            title      = :title,
            body      = :body,
            published_on  = :published_on
        WHERE
            id = :id
    ", [ 
        "id"      => $id
        ":title"    => request('title'),
        ":body"     => request('body') , 
        ":published_on" => request('published_on')
    ]);

---

Delete Details - DB::delete()

To delete any record from the database use the following way. delete will return no of rows affected.

$post = \DB::delete("
        DELETE
        FROM 
            posts
        WHERE
            id = :id
    ", [ 
        "id"      => $id
    ]);

---

Generic Statements - DB::statement()

Some queries don't return any results, so to run these generic statements use this function.

\DB::statement("DROP TABLE posts");

---

Conclusion

Hope you really like this article, if so then please share with your friends.

Articles For You

Setup AMP (Accelerated Mobile Pages) In PHP Laravel

Generate Sitemap in PHP Laravel

Add Analytics To AMP (Accelerated Mobile Pages) HTML Pages

Facebook Login With PHP Laravel Socialite

@stack @push and @prepend In Laravel Blade

Integrate Google Translate Into Your Website

Factories To Speed Up Test-Driven Development In Laravel

Sass or SCSS @function vs @mixin

Relationship Table Data With Route Model Binding In Laravel

Laravel App Deployment Checks

Getting Started With AMP (Accelerated Mobile Pages)

Increase Session Timeout In Laravel

GitHub Login With PHP Laravel Socialite

Run Raw Queries Securely In Laravel

Foreign Key Migrations Simplified In Laravel 7.x