gologiusの巣

プログラミング、モデリングなどのメモです。誰かの役に立てるとうれしいです。

Vueっぽく階層式プルダウンを作ったが、もっとスマートに書きたいという話

※Vue始めて3か月くらいです。

とりあえず作ったものを下記リンクに置いておく。

Vueでプルダウンテスト

ソースは下記に置いておく。JSなら動くのでgithubio便利。

gologius.github.io/test/PULLDOWN_SAMPLE at master · gologius/gologius.github.io · GitHub

リンク先を見るのが面倒な人向けにGIFも貼っておく

f:id:gologius:20200606152036g:plain
プルダウンのGIF

なぜ作ったのか

  • 3階層、4階層以降に対応したサンプルがなかった(気がする)
  • 超適当に調べたが、微妙にやりたいこととずれたものだった(気がする)
  • こんなんサイトちゃんと見なくても自力実装できるわwwwww(所感)

感想

1

HTMLはそこそこ綺麗かなと思いました

<div id="pulldown">
    <div v-for="(choices,layer) in selectable_items">

        <div v-bind:id="'layer' + layer" v-show="choices.length >= 1">    
            <h2>第{{layer+1}}階層</h2>
            <select v-bind:id="'pd-'+ layer" v-model="selected_stack[layer]">
                <option v-for="item in choices" v-bind:value="item.id">
                    {{item.name}}
                </option>
            </select>
        </div>

    </div>
</div>

<script src="pulldown.js"></script>

2

現状の選択に紐づく選択肢をJSでフィルターして渡してるんだけど、 HTML側のフィルターで頑張るべきなのか、JS側で頑張るべきなのか、ライブラリの思想としてどっちがいいのかがよくわからない

//現状のユーザー選択(selected_stack)に対して、表示すべき選択肢を取得
selectable_items: function () {
    
    var results = [];
    var layer = 0;
    for (layer = 0; layer <= this.selected_stack.length; layer++) {
        
        //上位層の選択結果を取得
        before_selected_id  = ''
        if (layer >= 1 ) {
            before_selected_id = this.selected_stack[layer-1];
        }

        //該当層かつ、上位層の選択肢に合致するもののみ抽出
        layer_result = MENU_M.filter(function (value) {
            return value.layer === layer && value.before_id === before_selected_id;
        });

        results.push(layer_result);
    }
    return results;
},

3

JS側が汚い。初期化処理とかキモイ。watch使っているんだけど謎ラッパーかまさないとListの検知ができない

computed: {
    //watch関数をまともに動作させるためのラッパー
    //参考:https://qiita.com/haruyanagi17/items/d74c0b9546719ff88c63
    watch_selected_stack: function () {
        return Object.assign({}, this.selected_stack); // ディープコピーしたものを返す
    },
},
watch: {
    //選択肢が入力された際の動作定義
    watch_selected_stack: function (newval, oldval) {
        
        //変更箇所を検知
        var update_flag = false;
        var layer = 0;
        for(layer=0; layer < this.selected_stack.length; layer++){
            
            //変更箇所より後の選択肢は初期化する
            if(update_flag) {
                this.selected_stack[layer] = '';
            }

            //変更されている箇所があれば、フラグを立てる
            if (newval[layer] !== oldval[layer]) {
                update_flag = true;
            }
        }
    },      
}

今後

もうちょいきれいに書く方法探しますわ

MySQLで住所CSVをDBテーブルにロードする

概要

住所情報をDBに入れて、検索させて候補表示したりすることはよくあると思います。

なので、その準備方法の一つとして、方法を記載しておきます。

なお、今回使うのは住所.jp様のCSVです。

項目の意味等は別途下記サイトから調べてください。

住所データCSV【住所.jp】

日本郵政にも同じようなCSVファイルがありましたが、読みが半角カナで変換が面倒だったので、 こちらを使用。

環境

Ubuntu 18.04

MySQL 14.14

テーブルを作る

下記SQLでテーブルを作成します。命名がダサいとおもった方は適宜変えてください。

 CREATE TABLE JUSYO(
        JUSYO_CD VARCHAR(10),
        TODOFUKEN_CD VARCHAR(10),
        SIKU_CD VARCHAR(10),
        CHOU_CD VARCHAR(10),
        ZIP VARCHAR(8),
        JIGYOSHO_FLG VARCHAR(1),
        HAISI_FLG VARCHAR(1),
        TODOFUKEN VARCHAR(100),
        TODOFUKEN_KANA VARCHAR(100),
        SIKU VARCHAR(100),
        SIKU_KANA VARCHAR(100),
        CHOU VARCHAR(100),
        CHOU_KANA VARCHAR(100),
        CHOU_HOSOKU VARCHAR(100),
        KYOTO_TORINA VARCHAR(100),
        AZA VARCHAR(100),
        AZA_KANA VARCHAR(100),
        HOSOKU VARCHAR(100),
        JIGYOSHO_NAME VARCHAR(100),
        JIGYOSHO_KANA VARCHAR(100),
        JIGYOSHO_JUSYO VARCHAR(100),
        NEW_JUSYO_CD VARCHAR(100)
    );

ファイルをサーバーに置く

適当な場所でよいです。

もしかしたらサーバーとDBの設定を変えれば、リモート越しでもできるかもしれません。

CSVロード

該当ファイルがある場所でmysql にログイン

先ほど作ったテーブルが存在するDBを選択し、下記SQLを実行 (ファイル名は zenkoku.csv を想定)

LOAD DATA LOCAL INFILE "zenkoku.csv"
INTO TABLE JUSYO
CHARACTER SET SJIS
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';
IGNORE 1 LINES;

参考: 【MySQL】CSVファイルをデータベースにインポートする | (株)シャルーン

エラー対処法

住所.jp様からDLできるCSVファイルはSJISなので、 DBがUTF8 かつ CHARACTER SET SJIS がない場合、下記のようなエラーで落ちる

ERROR 1300 (HY000): Invalid utf8 character string: '"' 

INTO TABLE *** の直後に CHARACTER SET SJIS をもってこないと、下記エラーになる模様

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'character SET sjis IGNORE 1 LINES' at line 1

おまけ

全部連結したテーブルを作っておけば、 サジェストなどに使いやすい形になると思います。

こんな感じのデータになります。

郵便番号 住所 住所読み
060-0042 北海道札幌市中央区大通西1丁目 ホッカイドウサッポロシチュウオウクオオドオリニシ01チョウメ

作成したければ、下記のようなSQLで、SELECT結果からTABLEを作成することができます。

create table JUSYO_MIN
as 
SELECT 
zip, 
replace(concat(TODOFUKEN,SIKU,CHOU,AZA,JIGYOSHO_NAME,JIGYOSHO_JUSYO)," ", "") as JUSYO_ALL, 
replace(concat(TODOFUKEN_KANA,SIKU_KANA,CHOU_KANA,AZA_KANA,JIGYOSHO_KANA), " ", "") as JUSYO_KANA_ALL 
FROM JUSYO;

MySQL+Python(Flask) でdatetimeが返却される

MySQLのDATETIME型を、SELECT文で取得すると、 pythonのdatetime型で返却される事象があるようです。

私も下記記事のようになりました

Python - PythonでMySQLのTIME型をSELECTする方法|teratail

例コード

import mysql.connector
DB_HOST = "192.168.0.XXX"
DB_NAME = "your_db_name"
DB_USER = "your_db_user"
DB_PW = "your_user_pw"
conn = mysql.connector.connect(user=DB_USER, password=DB_PW, host=DB_HOST, database=DB_NAME)

cur = conn.cursor(dictionary=True) #引数指定すると、辞書型で返してくれる
sql = "SELECT ADD_DATE FROM USER;"
cur.execute(sql)
data = cur.fetchall(); 

print(data)

>>[ { "ADD_DATE": datetime.datetime(2019, 08, 08) } ]

ちなみに、APIを実装するために、Flaskの関数 flask.jsonify() なんかでJSON変換して送信すると、 文字列がMon Mar 22 05:06:07 GMT 1999 のようになってしまう。

素直にYYYYMMDD hh:mm:ss で表示してほしい・・・

解決方法

MySQLならDATE_FORMAT関数があるので、そちらを使ってフォーマットされた文字列を出力する。

ただしそのままSELECTすると、Pythonで結果をfetchする際に、辞書型のキー名がDATE_FORMAT(****)になっていまいます。

なので、SQL文を

sql = 'SELECT DATE_FORMAT(ADD_DATE, %s) AS ADD_DATE  FROM USER;'
cur.execute(sql, ('%Y年%m月%d日 %h時%i分%s秒',))

のようにASを使用してあげるとよいと思います。

ダメな例

なお、Pythonパッケージmysql-connector-pythonを使用する場合、 リファレンス通りのSQLを作るとエラーになります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

NG例

sql = 'SELECT  DATE_FORMAT(LAST_UPDATE_DATE,'%Y年%m月%d日 %h時%i分%s秒') FROM USER;"'
cur.execute(sql)

恐らくSQL Statementで使用する'%'がエラーで引っかかるのだと思います。

Use mysql DATE_FORMAT in Python - CodeProject

以上

PythonでMySQLに接続、操作する(2019年風)

(追記 2019/08/21) 私のググり方が悪かったのか、

'rt'ではない方のパッケージ使っている方はちょくちょくいますね・・・

====

PythonMySQLに接続と操作をしようとしました。ググると下記記事が出てきます。

qiita.com

上記記事によると、 mysql-connector-python-rfmysql-connector-python の二つのパッケージが存在し、上記の記事は'rf'の方のパッケージについて記載されています。 rfはサポート終了してるようですので、今後はもう一つのパッケージを使うべきたと思います。

(追記8/13)また、上記記事ではSQL結果を辞書型で扱えないという旨が記載されていますが、 cursorの引数指定で扱うことが可能です。

というわけでmysql-connector-pythonを使用して、MySQLを触っていきます。

ぶっちゃけ大して使い方は変わっていないようです

ソースと参考文献

今回のソースまとめ

https://gist.github.com/gologius/b210556b7ce1f4b5ac33277bd1629e49

公式ドキュメント(詳細はここを読んでください ※英語)

MySQL :: MySQL Connector/Python Developer Guide :: 5.1 Connecting to MySQL Using Connector/Python

前準備

環境はPython実行側が Win10、Python3.7、DBサーバーはUbuntu18.04です。 MySQLの設定等はここでは触れません。

DBは下記のような構造を想定しています。

項目名 TYPE
id int
username varchar
createtime datetime

パッケージインストール

pip install mysql-connector-python

これだけ

接続

import mysql.connector

DB_HOST = "192.168.0.XXX"
DB_NAME = "your_db_name"
DB_USER = "your_db_user"
DB_PW = "your_user_pw"

conn = mysql.connector.connect(user=DB_USER, password=DB_PW, host=DB_HOST, database=DB_NAME)

INSERT

# 1レコードのみ
cur = conn.cursor()
insert_sql = "INSERT INTO USER () VALUES(%s, %s, %s);"
insert_value = (random.randint(1,100), 'testusername', datetime.datetime.now())
cur.execute(insert_sql, insert_value) #第二引数はタプル
conn.commit()

# 複数レコードある場合
insert_values = []
for i in range(10):
    v =  (random.randint(1,100), 'testusername', datetime.datetime.now())
    insert_values.append(v);

cur.executemany(insert_sql, insert_values) #第二引数はタプルリスト
conn.commit()

SELECT

(追記08/13)引数指定することで、辞書型で返却できる旨を追記

#シンプルなSQL実行
cur = conn.cursor(dictionary=True) #引数指定すると、辞書型で返してくれる
select_sql = "SELECT * FROM USER;"
cur.execute(select_sql)

# 辞書型リストを取得
data = cur.fetchall(); 

# 変数埋め込み
cur = conn.cursor() #引数に何も指定しない場合、タプルのリストが返却される
select_sql = "SELECT * FROM USER where id >= %s ORDER BY ID;"
where_value = random.randint(1,100)
cur.execute(select_sql , (where_value,)) #第二引数はタプル ※条件が一つの場合でもタプル型にする必要がある

#ループでも取り出し可能
for id, username, createtime in cur:
    print(id, username, createtime)

切断

cur.close()
conn.close()

あとがき

mysql-connector-python-rfと使い勝手はあまり変わらない気も・・・

随時追記します

【Python】SQLiteでSQLエラーが発生する

SQLiteプレースホルダー関連でエラーが発生するので解決砲を記載。ハマる人はハマるのではと思ったり。

なお、下記のソースはそのままでは動かないので適宜改変してください。

その1 ''で囲んでいる

ソース

sql = "SELECT NAME FROM MEMBERS WHERE NAME='?'"  #<<<<<<これ
name = "testname"

try :
   #SQL実行
   conn = sqlite3.connect("your db path.db")
   cur = conn.cursor()
   cur.execute(sql,(name, ))

   cur.close()
   conn.close()
except:
   print(traceback.format_exc())
   return DBResult.SQL_ERROR

結果

Traceback (most recent call last):
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 1 supplied.

原因

?を''で囲んだせいで、?が文字列として認識されてしまっているようです。 よって、プレースホルダ(?)が0個なのに、値(name)を一個プレスホルダに入れようとしていてエラーになっています。

SQLをかじった人なら、SQL内で文字列比較する際には''囲みをすることを把握しているかと思います。 それにより引き起こされるミスですね。

解決策

下記のようにする

sql = "SELECT NAME FROM MEMBERS WHERE NAME=?"

その2 タプルで渡していない

ソース

sql = "INSERT INTO MEMBERS (NAME, GROUP) VALUES (?, ?)"
values = ["testname", "A"] #<<<<<<これ

try :
   #SQL実行
   conn = sqlite3.connect("your db path.db")
   cur = conn.cursor()
   cur.execute(sql, values) #<<<<<<これ

   cur.close()
   conn.close()
except:
   print(traceback.format_exc())

エラー

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

原因

execute()の引数はタプル、executemany()の引数はタプルのリストとなります。 実際の内部処理はよくわかりませんが、プレースホルダ(?)が2に対して、引数がリストでまとめて1つ、 と判断されているのだと思います。

上記のエラーでとりあえずググれば出てくる、有名な話ですね。

解決策

タプルに変換すればいいです

values = ("testname", "A") #タプルにしてあげる
values = ("testname",) #要素が一つの場合は、末尾カンマも忘れずに

#executemanyを使用する場合、タプルのリストに変換してあげる
value_list = ["testname1","testname2","testname3"]
tuple_list = []
   for name in value_list:
       tuple_list.append((name,))

その3 executemanyをselectで使用している

ソース

sql = "SELECT NAME FROM MEMBERS WHERE NAME=?"

name_list = ["aaa","bbb","ccc"]
tuple_list = []
   for name in name_list:
       name_list.append((name,)) #引数がlist[tuple]でないと受け付けないため、変換する

try :
   #SQL実行
   conn = sqlite3.connect("your db path.db")
   cur = conn.cursor()
   cur.executemany(sql, tuple_list)

   cur.close()
   conn.close()
except:
   print(traceback.format_exc())

エラー

sqlite3.ProgrammingError: executemany() can only execute DML statements.

原因

executemanyはDML文でしか使用できません。

DML文とは、表の値を操作したり、削除したりと、「表に変更を与えるようなSQL」です。 最後にcommit()が必要な文ですね。

docs.oracle.com

SELECTは読み取るだけなので、表に変化は与えません。 なのでexecutemanyが使用できないみたいです。

解決策

条件文を駆使して、SQLの構文を用いてまとめて取得しましょう。 下記はIN文での例です

name_list = ["aaa","bbb","ccc"]
sql = "SELECT NAME FROM MEMBERS WHERE NAME IN (" + ",".join(name_list) + ")"

try :
   #SQL実行
   conn = sqlite3.connect("your db path.db")
   cur = conn.cursor()
   cur.execute(sql)

   cur.close()
   conn.close()
except:
   print(traceback.format_exc())

もしくはSQLを都度発行する方法もあります。 パフォーマンス的にはあまりよくないのかもしれませんが・・・

name_list = ["aaa","bbb","ccc"]

try :
    sql = "SELECT NAME, GROUP FROM MEMBER WHERE NAME = ?"    
    conn = sqlite3.connect("your db path.db")
    cur = conn.cursor()

    for name in name_list:    
        cur.execute(sql, (name,))
        records = cur.fetchall()
        print(records)

    cur.close()
    conn.close()
except:
    print(sql)
    print(traceback.format_exc())
    return DBResult.SQL_ERROR

まとめ

最近SQL全然使ってないのでハマった。

XAMMPでWEBサーバーを立てる際に、最低限セキュリティを考慮した設定をしたい

XAMMPでWEBサーバーを立てる機会があったので、 セキュリティについて調べました。

※以下のQiita記事を基に、適用されている作業の中身を独自で調べ直したものになります。

Apacheセキュリティ設定 - Qiita

最低限の知識はあるつもりですが(実は情報セキュリティスペシャリスト所持)、 あくまでネット上の情報をまとめただけです。 以下に記載された情報を基にされた作業に関して一切責任を持ちません。

条件

  • Win10 64bit
  • XAMMP 7.2.7 (PHPのバージョンと同じ)

設定ファイル

超雑に調べた

  • httpd.conf (Apacheのhttp(デフォルトポート80)へのアクセスの設定)
  • httpd-ssl.conf (Apachehttps(デフォルトポート443)へのアクセスの設定)
  • httpd-xammp.conf (PHP関連の設定がされている?PHPのバージョンを変更する際にここを編集するみたい)
  • php.ini (PHP自体の設定)
  • config.inc.phpphpMyAdmin(ブラウザからサーバー設定できるサイト)の設定ファイル)

対策

NW設定

特定のIP、セグメント、ポートからしか見る必要がない場合、設定しておいた方が無難。

httpd.confの設定では、ディレクトリ、ファイル単位で、許可/拒否 制御することが可能。

ファイル一覧出力の禁止

閲覧者(攻撃者含む)に対して余計な情報を与えないようにする。 隠してしまうほうが良い f:id:gologius:20190129120340p:plainf:id:gologius:20190129120401p:plain

バージョン情報の隠ぺい

バージョンが分かると、「この攻撃方法はまだ対策されてないな」と攻撃者に情報を与えてしまう。 隠してしまう方がよいと私は思っています(賛否両論あり) f:id:gologius:20190129120325p:plainf:id:gologius:20190129120428p:plain

httpoxy対策

httpoxy は脆弱性の名前

HTTPリスエストのヘッダの一つにPROXYがある。 脆弱性があるサーバーは、リクエストヘッダの情報を、(環境)変数に入れてしまう。

PROXY情報は通常クライアント側からはアクセスできないが、設定できてしまう。 これにより、不正なプロキシサーバーを設定して、そのサーバーに情報を流し続ける、ということができてしまう

クリックジャギング対策

正常なWEBサイトを、iframeから呼び出す。 さらにそのiframeの上に、透明な悪意のあるリンクをかぶせる これにより、正常なWEBサイトのリンクをクリックしたつもりが、遷移先は別の悪意のあるサイトに飛ばされる

外部から呼び出すことを想定していないサイトについては、ブロックする(特定のWEBサイトのみ、iframe呼び出しを許可する)

XSS対策

XSS=Cross Site Scripting クロスサイトスクリプティング

文字列のエスケープをしていないため、悪意のあるHTMLやJavaScriptが埋め込まれる。

文字入力欄に

名前  「田中 太郎」

とかいれる想定なところに

名前  「田中太郎;<script>alert(`不正なプログラムです`)</script>

などと入力する。

DBにはこの文字列がそのまま登録される。

HTMLとして表示される時に、JSが実行されてしまう。

XST対策

XST=Cross Site Tracing クロスサイトトレーシング

XSSではブラウザ(クライアント)からサーバーへのHTTPリクエストヘッダを取得できない(らしい。なぜ?)。 なので、ヘッダ内に含まれる認証情報(クッキーや認証IDPWなど)は取れない。

話は変わるが、HTTPリクエストメソッド(GET,POST,PUT,DELETE)などの一種にTRACEがある。 TRACEはHTTPリスエストをオウム返しするメソッドである。

なので、XSSを利用して、TRACEメソッドでリクエストを送ると、リクエストヘッダを取得できる。 前述したクッキーや認証情報も取得できてしまう。

なお、攻撃方法としては現役引退(化石化)している。

現在ではすべてのブラウザでXST対策がとられ、XST攻撃を行うことはできなくなっています https://blog.tokumaru.org/2013/01/TRACE-method-is-not-so-dangerous-in-fact.html

DOS攻撃対策

おなじみのF5アタックなどが該当。

パケットを送りまくって、サーバーに異常な負荷をかける攻撃。

slowloris対策

slowloris=ツール名。スローロリスと読むらしい

Apacheサーバーに不完全なリクエストヘッダーを送ると、サーバー側が最後のヘッダーを待ち続ける。 この脆弱性を利用して、slowlorisを用いて不完全なヘッダーを送り続け、Apacheのプロセスを消費させる。

https://www.drk7.jp/MT/archives/001527.html

作業内容

httpd.conf 設定

例のQiita記事の内容を参考に、httpd.confへ適用します。

Apacheセキュリティ設定 - Qiita

Windowsなのでディレクトリのパスだけ変更する必要があります。

  • /var/www/htmlC:\xampp\htdocs
  • /var/www/cgi-binC:\xampp\cgi-binになるはず

httpd-xammp.conf 設定

PHP無効化

httpd-xammp.conf内に「この拡張子ならPHPファイルとして処理しろ」と設定している箇所がある。 これをコメントアウトすればよい

<FilesMatch "\.php$">
    SetHandler application/x-httpd-php
</FilesMatch>
<FilesMatch "\.phps$">
    SetHandler application/x-httpd-php-source
</FilesMatch>

コメントアウト後は、何も表示されなくなる(正確には<?php ?>で囲まれた部分が全く処理されなくなる)

CGIスクリプト無効化

httpd.conf内で「この拡張子ならCGIとして認識しろ」と設定している箇所がある これをコメントアウトすればよい

AddHandler cgi-script .cgi .pl .asp

コメントアウト後は下記のようになる(ソースがそのまま表示) f:id:gologius:20190129120447p:plain

まとめと感想

XAMMPは手軽に環境を構築できますが、そのまま使ってセキュリティ的に大丈夫なのかずっと不安だったので、 今回調査しました。