Posted at

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

More than 3 years have passed since last update.

ニアです、こんにちはー!

今回は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のセキュリティ対策はしっかりとね!

それでは、See you next time!


5. 参考サイト