WSL(Ubuntu)-PHPからSQLServerへの道のり

「CentOSからWindows上のSQLServerへの道のり」のつづきです。

LinuxからWindows上の SQLServerへのアクセス方法については、世間的にはあまり需要がないのでしょう。まぁ、Linuxとかのunix系のOSでは、SQLServerなんて採用しないし、逆も然り。。。困ったもんだ。。。

さて、Linuxの各ディストリビューション用のMicrosoft製のODBCドライバが提供されていて手順を踏むと、LinuxからSQLServerへODBC経由でアクセスすることができました。

今度は LAMP環境、PHPから文字化けさせずにCRUDすることができるか、ウェブアプリケーションをちゃんと実装できるか、確認です。とりあえずLinux各ディストリビューション用にマイクロソフト製のODBCドライバをインストールしていることが前提です。

PHPでたとえば、以下のようなコードでテーブルを作成し、SQLServer Management Studioとかで確認すると、日本語が全部文字化けします。
sqlcmdコマンドで日本語交じりのSQL文を発行すると化けないので、おそらく、PHPの問題でしょう。DSNで odbc: を使うのがだめなんでしょうね。odbcドライバをインストールしたんだから、当然odbcプレフィックスを使うものだと勘違いしてまして、これが罠でした・・・。日本語関係の処理が全くされない。。。この辺、よくわからん・・・。知識が圧倒的に足りない。SQLServerのODBCドライバとsqlsvr(PHP SQLServer用のドライバ?)の関係が全然わからない。PHPのマニュアル見てもよくわからない・・・。

// これが間違い
$pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_hoge(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec("INSERT INTO tbl_hoge values(1,N'日本語で挿入・・・なんかエロいな')");

接続文字列でエンコーディング関係のパラメータがあるのかなと、調べてみたけどないっぽい。さらにネットでの情報がほとんどなく。。。ヒットするのはFreeTDSを使ったものばかりで、多くはPHP5の情報ばかりで、PHP7以降のものは出てこず。。。

で、困ったときはグーグルで検索・・・ではなく、まずマイクロソフトの公式のドキュメントを漁りましょうってことですね(^^;
ググらなくても最近のマイクロソフトはちゃんとドキュメントを用意してくれてます。(MSも変わったな。。。)

Build an app using SQL Server


上記サイトの PHP ⇒ UBUNTU をクリックすると、Linux版 SQL Serverのセットアップから詳細な手順が書かれています。英語ですが、簡単な英文なので読みましょう!

今までSQLServer用のODBCドライバとPHPでのSQLServer用のドライバ、それぞれが別々に説明がされてて、一体何がどうなってってるのか理解できなかったけど、上記サイトで Ubuntu + PHP では、この手順、Redhat系では、この手順、という風にまとめて手順が説明されているので、これで悩まなくて済むようになった。PHP7以降限定ですけどね。 まぁPHP5系はこの先消えていく(deprecated)のでまぁいいか、という感じ。

要するに ODBCドライバに加えて、peclでsqlsvr/pdo_sqlsvrをインストールすれば完了。やっと 非Windows環境でも労せずSQLServerへフツーにアクセスできるようになりました。

上記マイクロソフトのサイトには、Linux用のSQL Serverのセットアップから説明されていますが・・・SQLServerは Windows用のものをインストールしましょう。というか、開発マシンがWindowsの場合は、素直にWindows用のSQLServer Developper Editionをインストールした方がいいです。開発目的ならライセンスフリーですし。(サーバー環境で運用はできないけど)
開発マシンがMacやLinuxの方は・・・そもそもSQLServerに接続するような仕事してないでしょ?w

WSL(Ubuntu)でPHPをインストールしていない場合、aptで適当にphp7系をインストールしてください。その際、php-dev パッケージは必須です。
また、最新のsqlsrv,pdo-sqlsrvドライバはphp7.0をサポートしないので、最新に拘る人はphp7.1以上にするべし?php7.0をサポートするMicrosoftのsqlsrv/pdoドライバのバージョンは5.3です。最新は確か5.6だったかな?
システム要件:https://docs.microsoft.com/ja-jp/sql/connect/php/system-requirements-for-the-php-sql-driver

$ sudo apt install php php-cli php-dev php-... (必要なパッケージ)
# apacheで動作するには必要
$ sudo apt install libapache2-mod-php

1, 先に マイクロソフトのサイトからODBC Driver for SQLServer をインストールしておく(上述)

2, sqlsrv/pdo_sqlsrv のインストール (ubuntu 1604 の場合)

$ sudo apt install php-dev
$ sudo pecl install sqlsrv
$ sudo pecl install pdo_sqlsrv
$ sudo echo "extension=pdo_sqlsrv.so" > /etc/php/7.0/mods-available/pdo_sqlsrv.ini
$ sudo echo "extension=sqlsrv.so" > /etc/php/7.0/mods-available/sqlsrv.ini
$ sudo phpenmod -v 7.0 -s ALL  pdo_sqlsrv sqlsrv

apache2な人は再起動するべし。
参考リンク:the installation instructions on Microsoft Docs.

3, PHPで確認
PDOで、DSNのプレフィックスをodbc: ではなく sqlsrv: で。

$pdo = new PDO('sqlsrv:Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_サンプル(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec(sprintf('INSERT INTO tbl_hoge values(1,N%s)',$pdo->quote('日本語で挿入・・・なんかエロいな'));

参考リンク: https://github.com/Microsoft/msphpsql/tree/master/sample

文字列リテラルをNプレフィックスをつけずにINSERTとすると絵文字が化ける。文字列リテラルの場合は Nプレフィックスを忘れずに。。。
ただ、プリペアドステートメントを利用する場合、自動的にNプレフィックスはつけてくれるみたい。SQLServer Data Profilerで実際実行されたSQL文を確認すると、PDO::execでは自動的にNプレフィックスはつけてくれないみたい。まぁ当たり前といえば、そうなんだけど。。。

あと、東京オリンピックまでには、apt install php-sqsrv / yum install php-sqlsrv とか標準のリポジトリでインストールできるようにしてほしい。。。

\(^o^)/

NET::ERR_CERT_COMMON_NAME_INVALID なんだこれ?

備忘録。あしらからず。

WSLのおかげでもうほとんどLinuxな仮想マシンは要らなくなったのでホントにラクチン。まぁ、業務上必要なのでまだまだ HyperVにはお世話にならんといけませんが・・・。

で、WSL-UbuntuにSSL通信(HTTPS)させようと、ひさしぶりにopensslコマンドとか使う。なにげにopensslは万能コマンドすね。乱数からダイジェストから暗号化まで全部できちゃう!(^^; で、まぁ、お約束どおりCA.pl -newcaとかで認証局を作って、認証局の証明書をWindowsの証明書マネージャで「信頼されたルート証明機関」にインポート後、openssl.cnfいじって、あとはお決まりのCSR作って、さっき作った認証局で署名して、そのサーバー証明書(CN=localhost)をapache2に食わせて、https://localhost/ にアクセスしたんすよ。

結果、撃沈。昔はこれでいけたんです! IE11だと問題ないし。

最新のChrome/FireFox/Edge ぜんぶダメ。chromeだとNET::ERR_CERT_COMMON_NAME_INVALIDとかいう謎のエラーが出る。。。いやいや、証明書のプロパティを見るとちゃんと CN=localhostってなってるし・・・。

で、このエラーをそのままググると、最近のブラウザはCNは無視するみたいで・・・って、よくわからんが、最近は、CNの一致ではなく、SANを見てるんだとか・・・。理屈はともかく、理由が分かればあとは証明書の作り方を変えればオケ。

とりあえず、CSR(署名要求)まではお約束通り。
※openssl.cnfの設定はググればよろし。ただ、CA向け用とサーバー向け用とファイルを分けて使い分けるのが間違いがなくてよろしかと。
デフォルトのopenssl.cnfをCA用として修正し、サーバー証明書作成時に使う設定は openssl-server.cnfと別ファイルにして使ってます。

#1.認証局作成
$ cd /usr/lib/ssl/misc
$ ./CA.pl -newca

# Windowsの証明書マネージャで「信頼されたルート証明機関」にインポートとするため der に変換。
# でも cacert.pem を直接インポートしたら普通にできたので、↓要らないかも。
$ cd demoCA
$ openssl x509 -in cacert.pem -inform pem -out cacert.der -outform der

#2.サーバーキー作成
$ cd /etc/ssl
$ mkdir server; cd server
$ openssl genrsa -out localhost.pem -aes256 2048
$ openssl rsa -in localhost.pem -out localhost.key

#3.署名要求作成
$ openssl req -new -config ../openssl-server.cnf -key localhost.pem -out localhost.csr

そして・・・CSR(署名要求)を認証局に署名してもらうときに、下記の内容のテキストファイルを用意して、-extfile オプションをつけて食わせるといいようです。理屈はどうでもいい。まずは手順さえ覚えて、あとから調べればいいんです。逃げ。

subjectAltName=DNS:localhost

複数の場合はカンマで区切れば行けそう?

subjectAltName=DNS:localhost,IP:127.0.0.1

このファイルを署名するときに食わせる。

$ echo "subjectAltName=DNS:localhost" > localhost.san
$ openssl ca  -config ../openssl-server.cnf -in localhost.csr -out localhost.crt -extfile localhost.san

そして、できた localhost.keyとlocalhost.crt の二つを /etc/apache2/sites-available/default-ssl.conf の SSLCertificateFile とSSLCertificateKeyFile に記述。そして起動する。

$ a2enmod ssl
$ a2ensite default-ssl
$ service apache2 start

そして、1.で作ったcacert.der をデスクトップにコピーして 右クリック⇒証明書のインストールで「信頼されたルート証明機関」へストア。

ってなわけで?めでたく、最新のブラウザでも https://localhost/ で緑色の鍵マーク付きで表示された。

オプションの超手抜きパース

今日は親戚のお葬式に出席。「生きる」意味って何なんだろうな?と、葬式に出る度にいつも考えてします。まぁ、日々の雑務に追われてすぐ忘れてしまうんですけどね(^^;

そんなことはともかく。

最近、シェルスクリプトを書く機会が多くなってきました。OSにほぼ標準で入っている小さいコマンドを組み合わせて目的の処理を実現するというUNIX?の思想っていうWindowsとは全く相容れない考え方をようやく理解しつつあります(^^;

で、やっぱり最初につまづいてしまうのが、コマンドラインからのオプション引数のパース。
最初は泥臭くshift, if ,case とか駆使しなんとかやってたんですが、もっと効率のいい方法を試行錯誤した結果下記に行きついた。

僕の書くシェルスクリプトの用途ではオプションで指定した値をオプションの名前の変数にそのまま変格納してくれれば十分なので、”-A xxx” みたいなショートオプションは捨て、”–optionA=xxxx” のようなロングオプションだけ対応することで手抜きパースすることにした。

$ parse.sh --opta=xxx --optb=yyy

これをパース。

#!/bin/sh
# parse.sh
#デフォルト値
opta=.
optb=1

#パース
if [ -n "$*" ] ; then
	for arg in "$@"
	do
		eval `echo "$arg" | sed -nr 's/^--(opta|optb)=(.+)$/\1=\"\2\"/p'` 
	done
fi

#確認
for var in opta optb
do
	eval echo $var = \$$var
done

デフォルト値を定義しといて、オプションで得られる値を上書きすることにした。
最初は、”grep -P -o” でオプションを一個づつ食わせてたんですが・・・2重ループになるのでかなり遅い。
で、結局 sed で抽出して eval で手抜きした(^^; getoptってのがあるみたいですが、正直よくわからん。

でも、このコードだとオプションの数が10個も20個も必要な場合かなりダサいコードになるのは、素人まるだし(^^;;;
まぁ、いいや。

開発環境見直し for WORDPRESS on WSL

2017/12/21 追記:
bashを終了すると、立ち上げたデーモン全部落ちてしまいますが、Windows10の次のなんちゃら UPDATE で、bashを終了してもデーモンが生き残るようになるとのこと。朗報ですな。うんうん(^▽^)/ っていうか、僕的に、もうWindows要らね、って・・・。 Apple iTunesと Adobe Creative Cloud が linux 対応してくれたらももうWindows使うの終わりかもw

2017/11/09 追記:
LAN内の他のPCからアクセスするには、コントロールパネル ≫ Windows Defender ファイヤーウォール の新規受信規則で、ポート80番を開けないといけません。


ちょっと前に今の仕事の開発環境を新しくしたのですが、Windows 10 Fall Creators Update(以下、FCU)で、Windows Subsystem for Linux(WSL)がベータ版から正式版に変わったんで、また見直し。見直しというか、Wordpress用。

HyperVを使った前のやつは、こっち、でCIFSマウントしているディレクトリへのアクセスがたまにエラー?で読み取れなくなるぐらいで、特に不自由もなかったんですが、WSLの記事をググると、思いの他進化しているので、WORDPRESSでのお手軽サイト構築用の環境として整備することにした。

Windows10 FCU からは、限定的ですが、ネットワークフォルダをマウントすることができて、WSLはかなり実用的な開発ツールセットになってきています。しぶしぶMac使っている人(そんな奴いるのか?)は、やっと大好きな(笑) 使い慣れたWindowsでウェブ系のお仕事ができます!( ̄▽ ̄)

BoWそのものは、マイクロソフトストアからインストールできるので、もう説明要らないでしょう。Windows10 FCUから開発者モードにする必要もなくなったし。

手順を記録しとこう、という備忘録です。特にこれといって新しいことが書かれているわけではなく、グーグル先生に聞いた内容を自分用にまとめたものなので、あまり参考にはなりません。あしからず。

>>以下の手順を全自動で行うシェルスクリプト

■ パッケージのインストール
apt コマンドで、必要なパッケージをインストールしていきます。sudo するのがかったるいので、全部 root で作業します。
の、前に・・・、まず、/etc/apt/source.list の中のリポジトリを日本のものに差し替えるのが定石だそうですので、変更してからにします。
また、mysql-serverをインストールする最中に、root パスワードを入力するように促されるので適当に。

sudo su -

sed -i.orig -e 's%http://archive.ubuntu.com/ubuntu/%http://ftp.jaist.ac.jp/pub/Linux/ubuntu/%g' /etc/apt/sources.list
apt update
apt -y upgrade

apt -y install apache2 apache2-doc mysql-server mysql-client
apt -y install php php-mbstring php-mysql php-sqlite3 php-gd php-mcrypt libapache2-mod-php 

php の方は、必要なモジュールを別途 apt でインストールする必要があります。・・・その辺はまぁ適当に。

■ Apache2 設定
まず、そのまま “service apache2 start” とかやると、ワーニングがでます。まぁ、無視すりゃいいんですが、気になるので・・・。
/etc/apache2/apache2.conf を開いて下記2行を追記します。

AcceptFilter http none
AcceptFilter https none

あと、開発するファイル等が入った、ドライブなり、フォルダなりを apacheから見えるようにしないといけません。
windows.confは、僕の環境では、下記のような感じ

#
# /etc/apache2/sites-available/windows.conf
#
<Directory "/mnt/d/devel">
AllowOverride All
Options All
Require all granted
</Directory>
Alias /devel /mnt/d/devel

で、有効化します。

a2ensite windows

php.iniの修正も若干必要ですかね。デフォルトで2MBしかアップロードできませんし、開発環境なんで、memory_limitは無制限にしたい。
ってなわけで、/etc/php/7.0/apache2/php.ini で、以下の項目を修正

  • date.timezone
  • memory_limit
  • post_max_size
  • upload_max_filesize

それと、まぁ、ほとんど使わないと思いますが・・・DocumentRootが /var/www/html で設定されているのですが、これのオーナーとグループを自分のユーザーにしときましょう。いちいち sudo してコピーするのメンドクサイでしょう。ついでにホームディレクトリにリンクを作っときましょう。

#一度 root を exit して・・・
exit
#myuserは自分のアカウントで置き換える
sudo chown -R myuser:myuser /var/www/html
ln -s /var/www/html ~/wwwroot
# root に戻る
sudo su -

■ MySQL 設定
次、mysql。apacheと同じく、”service mysql start” とかやると、僕の環境ではワーニングが出ます。これも無視すればいいんでしょうけど・・・。
mysqlユーザーのホームディレクトリを作ってやります。すでにあればやる必要なし。

mkdir /home/mysql
chown mysql:mysql /home/mysql
usermod -d /home/mysql mysql

mysql の 文字コードを UTF8にしときます。
/etc/mysql/my.cnf を開いて、下記を追記

[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqld]
character-set-server = utf8

ここで、一旦 mysql を起動して、mysqlクライアントからユーザー、データベースを作っちゃいましょう。

service mysql start
mysql -u root -p

ここから mysql シェル

まずユーザー(wpuser)を作成。パスワードは適当に 0000 とか。
> CREATE USER wpuser IDENTIFIED BY '0000';

WORDPRESS用のデータベースを作っときます。
> CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8;

作ったユーザーに、データベースの権限を全許可します。
> GRANT all privileges ON wordpress.* TO wpuser

> quit

あと、phpMyAdmin は必須っすね。aptコマンドでインストールしてもいいんですが、最新のものをマニュアルで入れときます。

#一旦rootを抜けて、一気に片付けましょう。
exit
cd /var/www/html
wget -O - https://files.phpmyadmin.net/phpMyAdmin/4.7.5/phpMyAdmin-4.7.5-all-languages.tar.gz | tar xvzf -
mv phpMyAdmin-4.7.5-all-languages phpMyAdmin
cd phpMyAdmin
cat config.inc.sample.php | sed -r 's/^(\$cfg\['\''blowfish_secret'\''\]) *= *'\''.*'\''/\1 = '\''32_moji_no_randamu_na_ei_suuji_x'\''/g' > config.inc.php

WSLではサービスとして動かすことができないので、bash を終わらせるとapache,mysqlのデーモンが全部落ちます。まぁバックグラウンドで走らせてしまうと、Windows Serverの顔が立たない(^^;ので致し方ありません。
bashを立ち上げるたびに、

sudo sh -c 'service mysql start; service apache2 start' 

とかやる必要があります。まぁこれは、alias 一個作っとけばいいだけなんすけどね。

WORDPRESSでのサイト構築ではもう十分な感じ。でも WSLのubuntuで docker が動いたらこんなメンドクサイことしないで済むのになぁ・・・。いずれ対応してくれることを祈る。

実際にはシェルスクリプトにすることで、インストールしてコマンド一発で、LAMP環境をセットアップできるようしています。
そのスクリプトは、こっち。

CentOS から Windows上のSQLServerへの道のり

副題: Road to SQLServer on Windows. (^^;

【追記 2019年3月12日】
CentOS7 と PHP7系については、こちらにまとめてます。
https://ptsv.jp/repository/rhel7-php71-sqlserver/


備忘録です。

今まで、CentOS6からSQLServerへのアクセスは、FreeTDS を使っていました。PHPからはPDO_DBLIB 経由。これは結構簡単にセットアップできました。yum で FreeTDSとか、もっと簡単に、php-sybaseなりphp-mssqlなり入れれば依存関係で必要なライブラリは勝手にインストールされますしね。設定自体も、/etc/freetds.conf と /etc/locales.conf とかいじればあとは、PDOからアクセスできます。

で、非常に疎かったので知らなかったんですが、Microsoftから Linux向けのSQLServer ODBCドライバが供給されている、とのこと。

今更何言ってんの??? って感じですが、知らなかったものはしょうがない。説明通りにやってみます(^^;

# curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit root

$ sudo ACCEPT_EULA=Y yum install msodbcsql
$ sudo ACCEPT_EULA=Y yum install mssql-tools
$ sudo yum install unixODBC-devel
$ sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
$ sudo ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

エラーもなく、インストール完了。
ここまでは説明書通り。

さて、ここから設定。

/etc/odbcinst.ini を開くと、以下のセクションが追加されているはず。

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.6.0
UsageCount=1

そして、/etc/odbc.ini を編集します。僕の環境ではodbc.ini は空のファイルでした。
データソースを記述していきます。

[Development]
Driver = ODBC Driver 13 for SQL Server #odbcinst.ini に追加されたセクション名
Description = for development databse #適当
Trace = Yes #わからん。 
Server = 10.1.1.1 #稼働中のSQLServerのIPアドレスもしくはホスト名 ファイヤーウォールでアクセス許可を出すこと。
Port = 1433 #SQLServer Configuration Manager で TCP/IP を有効に。
Database = Sample  #データベース名

この状態で、まず、isql コマンドで接続してみます。書式は、isql [データソース名] [ユーザー名] [パスワード]ですね。

$ isql Development dbuser dbpass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from hogehoge
+------------+
|            |
+------------+
| 5500       |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL>

無事接続できました!

今度は、マイクロソフトが提供しているツールを使用してみましょう。

$ sqlcmd -S 10.1.1.1  -U dbuser
Password: xxxxxx
1> use Sample
2> go
データベース コンテキストが 'Sample' に変更されました。
1> select count(*) from hogehoge
2> go

-----------
       5500

(1 rows affected)
1>

接続できました。

次に、PHP で PDO_ODBC経由で接続してみます。
DSNには、odbc.ini で設定した、データソース名ではなく、ドライバ名・サーバー名・データベース名をそれぞれ直接指定してみました。

$ php -a
Interactive shell

php > $pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=10.1.1.1;Database=Sample','dbuser','dbpass');
php > $sth = $pdo->query('select count(*) from hogehoge');
php > echo $sth->fetchColumn();
5500
php >

接続できました。

日本語(UTF-8)が正常にCRUDできるか、まだテストしていませんが、とりあえずは、正常に接続できることを確認できたことでヨシとしましょう。

:continue 続きはこちら