LaravelのDB::rawのエスケープとSQLインジェクション

SQL Injectionとは?
->アプリケーションが想定しないSQL文を実行させること

公式: https://readouble.com/laravel/6.x/ja/queries.html

rawメソッドはクエリを文字列として挿入するため、SQLインジェクションの脆弱性を生まないように十分気をつけてください。

DB::rawメソッド使用時にはエスケープされない。

 User::where(DB::raw('CONCAT(last_name, first_name)'), 'like', '%'.$text.'%')->get();

mysqlのlike演算子では、% と _ 特別な意味を持つが、DB::rawではエスケープされないので、$textに’%’や’_’が入っていた場合など、SQLインジェクションの脆弱性を生む。
$textの値は、¥%、¥¥、¥_と言うようにエスケープしないといけない。

以下のような使い方の場合は問題なしか。

User::select(DB::Raw('concat(last_name, first_name) as name'), 'id')

Laravel 6.x softdeleteしたユーザ情報を取得

softdeleteしたユーザ情報を取得したい場合は、withTrashed();をコントローラやモデルで付与する。

### controller

User::withTrashed()->get();

### belongsToの場合

public function user(){
        return $this->belongsTo('App\User')->withTrashed();
    }

このようにすれば、わざわざViewで条件処理を加えなくて済む。

{{ $hoge->user ? $hoge->user->name : 'ユーザ削除済' }}

### User::query()の場合
-> 後ろにつける

$query = User::query()->withTrashed();

User::withTrashed()->query();とするとエラーになるので注意が必要

条件に一致するcount

countの条件処理は、count関数の中ではなく、前で行う

$count =  Hoge::where('user_id', $id)->whereMonth('time', '=', date('m'))->count('apply_id' == 2);

$count =  Hoge::where('user_id', $id)->whereMonth('time', '=', date('m'))->where('apply_id', 2)->count();

公式: https://readouble.com/laravel/5.8/ja/queries.html#aggregates
公式ドキュメントや検索に引っかからない時はデバッグしながらやるしかありません。

Laravel 6.x カラムを結合してCollectiveでselect文を作る書き方

– last_name, first_nameを結合して、bladeのselect文で選択できるようにしたい。
— 以下のようなpluckだと、引数は2つだけしか取得できないので、上手く行かない。

$names = User::pluck('last_name', 'first_name', 'id')->all();

## solution
concatでカラムの値を繋げてあげる。
controller

use DB;
// 省略
$users = User::select(DB::Raw('concat(first_name, last_name) as name'), 'id')->pluck('name','id');
dd($users);

blade

{!! Form::select('name', $users, null, ['class' => 'form-control','placeholder'=>'選択してください']) !!}

OK🤩

belongsToのテーブルを検索するときのwhereHas

– 複数の入力フォームから、ユーザの入力値に応じて、belongsToのテーブルも検索したい

### blade

{!! Form::open(['method'=>'POST', 'action'=>'SearchController@index']) !!}
 // 省略
{!! Form::close() !!}

### model

public function user(){
        return $this->belongsTo('App\User');
    }

### route

Route::match(['get','post'],'/search', 'SearchController@index');

### controller
– Data::query()->whereHas(‘user’, function($q){})で検索する
– 検索時にユーザ入力の変数で検索したい場合は、whereHas(‘user’, function($q) use($inputs){} として、渡す

public function index(Request $request){
 if($request->isMethod('post')){
 $inputs = $request->all();
 $query = Data::query();

 if(!empty($inputs['start'])){
    $query->where('start_at','>=', date('Y-m-d', strtotime($inputs['start'])) );
                }
// 省略

// 名前検索
            if(!empty($inputs['name'])){
                $works = $query->whereHas('user', function($q) use($inputs){
                    $q->where('last_name','like', '%'.$inputs['name'].'%')->orWhere('first_name','like', '%'.$inputs['name'].'%');
                })->orderBy('start','ASC')->paginate(10);
            } else {
                $data = $query->orderBy('start','ASC')->paginate(10);
            }

} else {
            $data = Data::whereMonth('start', '=', date('m'))->orderBy('start','ASC')->paginate(10);
        }
// 省略

whereHas(‘user’, function($q, $inputs){} で上手く行きそうな気がしたのですがエラーに成ります。
入力フォームの設置順に検索するのではなく、DBのリレーション順に検索をかけるので、注意が必要です。

Laravel 確認画面でbelongsToの値を表示する

ユーザの権限テーブルを作り、ユーザ登録画面確認画面でデータベースのリレーションを使って権限テーブルから値を取得して表示させたい時

### usersテーブル作成

Schema::create('users', function (Blueprint $table) {
            // ...省略
            $table->integer('role_id')->index()->unsigned()->nullable();
            // 省略...
        });

### rolesテーブルの作成
$ php artisan make:model Role -m
migrationファイル

public function up()
    {
        Schema::create('roles', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->timestamps();
        });
    }

$ php artisan migrate;
insert into roles (name) values (‘権限A’);
insert into roles (name) values (‘権限B’);

User.php

public function role(){
        return $this->belongsTo('App\Role');
    }

mass assignment
// 省略

### 登録画面
create.blade.php
– LaravelCollectiveでradioボタンを使って権限を選択する

<div class="form-check">
	            				{{Form::radio('role_id', 1, true, ['class' => 'form-check-input'])}}
	            				{!! Form::label('role_id', '権限A', ['class'=>'form-check-label']) !!}
            				</div>
            				<div class="form-check">
	            				{{Form::radio('role_id', 2, null, ['class' => 'form-check-input'])}}
	            				{!! Form::label('role_id', '権限B', ['class'=>'form-check-label']) !!}
            				</div>

### Controllerでの読み込み
UsersController.php
– plunkで、rolesテーブルのidとnameを配列で取得して、入力値と一緒にviewに渡す

use App\Role;
public function confirm(Request $request)
    {
        $roles = Role::pluck('name','id')->all();
        $inputs = $request->all();
        return view('admin.account.confirm', compact('inputs','roles'));
    }

### 確認画面での表示
confirm.blade.php
– 入力画面から渡ってきたrolesテーブルのrole_idの値を表示

{{ $roles[$inputs['role_id']] }}

Laravel OneToMany(hasMany)の 作り込みの流れと注意点

– ログイン系のアプリケーションでは、まずUserを作り、一通りmodel, view, controller, middlewareが出来てから、OneToManyの機能を作り込んでいく
– 主要機能や機能的な優先度から開発するのではなく、データベースのテーブル構造に沿って、開発をしていく
– 先にDBにデータを入れながらコーディングしていく

$ php artisan make:controller –resource AdminPostsController

AdminPostsController.php

public function index()
    {
        //
        return view('admin.posts.index');
    }

/layouts/admin.blade.php

<li>
                                <a href="{{route('admin.posts.index')}}">All Posts</a>
                            </li>

                            <li>
                                <a href="{{route('admin.posts.create')}}">Create Post</a>
                            </li>

$ php artisan make:model Post -m

posts migration file

Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned()->index();
            $table->integer('category_id')->unsigned()->index();
            $table->integer('photo_id')->unsigned()->index();
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });

$ php artisan migrate

Model:Post.php

protected $fillable = [
    	'category_id',
    	'photo_id',
    	'title',
    	'body'
    ];

$ php artisan tinker;
Psy Shell v0.7.2 (PHP 7.1.7 — cli) by Justin Hileman
>>> $post = App\Post::create([‘title’=>’my first post’, ‘body’=>’I love laravel’]);
=> App\Post {#661
title: “my first post”,
body: “I love laravel”,
updated_at: “2019-12-14 20:40:37”,
created_at: “2019-12-14 20:40:37”,
id: 1,
}

AdminPostsController.php

use App\Post;
public function index()
    {
        //
        $posts = Post::all();
        return view('admin.posts.index', compact('post'));
    }

resources/views/posts/index.blade.php

<table class="table table-striped">
		<thead>
		<tr>
		  <th>Id</th>
		  <th>User</th>
		  <th>Category</th>
		  <th>Photo</th>
		  <th>Title</th>
		  <th>body</th>
		  <th>Created</th>
		  <th>Updated</th>
		</tr>
		</thead>
		<tbody>
		@if($posts)
			@foreach($posts as $post)
			<tr>
			  <td>{{$post->id}}</td>
			  <td>{{$post->user_id}}</td>
			  <td>{{$post->category_id}}</td>
			  <td>{{$post->photo_id}}</td>
			  <td>{{$post->title}}</td>
			  <td>{{$post->body}}</td>
			  <td>{{$post->created_at->diffForHumans()}}</td>
			  <td>{{$post->updated_at->diffForHumans()}}</td>
			</tr>
			@endforeach
		@endif
		</tbody>
	</table>

Model:User.php

public function posts(){
        return $this->hasMany('App\Post');
    }

Model:Post.php

public function user(){
    	return $this->belongsTo('App\User');
    }

    public function photo(){
    	return $this->belongsTo('App\Photo');
    }

    public function category(){
    	return $this->belongsTo('App\Category');
    }

> update posts set user_id=10 where id=1;

posts/index.blade.php

<td>{{$post->user->name}}</td>

Role.php

protected $fillable = [
    	'name'
    ];

外部設計やワイヤーフレームだと、ユーザーエクスペリエンスが非常に重要なので、まずユーザーの主要機能を先に書いて管理者ページは後に書いた方が書きやすいが、実装フェーズだとユーザー登録できる管理者から先にコーディングしていく。
他のフレームワークでもそうなのかもしれないが、Laravelの開発ではDBのリレーションが非常に重要なんですね。

設計時と実装時で、作っていく順番が異なるので、気を付けないといけない。主要機能や難易度などから順番に実装するのではなく、DBのリレーション構造順。

Laravel QueryScopeのサンプル

Query Scopeはoperationのショートカット機能

e.g. latest

public function index()
    {
        $posts = Post::latest()->get();

        // return $posts;
        return view("posts.index", compact('posts'));
    }

order by created desc | ascと一緒です。

$posts = Post::orderBy('id', 'desc')->get();

qeuryScopeを自作する
Model: Post.php
public function static scope.${functionName}がコンベンション。${functionName}はcamelCaseで書く

public static function scopeLatest($query){
			return $query->orderBy('id', 'desc')->get();
	}

PostsController

public function index()
    {
        $posts = Post::latest();

        // return $posts;
        return view("posts.index", compact('posts'));
    }

queryのインクルード機能のようなものか。

Polymorphic many to many relation(morphToMany)

The last of laravel database relation.

$ php artisan make:model Post -m
$ php artisan make:model Video -m
$ php artisan make:model Tags -m

Schema::create('videos', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });

$ php artisan make:model Taggable -m

Schema::create('taggables', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('tag_id');
            $table->integer('taggable_id');
            $table->string('taggable_type');
            $table->timestamps();
        });

$ php artisan migrate

Post.php, Video.php

    protected $fillable = ['name'];
    public function tags(){
    	return $this->norphToMany('App\Tags', 'taggable');
    }

Tags.php

class Tags extends Model
{
    protected $fillable = ['name'];
}

insert into tags (name) values (‘php’);
insert into tags (name) values (‘ruby’);
model name間違えた。。
alter table taggables change column tag_id tags_id int;

Route::get('/create', function(){

	$post = Post::create(['name'=>'my frist post']);
	$tag1 = Tags::findOrFail(1);
	$post->tags()->save($tag1);

	$video = Video::create(['name'=>'video.now']);
	$tag2 = Tags::findOrFail(2);
	$video->tags()->save($tag2);

});

read

Route::get('/read', function(){

	$post = Post::findOrFail(9);
	foreach($post->tags as $tag){
		echo $tag;
	}
});

update

Route::get('/update', function(){

	$post = Post::findOrFail(9);
	foreach($post->tags as $tag){
		$tag->whereId(1)->update(['name'=>'google']);
	}
});

delete

Route::get('/delete', function(){

	$post = Post::find(1);

	foreach($post->tags as $tag){
		$tag->whereId(1)->delete();
	}
});

Polymorphic relation

Polymorphicの例は、staffの写真と商品の写真をphotosというテーブルで管理し、staffと商品を連携させることです。

$ php artisan make:model Staff -m

public function up()
    {
        Schema::create('staff', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

$ php artisan make:model Product -m

Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });

$ php artisan make:model Photo -m

Schema::create('photos', function (Blueprint $table) {
            $table->increments('id');
            $table->string('path');
            $table->integer('imageable_id');
            $table->string('imageable_type');
            $table->timestamps();
        });

$ php artisan migrate

Photo.php

public function imageable(){
    	return $this->morphTo();
    }

Staff.php, Product.php

public function photos(){
    	return $this->morphMany('App\Photo', 'imageable');
    }

staffsとproductsのテーブルにレコード挿入
mysql> insert into staff (name) values (‘peter’);
Query OK, 1 row affected (0.06 sec)

mysql> insert into staff (name) values (‘john’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into product (name) values (‘laravel’);
ERROR 1146 (42S02): Table ‘polymorphic.product’ doesn’t exist
mysql> insert into products (name) values (‘laravel’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into products (name) values (‘cake’);
Query OK, 1 row affected (0.00 sec)

Route::get('/create', function(){

	$staff = Staff::find(1);
	$staff->photos()->create(['path'=>'example.jpg']);

});

read

Route::get('/read', function(){

	$staff = Staff::findOrFail(1);

	foreach($staff->photos as $photo){
		return $photo->path;
	}

});

update

Route::get('/update', function(){

	$staff = Staff::findOrFail(1);

	$photo = $staff->photos(1)->whereId(2)->first();

	$photo->path = "update exampl.jpg";
	$photo->save();
});

delete

Route::get('/delete', function(){

	$staff = Staff::findOrFail(1);
	$staff->photos()->delete();
});

connect

Route::get('/assign', function(){

	$staff = Staff::findOrFail(1);

	$photo = Pohoto::findOrFail(3);

	$staff->photos()->save($photo);

});