SQLite3

久々です。

筆者は現在、Android関連の技術調査を行っているのですが、そこであった笑い話をひとつ。


Android端末にはSQLiteという簡易データベースが内臓されているのですが、
このSQLite、簡易というだけあって他のデータベース（オラクルとかPostgreSQLとか・・・）とは
かなり異なります。

日付の扱いも異なるひとつですが、日付についてのマニュアルの翻訳が載っているサイト
（こちら）
を見て、試していてそれは起こりました。

『date 関数は、strftime('%Y-%M-%D', timestring, ...) のエイリアスです。』
という説明があったので、筆者はそのとおりに試してみたのですがどうもうまくいきません。
試したかったのは『'%Y-%M-%D'』部分で、この場合の処理結果は『2011-07-05』とかなるのですが
筆者が欲しいのは『20110705』・・・　"-"が邪魔なわけです。
あれこれ試して、サイトを見直してみると・・・
%Y 年 (0000-9999)
これは問題ないです。
%M 分 (00-59)
　　%m 月 (01-12)
　　%d 月における日 (00-31)
ををう！　orz

日付を取りたくて"%D"としていたのがエラーになっていた様子・・・
マニュアルといえど人の作ったもの。
まるっと信じてはいけないですね。
（そんなことをいつも考えていると禿げそうになりますが・・・）

SQLインジェクション対応で思うこと

絶賛単純作業（見た目のみ）中の筆者です。

SQLインジェクション対応というのをやっているのですが、作業的には
１、共通関数（無毒化を行う関数）の作成
２、作成した共通関数を対象プロジェクトのソースの『適切な場所』に組み込む
だけのことです。
２、が対象プロジェクトの規模に比例して作業量が増えるのですけどね～
具体的には２プロジェクトで２０００本程度のSQL文の確認と共通関数組み込みとなります。

組み込んでよい場所だめな場所の判別もしているので、脳内では単純作業じゃないのですが、見た目は共通関数のコピペなので単純作業に見える、と・・・　orz


そんな作業をしていると、ふとWebシステムのセキュリティ対策について考えてしまったりします。
今回対象となっている「SQLインジェクション」はSQL文の可変部分に与える文字列を工夫することで悪さをしようというものです。
具体的にいうと・・・

Select カラムA From テーブルA Where カラムB = '（入力値）';

というSQL文があり、（入力値）は画面上のテキストボックスの入力値をそのまま入れるとします。
一昔前のクライアント－サーバ型システムでのDBアクセスではお約束といえる書き方です。
ここで、入力値に

”' or 'A' = 'A”

と入れるとどうなるでしょう・・・

Select カラムA From テーブルA Where カラムB = '' or 'A' = 'A';

条件部分が
「カラムＢ=""」　または　「"A" = "A"」
となってしまいました。

テーブルＡの全レコードのカラムＡの情報が取れそうな感じですね。

まぁ、これだけならあまり害はないのですが、いくらでも好きなＳＱＬ文に出来てしまう可能性があるのがわかると思います。
具体的には書きませんが、色々ＤＢを弄くれる可能性も・・・　ということで極めて危険なわけです。

対応の方法は色々とありますが、最低限「'」（シングルクオート）を「''」に置換する必要があります。


実はＳＱＬインジェクションというものは、厳密に画面の入力を定義してやればかなり防ぐことが可能だったりします。
入力値で攻撃する性質上、数値だけしか入力できないテキストボックスからは何も出来ません。
入力をプルダウンメインにしてしまえばもちろん何も出来ません。
入力可能な文字数が短ければやはり攻撃しづらい、と・・・
まぁ、フレームワーク側での対応が進んでいるので意識しなくても良いともいえるのが現在の開発環境だったりするのですけどね。

メーリングリストに悩む

相変わらずPostgreSQLと格闘中の筆者です。

このごろ、さすがにWeb検索での調査に限界が見え始めてきたので
PostgreSQLの日本語メーリングリストへの登録を考え始めています。
本家（英語版）じゃないのかって？　英語じゃ投稿どころかメールを読むこともできませんてｗ
いや、読めるけど気合い入れないと読めないので、量をこなせないのですよ。
ちなみに、英語読めないのは読もうとしないからなのですよね。
少なくとも高校までである程度習っているのだから読めるはず…　なのですけどね。

メーリングリスト、というものはなかなかに厄介でして、
適切な質問をすれば比較的回答を得やすいのですが、適切に質問できるほど整理されていれば
苦労しないわけで…
善意で参加している人がほとんどだと思いますが、それでもアレな人もいるわけで
「無知な私の質問じゃ、馬鹿にされるんじゃないか」
と弱腰になるわけです。

とりあえず、調べて試して悩み続けるのかな？
という今日この頃です。

ＤＢおほへ書

このごろ晴れが続くなぁ、と思っていたら、梅雨が明けているとかいないとか。

異常気象という気はありませんが、わかりづらい気候ですね。

とりあえず、脱水対策として家の冷凍庫の氷を量産しようと思います。
アイスティーを淹れるとすぐに氷がなくなるもので＾＾；

さて、本日のタイトルの元ネタは戦国モノです。
タイトルと内容はほとんど関係ありません。


ＤＢ（データベース）というのは、とても重要な割に理解している人が少ないものだと思います。
かく言う筆者自身、製造工程で使用する為のＤＢサーバくらいならなんとかかんとか作れなくもありませんが、実環境（実際にユーザが使用する環境）のＤＢサーバは厳しいです。
何が一番大変かと言うと、データが過不足なくリアルタイムに正しく存在しなくてはならない（しかもシステム稼働中を通してずっと）のです。
中身を理解していないとそんな保証はできないです。

大半のＳＥが基本的なＳＱＬ文（ＤＢを操作するおまじないと思っておけばＯＫです）が書ければそれで良いと思っている昨今ですが、筆者はある課題の為に調べ物漬け状態になっています。
その過程で出てきた話をちらっと書いてみようと思います。


一番単純なＤＢには、テーブルと呼ばれるデータ格納領域だけが存在します。
テーブルとは何ぞ？　といいますと、エクセルで作った表のようなイメージを持っていただくと良いかもしれません。
カラム（列）が幾つかあって、レコード（行）を構成する。
たとえば、名簿なんかがわかりやすいですが、学籍番号・氏名・備考、という３つのカラムが存在し、学生一人につき３つのカラムそれぞれに情報が入ったレコードが１つ作られます。

実際のＤＢでは、テーブルとテーブルの関係（上記の「名簿」テーブルの学籍番号と、Ａ講座の成績表テーブルの学籍番号を関連付けることで、Ａ講座の成績表に学生の氏名が登録されていないのに『＊＊さんの成績は【Ａ】です』という情報が得られる）が重要なのですがその辺はそこらへんの解説サイトなどにお任せします。
数学の集合論なんかと絡めても面白いのでしょうけどマニアックすぎるので＾＾；

では、単純じゃないＤＢとは？　というと、その一つとして「他のＤＢから情報を参照する」というのがあります。
社内で昔から使っているシステムのＤＢから必要な情報を取得したい、という場合などが該当しますが、この時に新しいシステムから比較的簡単に情報を参照する方法が【view】です。

えー、現役ＳＥの中からは「viewは複数のテーブルの情報をまとめるものだろ？」などと突っ込まれそうですが、その用途は無論正しいです。
しかし、viewは他のＤＢの参照が（恐らく）一番簡単に出来る手段でもあるのです。

～サンプル（始）～

CREATE OR REPLACE VIEW 【view名】 AS 

 SELECT dblink.【参照したいカラム名】

   FROM dblink('host=【ホスト名】 port=【ポート番号】 dbname=【ＤＢ名】 user=【ユーザ名】 password=【パスワード】'::text, 'select * from 【参照したいテーブル名】;'::text) dblink(【参照したいカラム名】 【カラムの型】);

～サンプル（終）～

はい、何のことやらな方もいるかと思われますが、これが他のＤＢを参照するviewを作るＳＱＬ文です。
ちょっと手抜きでPostgresのビュー定義からコピペしてしまいましたのでわかりづらいところがあるかもです。
簡単に説明しますと…

CREATE OR REPLACE VIEW　【view名】：CREATE VIEWの直訳でわかるように「【view名】という名前のVIEWを作る」という意味です。

SELECT　以下：SELECT カラム名　FROM テーブル名　が参照のＳＱＬ文の基本になります。そういうものだと思ってください。

SELECT dblink.【参照したいカラム名】：「dblinkテーブルの【参照したいカラム名】カラムの情報を取得する」という意味です。

FROMは長めなので分割して…
dblink('host=【ホスト名】 port=【ポート番号】 dbname=【ＤＢ名】 user=【ユーザ名】 password=【パスワード】'::text, ：【ホスト名】というホスト（ＤＢサーバ）に【ポート番号】番ポートからアクセスして、【ＤＢ名】というＤＢに【ユーザ名】と【パスワード】でログインする、という意味です。

 'select * from 【参照したいテーブル名】;'::text)：dblinkした先で実行したい参照用ＳＱＬ文です。

dblink(【参照したいカラム名】 【カラムの型】);：取得したいカラム（情報）の一覧です。カラム名と型（文字列なのか？　数値なのか？　日付なのか？　などなど）が必要です。

まとめると
『dblinkで接続した先のテーブルから、指定したカラムの情報を取得して参照する』
となります。
まとめるときに切り捨てた部分が技術的に重要だったりしますが、そこまで入れるとサンプルのＳＱＬ文が一番シンプルと言う＾＾；
ＳＥの会話は宇宙語だ、と思う人もいるようですが、このへんの感覚が理由なのかな？


自分用の覚書兼同じ悩みを持っている方の指針となれば、という記事でした。