今日は突発な用事ができたので途中までしか学習時間が作れなかった。
データベースの作成が何と無く感触が掴めたかな。

因みにshow databases;で確認したところ、以前に作った無駄なデータベースが多く整理がされてなかったから今回は記述しない。

余りにも中途半端過ぎるので一応メモに残し、翌日はプラスアルファ新たに学んだ内容を記録に残す。

①MySQL

操作対象のデータベースを調べる記述

mysql> select database(); 

結果

+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

NULLと表示されたので操作対象のデータベースは無い。databaseの下枠に操作対象のデータベースが表示される。

mysql> use データベース名; //データベースを操作対象にする記述

mysql> use testdb01;

結果

Database changed

この表示でtestdb01が操作対象になった。予め作っておいたデータベースをこの記述で操作できるようになる。

明日の課題
①MySQLで外部ファイルの記述を学ぶこと。その内容をQiitaに投稿する。
②HTMLのnavタグの処理の流れを実際にコードを書いて掴む

はじめに

未来電子テクノロジー
https://www.miraidenshi-tech.jp/
というところでインターンをしている＜haru--mimiko＞です。
プログラミング初心者であるため、内容に誤りがあるかもしれません。
もし、誤りがあれば修正するのでどんどん指摘してください。

SQLにログインする際に、(2019, “Can’t initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)” というエラーに出会ったので、解決法として調べたものをまとめておきます。

方法1

my.confのmysqldとclient、それぞれのcharacter-setにutf8mb4をセットする。

my.conf

[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4

方法2

/XAMPP/samppfiles/share/charsets/Index.xmlに以下のコードを挿入する。

Index.xml

<charset name="utf8mb4">
      <family>Unicode</family>
      <description>UTF-8 Unicode</description>
      <alias>utf-8</alias>
      <collation name="utf8_general_ci"     id="33">
       <flag>primary</flag>
       <flag>compiled</flag>
      </collation>
      <collation name="utf8_bin"            id="83">
        <flag>binary</flag>
        <flag>compiled</flag>
      </collation>
    </charset>

はじめに

久しぶりにMySQLWorkbenchでDumpを出力しようとしたら、初めて見るエラーが発生。
対処方法を調べたら英語のページしか見つからなかったので、自分記録用に記事を書いてみた。

発生したエラー

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM,
'$."number-of-buckets-specified"') FROM
information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'スキーマ名' AND
TABLE_NAME = 'テーブル名';':
Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

information_schema スキーマに「COLUMN_STATISTICS」というテーブルが無いと怒られた。

対象方法

MySQL Workbench 8.0の場合、データのエクスポートが正常に行われないケースがあるらしい。

まずはWorkbenchのバージョン確認

確認方法

1.MySQL Workbenchを起動する
2.画面上部の「Help」メニューを開く
3.「About Workbench」を選択
4.バージョンが表示される『Workbench バージョン番号』

MySQL Workbench 8.0でした。

設定変更手順

1.MySQLWorkbenchを起動する
2.画面上部「Server」メニューを開く（Managementタブを開いてもいい）
3.「Data　Export」を選択
4.右上の「「Advanced Options...」をクリック

5.Other の中にある「column-statics -Writing……(set 0 to diasble).」を"0"に設定

更新・設定反映ボタンは特になかったので[<Return]で戻った。

再度エクスポート

エクスポート正常終了したのでOK

コマンドで実行する時は

mysqldumpオプションに「--column-statistics=0」を追加すればよいらしい。

mysqldump --column-statistics=0 --host=<server> --user=<user> --password=<password> 

まとめ

mysqldump 8.0.2で新しく追加されたオプション[--column-statistics]
これが古いバージョンだと対応できずにエラーが発生したのでは？と推測できる。

公式でオプションの内容を調べたが、正直どういう使い方をするためのオプションなのか、その優位性を理解するまでには至らなかったので、古い(ver8.0以下)MySQLを使用するケースがある方はデフォルト「0」に設定しておいてとりあえずは問題ないだろう。

参考

• 【公式サイト】 mysqldump — A Database Backup Program
• 【serverfault】 mysqldump throws: Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

hostnameは
docker-compose psもしくはcontainer_name

docker-compose run <host_name> mysql -u root -p<pass> -h <host_name> database_development < ./database.dump

docker-compose run db psql -h db -U postgres -d xxx_docker < dumpfile.dump

前置き

古くからあるテーブル（old_posts）を新しいテーブル（new_posts）に切り替える対応をしていて、その過程でレコードを同期するためにridgepoleでいろいろ操作したのでそのメモです。

コード

# old_postsにある既存のレコードをすべてnew_postsにコピーする
execute(<<-COPY) do |c|
  INSERT INTO new_posts(title, content, created_at, updated_at)
  SELECT title, content, created_at, updated_at FROM old_posts;
COPY
  # まだnew_postsにレコードがない場合のみ実行
  c.raw_connection.query(<<-SQL).count.zero?
    SELECT 1 FROM new_posts
  SQL
end

# TRIGGERを設定して、old_postsへのレコード追加時にnew_postsにもレコード追加する
execute(<<-TRIGGER) do |c|
  CREATE TRIGGER copy_post AFTER INSERT ON old_posts
  FOR EACH ROW
  BEGIN
    INSERT INTO new_posts(title, content, created_at, updated_at)
    VALUES (NEW.title, NEW.content, NEW.created_at, NEW.updated_at);
  END;
TRIGGER
  # まだcopy_post TRIGGERがない場合のみ実行
  c.raw_connection.query(<<-SQL).count.zero?
    SHOW TRIGGERS WHERE `Trigger` = 'copy_post'
  SQL
end

今回は扱うテーブルの性質上、レコード更新／削除時は考慮していません。
（必要な場合は別途TRIGGERを追加していけばOK）

このあと、モデルのテーブル名を変更するなどの対応を進めていく予定。

いやー、DB系操作するの結構疲れるなぁ。

以上です。

参考

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.5.1 INSERT ... SELECT 構文
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 20.3.1 トリガーの構文と例
• Ruby on Railsのmigrationをうまく使って無停止でMySQLのテーブル名変更を行う - Qiita
• 3分でわかるトリガー -使い所と問題点を考える- - Qiita

このシリーズの目的

体系的なwebコーディングの訓練ができるようになるためにPHPの初学のきっかけかつ、PHPでログインフォームやフォームを実装することができるようになるために

PHP For Beginners

上記のチュートリアルを進めているのでその備忘録。

前回

内容

今回のチュートリアル

How To Create Forgot Password System In PHP & MySQLi [2018]

このチュートリアルでやること

・ログインフォームにパスワードリセットを実装する
(その12で作成した成果物に追加する）

成果物

forgotPassword.php

<?php 

    $msg = "";
    use PHPMailer\PHPMailer\PHPMailer;
    use PHPMailer\PHPMailer\Exception;
    use PHPMailer\PHPMailer\SMPT;

    require_once 'function.php';


    if (isset($_POST['email'])) {
        $conn = new mysqli('localhost','root','','register');

        $email = $conn->real_escape_string($_POST['email']);

        $sql = $conn->query("SELECT id FROM users WHERE email='$email' ");
        if ($sql->num_rows > 0) {

            $token = generateNewString();

            $conn->query("UPDATE users SET token='$token',
                            tokenExpire = DATE_ADD(NOW(), INTERVAL 5 MINUTE)
                            WHERE email = '$email'
                ");

            mb_language("japanese");
            mb_internal_encoding("UTF-8");
            require 'vendor/autoload.php';
            require 'Mailtrap-config.php';


                $mail = new PHPMailer();

                // Server

                $mail->SMTPDebug = 0; //本番では0とかにする。
                $mail->isSMTP();
                $mail->SMTPAuth = true;
                $mail->Host = MAIL_HOST;
                $mail->Username = MAIL_USERNAME;
                $mail->Password = MAIL_PASSWORD;
                $mail->SMTPSecure = MAIL_ENCRPT;
                $mail->Port = SMTP_PORT;

                // Recipients
                $mail->setFrom(FROM_MAIL);
                // $toname = mb_encode_mimeheader("$name", 'ISO-2022-JP', 'B', "\n");
                $mail->addAddress($email);
                // $mail->addAttachment($attachment);

                // Content
                $mail->Subject = mb_encode_mimeheader("Reset Your Password", "ISO-2022-JP", "UTF-8");
                $mail->Body = mb_convert_encoding("
                    パスワードリセットのリクエストがありましたので以下のリンクをクリックしてパスワードのリセットを行ってください。:<br><br>
                    <a href='http://localhost/Laravel/PHPMailer/Training/phptutorial17/resetPassword.php?email=$email&token=$token'>パスワードリセットはこちらから</a><br>

                    七花 京
                ","JIS","UTF-8");
                $mail->CharSet = 'ISO-2022-JP';
                $mail->Encoding = "7bit";

                // Select HTML or NOT

                $mail->isHTML(true);

               if ($mail->send())
                    exit(json_encode(array("status" => 1, "msg" => 'Please Check Your Email Inbox!')));

                 else 
                    exit(json_encode(array("status" => 0, "msg" => 'Something Wrong Just Happened')));

                } else
                    exit(json_encode(array("status" => 0, "msg" => 'Please Check Your Input!')));
    }

 ?>



 <!DOCTYPE html>
 <html>
 <head>
    <title>Forgot Password?</title>
    <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js">
    </script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js" type="text/javascript">
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js">
    </script>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js">
    </script>
    <link rel="stylesheet" type="text/css" href="css/forgotPassword.css">
 </head>

 <body>
    <div class="container">
        <div class="row justify-content-center">
            <div class="form col-md-6">
                <form action="forgotPassword.php" method="post" enctype="multipart/form-data">
                    <input  class="form-control" id="email" placeholder="Your Email Address"><br><br>
                    <input type="button" class="btn btn-primary" value="Reset Your Password"><br><br>

                    <p id="response"></p>
                </form>
            </div>
        </div>
    </div>

    <script src="https://code.jquery.com/jquery-3.4.1.min.js"integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo="crossorigin="anonymous"></script>

    <script>
        let email = $('#email');

            $(document).ready(function () {
                $(".btn-primary").on('click',function () {
                if(email.val() !="") {

                    email.css("border","1px solid green");

                    $.ajax({
                        url:'forgotPassword.php',
                        method:'POST',
                        dataType:'json',
                        data:{
                            email:email.val()
                        }, success:function(response) {
                            if (!response.success) 
                                $("#response").html(response.msg).css('color','red');

                            else

                            $("#response").html(response.msg).css('color','green');
                        }
                    });

                } else
                    email.css("border","1px solid red");

            });
        });
    </script>
 </body>
 </html>

function.php

<?php 
    function generateNewString($len = 10) {
        $token = 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM0123456789!$/()*';
        $token = str_shuffle($token);
        $token = substr($token, 0, $len);

        return $token;

    }

    function redirectToLoginPage() {
    header('location:login.php');
    exit();
}

 ?>

resetPassword.php

<?php
    require_once "function.php";

    if (isset($_GET['email']) && isset($_GET['token'])) {
        $conn = new mysqli('localhost','root','','register');

        $email = $conn->real_escape_string($_GET['email']);
        $token = $conn->real_escape_string($_GET['token']);

        $sql = $conn->query("SELECT id FROM users WHERE
            email='$email' AND token='$token' AND token<>'' AND tokenExpire > NOW()
        ");

        if ($sql->num_rows > 0) {
            $newPassword = generateNewString();
            $newPasswordEncrypted = password_hash($newPassword, PASSWORD_BCRYPT);
            $conn->query("UPDATE users SET token='', password = '$newPasswordEncrypted'
                WHERE email='$email'
            ");

            echo "Your New Password Is $newPassword<br><a href='login.php'>Click Here To Log In</a>";
        } else
            redirectToLoginPage();
    } else {
        redirectToLoginPage();
    }
?>

動作

https://youtu.be/oHF7gYZt0RA

手順

・大枠のアルゴリズムを考える

1.まず、パスワードリセット申請フォーム(以下forgotPassword.php)に入力されたEmailアドレスが、登録情報が登録されているテーブルに存在するか検証

2.検証の結果、存在するのであれば任意の時間制限付きのトークンを発行し、再登録フォームへのリンク誘導を添えたEmailを送信する。
（その12でやったEmail認証リンクの仕組みに時間制限トークンを導入する）

3.リンクをクリックした際にトークン及びEmail認証が正常に通った場合、ランダム文字列でパスワードを作りそれを新しいパスワードとして、成功メッセージとともに画面上に表示し、同時にそれをハッシュ化してデータベースに新しいパスワードとして登録し直す。

なお、通常の実装の場合はこれの後にユーザーがログインして任意のパスワードに自分で登録し直すかまたは、3の段階でユーザーに新しいパスワードを登録させることが考えられる。

・各段階でのアルゴリズムを考える

＊データベースの接続などは割愛。

1.forgotPassword.php
→
・フォームに入力された文字列をエスケープする
・データベースにアクセスする
・エスケープした文字列を変数に代入する
・メールアドレスを検索フォームにし、入力されたメールアドレスがテーブルに存在する時はidを返す。存在しない時はエラーメッセージを表示する

・idが返ってきたらトークンを生成し、検索フォームをメールアドレスにし、データベースに再度アクセスして該当するメールアドレスが存在するデータに時間制限付きのトークンを発行する

・認証リンク付きのメールアドレスを送信し、送信完了の旨のメッセージを表示する。メールが送信できない場合はエラーメッセージを表示する

・PHPMaillerなどを用いて、テーブルに登録されたユーザー宛に認証リンクが記載されたメールを送信する。

2.function.php
→
・ランダム文字列を生成する処理とリダイレクト処理を書いておいて、requireなどで呼び出せるようにしていおく。
文字列生成とリダイレクトはこれまでのチュートリアルでやったことなのでここでは割愛。

3.resetPassword.php
→
・その12 で作ったものと同じ処理をさせる。

・トークンとメールアドレスを取得する(認証リンクが踏まれているか確認する）。取得できたらデータベースに接続し、取得したメールアドレス及びトークンをエスケープして変数に代入する

・データベースに接続し、メールアドレス・トークン(空ではないという条件を付随する)・トークンに設定された有効期限が現在の時刻より先であるという条件で検索しする

・該当するデータが有ればデータベースに接続し、新しいパスワードを生成する。それをハッシュ化し、データのトークンを削除し、新しく生成したパスワードを登録する。該当するデータがない場合はログインフォームへリダイレクトする

・生成したパスワードとログインフォームへのリンクを表示する

今回のコードの注釈

tokenExpire周りについて

>query("UPDATE users SET token='$token',
        tokenExpire = DATE_ADD(NOW(), INTERVAL 5 MINUTE)
                        WHERE email = '$email'
                ");

今回新しく出てきたのはDATE_ADD(NOW(), INTERVAL)の形。
DATE_ADDは日付と時刻を取得する関数、第一引数に取得する日付及び時刻を設定する。
今回のNOW()は現在の日付と時刻を取得する、つまり処理が行われた時点での日付と時刻を取得することになる。
INTERVALには任意の時間を設定することで第一引数に指定した時刻から設定した分先の時間を取得する。
つまり、今回はINTERVALに5分と設定したので処理をが行われた時点の日付と時刻から5分先の時間を取得し、tokenExpaireに代入するということになる。

<>演算子

$sql = $conn->query("SELECT id FROM users WHERE
            email='$email' AND token='$token' AND token <> '' AND tokenExpire > NOW()
        ");

<>は左辺と右辺が等しくないということを表す比較演算子及び配列演算子。
今回は万が一他者にメールアドレスと認証リンク及び空のトークン利用されてパスワードをリセットされないようにするために用いている。