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.







How To Run Raw Queries Securely In Laravel


LARAVEL DATABASE CRUD PHP QUERY BASIC SECURITY PREVENT SQL INJECTION LARAVEL
03rd May 2020 6 mins read
Share Article     Share On WhatsApp   Buy Me Coffee | StackCoder Buy Coffee


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.


WHATS NEXT?


You might be interested in reading few of my other articles


Laravel 7.x Multiple Database Connections, Migrations, Relationships & Querying

How To Install Apache Web Server On Ubuntu 20.04 / Linux & Manage It

How To Create / Save / Download PDF From Blade Template In PHP Laravel

How To Add Free SSL Certificate In cPanel With ZeroSSL & Certbot

How To Securely SSH Your Server & Push Files With FileZilla

How To Push Files To CPanel / Remote Server using FTP Software FileZilla

How To Install Linux, Apache, MYSQL, PHP (LAMP Stack) on Ubuntu

How To Cache Static Files With NGINX Server

Redirect www to a non-www website or vice versa

How To Create Free SSL Certificate With Lets Encrypt/Certbot In Linux (Single / Multiple Domains)

How To Install Linux, NGINX, MYSQL, PHP (LEMP Stack) on Ubuntu

PHP Built-In Web Server & Testing Your Development Project In Mobile Without Any Software

How To Do Google reCAPTCHA Integration In PHP Laravel Forms


Happy Coding :)








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