Use Laravel Eloquent Query Builder In Any PHP Project

OWASP (Open Web Application Security Project) is a project that notes down the current threats to a web application. And I have been researching on their site and I have found this similarity in their 2010, 2013 and 2017 report that, SQL Injection or any other type of Injection is number 1 on this list, every time.

And that’s a part to worry.

This can cause you to get out of business, so this is pretty serious and your organisation should take care of the this issues and prevent yourself from it.

So, What’s Injection?

It is injection of untrusted content into the system’s interpreter directly, without any filter of the data, which can lead to SQL injection into sites, and on the worse day, could give the attacker full access to your system.

Example:

See the query below, for example, it’s malicious, especially when your allowing users to POST their own name to the PHP script that would ultimately consume it.

$name = "Mark';DROP TABLE users; -- ";
$query = "SELECT * FROM users WHERE name='$name'";

This will evaluate into:

SELECT * FROM users WHERE name='Mark';DROP TABLE users; -- '

The above evaluated query would, yep you guessed it, drop the full users table!

As Yoda would say:

Pretty harmful, it is.

How to prevent Injection in PHP applications?

First of all, there is nothing like Injection, it is just not properly formatted syntax, which is harmful and erroneous. And it can simply be solved if you use properly formatted SQL syntax or better separate query from the data.

How? By using prepared statements. Prepared statements takes responsibility of formatting the data and separate the query from the data.

And we can say, that, if we use prepared statements for our queries, we can be sure that, we are away from injection.

An example of using Prepared statement:

$statement = $db->prepare('SELECT * FROM table WHERE id = ? and name = ? ');
$statement->execute([1, "Mark"]);

But this is not only the way to be safe, there is even one more way to get safe, by using ORM (Object Relationship Mapping) or a query builder for your application.

I will show you a way to have query builder (Eloquent), which is being used by famous PHP framework, Laravel and install it in your application and then use it to execute your queries, which will ensure the formatting of syntax and data so it never fails.

You can learn more about Eloquent on laravel docs.

Installing Eloquent

Pre-requisites

Along with the obvious things such as PHP being installed, you will need to download and install Composer if you haven’t already. Composer is a command line terminal that allows rapid installation of libraries to your projects.

Let’s begin

It is always better to install an ORM when you are just starting out to structure your application.

Let’s assume we want to make a blog application which consists of posts and users.

Initial Configuration

The first thing you want to do is to make composer.json for your application. You can do that by running composer init and follow the instruction on your terminal.

eloquent composer setup screenshot

When it asks to define your dependencies, write illuminate/database . And the final output should look like above. Now you can run composer install to install the dependencies in your project.

Or if you have a composer.jsonalready, you can type composer require illuminate/database to install the dependency.

Now we will create a file named start.php at root of the application and paste the below code into it. I will explain the what it does.

require "vendor/autoload.php";
//If you want the errors to be shown
error_reporting(E_ALL); 
ini_set('display_errors', '1');
use Illuminate\Database\Capsule\Manager as Capsule;
 
 $capsule = new Capsule;
 
 $capsule->addConnection([
    "driver" => "mysql",
    "host" =>"127.0.0.1",
    "database" => "test",
    "username" => "root",
    "password" => "root"
 ]);
//Make this Capsule instance available globally.
 $capsule->setAsGlobal();
// Setup the Eloquent ORM.
 $capsule->bootEloquent();

On the first line, we are requiring vendor/autoload.php which will load all the packages inside vendor available to us across whole of our application.

Then we use use Illuminate\Database\Capsule\Manager as Capsule, which will let us initialize eloquent.

Next, we make an object of Capsule and define our DB credentials their as above and bootEloquent() .

Now, the first thing you should do, as it is obvious, to make the database named test and make sure to type your username and password correct.

Migrations

The best thing about Eloquent is you can make migrations as well.

For those who don’t know what migrations are:

A migration is a way to make the tables in your DB by writing PHP code.

We will create migration for our blog in migrations.php and add our code like below.

require "start.php";
use Illuminate\Database\Capsule\Manager as Capsule;
Capsule::schema()->create('users', function ($table) {
   $table->increments('id');
   $table->string('name');
   $table->string('email')->unique();
   $table->string('password');
   $table->timestamps();
});
Capsule::schema()->create('posts', function ($table) {
   $table->increments('id');
   $table->string('title');
   $table->text('body');
   $table->integer('created_by')->unsigned();
   $table->timestamps();
});

You can see how self explanatory above code is, by using Capsule, you are creating a schema named users and posts and defining their columns in the closure.

And for running this, go to localhost\your-project-name\migrations.php and if you see a blank screen, that means the migrations ran successfully and you can check that in your db.

php myadmin models example

Models

Now, the only thing remaining to do is to create models to accompany the tables.

Using Eloquent, you will make queries by the model as model will be the entity connected to the relevant table in the DB.

Make a folder named Models and create two files User.php and Post.phplike below.

namespace Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
   /**
    * The database table used by the model.
    *
    * @var string
    */
    protected $table = "users";
   /**
    * The attributes that are mass assignable.
    *
    * @var array
    */
    protected $fillable = [
        'name', 'email', 'password'
    ];
   /**
    * The attributes that should be hidden for arrays.
    *
    * @var array
    */
    protected $hidden = [
        'password', 'remember_token',
    ];
   /*
    * Get Todo of User
    *
    */
    public function posts()
    {
        return $this->hasMany(Post::class, 'created_by');
    }
}

And

namespace Models;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
   /**
    * The database table used by the model.
    *
    * @var string
    */
    protected $table = "posts";
  /**
   * The attributes that are mass assignable.
   *
   * @var array
   */
   protected $fillable = [
       'title', 'body', 'created_by'
   ];
 }

You need to make sure, these classes are loaded in your application by adding below to your composer.json.

"autoload": {
    "classmap": [
        "Models" // Folder where all your models are
     ]
}

Then run, composer dump-autoload to autoload files.

Working with the DB using Eloquent

This is it. But you need to test, so we will create index.php in our root folder and paste the below.

require "start.php";
use Models\User;
use Models\Post;
User::create(
 [
  'name' => 'Mark Mike',
  'email' => 'temp-email-1@mark.com',
  'password' => '1234'
 ]
);
Post::create(
 [
  'title' => 'New Blog Post',
  'body' => 'New Blog Content',
  'created_by' => 1
 ]
);
print_r(User::all());
print_r(Post::all());
print_r(User::find(1)->posts);

You can see how easily you can create entries using Eloquent. Plus, you have all the funky methods of Eloquent which can let you do anything and everything you were doing anyway, but secure.

Conclusion:

Eloquent is just a secure layer over your SQL queries and it will prevent us from making mistakes in our queries and handle everything. If you want to use it, and don’t want the additional bloat of the Laravel framework, then this tutorial shows you exactly how to do it. Reap the benefits from this elegant SQL-helper library in both cleaner code and security. Thanks for reading, until next time, catch me on twitter @bvipul9523

 

Interesting Links
Use Laravel Eloquent Query Builder In Any PHP Project
Posted in PHP

You May Also Like

Leave a Reply

Your email address will not be published.