MySQL – ページ 2 – igreks開発日記

とても便利なGROUP_CONCAT（）

今まであまり使う機会がなく知らなかったのだが、グループ化したカラム以外のカラム値をつなげて返してくれるこの関数はとても便利。
しかもDISTINCTも使えるのが最高に良い。（DISTINCTはUNIONでしか使えないと思っていた。。。）

使用例
tb_family

tb_parent

id	parent_name	family_id
1	山田　太郎	1
2	鈴木　花子	2
3	佐藤　次郎	3
4	山田　よしこ	1

tb_student

id	student_name	family_id
1	山田　息子	1
2	鈴木　息子	2
3	佐藤　息子	3
4	鈴木　娘	2

同じ家庭の保護者（parent）をグループ化して表示

SELECT `tb_family`.id AS family_id 
GROUP_CONCAT(
 `tb_parent`.parent_name
 ORDER BY `tb_parent`.id ASC
 SEPARATOR ',' -- カンマ区切り
 ) AS all_parent 
FROM `tb_family` 
LEFT JOIN `tb_parent` -- parentテーブル結合 
ON `tb\_parent`.family_id = `tb\_family`.id 
GROUP BY `tb\_family`.id -- 家庭IDでグループ化

SELECT `tb_family`.id AS family_id

GROUP_CONCAT(

`tb_parent`.parent_name

ORDER BY `tb_parent`.id ASC

SEPARATOR ',' -- カンマ区切り

) AS all_parent

FROM `tb_family`

LEFT JOIN `tb_parent` -- parentテーブル結合

ON `tb\_parent`.family_id = `tb\_family`.id

GROUP BY `tb\_family`.id -- 家庭IDでグループ化

結果

family_id	all_parent
1	山田　太郎,山田　よしこ
2	鈴木　花子
3	佐藤　次郎

同じ家庭の保護者（parent）と学生（student）をグループ化して表示

SELECT `tb_family`.id AS family_id 
GROUP_CONCAT(
 `tb_parent`.parent_name
 ORDER BY `tb_parent`.id ASC
 SEPARATOR ',' -- カンマ区切り
 ) AS all_parent, 
GROUP_CONCAT(
 `tb_student`.student_name
 ORDER BY `tb_student`.id ASC
 SEPARATOR ',' -- カンマ区切り
 ) AS all_student
FROM `tb_family` 
LEFT JOIN `tb_parent` -- parentテーブル結合 
ON `tb\_parent`.family_id = `tb\_family`.id 
LEFT JOIN `tb_student` -- studentテーブル結合 
ON `tb\_student`.family_id = `tb\_family`.id 
GROUP BY `tb\_family`.id -- 家庭IDでグループ化

SELECT `tb_family`.id AS family_id

GROUP_CONCAT(

`tb_parent`.parent_name

ORDER BY `tb_parent`.id ASC

SEPARATOR ',' -- カンマ区切り

) AS all_parent,

GROUP_CONCAT(

`tb_student`.student_name

ORDER BY `tb_student`.id ASC

SEPARATOR ',' -- カンマ区切り

) AS all_student

FROM `tb_family`

LEFT JOIN `tb_parent` -- parentテーブル結合

ON `tb\_parent`.family_id = `tb\_family`.id

LEFT JOIN `tb_student` -- studentテーブル結合

ON `tb\_student`.family_id = `tb\_family`.id

GROUP BY `tb\_family`.id -- 家庭IDでグループ化

結果

family_id	all_parent	all_student
1	山田　太郎,山田　よしこ	山田　息子,山田　息子
2	鈴木　花子,鈴木　花子	鈴木　娘,鈴木　息子
3	佐藤　次郎	佐藤　息子

このままだと保護者と学生の列数分ダブってしまうので、、、
同じ家庭の保護者（parent）と学生（student）をグループ化し、重複値は1つにまとめて表示

SELECT `tb_family`.id AS family_id 
GROUP_CONCAT(
 DISTINCT(`tb_parent`.parent_name) -- 重複削除
 ORDER BY `tb_parent`.id ASC
 SEPARATOR ','
 ) AS all_parent, 
GROUP_CONCAT(
 DISTINCT(`tb_student`.student_name) -- 重複削除
 ORDER BY `tb_student`.id ASC
 SEPARATOR ','
 ) AS all_student
FROM `tb_family` 
LEFT JOIN `tb_parent`
ON `tb\_parent`.family_id = `tb\_family`.id 
LEFT JOIN `tb_student`
ON `tb\_student`.family_id = `tb\_family`.id 
GROUP BY `tb\_family`.id

SELECT `tb_family`.id AS family_id

GROUP_CONCAT(

DISTINCT(`tb_parent`.parent_name) -- 重複削除

ORDER BY `tb_parent`.id ASC

SEPARATOR ','

) AS all_parent,

GROUP_CONCAT(

DISTINCT(`tb_student`.student_name) -- 重複削除

ORDER BY `tb_student`.id ASC

SEPARATOR ','

) AS all_student

FROM `tb_family`

LEFT JOIN `tb_parent`

ON `tb\_parent`.family_id = `tb\_family`.id

LEFT JOIN `tb_student`

ON `tb\_student`.family_id = `tb\_family`.id

GROUP BY `tb\_family`.id

結果

family_id	all_parent	all_student
1	山田　太郎,山田　よしこ	山田　息子
2	鈴木　花子	鈴木　娘,鈴木　息子
3	佐藤　次郎	佐藤　息子

WHERE句のINでプレースホルダを使う際の注意点

DBIモジュールでは、プレースホルダを使う場合、基本的な記述は下記のようになる。

　　：
# DB接続、SQL組み立て処理
　　：
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, $value); # 第三引数に値のデータタイプも指定可能
　　：
# 後処理
　　：

　　：

# DB接続、SQL組み立て処理

　　：

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, $value); # 第三引数に値のデータタイプも指定可能

　　：

# 後処理

　　：

んで、SQL分にWHERE句を含んでいる場合で、条件をINで複数指定している場合、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ, い, う");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ, い, う");

でいけると思いきや、値の最初の要素（カンマで区切った一番最初の値＝「あ」）のみプレースホルダに代入されて処理されてしまう。つまり、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ");

と同じ結果となってしまう。

どうやらカンマで連結した文字列を渡しても、配列要素とみなされるようだ。

なので、条件の数分だけ、プレースホルダを作り指定してやる必要がある。

単純に書くと、

my $sql = "SELECT * FROM table_name WHERE column_name IN (?,?,?)";
my $sth = $dbh->prepare("$sql");
$sth->bind_param(1, "あ");
$sth->bind_param(2, "い");
$sth->bind_param(3, "う");

my $sql = "SELECT * FROM table_name WHERE column_name IN (?,?,?)";

my $sth = $dbh->prepare("$sql");

$sth->bind_param(1, "あ");

$sth->bind_param(2, "い");

$sth->bind_param(3, "う");

てな感じ。

現実的にはINの条件数は動的になると思うので、うちでは下記のような感じで処理している。

・DB処理用ファイル（database.pl）

package database;
　　：
# DB接続処理
　　：
sub db_exec {
  my($sql, $bind) = @_;
  our $sth; my $exec;
  $sth = $db::dbh->prepare($sql) || die $db::dbh->errstr();
  if($bind){
    my $el = 1;
    map {$sth->bind_param($el,$$_{value},$$_{type}); $el++;} @$bind;
  }
  $exec = $sth->execute() || die $sth->errstr();
}

package database;

　　：

# DB接続処理

　　：

sub db_exec {

my($sql, $bind) = @_;

our $sth; my $exec;

$sth = $db::dbh->prepare($sql) || die $db::dbh->errstr();

if($bind){

my $el = 1;

map {$sth->bind_param($el,$$_{value},$$_{type}); $el++;} @$bind;

}

$exec = $sth->execute() || die $sth->errstr();

}

・呼び出し元

requare database.pl;
　　：
my @in = (あ, い, う);
&database::db_exec("
  SELECT * FROM table_name
  WHERE column IN (". join(',', map{'?'} @in ).") 
  ",
  [
    ( map {+{ value => $_ }} @in )
    # さらにプレースホルダがある場合はここに
    ,{ value => 'abc' }
    # のように書く
  ]
  #↑ +{...}はハッシュのリファレンスを示す
);
　　：

requare database.pl;

　　：

my @in = (あ, い, う);

&database::db_exec("

SELECT * FROM table_name

WHERE column IN (". join(',', map{'?'} @in ).")

[

( map {+{ value => $_ }} @in )

# さらにプレースホルダがある場合はここに

,{ value => 'abc' }

# のように書く

]

#↑ +{...}はハッシュのリファレンスを示す

);

　　：

PHPでもフレームワークによってここの処理が違ったりするので注意が必要。

非効率なサブクエリを改善する

サブクエリを使った検索において、検索件数が数百~数千程度であれば、マシンの力で何とかなることがあるが、この検索結果が数万単位の膨大な数となる場合、動作が極端に遅くなる場合がある。

例として、historyテーブルにメールの履歴、clickテーブルにメールの履歴番号をhistory_idとしたクリックURL、およびそのクリック回数が入っていて、レコードは複数あるとする。

historyテーブルにクリック数のデータはないので、clickテーブルを結合して、履歴データと、その履歴ごとのクリック数およびクリック率（CTR）の一覧を表示したい。またCTRの大きさで一覧をソートしたい。

例えば下記のSQL。

SELECT	SQL_CALC_FOUND_ROWS
	`history`.*,
	`click`.total_clicks,
	ROUND(
		(`click`.total_clicks / `history`.users) * 100,
		 2
	) AS ctr 
FROM	`history` 
LEFT JOIN (
	SELECT
		`click`.history_id,
		SUM(click_count) AS total_clicks 
	FROM	`click`
	GROUP	BY `click`.history_id
)
ON	`history`.id = `click`.history_id
ORDER BY ctr DESC
LIMIT   0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

`click`.total_clicks,

ROUND(

(`click`.total_clicks / `history`.users) * 100,

) AS ctr

FROM `history`

LEFT JOIN (

SELECT

`click`.history_id,

SUM(click_count) AS total_clicks

FROM `click`

GROUP BY `click`.history_id

)

ON `history`.id = `click`.history_id

ORDER BY ctr DESC

LIMIT 0, 30

この場合、clickテーブルのレコードが膨大になってしまうと、10~15行目のサブクエリ内のSELECTが全数検索になるため、結合後のテーブルが巨大となり、極端に動作が重くなる。history_idにINDEXを貼っていても、数万単位の量になってくると、同様だ。

いくら最後（18行目）にLIMIT句でレコード数を制限しても、内側のサブクエリから処理されるので、結合時点ではテーブルは巨大になってしまう。

「サブクエリ内でLIMIT使えばいいのでは？」と思うが、1つの履歴番号に紐付くclickテーブルのレコードが必ず存在するとは限らないし、存在したとしてもレコード数は一定ではないためこの時点でLIMITは確定できない。

つまり、「GROUP BY history.id」（15行目）したレコード数が、30になるとは限らない。仮にサブクエリ内で同じく「LIMIT 0,30」とやったとしたら、結合時にデータに不整合が生じる可能性がある。

またCTRは、historyテーブルに存在する読者数がわからないと出せないので、結合後にORDER BYするしかなく、サブクエリ内ではCTRによるORDER BYができない。この理由もありLIMITは使えない。

そこで発想の転換をして、clickテーブルにhistoryテーブルを結合してみる。

SELECT	SQL_CALC_FOUND_ROWS
	   `history`.*,
	   SUM(`click`.click_count) AS total_clicks,
	   ROUND(
              SUM(`click`.click_count) / `history`.users * 100,
              2
           ) AS ctr
FROM       `click`
RIGHT JOIN `history`
ON	   `click`.history_id = `history`.id
-- ここでwhereも使える --
GROUP BY   `history`.id
ORDER BY   SUM(`click`.click_count) / `history`.users * 100
LIMIT      0, 30

SELECT SQL_CALC_FOUND_ROWS

`history`.*,

SUM(`click`.click_count) AS total_clicks,

ROUND(

SUM(`click`.click_count) / `history`.users * 100,

) AS ctr

FROM `click`

RIGHT JOIN `history`

ON `click`.history_id = `history`.id

-- ここでwhereも使える --

GROUP BY `history`.id

ORDER BY SUM(`click`.click_count) / `history`.users * 100

LIMIT 0, 30

ポイントは、

clickテーブルを基本として、クリック数の合計とCTRの計算をする。
historyテーブルをRIGHT JOINして、historyテーブルのレコード数に、最終的な結果レコード数をあわせるようにする。
ORDER BY の対象を「click.history_id」ではなく「history.id」とし、履歴番号に紐付くclickテーブルのレコード数がない時も、対応できるようにする。

こうすることで、サブクエリを使わずに、LIMITをうまく使って膨大なクリックデータを高速に処理できるようになった。

当然ページ送りも、このLIMITの値を変更するだけで対応できる。

経験上、どうしてもサブクエリを使わなければならないってことはほとんどないんだよなあ、、、（経験不足なのか？）。

大体の場合、上記のように結合の方法を工夫するか、テンポラリテーブルを使って処理できる場合が多いと思う。（個人の感想です）

参考サイト）

http://nullnote.com/programs/mysql/join/

SQLでサブクエリを上手に使う6パターン

マスター・スレーブ間でMySQLのレプリケーションを取っている場合のとても簡単な死活監視。
keepalivedなどを使えない（VIPによる自動フェイルオーバーが使えない）環境のため、負荷分散および接続不可時の接続IP切り替えはアプリ側で制御した。（LBかまそうにも、LB自体が前述の理由で冗長化できないので。）
なので、以下はマスター・スレーブをお互いに監視して、落ちたときにアラート通知をするだけ。

以下、基本はマスター・スレーブ共通

シェル設置用のディレクトリ作成
# mkdir /root/bin
# cd /root/bin

監視用シェル作成（マスター側）
# vi mysql_alive_chk.sh

—————————————————————-
#!/bin/sh

##################################
# /root/bin/mysql_alive_chk.sh
##################################

#監視先
MYSQL_NAME=”mysql2.hoge.jp”
MYSQL_IP=”192.168.100.102″
MYSQL_USER=”user”
MYSQL_PASS=”pass”

ALERT_FLG=”/root/bin/${MYSQL_NAME}.alert”

RES_STRING=mysqladmin ping -h $MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS
ALIVE_STRING=”mysqld is alive”
echo $RES_STRING

if [ “$RES_STRING” != “$ALIVE_STRING” ] ; then
if [ ! -e “$ALERT_FLG” ] ; then
touch $ALERT_FLG
echo “MySQL on $MYSQL_NAME might be down!! Please check and restore.” | mail -s “System Alert” alert@hoge.jp, alert@hoge.com
exit
fi
else
if [ -e “$ALERT_FLG” ] ; then
rm -f $ALERT_FLG
fi
fi

exit
—————————————————————-

監視用シェル作成（スレーブ側）
# vi mysql_alive_chk.sh

—————————————————————-
#!/bin/sh

##################################
# /root/bin/mysql_alive_chk.sh
##################################

#監視先
MYSQL_NAME=”mysql.hoge.jp”
MYSQL_IP=”192.168.100.101″
MYSQL_USER=”user”
MYSQL_PASS=”pass”

ALERT_FLG=”/root/bin/${MYSQL_NAME}.alert”

RES_STRING=mysqladmin ping -h $MYSQL_IP -u$MYSQL_USER -p$MYSQL_PASS
ALIVE_STRING=”mysqld is alive”
echo $RES_STRING

exit
—————————————————————-

実行権限付与
# chmod 755 mysql_alive_chk.sh

アラートメール配信のためにMTAを起動しておく
# service postfix start

自動起動ON
# chkconfig postfix on

今回はCRONに登録
# vi /etc/crontab
—————-
# 動作が確認できればメールがうっとうしいので止める
MAILTO=root
　↓
MAILTO=””
　：
# monitering slave’s(master’s) mysql
*/5 * * * * root /root/bin/mysql_alive_chk.sh; >/dev/null 2>&1
—————-

以上。

試しにどちらかのmysqldを止めたりしてみて、ちゃんとアラートメールが来ればOK。

参考サイト）
http://d.hatena.ne.jp/bose999/20100925/1285408588
http://d.hatena.ne.jp/bose999/20100926/1285440284

MySQLのレプリケーション設定

■マスター側

設定ファイル編集
vi /etc/my.cnf

　：
[mysqld]

# バイナリログを出力
log_bin

# 特定のDBのみをスレーブするときは有効に
#binlog_do_db=dbname

# 他のレプリケーションと被らないサーバIDを決定
server_id=1

# バイナリログの肥大化防止のためバイナリログの保存期間を指定
expire_logs_days=7
　：

　：

[mysqld]

# バイナリログを出力

log_bin

# 特定のDBのみをスレーブするときは有効に

#binlog_do_db=dbname

# 他のレプリケーションと被らないサーバIDを決定

server_id=1

# バイナリログの肥大化防止のためバイナリログの保存期間を指定

expire_logs_days=7

　：

MySQL再起動

# service mysqld retart

1	# service mysqld retart

データディレクトリがにバイナリデータが作成されているのを確認

# ls -lat /var/lib/mysql
：
mysqld-bin.000001
mysqld-bin.index
：

# ls -lat /var/lib/mysql

：

mysqld-bin.000001

mysqld-bin.index

：

MySQLユーザと権限付与

# mysql -uroot -p

mysql> CREATE USER 'repl'@'スレーブのローカルIP' IDENTIFIED BY 'replユーザのパスワード'

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'スレーブのローカルIP' IDENTIFIED BY 'replユーザのパスワード' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

mysql> SELECT user,host,password FROM mysql.user;

# mysql -uroot -p

mysql> CREATE USER 'repl'@'スレーブのローカルIP' IDENTIFIED BY 'replユーザのパスワード'

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'スレーブのローカルIP' IDENTIFIED BY 'replユーザのパスワード' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

mysql> SELECT user,host,password FROM mysql.user;

データダンプ

$ mysqldump -u root -p --all-databases --master-data=2 --default-character-set=utf8 > mysql.dump

1	$ mysqldump -u root -p --all-databases --master-data=2 --default-character-set=utf8 > mysql.dump

※特定のDBのみdumpする場合は–all-databasesを削除しdb名を追加
※MyISAMの場合は、–master-dataによって、自動的に–lock-all-tablesが有効になるので、すべてのテーブルがロックされてしまうので注意
※InnoDBであれば、–single-transactionを付与することで、ロックされることなくdumpを取得できる。

dumpファイルをスレーブ側へ転送しておく

$ scp mysql.dump user@スレーブのローカルIP:

1	$ scp mysql.dump user@スレーブのローカルIP:

■スレーブ側設定

設定ファイル編集

vi /etc/my.cnf

：
[mysqld]

# サーバID設定
server_id=2

# スレーブなので更新禁止
read_only
：

vi /etc/my.cnf

：

[mysqld]

# サーバID設定

server_id=2

# スレーブなので更新禁止

read_only

：

再起動

# service mysqld restart

1	# service mysqld restart

マスターDBのダンプの読み込み

$ mysql -u root -p < /home/user/mysql.dump

1	$ mysql -u root -p < /home/user/mysql.dump

ダンプした時点の、マスターのバイナリログファイル名（MASTER_LOG_FILE）と開始位置（MASTER_LOG_POS）を確認しておく

$ head -n 100 mysql.dump | grep CHANGE
：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=240;
：

$ head -n 100 mysql.dump | grep CHANGE

：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=240;

：

レプリケーション設定

# mysql -uroot -p

mysql> CHANGE MASTER TO
 MASTER_HOST='マスターのローカルIP',
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD='マスターのreplユーザのパスワード',
 MASTER_LOG_FILE='mysqld-bin.000002',　-- ←先ほど確認したファイル名
 MASTER_LOG_POS=240;　 　　　　　　　　-- ←先ほど確認した開始位置

# mysql -uroot -p

mysql> CHANGE MASTER TO

MASTER_HOST='マスターのローカルIP',

MASTER_PORT=3306,

MASTER_USER='repl',

MASTER_PASSWORD='マスターのreplユーザのパスワード',

MASTER_LOG_FILE='mysqld-bin.000002',　-- ←先ほど確認したファイル名

MASTER_LOG_POS=240;　　　　　　　　　-- ←先ほど確認した開始位置

レプリケーションの開始

mysql> START SLAVE;

1	mysql> START SLAVE;

レプリケーションの動作確認

mysql> SHOW SLAVE STATUS\G

：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
：

mysql> SHOW SLAVE STATUS\G

：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

：

※Slave_IO_Running と Slave_SQL_Running がどちらもYesになっていれば、レプリケーションが正常に動作しています。

参考サイト）

レプリケーションが動かなくなった時

基本は以下のサイトの手順で復旧可能。

MySQLのレプリケーション時エラー対処方法

https://qiita.com/soramugi/items/3c33200b5903007f3ead

もしそれでもだめなら、当記事の「データダンプ」のとこからやり直す。

2019/1/28レプリケーションが機能していないと気付いた（「Slave_IO_Running 」が「Yes」にならない）。

その際はダンプからもう一度行うことで復旧した。

ストレージ障害とか、IO系の障害とかあった後は、要注意。

MySQLのクエリログの出力方法

/etc/my.cnfを開く。

[mysqld]ディレクティブにログの種類と出力場所を指定。

代表的なものは下記。

（エラーログ）※これはたぶん最初から書いてある
log_error=”/var/log/mysqld.log”
log_warnings=1

（一般クエリログ）
log=”/var/log/sql.log”

（バイナリログ）
log_bin=”/var/log/mysqlbin.log”
log_bin_index=”/var/log/mysqlbin.list”
max_binlog_size=1M
expire_logs_days=1

（スロークエリログ）
slow_query_log=1
slow_query_log_file=”/var/log/slowquery.log”
log_queries_not_using_indexes
log_slow_admin_statements

設定を書いて保存したら、出力ファイルを作成し、所有者をmysqlに変更しておく。

（一般クエリログの場合）
# touch /var/log/sql.log
# chown mysql:mysql /var/log/sql.log

最後にmysqldを再起動。
# service mysqld restart

一般クエリログの出力例
——————————-
　　：
18 Connect ****@localhost on dbname
18 Query set autocommit=1
18 Query SET NAMES utf8
18 Query SELECT * FROM tbl1
18 Query SELECT * FROM tbl2
WHERE id = ‘*****’
18 Query UPDATE tbl2
SET clm1 = UNIX_TIMESTAMP()
WHERE id = ‘*****’
18 Query SELECT * FROM tbl3 WHERE id=’****’
17 Quit
　　：
——————————-

ちなみにSQLインジェクション攻撃を受けている場合のログの例
——————————-
　　：
1019 Connect ****@localhost on dbname
1019 Query set autocommit=1
1019 Query SET NAMES utf8
1019 Query SELECT * FROM tbl1 WHERE id=’-2353′ OR ORD(MID((SELECT IFNULL(CAST(clm1 AS CHAR),0x20) FROM tbl2 LIMIT 1328,1),12,1))>112#’ AND clm2=’abcdefg’
1019 Query SELECT * FROM tbl3
1019 Quit
　　：
——————————-

ちなみに、
# tail -f /var/log/sql.log
とすれば発行クエリがリアルタイムでチェック可能。

DBI使って開発段階で生のSQLをチェックしたい時かあると思いますが、DBIx::QueryLogとか使うよりこっちのほうがアナログで簡単そう。

sqlmapの使い方

いろいろ資料は散見されるが、、、

弊社なりにまとめてみました。

まず、sqlmapをインストールしたディレクトリに移動。

$ cd sqlmap

基本は、

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga”

とやるだけでOK。Getパラメタに対してインジェクションチェックを行ってくれる。

POSTもチェックしたいときは、

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga” –data “postparm1=123&postparam2=456”

DBMSの種類がすでに分かっているときは、明示的に指定すれば時間の省略になる。

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga”　–dbms mysql

脆弱性が発見されたときに、同時にDB一覧の取得を試みる場合。

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga” –dbs

脆弱性が発見されたときに、同時にテーブル一覧の取得を試みる場合。

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga” –tables

脆弱性が発見されたときに、特定のテーブルのカラムデータ取得を試みる場合。

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga” -T “table name” –colum

※「column」ではなく「columns」と記載されているドキュメントも見かけたが、うちの環境の場合は「column」で通った。

脆弱性が発見されたときに、特定のテーブルの中身のダンプ取得を試みる場合。

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga” -T “table name” –dump

※実際表示されるとかなりショッキングです。。。。

基本は、上記の基本的なコマンドを発行し、デフォルトレベルのチェックで、

[CRITICAL] all tested parameters appear to be not injectable.

と判定されれば、とりあえずは脆弱性は無いということなので、あとは必要に応じてチェックレベルを上げてみてもよいかと思います。

レベルを指定する場合は下記のようにする。（0~5の範囲で指定可能）

$ ./sqlmap.py -u “http://hoge.jp/index.php?id=fuga” –level 3

その他、任意のヘッダを指定したりログレベルを調整したり、さまざまな使い方ができる模様。

詳細は下記の参考サイトをどうぞ。

参考サイト）

フリーでできるセキュリティWeb編(SQLMあｐを楽しもう) from abend_cve_9999_0001

オープンソースの SQL インジェクション脆弱性診断ツールの sqlmap を Kali Linux で使ってみる

sqlmapを使うための環境整備

sqlmapとは、オープンソースで開発されている SQL インジェクションの脆弱性診断ツール。

これを使うために、CentOS5.5とかFedora10とか比較的古いlinuxまわりで環境整備しようとしたが、最終的にpythonのバージョンが古くて使えないという壁にぶつかったため、まっさらなCentOS6.2_LAMP_x86_64から入れてみたら動くようになったので、その備忘録。

64bitのサーバなんて初めてだったけど。。。
まずユーザ追加
# useradd sqlmap
# passwd sqlmap

■ファイアフォール設定

内部から行ったアクセスに対する外部からの返答アクセスを許可
iptables -A INPUT -m state –state ESTABLISHED,RELATED -j ACCEPT

自ホストからのアクセスをすべて許可
iptables -A INPUT -i lo -j ACCEPT

ssh許可
iptables -A INPUT -p tcp –dport 22 -j ACCEPT

その他のアクセス、転送をすべて拒否
iptables -P INPUT DROP
iptables -P FORWARD DROP

確認
# iptables -nL

保存
# /etc/init.d/rc.d/iptables save

■SSH設定
RootでのSSHログイン禁止
# echo “PermitRootLogin no” >> /etc/ssh/sshd_config

再起動
/etc/init.d/rc.d/sshd restart

■リモート接続

SSHクライアントに「未サポートのエスケープシーケンスを見つけました。 ESC [?1034h」と言われたら、ターミナルのエミュレータをxtermからktermに変更してリトライ。

■依存パッケージインストール

yum実行時に「Cannot find a valid baseurl for repo: base」と出てmirrorlist.centos.orgに接続できない時

# vi /etc/yum.repos.d/CentOS-Base.repo

すべてのmirrorlist=の行をコメントアウトし、baseurlにrikenのURLを指定
——————————-
[base]
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra

baseurl=http://ftp.riken.jp/Linux/centos/$releasever/os/$basearch/

[updates]
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra

baseurl=http://ftp.riken.jp/Linux/centos/$releasever/updates/$basearch/

[extras]
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras&infra=$infra

baseurl=http://ftp.riken.jp/Linux/centos/$releasever/extras/$basearch/

[centosplus]
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus&infra=$infra

baseurl=http://ftp.riken.jp/Linux/centos/$releasever/centosplus/$basearch/

[contrib]
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=contrib&infra=$infra

baseurl=http://ftp.riken.jp/Linux/centos/$releasever/contrib/$basearch/
——————————-

libcurlをインストール

# yum install -y curl-devel
wget が入っていないと思われるのでインストール

# yum install -y wget

■Gitをインストール

# cd /usr/local/src
# wget https://git-core.googlecode.com/files/git-1.9.0.tar.gz
# tar zxvf git-1.9.0.tar.gz
# rm git-1.9.0.tar.gz
# cd git-1.9.0

# ./configure –prefix=/usr/local/

gccがないと言われたらインストール
# yum install -y gcc

# make

「expected specifier-qualifier-list before ‘z_stream」と言われたらzlibインストール
# yum -y install zlib-devel

再度 make

「Can’t locate ExtUtils/MakeMaker.pm in @INC」と言われたら、perl-ExtUtils-MakeMakerをインストール
# yum install -y perl-ExtUtils-MakeMaker

再度 make

「tclsh failed; using unoptimized loading」とでたら、gettextを入れる
# yum install -y gettext

再度 make

今度は通ったっぽい。

# make install

バージョン確認
# git –version
-> git version 1.9.0

ユーザのホームに戻り
$ git clone https://github.com/sqlmapproject/sqlmap.git
$ cd sqlmap

これでひとまず診断の準備完了。

参考サイト）
http://qiita.com/shyamahira/items/9f80d16c3436f9dea753
http://qiita.com/akata/items/57c81ae969255e3c9a70
http://kentai-shiroma.blogspot.jp/2014/10/centos65glibc.html
http://d.hatena.ne.jp/hateka/20110924/1316850271
http://qiita.com/yusukaaay/items/6a39cc2f983cb0319bd1

カテゴリー: MySQL

とても便利なGROUP_CONCAT（）

WHERE句のINでプレースホルダを使う際の注意点

非効率なサブクエリを改善する

超簡単なMySQL死活監視

MySQLのレプリケーション設定

■マスター側

■スレーブ側設定

参考サイト）

レプリケーションが動かなくなった時

MySQLのクエリログの出力方法

sqlmapの使い方

sqlmapを使うための環境整備