Good content takes time and effort to come up with.

Please consider supporting us by just disabling your AD BLOCKER and reloading this page again.







Run Raw Queries Securely In Laravel


Share On     Share On WhatsApp     Share On LinkedIn


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.







Author Image
AUTHOR

Channaveer Hakari

I am a full-stack developer working at WifiDabba India Pvt Ltd. I started this blog so that I can share my knowledge and enhance my skills with constant learning.

Never stop learning. If you stop learning, you stop growing