search

security-anthem / MAID

MAID -MAIl Detective-
MIT License
3 stars 6 forks source link

クライアントからの入力とDBの比較処理の実装 #8

Closed siva0410 closed 2 years ago

siva0410 commented 3 years ago

クライアントからの入力とDBの比較して，一致した数を記録する処理の実装

shu1oka28 commented 3 years ago 
{
    "received": [
        {
            "from": {
                "display": "mail.example.com",
                "reverse": "Unknown",
                "ip": "10.0.0.1"
            },
            "by": "mailsrv.example.com",
            "protocol": "ESMTP",
            "ssl": "(version=TLS1_2 cipher=ECDHE-ECDSA-AES128-SHA bits=128/128)",
            "spf": true,
            "dkim": true,
            "dmarc": false
        },
        {
            "from": {
                "display": "mail.example.com",
                "reverse": "Unknown",
                "ip": "10.0.0.1"
            },
            "by": "mailsrv.example.com",
            "protocol": "ESMTP",
            "ssl": "(version=TLS1_2 cipher=ECDHE-ECDSA-AES128-SHA bits=128/128)",
            "spf": true,
            "dkim": true,
            "dmarc": false
        }
    ],
    "attach": ["fuzzy hash of attachment1", "attachment2"],
    "pattern": ["service name 1", "service name 2"],
    "from": "",
    "reply-to": "",
    "subject": "hash"
}
testdb=> \d overview
                                Table "public.overview"
   Column    |  Type   | Collation | Nullable |                Default                 
-------------+---------+-----------+----------+----------------------------------------
 m_id        | integer |           | not null | nextval('overview_m_id_seq'::regclass)
 from_header | text    |           |          | 
 reply_to    | text    |           |          | 
 subject     | text    |           | not null | 
Indexes:
    "overview_pkey" PRIMARY KEY, btree (m_id)
Referenced by:
    TABLE "attach" CONSTRAINT "attach_m_id_fkey" FOREIGN KEY (m_id) REFERENCES overview(m_id)
    TABLE "pattern" CONSTRAINT "pattern_m_id_fkey" FOREIGN KEY (m_id) REFERENCES overview(m_id)
    TABLE "received" CONSTRAINT "received_m_id_fkey" FOREIGN KEY (m_id) REFERENCES overview(m_id)

testdb=> \d pattern
                              Table "public.pattern"
 Column  |  Type   | Collation | Nullable |                Default                
---------+---------+-----------+----------+---------------------------------------
 p_id    | integer |           | not null | nextval('pattern_p_id_seq'::regclass)
 m_id    | integer |           | not null | nextval('pattern_m_id_seq'::regclass)
 pattern | text    |           |          | 
Indexes:
    "pattern_pkey" PRIMARY KEY, btree (p_id)
Foreign-key constraints:
    "pattern_m_id_fkey" FOREIGN KEY (m_id) REFERENCES overview(m_id)

testdb=> \d received
                                Table "public.received"
    Column    |  Type   | Collation | Nullable |                Default                 
--------------+---------+-----------+----------+----------------------------------------
 r_id         | integer |           | not null | nextval('received_r_id_seq'::regclass)
 m_id         | integer |           | not null | nextval('received_m_id_seq'::regclass)
 from_display | text    |           |          | 
 from_reverse | text    |           |          | 
 from_ip      | text    |           |          | 
 by           | text    |           |          | 
 protocol     | text    |           |          | 
 ssl          | text    |           |          | 
 spf          | bit(1)  |           |          | 
 dkim         | bit(1)  |           |          | 
 dmarc        | bit(1)  |           |          | 
Indexes:
    "received_pkey" PRIMARY KEY, btree (r_id)
Foreign-key constraints:
    "received_m_id_fkey" FOREIGN KEY (m_id) REFERENCES overview(m_id)

testdb=> \d attach
                             Table "public.attach"
 Column |  Type   | Collation | Nullable |               Default                
--------+---------+-----------+----------+--------------------------------------
 a_id   | integer |           | not null | nextval('attach_a_id_seq'::regclass)
 m_id   | integer |           | not null | nextval('attach_m_id_seq'::regclass)
 attach | text    |           | not null | 
Indexes:
    "attach_pkey" PRIMARY KEY, btree (a_id)
Foreign-key constraints:
    "attach_m_id_fkey" FOREIGN KEY (m_id) REFERENCES overview(m_id)
HI-sec-a commented 3 years ago

(1) メールに関するJSON形式の文字列を受け取る (2) (1) を辞書型に変換 (3) クエリ対象のテーブルを判定 (4) SELECT COUNT(対象の項目=値) AS 変数名 FROM (3)で出したテーブル; (5) (3) (4)をJSONの各項目について繰り返す？

HI-sec-a commented 3 years ago

コードスニペット

import psycopg2
import os

def recvDataCompare():
    #受信データとDBに保存されたデータを比較し、比較結果を返す
    DATABASE_URL = os.environ['DATABASE_URL']
    connection = psycopg2.connect(DATABASE_URL, sslmode='require')
    """
    connection=psycopg2.connect('DBのURL')
    cur = connection.cursor()
    cur.execute("SELECT COUNT(host='hostname.example.com') AS HOST_MATCH, COUNT(ip='0.0.0.0') AS IP_MATCH FROM MAIL_HEADERS;")
    results = cur.fetchall()
    cur.close()

    """
    connection.close()
    results="IPアドレスの一致件数：2件,ドメインの一致件数：1件"

    return results

# (1) 引数としてメールに関するJSON形式の文字列を受け取る
# (2) (1)を辞書型に変換
# (3) 

import json

test_data = '''
{
    "received": [
        {
            "from": {
                "display": "mail.example.com",
                "reverse": "Unknown",
                "ip": "10.0.0.2"
            },
            "by": "mailsrv.example.com",
            "protocol": "ESMTP",
            "ssl": "(version=TLS1_2 cipher=ECDHE-ECDSA-AES128-SHA bits=128/128)",
            "spf": true,
            "dkim": true,
            "dmarc": false
        },
        {
            "from": {
                "display": "mail.example.com",
                "reverse": "Unknown",
                "ip": "10.0.0.1"
            },
            "by": "mailsrv.example.com",
            "protocol": "ESMTP",
            "ssl": "(version=TLS1_2 cipher=ECDHE-ECDSA-AES128-SHA bits=128/128)",
            "spf": true,
            "dkim": true,
            "dmarc": false
        }
    ],
    "attach": ["fuzzy hash of attachment1", "attachment2"],
    "pattern": ["service name 1", "service name 2"],
    "from": "",
    "reply-to": "",
    "subject": "hash"
}
'''

def json_string2dict(mail_json_str):
    mail_json_dict = json.loads(mail_json_str)
    return mail_json_dict

def compare_data(mail_json_dict):
    db_url = os.environ['DATABASE_URL']
    connection = psycopg2.connect(db_url, sslmode='require')
    cur = connection.cursor()
    match_num = {}

    cur.execute("SELECT COUNT(from_display=%s OR NULL) AS IP_MATCH FROM received;", (mail_json_dict,))
    results = cur.fetchall()
    match_num["from_display"] = results[0][0]

    cur.execute("SELECT COUNT(from_display=%s OR NULL) AS IP_MATCH FROM received;", ("mail.example.com",))
    results = cur.fetchall()
    match_num["from_display"] = results[0][0]

    cur.close()

    print(results)

if __name__ == "__main__":
    mail_json_dict = json_string2dict(test_data)
    compare_data(mail_json_dict)