LoginSignup
2
4

More than 1 year has passed since last update.

@nia_tn1012(Nia Tomonaka)

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

Last updated at Posted at 2015-09-28

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

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

私のブログでは、セキュリティ対策としてWordPressへのログイン履歴を残すためにプラグイン「Crazy Bone(狂骨)（→Github）」を入れています。

ログイン履歴を見ると、ログインエラーの履歴がずらり。
wl-00.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

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

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

「wp_user_login_log」テーブルの構造に移動したら、「エクスポート」タブをクリックします。
wl-02b.png

エクスポート画面にて、エクスポートから「MS Excel用のCSV」を選択し、オプションにある「1行目にフィールド名を追加する」のチェックボックスをオンにします。
wl-03.PNG

ページを下にスクロールして、右下にある「実行する」ボタンをクリックします。
wl-04.PNG

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

3. ADO.NETとLINQでログイン履歴から、ログインエラー時のユーザー名及びパスワードを割り出す

CSVファイルにエクスポートしたら、それを分析するプログラムを作成していきます。

3.1. ADO.NETを使ってCSVファイルをDataTableに読み込み

.NET Frameworkでは、OleDbConnectionクラスを使ってAccessのデータベースやExcelのワークシート、CSVファイルをデータソースとして接続することができます。

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

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クラスを使って、接続文字列を構成することもできます。

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

CSVファイルに接続したら、OleDbCommandクラスでSQL文を構成し、OleDbDataAdapterクラスを使ってCSVファイルのデータをDataTableに読み込みます。

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」であるレコードを抽出します。

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>エラー: 無効なユーザー名です。 <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>エラー: 無効なユーザー名です。 <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:")).*?(?=(";))*

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

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

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

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

ユーザー名によるログイン試行回数が多い順にソートし、ログインエラー時のユーザー名と試行回数を出力
// 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:")).*?(?=(";))"*

ログインエラー時のパスワードを取り出し、試行回数が多い順に出力
// パスワードを取り出すための正規表現です。
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()}" );
}

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

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. 参考サイト

2
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
4