Project Note

学校で利用されている情報システムのセキュリティが実は穴だらけだったというブログ記事

---

■昨今の学校のセキュリティ事情
■【第一章 学校のPC(生徒使用PC)について】
■【第二章 学校のWebサイトについて-SQLインジェクション】
■【第三章 学校のWebサイトについて-コマンドインジェクション/その他】
■【第四章 昨今の学校のセキュリティ事情のまとめ】
⇒Ｓｋｙ株式会社 - SKYMENU
⇒Ｓｋｙ株式会社 - SKYSEA Client View
⇒SQLインジェクション - Wikipedia

---

■山口県宇部市、小中学37校1300台のPCを仮想デスクトップ環境に移行
宇部市は、VMware Viewを導入することで、それぞれの教育現場で独自に運用、管理されていた学校教育資産を同市情報政策課に配備された19台の仮想デスクトップ用サーバに集約した。
■VMware View
PC環境をサーバ上に仮想化することでデータの集中化を行い、管理コストの低減やセキュリテイの強化、利便性の向上を実現するシンクライアントソリューションです。
■PCサーバ PRIMERGY - VMware Viewによるシンクライアント・ソリューション : 富士通

SQLServerの機能でミラーリングとレプリケーションとがあるが、どう違うのか？

ストレージの世界ではミラーリングはローカルに接続された複数の記憶装置を使って、それぞれの記憶装置に同じ情報を記録することを言い、レプリケーションはリモート接続等を使用し、遠隔地のシステムに同一の情報を保持させることを言う。情報が反映されるまでの時間で言うとミラーリングは同時・即時であり、レプリケーションでは必ずしもすぐにとは限らない。

データベースの世界ではミラーリングでは、データベース(インスタンス)のコピーを複数のコンピュータに配置される。クライアントがデータベースにアクセスし変更を加えると、他のコンピュータに配置されたデータベースにも変更が反映される。コンピュータ毎に分散配置されたデータベースはそれぞれが完全なのもとなっており、障害によりデータベースが失われても他のコンピュータ上のデータベースが引き継ぐことが出来る。

レプリケーションの場合、コピー対象はデータベース単位以外にも様々な指定方法があり、必要な部分のみのレプリケーションが可能である。更新が反映されるまでの時間は随時ではなく設定されたタイミングで同期が行われる。また同期の方法についてもいくつか種類があり、利用方法によって選択できる。

注意すべき点としてパブリッシャとサブスクライバとで扱う情報が異なり、ミラーリングのように片方に障害が発生したときに肩代わりすることは出来ないということがある。あくまでもパブリッシャはレプリケートされるデータの提供を行い、サブスクライバはレプリケートされた情報のみを扱う。

■まとめ
・データベースの可用性を上げたい！ ⇒ ミラーリング
・小規模、範囲の限定、負荷分散、遠隔地間の同期 ⇒ レプリケーション

---

■SQL Server 2005 データベース ミラーリング・レプリケーション
■SQL Server 2008 - レプリケーション
■SQL Server 2008 R2 - データベース ミラーリング
■SQL Server 2008 R2 - SQL Server のレプリケーション
■レプリケーションおよびデータベース ミラーリング
■ミラーリングとレプリケーション（SQLServer2005環境)の違い
■Wikipedia - レプリケーション

MS SQLServerでテーブルの使用量を調べるストアド「sp_MStablespace」っていうのがあるのだけれど、得られる結果「DataSpaceUsed」の単位って何なのでしょう。バイト、キロバイト、メガバイト？

クエリアナライザでストアドを実行して確認。

exec sp_MStablespace "dbo.Table1"

・新規でテーブルを作って⇒0
・ちょっとデータを入れて⇒8
・300行くらい挿入して⇒144

1文字入れただけで1バイト以上にはなるので、単位としてバイトは流石にないし、MBだと大きすぎる気がします。やはりキロバイトでしょうか。

mikehibm2005 様のSQLServerのDB内の全テーブルのデータ容量を表示するで公開されているSQLでは、実行結果を表示する部分で

(T_DATA+T_INDEX) / 1024 AS TOTAL_MB

とされています。テーブルとインデックスの容量を足して1024で割るとメガバイト。ということは元の数値はキロバイトという認識です。

---

ちょっとだけ関連するかと…。⇒ストアド「sp_MStablespace」を実行したとき常に正しい行数が取得できるとは限らないようです。定期的に調べた結果をどこにまとめているのでしょう。ストアド「sp_MStablespace」を実行する前に、

DBCC UPDATEUSAGE データベース名

を実行しておくとページと行のカウントが更新され、正しい結果が返って来るそうです。
※SQL Server 2000からアップグレードした場合、最初に実行しておくこと。以後適切に管理されます。
※SQL Server 2005以降では「DBCC UPDATEUSAGE」を定期的に実行しません。
※大規模なDBでは処理に時間がかかります。

---

■【SQL Server】テーブルの使用量を確認する
■SQLServerのDB内の全テーブルのデータ容量を表示する
■ディスク使用量の概要レポート - MSDN
■【SQLServer】 行数の取得
■DBCC UPDATEUSAGE (Transact-SQL) - MSDN

■MySQL
　MySQLの場合、SELECT文の中にLIMITという機能があります。
　以下の例では、先頭から100レコードを取得、31件目から10レコードを取得しています。

SELECT * FROM テーブル名 LIMIT 100;
SELECT * FROM テーブル名 LIMIT 30,10;

■Oracle
　Oracleの場合、各レコードに自動的に振られている行番号(ROWNUM)を利用します。
　ソートが含まれる場合、行番号はソート前に振られるため、行番号がバラバラになります。その場合はROW_NUMBER()関数を使用するか、ソートした後に再度SELECTして行番号を取得します。

SELECT * FROM テーブル名 WHERE ROWNUM <= 100;
SELECT * FROM テーブル名 WHERE ROWNUM BETWEEN 30 AND 40;

■SQLServer
　SQLServerの場合、SELECT文で先頭からの取得数を制限することは可能です。
　途中のレコードのみを取り出す機能はないので、①ソートしておく、②途中以降のレコードをWHERE条件で取得する、③TOPで取得する数を制限する、ということになります。
　画面表示用のストアドを組む方が楽かもしれません。あとは各レコードに連番を振っておくとか…。

SELECT TOP 100 * FROM テーブル名;

「A5:SQL Mk-2」は、ADOまたはODBCによるDBへの接続、SQLの作成・編集機能、ER図の作成、テーブル定義書の作成(Excel出力)などの機能を持つ、フリーのSQL開発環境です。
各種DB(Oracle、DB2、SQLServer、MySQL、PostgreSQL)について、実行計画を取得することが可能です。
ER図機能について、ER図からDDL文の作成機能、逆に既存DBからER図を作成することが可能です。

---

■A5:SQL Mk-2

たとえば、次のような状況で行の重複や欠落が発生します。

"READ UNCOMMITTED 分離レベル" で実行されるトランザクションや "NOLOCK ヒント" を使用した読み取りではロックが取得されないため、インデックスをスキャンしている時にページ分割が実行されても、そのページ分割中のページ内の行を読み取ることができます。そして、それらページ内のどの行まで読み取ったか、また、どの行がページ分割により新たなページへ移動されるかが関連し、その行が再び出現するか (重複)、もしくは出現しない (欠落) 可能性があります。

「READ UNCOMMITTED 分離レベル」や「NOLOCK」の時、テーブルやレコードに対して読み取り中にロックがかかっていない為、横からデータの操作が加わる恐れがあるとのことです。例として挙げられている現象はレコードを順番に見ているときに、並び順を管理しているツリーに変更が加わって、次のレコードを見てみたら以前と違うなんてことになるみたいです。

同じようにカーソルを開いたまま、ループをゆっくり回してファイル出力や帳票出力を行っていると、レコードが重複したりとかありそうです。

対処方法としては、
　①「NOLOCK」しない。
　②一行ずつなど極短い単位でレコードを取り出す。
　③レコードセットを取得したらすぐにクローンを取ってDBから分離する。
　④排他のロックを明示的にかける。
などが挙げられます。必要に応じて対応を行いましょう。

…NOLOCKを使用しない場合、対象のレコードに排他ロックがかかっていないかを見て、もしロックされているとそれを待ってしまうようなことがあるような…。しばらく待たされた気がするのですよ。NOLOCKを使うとそれらが無いため速い…速くても抜けているところがあるのですね～。

---

■文書番号: 975782 - SQL Server で "READ UNCOMMITTED 分離レベル" または "NOLOCK ヒント" を使用した SQL 文を実行してデータを参照すると、読み取ったデータの行が欠落または重複する場合がある

下記の手法で検査対象項目に外字を含むレコードを抽出します。

• Transact-SQLのLIKEキーワードは条件パターンに正規表現を使用できる


• データ型がnvarcharの場合はUNICODEの外字コードを、varcharの場合はShift_JISの外字コードをそれぞれ指定する必要がある

---

■検査対象項目のデータ型が nchar・nvarcharの場合（UNICODEの場合）

where [検査対象項目] like '%[' + nchar(0xE000) + '-' + nchar(0xF8FF) + ']%'

■検査対象項目のデータ型が char・varcharの場合（Shift_JISの場合）
　⇒　一旦nvarcharに変換してから比較する

where cast([検査対象項目] as nvarchar)　like '%[' + nchar(0xE000) + '-' + nchar(0xF8FF) + ']%'

where convert(nvarchar, [検査対象項目])　like '%[' + nchar(0xE000) + '-' + nchar(0xF8FF) + ']%'