技術評論社サイトのロゴ

Software Design 2019年7月号

サポートページ

ダウンロード

P.114　連載「挑戦！ Capture The Flag」

（2019年6月17日更新）

SECCON 2015のオンライン予選問題で出題された「Unzip the file」に使用するファイルが以下からダウンロードできます。詳細については、記事をご覧ください。

ダウンロード
unzip

同一のファイルは
https://github.com/SECCON/SECCON2015_online_CTF/tree/master/Crypto/100_Unzip%20the%20fileからもダウンロードできます。

免責：本ファイルを使用することによって、使用者が受けたあらゆる不利益に対して、原著者および技術評論社はその責任を負いません。

補足情報

P.166「Unixコマンドライン探検隊」補足：MariaDBへの接続ユーザと認証

（2019年6月17日最終更新）

MariaDBの認証のしくみは、Unixのものと似ている部分と異なっている部分があるので混乱しやすいところです。図1に示すように、MariaDBにおけるユーザ認証は、mysql.userテーブルにある、userとhostの組み合わせに対して行われます。同じuserであっても接続元のhostが異なるなら、認証は異なります。

図1　MariaDBのユーザの認証
unix_socketプラグインが指定してあるときの注意点

本誌にも記述したとおり、Ubuntu 18.04でaptからインストールした環境では、図2のようにUnixの一般ユーザからmysqlコマンドを実行してもログインできません。

図2　mysqldに接続できない状態
(@ubuntu)$ mysql -u root -h localhost
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

これは、unix_socket pluginが有効になっていて、ローカルのソケットを介してMariaDBに接続するときUnixの資格情報を使用しているためです（図3は、macOSで接続して認証に関するpluginの情報を取得したところです。図4のUbuntuの状況と比較するとpluginの項目が異なっています）。

図3　macOSで接続してログイン情報を確認する
(mac@~)$ mysql -u root
……（省略）……
MariaDB [(none)]> SELECT user,host,password,plugin FROM mysql.user;
+------+----------------+----------+--------+
| user | host           | password | plugin |
+------+----------------+----------+--------+
| root | localhost      |          |        |
……（省略）……
+------+----------------+----------+--------+
6 rows in set (0.000 sec)
図4　aptで導入直後の認証の状態
(@ubuntu)$ sudo mysql -u root
……（省略）……
MariaDB [(none)]> SELECT user,host,password,plugin FROM mysql.user;
+------+-----------+----------+-------------+
| user | host      | password | plugin      |
+------+-----------+----------+-------------+
| root | localhost |          | unix_socket |    ← pluginでunix_socketが有効
+------+-----------+----------+-------------+
1 row in set (0.00 sec)

図4のように初期状態ではroot@localhost以外のユーザは存在していませんので、ローカルホストからrootとして、unix_socketを使った接続のみが可能です。そのため、OSのrootとしてmysqldへ接続する必要があります。つまり、本誌面でも書いたように、sudoもしくは、suによってUnixユーザをrootに変更し、mysqlコマンドでMariaDBユーザrootを指定（-u rootオプション）して接続します。

また同様の理由で、本誌面で実施したように、unix_socketプラグインを有効にせずに新規に作成（誌面ではsbtestユーザを作成）するMariaDBのユーザでは、異なるUnixユーザからも接続できますし、unix_socketが有効であっても、OSのユーザとMariaDBのユーザが一致していれば接続できます。

しかし、Unixの一般ユーザから、suせずにMariaDBのroot@localhostにログインできるようにするには、unix_socketを無効にする必要があります。図5は、sudoによってUnixの資格としてのrootで接続し、UPDATE文を使って、ユーザrootのunix_socket pulginを無効にする例です。

図5　rootユーザでのunix_socketを無効にする
MariaDB [(none)]> UPDATE mysql.user SET plugin='' WHERE user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> SELECT user,host,password,plugin FROM mysql.user;    ← 確認
+------+-----------+----------+--------+
| user | host      | password | plugin |
+------+-----------+----------+--------+
| root | localhost |          |        |
+------+-----------+----------+--------+
1 row in set (0.00 sec)

$ sudo systemctl restart mariadb    ← 変更を有効にするには、mysqldの再起動が必要
(@ubuntu)$ mysql -u root
……（省略）……
MariaDB [(none)]>    ← 接続できた

オフィシャルページに、unix_socket pluginをシステムの起動時に無効にする設定の記述（図6、図7）があります。しかしこの操作だけでは、すでにunix_socketプラグインが有効になっているユーザに対しては、プラグインがないためにログインできなくなってしまいますので注意してください。

図6　unix_socketを無効にするmy.cnfの設定ファイルに記述
[mariadb]
...
unix_socket=OFF
図7　unix_socketを無効にするmysqldの起動オプション
--unix-socket=OFF

不便なようですが、ローカルホストでsudoしなければMariaDBのrootユーザとして接続できないというのは、セキュリティ面においては安全性が高いとも言えます。さらにここで確認したいずれの環境でも、デフォルトでrootのパスワードが空です。実際の運用の前には、パスワードを設定するなどセキュリティへの配慮を忘れないようにしましょう。（了）

P.166「Unixコマンドライン探検隊」補足：MariaDBでの文字コードの扱い

コンピュータの発展とともに、多くの文字コードが生まれてきました。たとえば日本語でも、一般的なコードの呼び方だけでも、JISコード、Shift-JIS、EUC、Unicodeなどさまざまです。厳密にはそれぞれのコードに対して、複数のバージョンや呼称があったりします。OSやDBMS、ブラウザなどのアプリケーションプログラムでも、コードセットが異なると、使える文字、使えない文字が異なったりして、これらを意識しなければならないことがあります。

少しややこしく感じるかもしれませんが、MariaDBの設定においても、データベース、テーブル、カラムに収納する文字、問い合わせで扱う文字、クライアントプログラムで扱う文字コードを意識しなければなりません。詳しくはオフィシャルサイトの「Character Set and Collation Overview」、「Supported Character Sets and Collations」に記述があります。

Character Sets――文字コード

MariaDBでは、デフォルトの文字コードの設定がServer Level（my.cnfで書くなど）→Database Level（CREATE DATABASEやALTER DATABASE時に指定する）→Table Level（CREATE TABLEやALTER TABLEなど）→Column Level（CREATE TABLE、ALTER TABLE時のカラム指定時）で、カスケードダウンし継承されます。収納するときにも、文字コードはSQL文の発行時に指定できます。導入しているMariaDBが扱える文字セットは、SHOW CHARACTER SETで確認できます。mysqlコマンドから確認しておきましょう。

デフォルトの指定をうっかり間違えて運用してしまっても、機能的には後に変更することができます。ここでは、データベースのデフォルトをutf8からutf8mb4に変更する例を見てみましょう。

【表のデフォルト文字コードとカラムの文字コードをそれぞれ変換する】

I. ALTER TABLE でデフォルト文字コードを変更してみる

1. まずはutf8をデフォルトで、RDBMSを立ち上げ〜選択

MariaDB [(none)]> create database utf8 charset=utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use utf8
Database changed

2. テーブル(t)をデフォルト文字セットで作り、確認...テーブルの文字セットもutf8になっている

MariaDB [utf8]> create table t (vt varchar(16));
Query OK, 0 rows affected (0.03 sec)

MariaDB [utf8]> show create table t;
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `vt` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3. 日本語文字列を挿入、続いて絵文字も挿入

MariaDB [utf8]> insert into t values ('あいう');
Query OK, 1 row affected (0.00 sec)

MariaDB [utf8]> select * from t;
+-----------+
| vt        |
+-----------+
| あいう    |
+-----------+
1 row in set (0.00 sec)

MariaDB [utf8]> insert into t values ('[[emoji?txt=`beer`]]');
Query OK, 1 row affected, 1 warning (0.01 sec)

4. 挿入されたデータを確認すると、日本語（あいう）はOK、[[emoji?txt=`beer`]]は?（＝不明の文字）になっている。

MariaDB [utf8]> select * from t;
+-----------+
| vt        |
+-----------+
| あいう    |
| ?         |
+-----------+
2 rows in set (0.00 sec)

5. utf8のテーブル（t）をALTER TABLE文で、デフォルト文字コードをutf8mb4にする。

MariaDB [utf8]> alter table t character set utf8mb4;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [utf8]> show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `vt` varchar(16) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

6. 挿入済みのデータを再確認。... すでに入っているデータに変化はない。

MariaDB [utf8]> select * from t;
+-----------+
| vt        |
+-----------+
| あいう    |
| ?         |
+-----------+
2 rows in set (0.00 sec)

7. 新規に[[emoji?txt=`beer`]]を含む行を追加して、確認。... またまた、?（不明の文字）になっている。

MariaDB [utf8]> insert into t values ('[[emoji?txt=`beer`]]');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [utf8]> select * from t;
+-----------+
| vt        |
+-----------+
| あいう    |
| ?         |
| ?         |
+-----------+
3 rows in set (0.00 sec)

8. テーブル（t）に加えて、列（vt）の文字コードをutf8mb4に変更。

MariaDB [utf8]> alter table t modify vt varchar(16) character set utf8mb4;
Query OK, 3 rows affected (0.04 sec)               
Records: 3  Duplicates: 0  Warnings: 0

9. [[emoji?txt=`beer`]]の行をさらに追加。確認してみると ... [[emoji?txt=`beer`]]が挿入できた。;-)

MariaDB [utf8]> insert into t values ('[[emoji?txt=`beer`]]');
Query OK, 1 row affected (0.01 sec)

MariaDB [utf8]> select * from t;
+-----------+
| vt        |
+-----------+
| あいう    |
| ?         |
| ?         |
| [[emoji?txt=`beer`]]         |
+-----------+
4 rows in set (0.00 sec)

【CONVERT TO を指定して、表の文字コードを変換する】

II. ALTER TABLE CONVERT TO

1. RDBMSのデフォルト文字セットがutf8の状態で、はじめから、同じ手順でテーブルを作り直す。

MariaDB [utf8]> drop table t;
Query OK, 0 rows affected (0.02 sec)

MariaDB [utf8]> create table t (vt varchar(16));
Query OK, 0 rows affected (0.02 sec)

2. [[emoji?txt=`beer`]]も挿入

MariaDB [utf8]> insert into t values ('[[emoji?txt=`beer`]]');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [utf8]> select * from t;
+------+
| vt   |
+------+
| ?    |
+------+
1 row in set (0.00 sec)

3. 次は、ALTER TABLE "CONVERT TO" で、文字コードをutf8mb4に変換。

MariaDB [utf8]> alter table t convert to character set utf8mb4;
Query OK, 1 row affected (0.03 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [utf8]> show create table t  \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `vt` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

4. 挿入済みのデータに変化はない。4byteの文字「[[emoji?txt=`beer`]]」を3byteの文字として登録されたので、情報はすでに欠落しているので、あたりまえ。

MariaDB [utf8]> select * from t;
+------+
| vt   |
+------+
| ?    |
+------+
1 row in set (0.00 sec)

5. 新規に[[emoji?txt=`beer`]]を挿入。IでのALTER TABLE文だけで文字コードを指定したときは、カラムのデフォルト文字コードの変更も必要だったが、今回はそのまま[[emoji?txt=`beer`]]が挿入できた。;-)

MariaDB [utf8]> insert into t values ('[[emoji?txt=`beer`]]');
Query OK, 1 row affected (0.00 sec)

MariaDB [utf8]> select * from t;
+------+
| vt   |
+------+
| ?    |
| [[emoji?txt=`beer`]]    |
+------+
2 rows in set (0.00 sec)

これらの結果からもわかるように、表の文字コードを変更する場合は、ALTER TABLE <表> CONVERT TOがそれぞれのカラムの文字コードも変更してくれるので対応忘れなどに陥りにくいですね（ここでは文字コードだけを指定しましたが、後述のCollationも文字コードに合わせて変更するようにしましょう）。文字コードやCollationは変更できるとはいえ、運用開始、データの投入の前から、扱うデータの性質をきちんと分析し、適切な設定をしておくのがよいのは明らかです。

Collation――照合順

MariaDBでは、文字セットのほかに、Collation（照合順序）の指定ができます。Collationとは、データの突き合わせのしかたです。たとえば、Aとaを区別する／しない（しないなら、select ... where col="apple"; で、appleもAppleも選択されます）、というルールのことです。日本語だと「は」と「ハ」、「ぱ」と「ば」「は」、「あ」と「ぁ」、「サーバ」と「サーバー」などを区別しないで検索したいときがありますよね。

MariaDBでは、デフォルトのCollationも文字コード同様、Server Level（my.cnfで書くなど）→Database Level（CREATE DATABASEやALTER DATABASE時に指定する）→Table Level（CREATE TABLEやALTER TABLEなど）→Column Level（CREATE TABLE、ALTER TABLE時のカラム指定時）で、カスケードダウンします。さらに問い合わせ時も同様、次のようにSELECT文を発行するときに、COLLATE句を用いて指定することもできます。

select * from sbtest1 where c like '6%' collate utf8mb4_bin limit 2;

ここでは、全体に関わるサーバレベルのデフォルトに注目してみましょう。文字セットと使えるCollationの対応は、図8のようにSHOW COLLATIONで確認できます。

図8　utf8mb4で使えるCollation、日本語に関係ある部分
SHOW COLLATION LIKE 'utf8mb4%';
+------------------------------+---------+-----+---------+----------+---------+
| Collation                    | Charset | Id  | Default | Compiled | Sortlen |
+------------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci           | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin                  | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci           | utf8mb4 | 224 |         | Yes      |       8 |
……（省略）……
| utf8mb4_unicode_520_ci       | utf8mb4 | 246 |         | Yes      |       8 |
……（省略）……

Collationのカラムを見ると、文字コードに加えて、ubf8mb4_「なにがし」のような記述があることが確認できます。MySQLのオフィシャルサイトに、Collationのネーミングコンベンションの記述があります（Collation Naming Conventions）。

ここに記述があるように、Collationは「なにがし」で照合（同一視）する文字の違いを表しています。generalはMySQL/MariaDB独自の基準で、unicodeはUnicode4.0に基づくということです。さらにunicodeの後ろに（520などのように）番号があるのはunicodeのバージョン（ここでは、520=Unicode5.2.0。詳しくはThe Unicode Consortiumを参照）です。だいたい末尾に_ciがついていますね。これは“Case Insensitive”の意味で、大文字小文字を区別しないということです。そしてbinはバイナリの意味で、文字コードが違えば違う文字と判定します。筆者の環境で確認した限りでは、アクセント記号（アルファベットだと文字の上や下についている点とかニョロとか、日本語なら濁点半濁点でしょうか）や長音のゆらぎなど、ciやbin以外のCollationは対応していないようです。

MariaDBが対応している文字コードとCollationは、Supported Character Sets and Collationsに記述があります。でも、それぞれが何をどう扱われるのかや、どのような文字が含まれている／いないのかなど、すべてを仕様から読み解くのはたいへんですね。対象の文字がどのように扱われるかは、実際にシステムにデータを入れて検索して確認するのが確実です。（了）

お詫びと訂正（正誤表）

本書の以下の部分に誤りがありました。ここに訂正するとともに、ご迷惑をおかけしたことを深くお詫び申し上げます。

（2019年6月20日最終更新）

P.22　第1特集第1章「バックエンド高速化の全体像」

ページ下部、箇条書き部分の各種ソフトウェアについて名称に誤りがありました。

誤 AWS：X-ray
GCP：StackDriver Trace
Azure：Azure Insight
OSS： OpenCensus
正 AWS：X-Ray
GCP：Stackdriver Trace
Azure：Azure Monitor
OSS：Jaeger、Zipkin

（以下2019年6月17日更新）

P.55　第1特集第4章「CDNのメリットとポイント」脚注

誤 注2）https://www.fastly.com/io 注3）https://www.itmedia.co.jp/enterprise/articles/1708/24/news118.html
正 注2）https://www.itmedia.co.jp/enterprise/articles/1708/24/news118.html 注3）https://www.fastly.com/io

P.116　連載「挑戦！ Capture The Flag」図3のキャプション

誤 SECCONのメールマガジン（http://2014.seccon.jp/mailmagazine/text/backnumber08.txt）
正 SECCONのメールマガジン（http://2014.seccon.jp/mailmagazine/backnumber08.txt）