[C#] ADO.NETとLINQを使って、Crazy Bone(狂骨)で記録したWordPressへのログイン履歴を分析してみよう

こんにちはー、ニアです。
今回はADO.NETとLINQを使って、MySQLからCSVファイルにエクスポートしたWordPressへのログイン履歴を分析したお話です。

# 1. 開設から1年も経っていない個人サイトであろうと容赦ない、ブルートフォースアタックの脅威

私のブログでは、セキュリティ対策としてWordPressへのログイン履歴を残すためにプラグイン「[Crazy Bone(狂骨)](http://dogmap.jp/2013/05/13/crazy-bone/)（→[Github](https://github.com/wokamoto/crazy-bone)）」を入れています。

ログイン履歴を見ると、ログインエラーの履歴がずらり。
![wl-00.PNG](https://qiita-image-store.s3.amazonaws.com/0/78881/ec5c0a22-e1f6-2ca1-3fd7-c105c9b674ad.png)

昨年10月末に開設したばかりにも関わらず、ひどい時には短時間に1万近くのブルートフォースアタックがありました。一応、ログイン試行回数を制限するプラグインも入れており、不正アクセスは防げていますが、とにかくクラッカーは容赦ないです。

そこで、WordPressへのログイン履歴を分析して、ログインエラー時のユーザー名及びパスワードを割り出し、今後のセキュリティ対策の参考にしようと思いました。


## この記事で扱っている環境
この記事で扱っているWordPress、Crazy Bone(狂骨)、MySQL、phpMyAdminは以下のバージョンを使用しております。

* WordPress : 4.3.1
* Crazy Bone(狂骨) : 0.5.5
* MySQL : 5.1
* phpMyAdmin : 3.2.5

# 2. ログイン履歴のデータをエクスポート

Crazy Bone(狂骨)で記録したWordPressへのログイン履歴は、MySQLデータベースの「**wp_user_login_log**」テーブルに格納されます。
![wl-01.PNG](https://qiita-image-store.s3.amazonaws.com/0/78881/9548f742-0164-80fa-bcd7-59739849cd64.png)


サーバー上のデータベースに直接弄るのは何なので、ローカルPCにエクスポートします。
phpMyAdminにログインし、WordPressで使用しているデータベースを開きます。

「構造」タブから「**wp_user_login_log**」テーブルを選択します。

「**wp_user_login_log**」テーブルの構造に移動したら、「エクスポート」タブをクリックします。
![wl-02b.png](https://qiita-image-store.s3.amazonaws.com/0/78881/87644b5d-6e35-458a-2d2a-9211729d10b2.png)

エクスポート画面にて、エクスポートから「**MS Excel用のCSV**」を選択し、オプションにある「**1行目にフィールド名を追加する**」のチェックボックスをオンにします。
![wl-03.PNG](https://qiita-image-store.s3.amazonaws.com/0/78881/5bdb2bc2-4ced-f514-7959-c8ed92c97930.png)

ページを下にスクロールして、右下にある「実行する」ボタンをクリックします。
![wl-04.PNG](https://qiita-image-store.s3.amazonaws.com/0/78881/55124a9a-1b8c-0a99-1dd9-9916f84369ae.png)

ファイルを保存するメッセージが出たら、CSVファイルを保存します。


# 3. ADO.NETとLINQでログイン履歴から、ログインエラー時のユーザー名及びパスワードを割り出す
CSVファイルにエクスポートしたら、それを分析するプログラムを作成していきます。

## 3.1. ADO.NETを使ってCSVファイルをDataTableに読み込み
.NET Frameworkでは、[OleDbConnection](https://msdn.microsoft.com/ja-jp/library/system.data.oledb.oledbconnection.aspx)クラスを使ってAccessのデータベースやExcelのワークシート、CSVファイルをデータソースとして接続することができます。

CSVファイルに接続する場合、OleDbConnectionで接続する文字列を以下のように構成します。

```csharp:OleDbConnectionで接続する文字列を構成
// WordPressへのログイン履歴を格納したCSVファイル名です。
string filename = "wp_user_login_log.csv";

// OleDbConnectionで接続する文字列を構成します。
OleDbConnectionStringBuilder oleDbConStr = new OleDbConnectionStringBuilder();
// データプロバイダーには Microsoft.Jet.OLEDB.4.0 を指定します。
// ※ Microsoft.ACE.OLEDB.12.0でもOKです。但し、Office 2013のみがインストールされている場合、
// 　 Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをインストールする必要があります。
// 　 http://www.microsoft.com/ja-JP/download/details.aspx?id=13255
oleDbConStr["Provider"] = "Microsoft.Jet.OLEDB.4.0";
// データソースにはCSVファイルがあるディレクトリを指定します。
oleDbConStr["Data Source"] = Path.GetDirectoryName( Path.GetFullPath( filename ) ) + @"\";
// Extended Propertiesには Text;HDR=YES;FMT=Delimited を指定します。
// Text : プレーンテキストとして扱います。
// HDR=YES : 1行目をヘッダー行ととして扱います。
// FMT=Delimited : CSVファイルのフォーマットとして扱います。
oleDbConStr["Extended Properties"] = "Text;HDR=YES;FMT=Delimited";

// OleDbConnectionStringBuilderで設定した文字列からOleDbConnectionを生成します。
// usingステートメントでオブジェクトを初期化すると、ステートメントの終了時に接続を閉じます。
using( OleDbConnection oleDbCon = new OleDbConnection( oleDbConStr.ConnectionString ) ){
	// 中略（CSVファイルのデータをDataTableに読み込み）
}
```

OleDbConnectionクラスのコンストラクターから接続文字列を直接指定する他に、[OleDbConnectionStringBuilder](https://msdn.microsoft.com/ja-jp/library/system.data.oledb.oledbconnectionstringbuilder.aspx)クラスを使って、接続文字列を構成することもできます。

今回エクスポートしたCSVファイルの1行目はフィールド名が格納されているので、Extended PropertiesのHDRをYESにし、1行目をヘッダーとして扱います。

CSVファイルに接続したら、[OleDbCommand](https://msdn.microsoft.com/ja-jp/library/system.data.oledb.oledbcommand.aspx)クラスでSQL文を構成し、[OleDbDataAdapter](https://msdn.microsoft.com/ja-jp/library/system.data.oledb.oledbdataadapter.aspx)クラスを使ってCSVファイルのデータをDataTableに読み込みます。

```csharp:CSVファイルのデータをDataTableに読み込み
// 読み込んだデータを格納するDataTableです。
DataTable dt = new DataTable();

// 中略（OleDbConnectionで接続する文字列を構成）

using( OleDbConnection oleDbCon = new OleDbConnection( oleDbConStr.ConnectionString ) ){
	try {
		// SQL文からOleDbCommandを生成します。
		// 使用するフィールドは、「activity_status」と「activity_errors」の2つです。
		// FROMにはCSSファイルの名前を指定します。
		OleDbCommand oleDbCmd = new OleDbCommand( $"SELECT [activity_status], [activity_errors] FROM[{Path.GetFileName( filename )}]", oleDbCon );
		OleDbDataAdapter oleDbAdpt = new OleDbDataAdapter( oleDbCmd );
		// CSVファイルのデータをDataTableに読み込みます。
		oleDbAdpt.Fill( dt );
	}
	catch( Exception ex ) {
		Console.WriteLine( $"CSVファイルの読み込み中にエラーが発生しました。\n{ex.Message}" );
		dt = null;
	}
}
```


## 3.2. LINQを使ってDataTableから、ログインエラー時のユーザー名及びパスワードを取り出す
CSVファイルのデータをDataTableに格納したら、LINQを使ってログインエラー時のユーザー名やパスワードを取り出してみましょう。

まずは、DataTableのRowsプロパティでレコードのコレクションを取得し、OfType\<DataRow\>メソッドで**IEnumerable\<DataRow\>型**に変換して、LINQが利用できるようにします。

次にWhereメソッドで「**activity_status**」フィールドの値が「**login_error**」であるレコードを抽出します。

```csharp:activity_statusフィールドの値がlogin_errorであるレコードを抽出
// activity_statusフィールドの値がlogin_errorであるレコードを抽出します。
var loginError = dt.Rows.OfType<DataRow>().Where( _ => _["activity_status"].ToString() == "login_error" );
```

今度は「**activity_errors**」フィールドからユーザー名を取り出したいのですが、そのフィールドの値は以下のようにPHPでシリアライズしたデータの形となっています。

>a:3:{s:6:"errors";a:1:{s:16:"invalid_username";a:1:{i:0;s:177:"\<strong\>エラー</strong>: 無効なユーザー名です。 \<a href="\[ブログのアドレス\]/wp-login.php?action=lostpassword"\>パスワードをお忘れですか ?\</a\>";}}**s:10:"user_login";s:5:"admin";**s:13:"user_password";s:3:"123";}

>a:3:{s:6:"errors"";a:1:{s:16:"invalid_username";a:1:{i:0;s:177:"\<strong\>エラー</strong>: 無効なユーザー名です。 \<a href="\[ブログのアドレス\]/wp-login.php?action=lostpassword"\>パスワードをお忘れですか ?\</a\>";}}**s:10:"user_login";s:8:"wpengine";**s:13:"user_password";s:8:"password";}
...

そこで、正規表現を使います。「**s:10:"user_login";**」を目印にして、パターン文字列を作成します。
> **(?<=(s\:10\:"user_login";s\:\d*\:")).*?(?=(";))**

```csharp:「activity_errors」フィールドからユーザー名を取り出す正規表現
// ユーザー名を取り出すための正規表現です。
Regex loginErrorUserRes = new Regex( "(?<=(s:10\\:\"user_login\";s\\:\\d*\\:\")).*?(?=(\";))" );
```

その正規表現を使って「activity_errors」フィールドからユーザー名を取り出し、GroupByメソッドで同じユーザー名同士でグループ化します。

```csharp:正規表現を使ってユーザー名を取り出し、グループ化
// 正規表現からユーザー名を取り出し、同じユーザー名同士でグループ化します。
var loginErrorUsers = loginError.Select( _ => loginErrorUserRes.Match( _["activity_errors"].ToString() ).Value ).GroupBy( _ => _ );
```

あとは、OrderByDecendingメソッドとCountメソッドでユーザー名によるログイン試行回数が多い順にソートし、ログインエラー時のユーザー名と試行回数をコンソール画面に出力します。

```csharp:ユーザー名によるログイン試行回数が多い順にソートし、ログインエラー時のユーザー名と試行回数を出力
// OrderByDescendingとCountでログイン試行回数が多い順に、
// ThenByで同じ試行回数の中では、文字列の昇順にソートし、
// ログインエラー時のユーザー名と試行回数を出力します。
foreach( var item in loginErrorUsers.OrderByDescending( _ => _.Count() ).ThenBy( _ => _.Key ) ) {
	Console.WriteLine( $"{item.Key} : {item.Count()}" );
}
Console.WriteLine();
```

ログインエラー時のパスワードは、「**s:13\:"user_password";**」を目印に以下のようなパターン文字列からなる正規表現を使って、「activity_errors」フィールドから取り出します。
>**"(?<=(s:13\:"user_password";s\:\d*\:")).*?(?=(";))"**

```csharp:ログインエラー時のパスワードを取り出し、試行回数が多い順に出力
// パスワードを取り出すための正規表現です。
Regex loginErrorPassRes = new Regex( "(?<=(s:13\\:\"user_password\";s\\:\\d*\\:\")).*?(?=(\";))" );
// 正規表現からパスワードを取り出し、同じパスワード同士でグループ化します。
var loginErrorPasses = loginError.Select( _ => loginErrorPassRes.Match( _["activity_errors"].ToString() ).Value ).GroupBy( _ => _ );
// OrderByDescendingとCountでログイン試行回数が多い順に、
// ThenByで同じ試行回数の中では、文字列の昇順にソートし、
// ログインエラー時のパスワードと試行回数を出力します。
Console.WriteLine( "パスワード : 試行回数\n-----------------------------" );
foreach( var item in loginErrorPasses.OrderByDescending( _ => _.Count() ).ThenBy( _ => _.Key ) ) {
	Console.WriteLine( $"{item.Key} : {item.Count()}" );
}
```

これらをまとめたプログラムを以下に示します。

```csharp:Program.cs
using System;
using System.Linq;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;

namespace WPLog {
	class Program {
		static void Main( string[] args ) {

			// WordPressへのログイン履歴を格納したCSVファイル名です。
			string filename = "wp_user_login_log.csv";
			// 読み込んだデータを格納するDataTableです。
			DataTable dt = new DataTable();

			// OleDbConnectionで接続する文字列を構成します。
			OleDbConnectionStringBuilder oleDbConStr = new OleDbConnectionStringBuilder();
			// データプロバイダーには Microsoft.Jet.OLEDB.4.0 を指定します。
			// ※ Microsoft.ACE.OLEDB.12.0でもOKです。但し、Office 2013のみがインストールされている場合、
			// 　 Microsoft Access データベース エンジン 2010 再頒布可能コンポーネントをインストールする必要があります。
			// 　 http://www.microsoft.com/ja-JP/download/details.aspx?id=13255
			oleDbConStr["Provider"] = "Microsoft.Jet.OLEDB.4.0";
			// データソースにはCSVファイルがあるフォルダーを指定します。
			oleDbConStr["Data Source"] = Path.GetDirectoryName( Path.GetFullPath( filename ) ) + @"\";
			// Extended Propertiesには Text;HDR=YES;FMT=Delimited を指定します。
			// Text : プレーンテキストとして扱います。
			// HDR=YES : 1行目をヘッダー行ととして扱います。
			// FMT=Delimited : CSVファイルのフォーマットとして扱います。
			oleDbConStr["Extended Properties"] = "Text;HDR=YES;FMT=Delimited";

			// OleDbConnectionStringBuilderで設定した文字列からOleDbConnectionを生成します。
			// usingステートメントでオブジェクトを初期化すると、ステートメントの終了時に接続を閉じます。
			using( OleDbConnection oleDbCon = new OleDbConnection( oleDbConStr.ConnectionString ) ) {
				try {
					// SQL文からOleDbCommandを生成します。
					OleDbCommand oleDbCmd = new OleDbCommand( $"SELECT [activity_status], [activity_errors] FROM[{Path.GetFileName( filename )}]", oleDbCon );
					OleDbDataAdapter oleDbAdpt = new OleDbDataAdapter( oleDbCmd );
					// CSVファイルのデータをDataTableに読み込みます。
					oleDbAdpt.Fill( dt );
				}
				catch( Exception ex ) {
					Console.WriteLine( $"CSVファイルの読み込み中にエラーが発生しました。\n{ex.Message}" );
					dt = null;
				}
			}

			if( dt != null ) {
				// activity_statusフィールドの値がlogin_errorであるレコードを抽出します。
				var loginError = dt.Rows.OfType<DataRow>().Where( _ => _["activity_status"].ToString() == "login_error" );
				// ログインエラー時のレコード数を出力します。
				Console.WriteLine( $"ログインエラー時のレコード数 : {loginError.Count()}\n" );

				// ユーザー名を取り出すための正規表現です。
				Regex loginErrorUserRes = new Regex( "(?<=(s:10\\:\"user_login\";s\\:\\d*\\:\")).*?(?=(\";))" );
				// 正規表現からユーザー名を取り出し、同じユーザー名同士でグループ化します。
				var loginErrorUsers = loginError.Select( _ => loginErrorUserRes.Match( _["activity_errors"].ToString() ).Value ).GroupBy( _ => _ );
				// OrderByDescendingとCountでログイン試行回数が多い順に、
				// ThenByで同じ試行回数の中では、文字列の昇順にソートし、
				// ログインエラー時のユーザー名と試行回数を出力します。
				Console.WriteLine( "ユーザー名 : 試行回数\n-----------------------------" );
				foreach( var item in loginErrorUsers.OrderByDescending( _ => _.Count() ).ThenBy( _ => _.Key ) ) {
					Console.WriteLine( $"{item.Key} : {item.Count()}" );
				}
				Console.WriteLine();

				// パスワードを取り出すための正規表現です。
				Regex loginErrorPassRes = new Regex( "(?<=(s:13\\:\"user_password\";s\\:\\d*\\:\")).*?(?=(\";))" );
				// 正規表現からパスワードを取り出し、同じパスワード同士でグループ化します。
				var loginErrorPasses = loginError.Select( _ => loginErrorPassRes.Match( _["activity_errors"].ToString() ).Value ).GroupBy( _ => _ );
				// OrderByDescendingとCountでログイン試行回数が多い順に、
				// ThenByで同じ試行回数の中では、文字列の昇順にソートし、
				// ログインエラー時のパスワードと試行回数を出力します。
				Console.WriteLine( "パスワード : 試行回数\n-----------------------------" );
				foreach( var item in loginErrorPasses.OrderByDescending( _ => _.Count() ).ThenBy( _ => _.Key ) ) {
					Console.WriteLine( $"{item.Key} : {item.Count()}" );
				}
			}
		}
	}
}
```

◆ 実行結果
>ログインエラー時のレコード数 : 623

>ユーザー名 : 試行回数
\-----------------------------
admin : 548
Webmaster : 32
wpengine : 32
myoga-tn : 2
administrator : 1
editor : 1
epruequic : 1
Matthewboug : 1
ohiforizikuk : 1
root : 1
webmaster : 1
wp_admin : 1
wpadmin : 1

>パスワード : 試行回数
\-----------------------------
adminadmin : 10
admin123 : 9
admin : 8
123123 : 7
123456 : 7
hoho17 : 7
password : 7
123456789 : 6
abc123 : 6
administrator : 6
qazwsx : 6
qwe123 : 6
(中略)
sys : 1
uhgeiwhipe : 1
wxk2r57AvS : 1
ytngfhjkz : 1
zaqxsw : 1

※パスワードは種類が多いので、一部のみを載せています。

# 4. 実行結果から分かる、WordPressのセキュリティ対策
私のブログのログイン履歴を分析したところ、645レコード中、ログインエラーは623レコードありました。

## 4.1. ユーザー名「admin」でのブルートフォースアタックが多い件

ログインエラー時のユーザー名で、「**admin**」がなんと**548レコード**（「admin」を含むユーザー名を含めると552レコード）と**全体の9割近く**も占めていました。もし、「admin」をユーザー名かつ管理ユーザーにしていたら、いつか乗っ取られそうです・・・。

次に多いのは「Webmaster」と「wpengine」の2つでした。後者は「WP Engine」が由来かも（注：「ペンギン」のスペルは「Penguin」です）。

ちなみに私のブログでは、「admin」は存在しないです。

## 4.2. 単純なパスワードや推測されやすいパスワードはダメ、絶対
ログインエラー時のパスワードでも、**「admin」を含む文字列**がそこそこ多かったです。他にも**「password」やドメイン名を含む文字列**、**キーボード上で隣接している文字列（「qwerty」や「qazwsx」、「123456」、「q1w2e3r4」など）**もありました。

サイトを見た時、容易に推測できないようなパスワードに設定するのが良いですね。
あとは定期的にパスワードを変更するのも手です。

WordPressのセキュリティ対策はしっかりとね！

# 5. 参考サイト
* CSV形式のファイルをDataTableや配列等として取得する | DOBON.NET  
  http://dobon.net/vb/dotnet/file/readcsvfile.html