Sometimes, there may be some requirements that need 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 with Laravel application and enthusiastic learning
- Here I will introduce simple examples that must give you detailed information on how to run complex queries
Running RAW Queries Syntax
To run raw queries, use the DB::select() method with the following syntax:
1 2 3 4 | DB::select(" /** Your Query */ "); |
Problem (SQL Injection)
You must run the raw queries as follows.
For example, I want to get posts that have author and published_on that are larger than a number of days
1 2 3 4 5 6 7 8 9 10 11 | $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 "); |
There is nothing wrong with the query. Everything works well.
What about security issues? Security plays an important role in your application, even if you run the application for internal purposes, there may be a need to publish it somewhere for remote access.
Observe published_on >= $publishedDate and author = $author are hardcoded, this is the real vulnerability where SQL Injection and your database are vulnerable.
Solution (Positional Bindings & Named Bindings)
Positional Bindings (?)
At the binding position we will use ? make room for the values and then convert these values in the second argument to a regular array and must follow the same sequence of positions.
NOTE: What I mean is that for the same sequence of locations in the example,
published_onappears first in the query, so$publishedDateappears first in the second argument array and then the correspondingauthor&$authorapplication.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $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 is placeholder. For example :publishedOn . There is no need to be in the first and second order as before
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $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 avoid SQL injection.
FUN PART
You can run your CRUD operations in the DB:select() . But should not do so. Because Laravel has given the 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 Bindings named with
:namebecause it will be more visible and maintainable in the future without much hassle.
Fetch Details – DB :: select ()
To retrieve any details from the database, you use this method as you saw earlier. This returns the resulting array.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | $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 into your database table, you use this method, which takes the query in the first parameter and the values in the second parameter:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $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 existing records we use the following. update returns the number of rows affected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $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 records from the database use the following way. delete will return the number of rows affected.
1 2 3 4 5 6 7 8 9 10 11 | $post = DB::delete(" DELETE FROM posts WHERE id = :id ", [ "id" => $id ] ); |
Generic Statements – DB :: statement ()
Many queries do not return results, so running general statements will use this method.
1 2 | DB::statement("DROP TABLE posts"); |
Conclude
Hope you really enjoyed this article, if so share it with your friends.
Document: https://stackcoder.in/posts/how-to-run-raw-queries-securely-in-laravel