LinuxのPHPからWindowsのSQLServerに接続

WIndows上のSQLServerに、Linuxサーバー上のPHPプログラムから接続する手順。

Microsoftがドライバーを提供していて、以下のチュートリアルを参考に作業。

https://learn.microsoft.com/ja-jp/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver16

今回のLinuxサーバーはCentOS Stream 9なので、Red Hatの部分を参考に進める。

1)ODBCドライバー

1-1)Linux用ODBCドライバー

https://learn.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15&tabs=redhat18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

レポを更新して

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

必要なものをインストールする。

sudo dnf install msodbcsql18
sudo dnf install mssql-tools18
sudo dnf install unixODBC-devel

1-2)WindowsServer側で接続許可

Windows側で、SQLServerのTCP/IP接続設定と、ファイアーウォールでLinuxサーバーからの接続許可をする。

1-3)ODBC設定

/etc/odbc.iniを編集して、DSNを定義。

https://learn.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?source=recommendations&view=sql-server-ver16

1-4)接続確認

sqlcmdで接続確認。暗号化を有効にしているので、証明書エラーになるのでCオプションを指定する。

/opt/mssql-tools18/bin/sqlcmd -D -C -S (DSN名) -U (ユーザー名) -P  (パスワード)

2)PHPからの接続

PHP用のドライバーについては、以下ページ。

https://learn.microsoft.com/ja-jp/sql/connect/php/overview-of-the-php-sql-driver?view=sql-server-ver16

SQLSRV ドライバーと PDO_SQLSRV ドライバーがあるが、今回はSQLSRV ドライバーを使うこととした。

2-1)SQLSRV ドライバーのインストール

以下コマンドでインストール。

sudo pecl install sqlsrv

ConoHaのVPSのCentOS Stream 9では、足りないものがありmakeエラーとなる。

(エラー)
/bin/ld: cannot find -lltdl
collect2: error: ld returned 1 exit status
make: *** [Makefile:238: sqlsrv.la] Error 1
ERROR: `make' failed

libtool-ltdl-develが足りていないよう。

sudo dnf install libtool-ltdl-devel

ところが、見つからないと出る。どうやら、repoの設定が無効になっているようで、/etc/yum.repos.d/centos.repoを修正して、crbを有効にすることで解決。

:
[crb]
:
enabled=1     ※ここを0から1に変更
  
[crb-debuginfo]
:

2-2)PHPの設定

起動時にドライバーを読み込ませる。

https://learn.microsoft.com/ja-jp/sql/connect/php/loading-the-php-sql-driver?view=sql-server-ver16#loading-the-driver-at-php-startup

/etc/php.d に、以下内容で30-sqlsrv.iniを作成。

extension=sqlsrv.so

2-3)接続確認

サンプルプログラムを修正して、ブラウザから確認する。

https://learn.microsoft.com/ja-jp/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-ver16#sqlsrv-example

SQLSRV ドライバーのAPIについては、以下ページ。

(APIリファレンス)

https://learn.microsoft.com/ja-jp/sql/connect/php/sqlsrv-driver-api-reference?view=sql-server-ver16

自己証明書を利用しているので、接続オプションにTrustServerCertificateを追加した。

<?php
$serverName = "myServer,9999";  # 9999:PortNo
$connectionOptions = array(
    "TrustServerCertificate" => "yes", # 自己証明書を信頼
    "database" => "myDatabase",
    "uid" => "myUser",
    "pwd" => "myPassword"
);
:

接続オプションについての詳細は以下ページ。

(接続オプション)

https://learn.microsoft.com/ja-jp/sql/connect/php/connection-options?view=sql-server-ver16

[Django]サーバーの構築

VisualSdudioで開発していたDjango3.2のプロジェクトを、CentOSサーバーに公開する手順についてのメモ書き。

デプロイに関する3.2のドキュメントは以下。

https://docs.djangoproject.com/ja/3.2/howto/deployment/

今回は最終的にuWSGIでサーバーを構築する予定で、設定の流れは以下が参考になる。

https://uwsgi.readthedocs.io/en/latest/tutorials/Django_and_nginx.html

流れとしては、以下の順に順次確認して最終系にする。

  1. Django
  2. uWSGI –> Django
  3. nginx –> uWSGI –> Django
  4. WAF –> nginx –> uWSGI –> Django

まずはDjangoプロジェクトを設定して単体で確認する。

0.今回の条件

  • クライアント開発環境:Visual Studio Community 2022
  • Djangoバージョン:3.2
  • サーバー:CentOS Stream9
  • WAF:nginx+Naxsiサーバー
  • ソース管理:自前Gitサーバー

1.Djangoプロジェクト構築

1-1. Python

サーバーにインストールされているpythonのバージョンを確認。

python --version

今回のサーバーは3.9で、クライアント開発時と同じのためそのまま使うことにし、pyenvは使わないこととした

1-2. pip,git

dnf install pip
dnf install git

1-3. アプリ実行ユーザー作成

Django実行用のアプリユーザー(仮にappユーザー)を作成し、ユーザー変更する。

adduser app
su - app

1-4. Gitクライアント設定

gitサーバーは同じセグメント内の別サーバーにあり、鍵認証としている。

1)appユーザーで、鍵を作成する。

ssh-keygen -t rsa -b 4096

2)作成した公開鍵を、Gitサーバーに登録

作成された公開鍵(~/.ssh/id_rsa.pub)を、Gitサーバーのgitユーザーのauthorized_keysに登録する。

1-5. Djangoソース取得

Djangoプロジェクト用のディレクトリを作成(仮に~/django)して移動して、gitからソースを取得(クローン)する。

git clone ssh://git@10.10.10.1:9999/~/myapp.git

1-6. python実行環境

プロジェクトフォルダ配下に環境を構築し、必要なパッケージをインストール。

cd myapp
virtualenv env
cd env
source bin/activate
pip install -r ../requirements.txt

appユーザーの.bash_profileにvirtualenvのactivateを仕込んでおく。

1-7. Djangoの設定

設定をサーバー用に修正して、デプロイチェック。

https://docs.djangoproject.com/ja/3.2/howto/deployment/checklist/

まずはDEBUG=Flaseとする。SSL周りの設定については、WAFからの呼び出しのため後に設定。

ログファイルのディレクトリとしては以下を作成する。

mkdir /var/log/django
chown app:app /var/log/django

データベースについて、新規に作成した場合は、マイグレーションとスーパーユーザーを作成する。

https://docs.djangoproject.com/en/3.2/topics/migrations/

1-8. Django単体の確認

Django開発サーバーを立ち上げて、起動できることを確認する。

python manage.py runserver 0.0.0.0:8000

ブラウザで確認すると、DEBUG=Flaseとしているためstatic以下が取得できないが、この時点では無視して、後でnginx設定時に対応する。

2.uWSGI構築

2-1. uWSGIインストール

https://uwsgi.readthedocs.io/en/latest/Install.html

dnf groupinstall "Development Tools"
dnf install python-devel
pip install uwsgi

2-2. uWSGI設定

https://docs.djangoproject.com/ja/3.2/howto/deployment/wsgi/uwsgi/

起動用のuwsgi.iniファイルを/home/app/django/myapp/に作成する。設定例は以下。

[uwsgi]
chdir=/home/app/django/myapp/
module=myapp.wsgi:application
master=True
pidfile=/tmp/myapp-master.pid
vacuum=True
max-requests=5000
daemonize=/var/log/uwsgi/daemon-@(exec://date +%%Y-%%m-%%d).log 
http-socket = :8000
#socket=/usr/share/nginx/tmp/uwsgi.sock ※nginxとunixソケットでつなぐ場合

/var/log/uwsgiは以下で作成

mkdir /var/log/uwsgi
chown app:app /var/log/uwsgi

パラメータに関するドキュメントは以下。

https://uwsgi.readthedocs.io/en/latest/Options.html

2-3. 起動コマンド

uWSGIの起動コマンドstart_uwsgi.shを、/home/app/django/myapp/binに作成する。

#!/bin/sh
cd /home/app/django/myapp/
source env/bin/activate
env/bin/uwsgi --ini uwsgi.ini

起動コマンドに実行権を付与して実行し、正常に動作することを確認する。

2-4. uwsgiサービス登録

/usr/lib/systemd/systemに、サービス定義ファイルuwsgi.serviceを作成する。

[Unit]
Description=UWSGI
After=network-online.target
Wants=network-online.target

[Service]
Type=forking
ExecStart=/home/app/django/myapp/bin/start_uwsgi.sh
KillSignal=SIGQUIT
TimeoutStopSec=5
KillMode=mixed
User=app
Group=app

[Install]
WantedBy=multi-user.target

定義ファイルを作成したら、サービスの起動設定を行う。

systemctl enable uwsgi.service
systemctl start uwsgi.service

3.nginx構築

3.1 nginxインストール

dnf install nginx

3.2 static設定

https://docs.djangoproject.com/en/3.2/howto/static-files/#deployment

1)ディレクトリ作成

staticファイル配置用のディレクトリを作成する。nginxから参照できる場所として以下とし、appユーザーからもアクセスできるようにする。

mkdir /usr/share/nginx/static
chmod 777 /usr/share/nginx/static

2)Django設定

Djangoプロジェクトのsetting.pyのSTATIC_ROOTに、上記ディレクトリを設定

STATIC_ROOT = '/usr/share/nginx/static/'

3)ファイル配置

appユーザーにて以下を実行する。

python manage.py collectstatic

3.3 設定

https://uwsgi.readthedocs.io/en/latest/Nginx.html

nginxの設定ファイルに以下を追加。

location / {
    include    uwsgi_params;
    uwsgi_pass 127.0.0.1:8000;
    #uwsgi_pass unix:/usr/share/nginx/tmp/uwsgi.sock; ※unixソケットの場合
}

location /static {
    alias /usr/share/nginx/static;
}

※unixソケットを使う場合はこちらを有効にして、uWSGI側の設定もこれに合わせる。

3.4 起動設定

設定ファイルが問題ないことを確認して、nginxサービスを起動する。

nginx -t
systemctl start nginx

httpでアクセスして正常に表示できることを確認する。

4.WAF設定

4.1 WAFサーバー設定

WAFサーバー経由での公開設定を行い、正式なSSL証明書を取得する。

4.2 DjangoのSSL設定

Djangoのデプロイチェックで保留していたSSL周りの設定を変更する。SECURE_PROXY_SSL_HEADERを設定する場合は、nginx側で、X-Forwarded-Proto ヘッダーを付与するように設定する。

https://docs.djangoproject.com/ja/3.2/ref/settings/#std:setting-SECURE_PROXY_SSL_HEADER

具体的には以下を追加。

proxy_set_header    X-Forwarded-Proto       $scheme;

4.3 Naxsi設定

最初はNAXSIを学習モードにして、ルール調整して、最終的にfail2banを有効にする。

ChromebookのVSCodeで改行コードが勝手に追加される

【現象】

WindowsのVisualStudioで作成したファイルを、ChromebookのVSCodeで編集すると、全行の間に空白行が勝手に追加される。

【原因】

Windowsで作成したファイルは改行コードが「CRLF」となっていたので、VSCode側で「CR」を「LF」に変更するため、「LF」+「LF」となっているため改行が増えてしまっている。

【解決方法】

Chromebookの「Settings」の「Files」の「Linebreaks for saving files」で、「CRLF」に変更してみたが、それでは治らなかった。

ファイルの改行コードを「LF」に変更することで問題は解決。

本番DBを開発用にマスキング

本番データを、開発環境に落とした後に個人情報部分を書き換えるクエリ。あくまでも開発環境用なので、データマスキングツールを使うまでもないので、簡単なクエリで書き換えるというだけ。以下はPostgreSQLでのサンプル。

1.固定名+連番に書き換え

名前(name)を固定値「試験 太郎」+キー番号にする場合。数値型のキーのcustomer_idを6文字以内(先頭から)の文字列として後ろにつけている。

UPDATE customer SET
 name = '試験 太郎' || CAST(customer_id AS character varying(6))
 WHERE name <> '';

「試験 太郎1」・・・「試験 太郎999999」のような感じ。

2.文字列の一部を書き換え

名前(name)の1文字目、3文字目、5文字目を「〇」に置き換える場合。

UPDATE customer SET
 name =  '〇' || SUBSTR(name, 2, 1) || '〇' || SUBSTR(name, 4, 1) || '〇' || SUBSTR(name, 6)
 WHERE  name <> '';

「山本文左衛門」が「〇本〇左〇門」となる。短い「孫文」のような場合、「〇文〇〇」となるので、まあテストデータなので特に気にしない。

3.電話番号

ハイフンで連結されている電話番号(tel)の市外局番だけはそのままで、残りは「9999-9999」として、「03-9999-9999」というような感じにする。

UPDATE customer SET
 tel =  CAST(SPLIT_PART(tel ,'-', 1) AS character varying(5)) || '-9999-9999'
 WHERE tel <> '';

ハイフンの無い携帯番号(mobile_no)の先頭6文字はそのままで、後ろ5文字を「99999」とする例で、「08012399999」という感じになる。

UPDATE customer SET
 mobile_no = SUBSTR(mobile_no , 1, 6)  || '99999'
 WHERE mobile_no <> '';

4.メールアドレス

ドメインはそのままで、アカウント部分を固定値「test」+キー番号とする場合。

UPDATE customer SET
 mail =  'test' || CAST(id_customer AS character varying(10)) || '@' || CAST(SPLIT_PART(mail ,'@', 2) AS character varying(64))
 WHERE mail <> '';

「yamada_tarou@hogehoge.com」がid_customer=869の場合、「test869@hogehoge.com」となる。

[WordPress]BogoとYoast Duplicate Postの問題

【現象】

多言語対応としてBogoを使っているが、翻訳済みの投稿をコピーして別の投稿を作ったところ、元の投稿の翻訳リンクが新しいページとなってしまった。

画面上からは解決する方法は無いようなので、投稿を削除して作り直すか、DBを手で直すしかない。投稿を作り直すのも面倒なので、DBパッチで直したので、参考まで。

【解決方法の調査】

Bogoのソースを見て、どのようにDBを使っているかを調べる。

https://github.com/takayukister/bogo/blob/71897a24c0897bcf0ecdb4d7d412573c94c2b3f8/includes/query.php#L236

メタテーブル(※)の、キー項目’_original_post’と’_locale’を使っていることが分かる。

※??_postmetaテーブル(??:WordPressインストール時の設定による。wpとか)

【対応内容】

DBのメタテーブルのレコード状態を見ておかしいものを直せばいい。自分の状態では、以下を対応した。

  1. コピー先の投稿のメタテーブルに、コピー元を指す余分な‘_original_post’があったので、このレコードを削除。
  2. コピー元の’_original_post’の値を修正。