C# DataTableをモデルクラスへ代入

直接カラム名を書いてしまうことが多いけど、リフレクションで作ってみる。

 

SSH、SSL/TLS メモ

SSH、SSL/TLSは分かりづらいのでメモ

SSH

1.ユーザーがサーバーに公開鍵を渡しておく。

2.ユーザーが秘密鍵を使って署名を作成、その署名をサーバー側が公開鍵を使って検証する。

3.公開鍵はユーザーも持っていて(秘密鍵に含まれる)ユーザーと公開鍵の対応が適切かどうかも判断。

公開鍵送付>公開鍵照合>署名送付>署名検証
という流れ。

SSL/TLS

1.サーバーはユーザーに公開鍵を送付

2.ユーザーは公開鍵を使って共通鍵を暗号化しサーバーに送付

3.サーバーは暗号化された共通鍵を秘密鍵で復号

4.サーバーとユーザーは一致した共通鍵を使って暗号通信

基本的にSSLの通信は共通鍵暗号となる。

この方法では、サーバーがユーザーに送る公開鍵が正しいかどうかチェックする必要がある。

1.サーバーは通信用公開鍵と署名を送付
(ここでの署名は通信用公開鍵のハッシュを署名用の秘密鍵で暗号化したもの)
(そもそも署名とはハッシュ化し秘密鍵で暗号化すること)
(認証局は署名用の秘密鍵を保持している)
(ここで渡すものがSSLサーバー証明書となる)

2.ユーザーは署名を署名用公開鍵で復号化しハッシュを得る

3.受け取った通信用公開鍵のハッシュを生成、比較し一致すれば公開鍵が正しいと判断

実際、通信用公開鍵と署名を入れ替えられてしまう恐れがあるため、通信用公開鍵+ドメイン名などの情報ファイル(CSR)に対して署名する。さらに、認証局自体の正当性チェックのための中間CA証明書もあり、通信用公開鍵+署名+中間CA証明書が初回リクエストでのサーバーから返すものとなる。

認証>通信用の公開鍵、秘密鍵が使い捨てで生成される。

サーバーでのSSL設定手順。

1.サーバーで秘密鍵からCSR作成。

2.CSRを認証局に渡しSSLサーバー証明書の発行を依頼

3.ドメイン認証

4.SSLサーバー証明書と中間CA証明書が届き、サーバーに設置

GCP Cloud SQL(MySQL) 使い方

先日VPS(Indigo)のSQLite→MySQLの実験を行ってみたが、GCPでもやってみることにした。

・初期設定

Cloud SQL 無料トライアルを実行する。
(既にクレジット等の情報は登録済みなのでそのまま完了)

コンソールにログイン>左側メニューのSQLを選択。
プロジェクト(すでにMyFirstProjectが存在している)をクリック。
インスタンスを作成をクリック。
MySQLをクリック。

インスタンスID、rootパスワードを入力する。
無料トライアルだが、実際運用を始めれば安く利用したいので、
asia-northeast1(東京)/db-f1-micro
SSD/10GB
自動バックアップしない
を選択する。インスタンス生成には少し時間がかかる。

・インスタンス作成後

コンソールにログイン>左側メニューのSQLを選択。
作成したインスタンスを選択。

左側メニューの接続を選択。
SSL接続のみ許可をクリック。
クライアント証明書を作成をクリックし、一意のIDを追加し作成。

・サーバー証明書:server-ca.pem
・クライアントの公開鍵証明書:client-cert.pem
・クライアント秘密鍵:client-key.pem
という3つのファイルをダウンロードする。

同じく左側メニューの接続から、ネットワークを追加をクリック。
ネットワークの項目:0.0.0.0/0
を追加し完了>保存を実行。

左側メニューの概要を選択、パブリックIPアドレスを確認。

・MySQL Wokbenchから接続

Standard TCP/IPを選択。

Parametersタブより
Hostname:パブリックIPアドレス
Port:3306
Username:root
パスワード:インスタンス作成時のrootパスワード

SSLタブより
Use SSL:Require and Verify CA
SSL Key File, SSL CERT File, SSL CA File
それぞれ、ダウンロードしたファイルを選択

とりあえずこれでつながる。

・C#(MySqlConnection)から接続

.Netではpemが使えないため、
(https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-ssl-pfx.html)
opensslのある環境上で(今回はVPSで実行)
openssl pkcs12 -export -inkey client-key.pem -in client-cert.pem -out test.pfx
を実行し、pfxファイルを作成する。(test部は任意の名前)

接続文字列
“server=IPアドレス;port=3306;database=DB名;user=MySQLユーザー名;password=MySQLパスワード;SslMode=Required;CertificateFile=test.pfx;CertificatePassword=pfxパスワード”;

MySQLのConnectorはNuGetでもインストーラーでもいける。インストーラなら参照設定から登録。

・VBAからの接続

 

・SQLiteのデータ整形

以下参考。

Ubuntu(Indigo) 初期設定

MySQLにインポート

・データベース作成

コンソール>SQL>インスタンス選択>データベース>データベースの作成をクリック。

文字セット:utf8
照合:デフォルトの照合

・ファイルアップロード(Storage)

コンソール>Storage>ブラウザ>バケットを作成をクリック。

バケットに一意の名前をつける。

ロケーションタイプ:Region
ロケーション:asia-northeast1(東京)
ストレージクラス:Standard
アクセス制御:均一

コンソール>Storage>ブラウザ>バケットを選択>ファイルをアップロードをクリック。

・インポート

コンソール>SQL>インスタンス選択>概要>インポートをクリック。

ソースにファイルを指定:アップロードしたファイル選択
ファイル形式:SQL
インポート先:作成したデータベース名

VPSのMySQLへはSQLiteで作成したファイルを直接インポートできたが、GCPではできなかったため、とりあえず今回はSQLite>MySQL(VPS)>MySQL(GCP)という流れになった。

Python 組み込みサーバー

 

・組み込みサーバーでCGIを処理

Win10設定>アプリ>アプリと実行エイリアス
python.exeをオフにする

sysdm.cpl>詳細設定>環境変数
からパスを通す
C:\Users\mail\AppData\Local\Programs\Python

バッチファイルを作成

バッチと同じ階層にpython.pyを作成

バッチと同じ階層にindex.htmlを作成

binファルダ以下にtest.pyを作成

 

Python XML・Json

 

Python データベース基本

 

Python ファイル関連

 

Python 基本文法

新しい言語を少し触ってみようかなということでPythonをはじめた。

VBA 並び替え

いつも忘れてしまうので。

 

Ubuntu(Indigo) 初期設定

ローカルにあるSQLiteをIndigo上のMySQLにエクスポートする実験。

SSH接続

公開鍵方式でのSSH接続、OSでUbuntuを選択すると、自然とユーザー名はubuntuとなる模様。

ちなみに、コンソールではなく自分でSSH設定する場合。

TeraTerm>設定>SSH鍵生成
公開鍵(id_rsa.pub)、秘密鍵(id_rsa)を保存する。

サーバーホームディレクトリ>.ssh>authorized_keys
の中にid_rsa.pubの中身を貼り付け。
(他に書き込みがある場合、新しい行でも大丈夫)

ufw

最初は起動されていない

sshを許可
sudo ufw allow 22/tcp;
あるいは、
sudo ufw allow ssh;

一度だけ起動なら、
sudo ufw enable;
自動起動なら、
sudo systemctl enable ufw
あるいは、
sudo /etc/init.d/ufw start

再起動
sudo ufw reload;

状態
sudo ufw status;

MySQL

インストール
sudo apt-get install mysql-server

バージョン確認
sudo mysql –version

my.cnfを探す
sudo mysql –help | grep my.cnf
今回は
/etc/mysql/mysql.conf.d/mysqld.cnf

bind-address(ここで指定したIPのみ接続を受け付ける)をコメントアウト。接続するIPがわかる場合は指定しても大丈夫だが、複数のIPは指定できない。

文字コード確認
show variables like ‘char%’;

| character_set_client | utf8
| character_set_connection | utf8 
| character_set_database | latin1 
| character_set_filesystem | binary 
| character_set_results | utf8 
| character_set_server | latin1 
| character_set_system | utf8 
| character_sets_dir | /usr/share/mysql/charsets/ 

mysqld.cnfに下記を追加する。
[mysqld]

character-set-server = utf8

これで、utf8に変わる。

| character_set_client | utf8 
| character_set_connection | utf8 
| character_set_database | utf8 
| character_set_filesystem | binary 
| character_set_results | utf8 
| character_set_server | utf8 
| character_set_system | utf8 
| character_sets_dir | /usr/share/mysql/charsets/ 

DB作成
create database db character set utf8;

ユーザー作成
mysql > create user user@host identified by ‘1234’;
ちなみに、auth_socket認証とする場合、
mysql > create user user@host identified with auth_socket;

MySQLのrootアカウントは標準でauth_socket認証しており、パスワード設定しても、パスワード無しでログインできる。ここで少しつまづいた。

権限設定(ユーザー作成)
mysql > grant select,insert,update,delete on db.table to user@host
ちなみに、ユーザーが存在しない場合作成されるので、
grant select,insert,update,delete on db.table to user@host identified by ‘1234’;
とすればパスワード付きで作成される。

権限設定(ユーザー作成)をシェルからワンライナー
pass=$(head /dev/urandom | tr -dc a-km-np-z1-9 | head -c 13); sudo mysql -u root -e “grant select,insert,update,delete on db.table to user@host identified by ‘${pass}'”; echo $pass;

権限確認
mysql > show grants for user@host;

権限削除
mysql > revoke all, grant option from user@host;

パスワード設定(変更)
mysql > alter user user@host identified by ‘2345’;
あるいは、
mysql > update mysql.user set authentication_string=password(‘3456′) where user=’user’;

パスワード設定をシェルからワンライナーの場合、
pass=$(head /dev/urandom | tr -dc a-km-np-z1-9 | head -c 13); sudo mysql -u root -e “alter user user@host identified by ‘${pass}'”; echo $pass;

アカウント情報確認
mysql > select host, user, authentication_string, plugin from mysql.user;

MySQL SSL接続

状態確認
show variables like ‘%ssl%’;

| have_openssl | YES
| have_ssl | YES
| ssl_ca | ca.pem
| ssl_capath
| ssl_cert | server-cert.pem
| ssl_cipher
| ssl_crl
| ssl_crlpath
| ssl_key

初回起動時に、mysql_ssl_rsa_setup()が実行され、このようにSSLが有効になっていた。MySQLのデータディレクトリ以下に生成される。

ca.pem:自己署名CA証明書
ca-key.pem:CA秘密鍵
server-cert.pem:サーバ用公開鍵
server-key.pem:サーバ用秘密鍵
client-cert.pem:クライアント用公開鍵
client-key.pem:クライアント用秘密鍵
private_key.pem:caching_sha2_password, sha256_password用秘密鍵
public_key.pem:caching_sha2_password, sha256_password用公開鍵

データディレクトリ確認
show variables like ‘%datadir%’;

ここでpermission denied
となる場合、
sudo su

データディレクトリから、
client-cert.pem
client-key.pem
ca.pem (必要なら)
をダウンロードする。

ユーザー状態確認
select user,host,ssl_type from mysql.user (anyはssl必須)
show grants for app_user@’%’;
select * from mysql.user where user=’app_user’ \G;

全ての権限を削除
revoke all, grant option from app_user@’%’;

権限追加
grant all on *.* to app_user@’%’ identified by ‘123’ require X509; (SSL / X509)

SSL
暗号化接続はサーバーのcert,keyのみなので、クライアントのcert,keyは指定しなくても大丈夫。

X509
クライアントを証明する必要があるためクライアントにcert,keyを指定する必要がある。

ここの指定とは別にクライアント側からもオプションがある。

・DISABLED
・PREFERRED (default)
サーバが暗号化接続をサポートしている場合は暗号化接続。接続できない場合は通常の接続。
・REQUIRED
サーバー認証を行なって接続する。
・VERIFY_CA
サーバー認証とクライアント認証を行って接続する。

MySQL 外部からの接続

接続もとが固定IPならいいが、そうではない場合、MySQLのbind-addressは複数指定できず、ユーザーのホスト部を頻繁に変更するのも手間なのでufwで弾くのが簡単。

sudo ufw allow from IPアドレス to any port 3306 proto tcp
これで、特定のIPのみMySQLに接続できる。

IPアドレスが変更になったら、
sudo ufw status numbered
でNoを確認し、
sudo ufw delete 番号
(複数不可)
で該当ルールを削除し、再度、
sudo ufw allow from IPアドレス to any port 3306 proto tcp
で変更後のIPアドレスを指定する。

SQLiteをMySQLへエクスポート

とりあえず動かすためだけの設定で実際は細かく調整する必要がある

DB Browser for SQLiteにて、
ファイル>エクスポート>データベースをSQLファイルへ

・insert into にカラム名を保持
・insert文に複数行(VALUES)
にチェック。

・すべてをエクスポート
・古いスキーマを上書き(DROP TABLE した後に CREATE TABLE)
を選択して保存。(DROPしないと何度か実行するときに作成済みのテーブルが残ったりする)

それぞれデータの内容によって違うが今回は書き出したSQLファイルを下記の通り修正。

・先頭のBIGIN TRANSACTIONをSTART TRANSACTIONに変更。

・全てのダブルクォーテーションの削除。
テーブル名やカラム名がダブルクォーテーションで囲われているため。

・CREATE TABLEで型が抜けている部分にtextを追加。

・全ての型をtextに変更。
insertの部分でint等も空の部分がシングルクォーテーションになっているため

・PRIMARY KEY()を削除。
上の行末のカンマも削除

ファイルサイズが大きいので、WinSCPでアップロードし、
mysql -u root -p db名 < SQLファイル.sql
として実行。
※ちなみに、エクスポートは、
mysqldump –single-transaction -u ユーザ名 DB名 > 出力先ファイル名

Workbenchの場合、
Server>Data Import>Import from Self-Contained File
Default Target Schemaからデータベース名を選択しStart Importを実行。

ERROR 2006 (HY000) at line 1820: MySQL server has gone away
Operation failed with exitcode 1

というエラーがでた場合、
set global max_allowed_packet = 16777216;
(一時的)

インポート後にカラム1つずつデータ型を変更する。
alter table table_name modify column_name int not null primary key auto_increment;
のように処理し、
intに関しては、SQLiteで空文字が入ってしまっているとエラーがでるので、
update table_name set column_name = null where column_name = “”;
このようにした。