Skip to content
Readerstacks logo Readerstacks
  • Home
  • Softwares
  • Angular
  • Php
  • Laravel
  • Flutter
Readerstacks logo
Readerstacks
Efficient and Secure Way to Write Raw Queries in Laravel

Efficient and Secure Way to Write Raw Queries in Laravel

Aman Jain, July 1, 2024July 1, 2024

Laravel is a powerful and versatile PHP framework that simplifies many aspects of web development, including database interactions. While Laravel’s Eloquent ORM and query builder are incredibly useful, there are times when you need to write raw SQL queries for more complex operations. However, writing raw queries requires a careful approach to maintain efficiency and security. Here’s how to do it right:

1. Understanding When to Use Raw Queries

Before diving into how to write raw queries, it’s important to understand when they are necessary:

  • Complex Queries: When you need to perform operations that are too complex for Eloquent or the query builder.
  • Performance Optimization: When raw queries provide better performance for specific tasks.
  • Legacy Systems: When integrating with legacy systems that require specific SQL commands.

2. Writing Raw Queries in Laravel

In Laravel, you can use the DB facade to execute raw queries. Here’s a basic example:

use Illuminate\Support\Facades\DB;

$users = DB::select('SELECT * FROM users WHERE active = ?', [1]);

For more complex queries, you can use DB::statement for non-select queries:

DB::statement('UPDATE users SET active = 1 WHERE id = ?', [1]);

If you are using multiple variable than don’t forget to add below config in coonfig/database.php
PDO::ATTR_EMULATE_PREPARES => true,

 'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
                PDO::ATTR_EMULATE_PREPARES => true,
            ]) : [],
        ],

3. Ensuring Efficiency

To ensure your raw queries are efficient:

  • Indexing: Ensure that your database tables are properly indexed. This can drastically improve the performance of your queries.
  • Avoid N+1 Queries: Fetch related data in a single query whenever possible to avoid the N+1 query problem.
  • Use Caching: Cache frequently accessed data to reduce the number of database queries.

Example of using caching:

$users = Cache::remember('active_users', 60, function () {
    return DB::select('SELECT * FROM users WHERE active = ?', [1]);
}); 

4. Enhancing Security

Security is paramount when dealing with raw queries. Here are some best practices:

  • Use Bindings: Always use parameter binding to prevent SQL injection attacks. Laravel’s query builder and raw methods support bindings

    $users = DB::select('SELECT * FROM users WHERE email = ?', [$email]);

    Avoid User Input in Raw Queries: Never directly inject user input into your raw queries. Always sanitize and validate the input first.
  • Database Permissions: Restrict database permissions to the minimum required for your application to function. This limits the damage in case of a security breach.

5. Debugging and Logging

Efficiently debug and log your raw queries to keep track of what’s happening in your application:

  • Enable Query Logging: Laravel can log all database queries, which is useful for debugging.php

    DB::listen(function ($query) { Log::info($query->sql); });
  • Use Laravel Telescope: For a more robust solution, use Laravel Telescope to monitor queries and other application activities.

6. Testing

Always test your raw queries thoroughly:

  • Unit Tests: Write unit tests to ensure your raw queries return the expected results.
  • Load Testing: Perform load testing to ensure your queries perform well under high traffic conditions.

Conclusion

Writing raw queries in Laravel can be both efficient and secure if done correctly. By understanding when to use raw queries, ensuring efficiency through indexing and caching, enhancing security through parameter binding and permissions, and thoroughly testing your queries, you can leverage the full power of SQL within your Laravel applications.

Happy coding!

Related

Laravel laravelRaw QuerySecure Way

Post navigation

Previous post
Next post

Related Posts

Php How to fetch Soft Deleted Records in Laravel 9

How to Fetch Soft Deleted Records in Laravel ?

June 17, 2022June 21, 2022

In this article we will learn to fetch soft deleted records in Laravel. In our recent article Use Soft Delete to Temporary (Trash) Delete the Records in Laravel ? we learnt to delete the file without actually deleting from database and sometimes we want to show records that are soft…

Read More
Php How to Send Mail in Laravel Through Sendmail and SMTP

How to Send Mail in Laravel 8 / 9 Through Sendmail and SMTP ?

May 8, 2022August 20, 2022

Email is very common operation of any website like sending an email to users after registration, Send newsletters to users and many more. In this tutorial i will show you to send Mail in Laravel Through Sendmail and SMTP. Laravel provides multiple drivers or services to send mail from different…

Read More
Php How to Permanent Delete Soft Deleted Records in Laravel 9

How to Permanent Delete Soft Deleted Records in Laravel 9 ?

June 18, 2022June 18, 2022

In this article we will learn to Permanent Delete soft deleted records in Laravel. In our recent article How to fetch Soft Deleted Records in Laravel 9 ? and How to Restore Soft Deleted Records in Laravel 9 we learnt to fetch the records from database and Restore the records…

Read More

Aman Jain
Aman Jain

With years of hands-on experience in the realm of web and mobile development, they have honed their skills in various technologies, including Laravel, PHP CodeIgniter, mobile app development, web app development, Flutter, React, JavaScript, Angular, Devops and so much more. Their proficiency extends to building robust REST APIs, AWS Code scaling, and optimization, ensuring that your applications run seamlessly on the cloud.

Categories

  • Angular
  • CSS
  • Dart
  • Devops
  • Flutter
  • HTML
  • Javascript
  • jQuery
  • Laravel
  • Laravel 10
  • Laravel 11
  • Laravel 9
  • Mysql
  • Php
  • Softwares
  • Ubuntu
  • Uncategorized

Archives

  • July 2025
  • June 2025
  • May 2025
  • April 2025
  • October 2024
  • July 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • July 2023
  • March 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • June 2021

Recent Posts

  • Mapping Together: The Vibrant Spirit of OpenStreetMap Japan
  • Understanding High Vulnerabilities: A Deep Dive into the Weekly Summary
  • Building a Million-Dollar Brand: The Journey of Justin Jackson
  • Mastering Schedule Management with Laravel Zap
  • The Resilience of Nature: How Forests Recover After Fires
©2023 Readerstacks | Design and Developed by Readerstacks
Go to mobile version