Use Laravel Eloquent queries alone in your PHP project to avoid SQL injection

OWASP (Open Web Application Security Project) is a project that records the current threats to web applications. I have been following their site and I see some similarities in the reports from 2010, 2013 and 2017, with SQL or other types of injection threats at the top of the list.

This is a serious problem.

It will cause you to go bankrupt, so this matter is related to life and death, and your organization should focus on dealing with this type of problem to avoid its occurrence.

What is injection?

The so-called injection means that the data is not filtered and untrustworthy content is written directly into the system interpreter. This behavior will lead to SQL injection into the site. What's worse, the attacker may will gain full permissions on the system.

For example:

Look at the following malicious query statement. It will put the SQL statement containing malicious behavior in the $name variable, and then allow the user to pass POST method is passed to the PHP script, so as to achieve the ultimate purpose of using the incoming malicious code to carry out attacks.

// 将恶意代码，DROP TABLE 写入 $name 变量
$name = "Mark';DROP TABLE users; -- ";\ $query = "SELECT * FROM users WHERE name='$name'";

After parsing by the PHP script, this will eventually generate a SQL statement like this:

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

As you might guess, the above statement will delete the entire users data table from the database.

As Yoda said:

This is too dangerous, yes, too dangerous.

How to prevent malicious injection into PHP applications?

First of all, nothing is actually injected into the database. This error is just due to the incorrect formatting of the query statement. The solution is simple, as long as you format the SQL statement correctly, or directly process the query statement and data separately.

How to do it? Use parameterized queries to format data and separate query statements from data.

Using parameterized queries can ensure that the program is away from injection risks.

Examples are as follows:

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

In addition, there is a safe way to use ORM (Object Relational Mapping) or query builder in the project.

What I want to recommend is Eloquent, which is also used by the famous PHP framework Laravel. Next, I will teach you how to install and use it, which can help us format data to effectively avoid injection hazards.

Installing Eloquent

Preparation

Please make sure you have installed PHP and Composer.

Official start

It is best to install the ORM at the beginning of the project.

Suppose we want to build a blog application, including a posts table and a users table.

Initialization configuration

The first thing to do is to create the composer.json file for the program. You can run composer init on the terminal and follow the prompts on the terminal.

#When it asks you to define dependencies, write illuminate/database . The final output should look like the one shown in the image above. Now you can install the corresponding dependencies in the project by running composer install .

Or, if you already have the composer.json file, you can directly enter composer require illuminate/database in the terminal to install the corresponding dependencies.

Now we need to create the start.php file in the root directory of the application and paste the following code into the file. I'll explain their role below.

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 能在全局使用
 $capsule->setAsGlobal();
// Setup the Eloquent ORM.
 $capsule->bootEloquent();

In the first line we need to introduce the vendor/autoload.php file. In this way we can load all packages in the vendor directory.

Then we introduce use Illuminate\Database\Capsule\Manager as Capsule and give it an alias so that we can use eloquent.

Next, we create a Capsule object and initialize our database connection, as above bootEloquent().

Now, obviously the first thing we need to do is create a database named test. Please make sure you enter the correct username and password locally.

Migrations / Data Migration

One of the biggest benefits of using Eloquent is that you can use migrations.

If you don’t understand what migrations are, you can read the explanation below:

Migration is a way to create data tables through PHP code.

Create migration in the migrations.php file:

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();
});

The above code creates two data tables through the Capsule class, one is the users table and the other is the posts table, and respectively Field names are defined for them.

Run this file. If you see a white screen, it means that migrations has been run successfully. Now you can open the database to see if these two tables have been generated.

Models

Now, the only thing to do is to create the Model class corresponding to the data table.

用了 Eloquent，你就可以在 Model 类里操作相应的数据表，执行查询语句了。

创建一个 Models 文件夹，然后在其中分别创建 User.php 和 Post.php 文件：

namespace Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
   /**
    * 对应的数据表
    *
    * @var string
    */
    protected $table = "users";
   /**
    * 允许插入的字段
    *
    * @var array
    */
    protected $fillable = [
        'name', 'email', 'password'
    ];
   /**
    * 需要被隐藏的字段
    *
    * @var array
    */
    protected $hidden = [
        'password', 'remember_token',
    ];
   /*
    * 给 User 类添加方法
    *
    */
    public function posts()
    {
        return $this->hasMany(Post::class, 'created_by');
    }
}
And
namespace Models;
use Illuminate\Database\Eloquent\Model;
class Post extends Model
{
   /**
    * 对应的数据表
    *
    * @var string
    */
    protected $table = "posts";
  /**
   * 允许插入的字段
   *
   * @var array
   */
   protected $fillable = [
       'title', 'body', 'created_by'
   ];
 }
在 composer.json 文件中加入如下代码，以确保上面创建的类文件能够被自动加载。
"autoload": {
    "classmap": [
        "Models" // Folder where all your models are
     ]
}

然后执行 composer dump-autoload。

通过 Eloquent 操作数据库

基本大功告成了。 测一下吧，在根目录创建 index.php 文件，添加如下代码：

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);

如你所见，用 Eloquent 操作数据库就是这么简单。除此之外，Eloquent 还提供了很多方法供你使用，而且很安全。

结语:

Eloquent 就像是给你的 SQL 查询加了一道安全层，它可以过滤掉我们在执行 SQL 查询时所犯的错误。如果你想用它，但是又不想安装 Laravel 框架，那么我想你已经从这篇文章中学到了该如何去做。这个优雅的 SQL 助手，将帮助你写出更干净且更安全的代码。

更多PHP相关知识，请访问PHP中文网！

The above is the detailed content of Use Laravel Eloquent queries alone in your PHP project to avoid SQL injection. For more information, please follow other related articles on the PHP Chinese website!