
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.
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.
Working Laravel application and zeal to learn :)
Here I will demo simple examples which must give you insights how to run the complex queries
To run raw queries you use DB::select()
function with the following syntax
\DB::select("
/** Your Query */
");
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.
Observepublished_on >= $publishedDate
&author = $author
which is hardcoded, this is the real criminal which is prone to SQL Injection and exploiting your database.
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 examplepublished_on
comes first in query so does the$publishedDate
in 2nd parameter array and thenauthor
&$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 ]);
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.
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
Tip: I usually prefer Named Bindings with :name
as it will be more clear and maintainable in future too without much hassle
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 ]);
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')
]);
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')
]);
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
]);
Some queries don't return any results, so to run these generic statements use this function.
\DB::statement("DROP TABLE posts");
Hope you really like this article, if so then please share with your friends.
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?
Composer Install v/s Composer Update