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

Simple Way To Create Resourceful API Controller In Laravel

Add Analytics To AMP (Accelerated Mobile Pages) HTML Pages

What Is Composer? How Does It Work? Useful Composer Commands And Usage

PHP extension ext-intl * is missing

Lazy Load Images In Chrome With This Simple Trick

SummerNote WYSIWYG Text Editor

Install Letsencrypt SSL Certificate for RabbitMQ Server and RabbitMQ Management Tool

Supervisor For Laravel Queue Scheduling

Comment And Like System Using Disqus

Debugging Laravel Queue Email Issues

What Is Laravel Resourceful Controllers?

Prevent Multiple Form Submits

Composer Install v/s Composer Update

Free SSL Certificate For CPanel

Free SSL Certificate In cPanel With ZeroSSL & Certbot