_Server.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " \tCreator Name:   Sara Baradaran, Mahdi Heidari\t\t\n",
    " \tCreate Date:    Aug 2020 \t\t\t\t\n",
    " \tModule Name:    Server.py \t\t\t\t\n",
    " \tProject Name:   Secure File Sharing \t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import socket\n",
    "import hashlib\n",
    "import base64\n",
    "import secrets\n",
    "from Crypto.Util import Counter\n",
    "from Crypto import Random\n",
    "import string\n",
    "import json\n",
    "import pandas as pd\n",
    "import pyodbc\n",
    "import random\n",
    "from threading import Thread\n",
    "from uuid import uuid4\n",
    "from datetime import datetime\n",
    "from Crypto.Cipher import AES\n",
    "from cryptography.hazmat.backends import default_backend\n",
    "from cryptography.hazmat.primitives import hashes,serialization\n",
    "from cryptography.hazmat.primitives.asymmetric import ec\n",
    "from cryptography.hazmat.primitives.kdf.hkdf import HKDF\n",
    "from password_strength import PasswordPolicy, PasswordStats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "conn = pyodbc.connect('Driver={SQL Server};'\n",
    "                  'Server=DESKTOP-DH5KE9Q\\SQLEXPRESS;'\n",
    "                  'Database=SecureFileSharing;'\n",
    "                  'Trusted_Connection=yes;')\n",
    "\n",
    "policy = PasswordPolicy.from_names(\n",
    "    length=8,               # min length: 8\n",
    "    uppercase=1,            # need min. 2 uppercase letters\n",
    "    numbers=1,              # need min. 2 digits\n",
    "    special=1,              # need min. 2 special characters\n",
    "    nonletters=1,           # need min. 2 non-letter characters (digits, specials, anything)\n",
    "    entropybits=30,         # need a password that has minimum 30 entropy bits (the power of its alphabet)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "AouthCode = '0'\n",
    "\n",
    "def func(connection_sock):\n",
    "    \n",
    "    print(str(connection_sock.getsockname()[0]))\n",
    "    key = key_exchange(connection_sock)\n",
    "    if key == None:\n",
    "        print('Connection has been blocked. Failed to set session key')\n",
    "        return 0\n",
    "    while(1):\n",
    "        \n",
    "        global AouthCode\n",
    "        data = connection_sock.recv(5000)\n",
    "        if not data:\n",
    "            c_IP , c_port = connection_sock.getpeername()\n",
    "            print('client with ip = {} and port = {} has been disconnected at time {}'\n",
    "                  .format(c_IP, c_port, str(datetime.now())))\n",
    "            connection_sock.shutdown()\n",
    "            connection_sock.close()\n",
    "            return 1\n",
    "        data = data.decode('utf-8')\n",
    "        text = decrypt(data, key)\n",
    "        print('received_msg', text)\n",
    "        content = ''\n",
    "        text = text.split(\"\\n\")\n",
    "        command = text[0].split()\n",
    "        \n",
    "        for i in range(1, len(text) - 1):\n",
    "            content += text[i]\n",
    "        \n",
    "        AouthCode = text[len(text) - 1]\n",
    "     \n",
    "        print('AouthCode', AouthCode)\n",
    "        print('command', command)\n",
    "        print('content', content)\n",
    "        msg = ''\n",
    "        \n",
    "       ##############################################################################\n",
    "    \n",
    "        if command[0] == \"register\" and len(command) == 5:\n",
    "\n",
    "            userID = check_username(command[1], conn)\n",
    "            register_status = 0\n",
    "            if userID == None:\n",
    "                pass_str = CheckPasswordStrength(command[1], command[2])\n",
    "                if pass_str == '1':\n",
    "                    register_status = user_registeration(command[1], command[2], command[3], command[4], conn)\n",
    "                    msg = str(register_status) + \" register \" + command[1] + \" \" + str(datetime.now())\n",
    "                else:\n",
    "                    msg = \"-3 register \" + command[1] + \" \" + str(datetime.now()) + '\\n' + pass_str\n",
    "            else:\n",
    "                msg = \"-2 register \" + command[1] + \" \" + str(datetime.now())\n",
    "            \n",
    "            # register log\n",
    "            add_registre_logs(command[1], command[3], command[4], str(register_status))\n",
    "            \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "                \n",
    "       ##############################################################################\n",
    "    \n",
    "        elif command[0] == \"login\" and len(command) == 3:\n",
    "            \n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            \n",
    "            ban_min = check_ban(command[1], conn)\n",
    "            if ban_min < 0: \n",
    "                msg = \"-4 login \" + command[1] + \" \" + str(datetime.now()) + '\\n' + str(-ban_min) + '\\n0'\n",
    "            else:\n",
    "                pass_status = check_password(command[1], command[2], conn)\n",
    "                login_status = 0\n",
    "                AuthCode = 0\n",
    "                if pass_status:\n",
    "                    login_status, AuthCode = user_login(command[1], ip, port, conn)\n",
    "\n",
    "                    if login_status == 1:\n",
    "                        msg = \"1 login \" + command[1] + \" \" + str(datetime.now()) + '\\n\\n' + str(AuthCode)\n",
    "                    else:\n",
    "                        msg = \"0 login \" + command[1] + \" \" + str(datetime.now()) + '\\n\\n0'\n",
    "                else:\n",
    "                    msg = \"-1 login \" + command[1] + \" \" + str(datetime.now()) + '\\n\\n0'\n",
    "\n",
    "                #login log\n",
    "\n",
    "                add_login_logs(command[1], command[2], ip, port, AuthCode, str(login_status))\n",
    "                update_ban_state(command[1], login_status, conn)\n",
    "\n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "                \n",
    "                    \n",
    "       ##############################################################################\n",
    "        \n",
    "        elif command[0] == \"grant\" and len(command) == 4:\n",
    "            \n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            fileID = check_file(command[1], conn)\n",
    "            \n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            owner_status = check_access(userID, fileID, 'o' , conn)\n",
    "            \n",
    "            userID_g = check_username(command[2], conn)\n",
    "            file_conf, file_int = get_file_lables(fileID, conn)\n",
    "            \n",
    "            grant_status = 0\n",
    "            if userID != None:\n",
    "                \n",
    "                if fileID != None:\n",
    "                    if owner_status:\n",
    "                        insert_access(userID_g, fileID, command[3], conn)\n",
    "                        insert_access(userID_g, fileID, command[3], conn)\n",
    "                        msg = \"1 grant \" + command[1] + \" \" + str(datetime.now())\n",
    "                    else:\n",
    "                        msg = \"0 grant \" + command[1] + \" \" + str(datetime.now())\n",
    "                else:\n",
    "                    msg = \"-2 grant \" + command[1] + \" \" + str(datetime.now())\n",
    "            else:\n",
    "                msg = \"-1 grant \" + command[1] + \" \" + str(datetime.now())\n",
    "                  \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "                \n",
    "        ##############################################################################\n",
    "                \n",
    "        elif command[0] == \"revoce\" and len(command) == 4:\n",
    "            \n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            fileID = check_file(command[1], conn)\n",
    "            \n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            owner_status = check_access(userID, fileID, 'o' , conn)\n",
    "            \n",
    "            userID_g = check_username(command[2], conn)\n",
    "            file_conf, file_int = get_file_lables(fileID, conn)\n",
    "            \n",
    "            grant_status = 0\n",
    "            if userID != None:\n",
    "                \n",
    "                if fileID != None:\n",
    "                    if owner_status:\n",
    "                        revoc_access(userID_g, fileID, command[3], conn)\n",
    "                        revoc_access(userID_g, fileID, command[3], conn)\n",
    "                        msg = \"1 revoce \" + command[1] + \" \" + str(datetime.now())\n",
    "                    else:\n",
    "                        msg = \"0 revoce \" + command[1] + \" \" + str(datetime.now())\n",
    "                else:\n",
    "                    msg = \"-2 revoce \" + command[1] + \" \" + str(datetime.now())\n",
    "            else:\n",
    "                msg = \"-1 revoce \" + command[1] + \" \" + str(datetime.now())\n",
    "                \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "        \n",
    "        ##############################################################################\n",
    "        elif command[0] == \"put\" and len(command) == 5:\n",
    "            \n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            creatorID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            fileID = check_file(command[1], conn)\n",
    "\n",
    "            put_status = 0\n",
    "            if creatorID != None:\n",
    "            \n",
    "                if fileID == None:\n",
    "                    put_status = put_file(command[1], content, creatorID, command[2], command[3], command[4], conn)\n",
    "                    fileID = check_file(command[1], conn)\n",
    "                    msg = str(put_status) + \" put \" + command[1] + \" \" + str(datetime.now())\n",
    "                    if put_status:\n",
    "                        insert_access(creatorID, fileID, 'w', conn)\n",
    "                        insert_access(creatorID, fileID, 'r', conn)\n",
    "                else:\n",
    "                    msg = \"-2 put \" + command[1] + \" \" + str(datetime.now())\n",
    "            else:\n",
    "                msg = \"-1 get \" + command[1] + \" \" + str(datetime.now())\n",
    "            \n",
    "            add_put_logs(creatorID, fileID, command[1], command[2], command[3], content, str(put_status))\n",
    "            \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "\n",
    "\n",
    "       ##############################################################################\n",
    "                \n",
    "        elif command[0] == \"get\" and len(command) == 2:\n",
    "\n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            fileID = check_file(command[1], conn)\n",
    "            owner_status = check_access(userID, fileID, 'o' , conn)\n",
    "            file_conf, file_int = get_file_lables(fileID, conn)\n",
    "            \n",
    "            if userID != None:\n",
    "                \n",
    "                if fileID != None:\n",
    "                    if owner_status:\n",
    "                        content = get_file(command[1], conn)\n",
    "                        msg = \"1 get \" + command[1] + \" \" + str(datetime.now()) + \"\\n\" + content\n",
    "                        revoc_access(userID, fileID, 'w', conn)\n",
    "                        revoc_access(userID, fileID, 'r', conn)\n",
    "                    else:\n",
    "                        msg = \"0 get \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "                else:\n",
    "                    msg = \"-2 get \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "            else:\n",
    "                msg = \"-1 get \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "                \n",
    "            add_get_logs(userID, fileID, command[1], file_conf, file_int, str(owner_status))\n",
    "                \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "\n",
    "       ##############################################################################\n",
    "\n",
    "        elif command[0] == \"read\" and len(command) == 2:\n",
    "            \n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            fileID = check_file(command[1], conn)\n",
    "            file_conf, file_int = get_file_lables(fileID, conn)\n",
    "            \n",
    "            read_status = 0\n",
    "            if userID != None:\n",
    "                \n",
    "                if fileID != None:\n",
    "                    cond, BIBA_status, BLP_status, ACL = check_access_mode(userID, fileID, 'r' , conn)\n",
    "                    if cond:   \n",
    "                        content = read_file(command[1], conn)\n",
    "                        if content == None:\n",
    "                            msg = \"0 1 1 read \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "                        else:\n",
    "                            read_status = 1\n",
    "                            msg = \"1 1 1 read \" + command[1] + \" \" + str(datetime.now()) + \"\\n\" + content\n",
    "                    else:\n",
    "                        msg = \"0 \" + str(BLP_status) + \" \" + str(BIBA_status) + \" read \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "                else:\n",
    "                    msg = \"-2 read \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "            else:\n",
    "                msg = \"-1 read \" + command[1] + \" \" + str(datetime.now()) + \"\\n \"\n",
    "            \n",
    "            add_read_logs(userID, fileID, command[1], file_conf, file_int, str(read_status))\n",
    "            \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "\n",
    "       ##############################################################################\n",
    "         \n",
    "        elif command[0] == \"write\" and len(command) == 2:\n",
    " \n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            fileID = check_file(command[1], conn)\n",
    "            file_conf, file_int = get_file_lables(fileID, conn)\n",
    "            \n",
    "            write_status = 0\n",
    "            if userID != None:\n",
    "                \n",
    "                if fileID != None:\n",
    "                    cond, BIBA_status, BLP_status, ACL = check_access_mode(userID, fileID, 'w' , conn)\n",
    "                    if cond:        \n",
    "                        write_status = write_file(command[1], content, conn)\n",
    "                        if write_status == 0:\n",
    "                            msg = \"0 1 1 write \" + command[1] + \" \" + str(datetime.now())\n",
    "                        else:\n",
    "                            msg = \"1 1 1 write \" + command[1] + \" \" + str(datetime.now())\n",
    "                    else:\n",
    "                        msg = \"0 \" + str(BLP_status) + \" \" + str(BIBA_status) + \" write \" + command[1] + \" \" + str(datetime.now())\n",
    "                else:\n",
    "                    msg = \"-2 write \" + command[1] + \" \" + str(datetime.now())\n",
    "            else:\n",
    "                msg = \"-1 write \" + command[1] + \" \" + str(datetime.now())\n",
    "\n",
    "            add_write_logs(userID, fileID, command[1], file_conf, file_int, content, str(write_status)) \n",
    "            \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "                \n",
    "                \n",
    "       ##############################################################################\n",
    "                \n",
    "        elif command[0] == \"list\" and len(command) == 1:\n",
    "            \n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            if userID == None:\n",
    "                msg = \"-1 list \" + str(datetime.now()) \n",
    "            else: \n",
    "                msg = \"1 list \" + str(datetime.now()) + \"\\n \"\n",
    "                files = list_files()\n",
    "                msg += files\n",
    "                \n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "                \n",
    "                \n",
    "        elif command[0] == \"logout\" and len(command) == 1:\n",
    "            \n",
    "            userID = check_AouthCode(ip, port, AouthCode, conn)\n",
    "            ip = str(connection_sock.getsockname()[0])\n",
    "            port = str(connection_sock.getsockname()[1])\n",
    "            if userID != None:\n",
    "                logout_status = user_logout(userID, ip, port, AouthCode, conn)\n",
    "            else:\n",
    "                msg = \"-1 logout \" + command[1] + \" \" + str(datetime.now())\n",
    "                \n",
    "            msg = str(logout_status) + \" logout \" + str(datetime.now())\n",
    "            cipher_text = encrypt(msg, key)\n",
    "            if cipher_text != None:\n",
    "                connection_sock.send(cipher_text.encode('utf-8'))\n",
    "                \n",
    "        print(msg)\n",
    "        \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Socket handling\n",
    "#########################################################################################################\n",
    "def setup_server():\n",
    "    s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)\n",
    "    if s == 0:\n",
    "        print ('error in server socket creation\\n')\n",
    "\n",
    "    server_ip = socket.gethostname()\n",
    "    server_port = 8500\n",
    "    s.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)\n",
    "    s.bind((server_ip, server_port))\n",
    "    s.listen(5)\n",
    "    print(\"server is listening for any connection ... \")\n",
    "    return s\n",
    "\n",
    "\n",
    "def server_listening(s):\n",
    "    connection_socket , client_addr = s.accept()\n",
    "    print(\"client with ip = {} has been connected at time {}\".format(client_addr, str(datetime.now())))\n",
    "    return connection_socket\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Cryptography\n",
    "\n",
    "#########################################################################################################\n",
    "def key_exchange(client_sock): # first makes a private key, sends and receives public keys, then derives the secret key\n",
    "\n",
    "    try:\n",
    "        backend = default_backend()\n",
    "        client_rcv_pub = client_sock.recv(200) # client_rcv_pub is client received public key in bytes\n",
    "        client_pub = serialization.load_pem_public_key(client_rcv_pub, backend) # client_pub is client public key in curve object\n",
    "        server_pr = ec.generate_private_key(ec.SECP256R1(), backend)\n",
    "        server_pub = server_pr.public_key()\n",
    "        client_sock.send(server_pub.public_bytes(serialization.Encoding.PEM, serialization.PublicFormat.SubjectPublicKeyInfo))\n",
    "        shared_data = server_pr.exchange(ec.ECDH(), client_pub) \n",
    "        secret_key = HKDF(hashes.SHA256(), 32, None, b'Key Exchange', backend).derive(shared_data)\n",
    "        session_key = secret_key[-16:] # to choose the last 128-bit (16-byte) of secret key\n",
    "        print('key exchanged successfully.')\n",
    "        return session_key\n",
    "    except:\n",
    "        print('error in key exchange')\n",
    "        return None\n",
    "\n",
    "\n",
    "def encrypt(plain_text, key): # returns a json containing ciphertext and nonce for CTR mode\n",
    "    \n",
    "    nonce1 = Random.get_random_bytes(8)\n",
    "    countf = Counter.new(64, nonce1)\n",
    "    cipher = AES.new(key, AES.MODE_CTR, counter=countf)\n",
    "    cipher_text_bytes = cipher.encrypt(plain_text.encode('utf-8'))\n",
    "    nonce = base64.b64encode(nonce1).decode('utf-8')\n",
    "    cipher_text = base64.b64encode(cipher_text_bytes).decode('utf-8')\n",
    "    result = json.dumps({'nonce':nonce, 'ciphertext':cipher_text})\n",
    "    return result\n",
    "\n",
    "def decrypt(data, key):\n",
    "\n",
    "    try:\n",
    "        b64 = json.loads(data)\n",
    "        nonce = base64.b64decode(b64['nonce'].encode('utf-8'))\n",
    "        cipher_text = base64.b64decode(b64['ciphertext'])\n",
    "        countf = Counter.new(64, nonce)\n",
    "        cipher = AES.new(key, AES.MODE_CTR, counter=countf)\n",
    "        plain_text = cipher.decrypt(cipher_text)\n",
    "        return plain_text.decode('utf-8')\n",
    "    except ValueError:\n",
    "        return None\n",
    "\n",
    "\n",
    "#########################################################################################################\n",
    "        \n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check_access_mode(userID, fileID, access_type , conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    select AccessMode\n",
    "    from ValidFiles where FileID = ?\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn, params={fileID,} )\n",
    "    \n",
    "    acc_mode = int(str(sql_query['AccessMode']).split()[1])\n",
    "    \n",
    "    cond = 1\n",
    "    \n",
    "    BIBA = check_BIBA(userID, fileID, access_type, conn)\n",
    "    BLP = check_BLP(userID, fileID, access_type, conn)\n",
    "    ACL = check_access(userID, fileID, access_type, conn)\n",
    "    \n",
    "    if acc_mode % 2 == 0:\n",
    "        cond = cond & BLP\n",
    "    if acc_mode % 3 == 0:\n",
    "        cond = cond & BIBA\n",
    "    if acc_mode % 5 == 0:\n",
    "        cond = cond & ACL\n",
    "    \n",
    "    return cond, BIBA, BLP, ACL\n",
    "\n",
    "\n",
    "def check_AouthCode(ip, port, AouthCode, conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    select UserID\n",
    "    from ValidConnections where [AouthCode] = ? and [Ip] = ? and [Port] = ?\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn, params=(AouthCode, ip, port,) )\n",
    "    \n",
    "    if str(sql_query).split()[0] == 'Empty':\n",
    "        return None\n",
    "    else:\n",
    "        return str(sql_query['UserID']).split()[1]\n",
    "\n",
    "\n",
    "def list_files():\n",
    "    \n",
    "    MyQuery = '''\n",
    "    select FileName, LastModifiedDate\n",
    "    from ValidFiles\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn )\n",
    "\n",
    "    return str(sql_query)\n",
    "\n",
    "\n",
    "def read_file(filename, conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    select Content\n",
    "    from ValidFiles where FileName = ?\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn, params={filename,} )\n",
    "    if str(sql_query).split()[0] == 'Empty':\n",
    "        return None\n",
    "    else:\n",
    "        return str(sql_query['Content'][0])\n",
    "\n",
    "\n",
    "def write_file(filename, content, conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    update Files set Content = ?\n",
    "    where [FileName] = ? and [Status] = '1'\n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute( MyQuery, content, filename)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "    new_content = read_file(filename, conn)\n",
    "    if content == new_content:\n",
    "        return 1\n",
    "    return 0\n",
    "        \n",
    "    \n",
    "def get_file(filename, conn):\n",
    "    \n",
    "    content = read_file(filename, conn)\n",
    "    \n",
    "    MyQuery = '''\n",
    "    update Files set [Status] = '0'\n",
    "    where [FileName] = ? and [Status] = '1'\n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute( MyQuery, filename)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "    return content\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def check_username(username, conn):\n",
    "\n",
    "    MyQuery = '''\n",
    "    select UserID\n",
    "    from Users where UserName = ?\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn, params={username,} )\n",
    "    \n",
    "    if str(sql_query).split()[0] == 'Empty':\n",
    "        return None\n",
    "    else:\n",
    "        return str(sql_query['UserID']).split()[1]\n",
    "            \n",
    "#conf_range = ['Top Secret', 'Secret', 'Confidential', 'Unclassified']\n",
    "#int_range = ['Very Trusted', 'Trusted', 'Slightly Trusted', 'UnTrusted']\n",
    "\n",
    "int_range = ['1', '2', '3', '4']\n",
    "conf_range = ['1', '2', '3', '4']\n",
    "\n",
    "def user_registeration(username, password, conf_label, integrity_label, conn):\n",
    "    if username != \"all\":\n",
    "        \n",
    "        salt = ''.join(secrets.choice(string.ascii_letters) for _ in range(25))\n",
    "        pass_hash = hashlib.sha256((password + salt).encode('utf-8')).hexdigest()\n",
    "        pass_hash = str(pass_hash)\n",
    "\n",
    "        MyQuery = '''\n",
    "        insert into Users ( UserName, PasswordHash, Salt, ConfLable, IntegrityLable) values (?, ?, ?, ?, ? );\n",
    "        '''\n",
    "\n",
    "        if conf_label in conf_range and integrity_label in int_range:\n",
    "            cursor = conn.cursor()\n",
    "            cursor.execute( MyQuery, username, pass_hash,salt, conf_label, integrity_label)\n",
    "            conn.commit()\n",
    "            cursor.close()\n",
    "            if check_username(username, conn) == None:\n",
    "                return 0\n",
    "            return 1\n",
    "    return 0\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def CheckPasswordStrength(username, password):\n",
    "    \n",
    "    if password.find(username) != -1:\n",
    "        return \"password should not include username\"\n",
    "    #check condition 1\n",
    "    Condition1 = policy.test(password)\n",
    "    if Condition1:\n",
    "        return ''.join(str(Condition1))\n",
    "    #check condition 2\n",
    "    f = open(\"PwnedPasswordsTop100k.txt\",\"r\")\n",
    "    for x in f:\n",
    "        x = str(x).strip()\n",
    "        if x == password:\n",
    "            return \"Password is in top 100,000 pwned passwords\"\n",
    "    \n",
    "    return '1'\n",
    "\n",
    "def check_password(username, password, conn):\n",
    "    \n",
    "    MyQuery1 = '''\n",
    "    select [Salt]\n",
    "    from Users where UserName = ?\n",
    "    '''\n",
    "    MyQuery2 = '''\n",
    "    select dbo.CheckPassHash(?, ?) as correctness\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery1 ,conn, params={username,} )\n",
    "    \n",
    "    salt = str(sql_query['Salt']).split()[1]\n",
    "    print('salt',salt)\n",
    "    pass_h = str(hashlib.sha256((password + salt).encode('utf-8')).hexdigest())\n",
    "    print('pass_h',pass_h)\n",
    "    print(username)\n",
    "    sql_query2 = pd.read_sql( MyQuery2 ,conn, params=(username, pass_h) )\n",
    "    print(sql_query2)\n",
    "    if str(sql_query2['correctness']).split()[1] == '1':\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def check_file(filename, conn):\n",
    "\n",
    "    MyQuery = '''\n",
    "    select FileID\n",
    "    from ValidFiles where FileName = ?\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn, params={filename,} )\n",
    "    \n",
    "    if str(sql_query).split()[0] == 'Empty':\n",
    "        return None\n",
    "    else:\n",
    "        return str(sql_query['FileID']).split()[1]\n",
    "\n",
    "    \n",
    "def put_file(filename, content, creatorID, conf_label, integrity_label, access_mode , conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    insert into Files ([FileName], [FileCreatorID], [ConfLable], [IntegrityLable], [AccessMode], [Content], [Status])\n",
    "    values (?, ?, ?, ?, ?, ?, ?)\n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, filename, creatorID, conf_label, integrity_label, access_mode, content, '1')\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "    if check_file(filename, conn) == None:\n",
    "        return 0\n",
    "    return 1\n",
    "\n",
    "\n",
    "def update_ban_state(username, status, conn):\n",
    "        \n",
    "    MyQuery1 = '''UPDATE BanUser\n",
    "    SET StartBanTime = CURRENT_TIMESTAMP, BanLvl= ?\n",
    "    where UserID = (select UserID from Users where UserName= ?)\n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    if status:\n",
    "        cursor.execute(MyQuery1, 0, username)\n",
    "        \n",
    "    else:      \n",
    "        MyQuery2 = '''\n",
    "        select dbo.FindLastFailedLogin(?) as lastfail\n",
    "        '''\n",
    "        sql_query2 = pd.read_sql( MyQuery2 ,conn, params={username,} ) \n",
    "        lastfail = str(sql_query2['lastfail']).split()[1]\n",
    "        lastfail = int(lastfail)\n",
    "        if lastfail % 3 == 0:\n",
    "            cursor.execute(MyQuery1, lastfail//3, username)\n",
    "        \n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "\n",
    "def check_ban(username, conn):\n",
    "    \n",
    "    MyQuery2 = '''\n",
    "    select dbo.IsBan(?) as ban_min\n",
    "    '''\n",
    "    sql_query2 = pd.read_sql( MyQuery2 ,conn, params={username,} ) \n",
    "    return int(str(sql_query2['ban_min']).split()[1])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Logs\n",
    "\n",
    "##############################################################################\n",
    "def add_registre_logs(username, conf_lable, integrity_lable, status):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    INSERT INTO RegisterLogs ( UserName, ConfLable, IntegrityLable, [Status])\n",
    "    VALUES (?, ?, ?, ?); \n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, username, conf_lable, integrity_lable, status)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "def add_login_logs(username, password, ip, port, AouthCode, status):\n",
    "\n",
    "    MyQuery = '''\n",
    "    INSERT INTO LoginLogs ( UserName, [password], ConnectionIp, ConnectionPort, AuthenticationCode, [Status])\n",
    "    VALUES (?, ?, ?, ?, ?, ?);\n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, username, password, ip, port, AouthCode, status)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "def add_put_logs(creatorID, fileID, filename, file_conf, file_int, content, status):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    INSERT INTO PutLogs ( CreatorID, FileID, FileName ,CurFileConfLable, CurFileIntegrityLable, Content, [Status])\n",
    "    VALUES (?, ?, ?, ?, ?, ?, ?);\n",
    "    '''\n",
    "    if fileID == None:\n",
    "        fileID = 0\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, creatorID, fileID, filename, file_conf, file_int, content, status)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "\n",
    "def add_get_logs(userID, fileID, filename, file_conf, file_int, status):\n",
    "\n",
    "    MyQuery = '''\n",
    "    INSERT INTO GetLogs ( UserID, FileID, FileName ,CurFileConfLable, CurFileIntegrityLable, [Status])\n",
    "    VALUES (?, ?, ?, ?, ?, ?);\n",
    "    '''\n",
    "    if fileID == None:\n",
    "        fileID = 0\n",
    "    \n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, userID, fileID, filename, file_conf, file_int, status)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "\n",
    "def add_read_logs(userID, fileID, filename, file_conf, file_int, status):\n",
    "\n",
    "    MyQuery = '''\n",
    "    INSERT INTO ReadLogs ( UserID, FileID, FileName, CurFileConfLable, CurFileIntegrityLable, [Status])\n",
    "    VALUES (?, ?, ?, ?, ?, ?);\n",
    "    '''\n",
    "    if fileID == None:\n",
    "        fileID = 0\n",
    "        \n",
    "    print(userID, fileID, filename, file_conf, file_int, status)\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, userID, fileID, filename, file_conf, file_int, status)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "def add_write_logs(userID, fileID, filename, file_conf, file_int, content, status):\n",
    "\n",
    "    MyQuery = '''\n",
    "    INSERT INTO WriteLogs ( UserID, FileID, FileName, CurFileConfLable, CurFileIntegrityLable, Content, [Status])\n",
    "    VALUES (?, ?, ?, ?, ?, ?, ?);\n",
    "    '''\n",
    "    if fileID == None:\n",
    "        fileID = 0\n",
    "        \n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, userID, fileID, filename, file_conf, file_int, content, status)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    " \n",
    " ##############################################################################   \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#Access Control\n",
    "\n",
    "##############################################################################\n",
    "def insert_access(userID, fileID, access_type, conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    insert into AccessList ([UserID], [FileID], [AccessType])\n",
    "    values (?, ?, ?)\n",
    "    '''\n",
    "    \n",
    "    if check_access(userID, fileID, access_type, conn) == 0:\n",
    "    \n",
    "        cursor = conn.cursor()\n",
    "        cursor.execute(MyQuery, userID, fileID, access_type)\n",
    "        conn.commit()\n",
    "        cursor.close()\n",
    "    \n",
    "\n",
    "def revoc_access(userID, fileID, access_type, conn):\n",
    "\n",
    "    MyQuery = '''\n",
    "    delete AccessList\n",
    "    where UserID = ? and FileID = ? and AccessType = ?\n",
    "    '''\n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute(MyQuery, userID, fileID, access_type)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "\n",
    "def check_access(userID, fileID, access_type, conn):\n",
    "    \n",
    "    if access_type == 'o':\n",
    "        MyQuery = '''\n",
    "        select *\n",
    "        from Files\n",
    "        where FileID = ? and FileCreatorID = ? \n",
    "        '''\n",
    "        sql_query = pd.read_sql( MyQuery ,conn, params=(fileID, userID,) )\n",
    "    else:\n",
    "        \n",
    "        MyQuery = '''\n",
    "        select *\n",
    "        from AccessList\n",
    "        where FileID = ? and UserID = ? and AccessType = ? \n",
    "        '''\n",
    "        sql_query = pd.read_sql( MyQuery ,conn, params=(fileID, userID, access_type,) )\n",
    "    \n",
    "    if str(sql_query).split()[0] == 'Empty':\n",
    "        return 0\n",
    "    else:\n",
    "        return 1\n",
    "\n",
    "def get_file_lables(fileID, conn):\n",
    "    \n",
    "    MyQuery = '''\n",
    "    select[IntegrityLable], [ConfLable]\n",
    "    from Files\n",
    "    where FileID = ?\n",
    "    '''\n",
    "    sql_query = pd.read_sql( MyQuery ,conn, params={fileID,} ) \n",
    "    \n",
    "\n",
    "    if str(sql_query).split()[0] == 'Empty':\n",
    "        file_conf = ''\n",
    "        file_int = ''\n",
    "        \n",
    "    else:\n",
    "        file_conf = str(sql_query['ConfLable']).split()[1]\n",
    "        file_int = str(sql_query['IntegrityLable']).split()[1]\n",
    "    \n",
    "    return file_conf, file_int\n",
    "    \n",
    "\n",
    "def check_BLP(userID, fileID, access_type, conn):\n",
    "    \n",
    "    \n",
    "    MyQuery1 = '''\n",
    "    select[ConfLable]\n",
    "    from Users\n",
    "    where UserID = ?\n",
    "    '''\n",
    "    sql_query1 = pd.read_sql( MyQuery1 ,conn, params={userID,} )\n",
    "    \n",
    "    MyQuery2 = '''\n",
    "    select[ConfLable]\n",
    "    from Files\n",
    "    where FileID = ?\n",
    "    '''\n",
    "    sql_query2 = pd.read_sql( MyQuery2 ,conn, params={fileID,} )\n",
    "    \n",
    "    user_conf = int(str(sql_query1['ConfLable']).split()[1])\n",
    "    file_conf = int(str(sql_query2['ConfLable']).split()[1])\n",
    "    \n",
    "    if access_type == 'w':\n",
    "        if(user_conf > file_conf):\n",
    "            return 0\n",
    "        else:\n",
    "            return 1\n",
    "    \n",
    "    elif access_type == 'r':\n",
    "        if(user_conf < file_conf):\n",
    "            return 0\n",
    "        else:\n",
    "            return 1\n",
    "     \n",
    "    \n",
    "def check_BIBA(userID, fileID, access_type, conn):\n",
    "    \n",
    "    \n",
    "    MyQuery1 = '''\n",
    "    select[IntegrityLable]\n",
    "    from Users\n",
    "    where UserID = ?\n",
    "    '''\n",
    "    sql_query1 = pd.read_sql( MyQuery1 ,conn, params={userID,} )\n",
    "    \n",
    "    MyQuery2 = '''\n",
    "    select[IntegrityLable]\n",
    "    from Files\n",
    "    where FileID = ?\n",
    "    '''\n",
    "    sql_query2 = pd.read_sql( MyQuery2 ,conn, params={fileID,} )\n",
    "    \n",
    "    user_int = int(str(sql_query1['IntegrityLable']).split()[1])\n",
    "    file_int = int(str(sql_query2['IntegrityLable']).split()[1])\n",
    "    \n",
    "    if access_type == 'w':\n",
    "        if(user_int < file_int):\n",
    "            return 0\n",
    "        else:\n",
    "            return 1\n",
    "    \n",
    "    elif access_type == 'r':\n",
    "        if(user_int > file_int):\n",
    "            return 0\n",
    "        else:\n",
    "            return 1\n",
    "\n",
    "##############################################################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def user_login(username, Ip, Port, conn):\n",
    "        \n",
    "    userID = check_username(username, conn)\n",
    "    \n",
    "    MyQuery = '''\n",
    "    insert into Connections ([UserID], [Ip], [Port], [AouthCode], [ConnectionDate], [Status])\n",
    "    values (?, ?, ?, ?, ?, ?)\n",
    "    '''\n",
    "    AouthCode = uuid4()\n",
    "    ConnectionDate = str(datetime.now())[:-3]\n",
    "    \n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute( MyQuery, userID, Ip, Port, AouthCode, ConnectionDate, '1')\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "    MyQuery2 = '''\n",
    "    select  [UserID], [Ip], [Port], [AouthCode], [ConnectionDate], [Status]\n",
    "    from connections\n",
    "    where [UserID] = ? and [Ip] = ? and [Port] = ? and [AouthCode] = ? and [ConnectionDate] = ? and [Status] = '1' \n",
    "    '''\n",
    "    \n",
    "    sql_query2 = pd.read_sql( MyQuery2 ,conn, params=(userID, Ip, Port, AouthCode, ConnectionDate,) )\n",
    "\n",
    "    if str(sql_query2).split()[0] == 'Empty':\n",
    "        return 0 , None\n",
    "    \n",
    "    return 1, AouthCode\n",
    "\n",
    "def user_logout(userID, Ip, Port, AouthCode, conn):\n",
    "        \n",
    "    MyQuery = '''\n",
    "    update connections set [Status] = '0', ConnectionCloseDate = ? \n",
    "    where [UserID] = ? and [Ip] = ? and [Port] = ? and [AouthCode] = ? and [Status] = '1'\n",
    "    '''\n",
    "    \n",
    "    ConnectionCloseDate = str(datetime.now())\n",
    "    \n",
    "    cursor = conn.cursor()\n",
    "    cursor.execute( MyQuery, ConnectionCloseDate, userID, Ip, Port, AouthCode)\n",
    "    conn.commit()\n",
    "    cursor.close()\n",
    "    \n",
    "    MyQuery2 = '''\n",
    "    select [Status]\n",
    "    from connections\n",
    "    where [UserID] = ? and [Ip] = ? and [Port] = ? and [AouthCode] = ? and [Status] = '0' and ConnectionCloseDate = ?\n",
    "    '''\n",
    "    sql_query2 = pd.read_sql( MyQuery2 ,conn, params=(userID, Ip, Port, AouthCode, ConnectionCloseDate) )\n",
    "\n",
    "    if str(sql_query2).split()[0] == 'Empty':\n",
    "        return 0\n",
    "    return 1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n",
    "if __name__ == \"__main__\":\n",
    "    \n",
    "    s = setup_server()\n",
    "    while True:\n",
    "        connection_sock = server_listening(s)\n",
    "        try:\n",
    "            Thread(target=func,args=(connection_sock,)).start()\n",
    "        except:\n",
    "            print('Server is busy. Unable to create more threads.')\n",
    "    s.shutdown()\n",
    "    s.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}