{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sqlite database\n",
    "<a href=\"https://colab.research.google.com/github/rambasnet/FDSPython-Notebooks/blob/master/Ch21-SqliteDB.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>\n",
    "\n",
    "- https://www.sqlite.org/\n",
    "- C-based, one of the most used embedded database (zero configuration)\n",
    "\n",
    "## SQL basics\n",
    "- Structured Query Language\n",
    "- case insensitive language; usually written in uppercase\n",
    "- let's you or program use SQL-based databases such as SQLite, MySQL, MSSQL, PostgreSQL, etc.\n",
    "- most important basic statents to learn: CRUD\n",
    "- C: create (database, table, create and insert records)\n",
    "- R: retrieve/read data\n",
    "- U: update data\n",
    "- D: delete data\n",
    "- http://www.w3schools.com/sql/default.asp\n",
    "\n",
    "## sqlite browser\n",
    "- GUI-based sqlite db explorer\n",
    "- makes it easy to see data and learn SQL\n",
    "- http://sqlitebrowser.org/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## sqlite3 module\n",
    "- python3 provides sqlite3 library to work with sqlite database\n",
    "- https://docs.python.org/3/library/sqlite3.html\n",
    "- SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB \n",
    "\n",
    "|SQLite type|Python type|\n",
    "| ---|---|\n",
    "| NULL | None |\n",
    "| INTEGER | int |\n",
    "| REAL | float |\n",
    "| TEXT | str |\n",
    "| BLOB | bytes |\n",
    "\n",
    "## in memory db example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d803b0>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "# connect to the memory database\n",
    "con = sqlite3.connect(\":memory:\")\n",
    "\n",
    "# create a table\n",
    "con.execute(\"create table person(fname, lname)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d80570>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fill the table with data\n",
    "persons = [('Hugo', 'Boss'), ('Calvin', 'Klien')]\n",
    "con.executemany(\"insert into person(fname, lname) values (?, ?)\", \n",
    "                persons)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Hugo', 'Boss')\n",
      "(2, 'Calvin', 'Klien')\n"
     ]
    }
   ],
   "source": [
    "# print the table contents\n",
    "for row in con.execute(\"select rowid, fname, lname from person\"):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "I just deleted 1 rows\n"
     ]
    }
   ],
   "source": [
    "print(\"I just deleted\", con.execute(\"delete from person where rowid=1\").rowcount, \n",
    "      \"rows\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## db file example\n",
    "### create database, create table and insert data into table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d808f0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "# create connection\n",
    "conn = sqlite3.connect('example.db')\n",
    "# create cursor object\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"\"\"CREATE TABLE IF NOT EXISTS students (\n",
    "                firstName text, \n",
    "                lastName text, \n",
    "                test1 real, \n",
    "                test2 real,\n",
    "                average real,\n",
    "                grade text\n",
    "                )\n",
    "            \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d808f0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\" INSERT INTO students (firstName, lastName, \n",
    "            test1, test2) values (?, ?, ?, ?)\n",
    "        \"\"\"\n",
    "cur.execute(query, ('John', 'Smith', 99, 95.5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d808f0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute(query, ('Michael', 'Jordan', 50, 65))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save/commit the changes to the db\n",
    "conn.commit()\n",
    "# close the database if done\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### open database, read and update table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('example.db')\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('John', 'Smith', 99.0, 95.5, None, None)\n"
     ]
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM students where rowid = 1')\n",
    "row = cur.fetchone() # returns one row as tuple if rowid with value 1 exists\n",
    "print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "John\n",
      "Smith\n",
      "99.0\n",
      "95.5\n",
      "None\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "for col in row:\n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'list'>\n"
     ]
    }
   ],
   "source": [
    "cur.execute('SELECT rowid, * FROM students')\n",
    "rows = cur.fetchall()\n",
    "print(type(rows))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'John', 'Smith', 99.0, 95.5, None, None)\n",
      "(2, 'Michael', 'Jordan', 50.0, 65.0, None, None)\n"
     ]
    }
   ],
   "source": [
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<strong>update table</strong>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in rows:\n",
    "    avg = (row[3] + row[4])/2\n",
    "    # grade = ?\n",
    "    cur.execute('update students set average=? where rowid=?', (avg, row[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('John', 'Smith', 99.0, 95.5, 97.25, None), ('Michael', 'Jordan', 50.0, 65.0, 57.5, None)]\n"
     ]
    }
   ],
   "source": [
    "cur.execute('select * from students')\n",
    "print(cur.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# commit changes and close connection\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Injection Vulnerability\n",
    "- how not to write sql query in programs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d80730>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "conn = sqlite3.connect('example.db')\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute(\"\"\"CREATE TABLE IF NOT EXISTS users (\n",
    "                username text unique, \n",
    "                password text \n",
    "                )\n",
    "            \"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your username: john\n",
      "Pick a password: password\n"
     ]
    }
   ],
   "source": [
    "# Prompt user to create account\n",
    "username = input('Enter your username: ')\n",
    "password = input('Pick a password: ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "insert into users (username, password) values ('john', 'password')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x109d80730>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# bad passwords\n",
    "# insecure way to create sql statements\n",
    "sqlinsert = \"insert into users (username, password) values ('{0}', '{1}')\".format(username, password)\n",
    "print(sqlinsert)\n",
    "cur.execute(sqlinsert)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('john', 'password')\n"
     ]
    }
   ],
   "source": [
    "# check database\n",
    "conn.commit()\n",
    "for row in cur.execute('select * from users'):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### what is wrong with the above codes?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### authenticate users and SQL injection attack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prompt user to create account\n",
    "def insecureAuthentication():\n",
    "    username = input('Enter your username: ')\n",
    "    password = input('Pick a password: ')\n",
    "    sqlSelect = \"select * from users where username = '{0}' \\\n",
    "                    and password = '{1}'\".format(username, password)\n",
    "    cur.execute(sqlSelect)\n",
    "    row = cur.fetchone()\n",
    "    if row:\n",
    "        print('Welcome {}, this is your kingdom!'.format(row[0]))\n",
    "    else:\n",
    "        print('Wrong credentials. Try Again!')\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your username: john\n",
      "Pick a password: password\n",
      "Welcome john, this is your kingdom!\n"
     ]
    }
   ],
   "source": [
    "insecureAuthentication()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your username: john' or '1'='1\n",
      "Pick a password: adfadsfdsf\n",
      "Welcome john, this is your kingdom!\n"
     ]
    }
   ],
   "source": [
    "# sql injection; authenticate without using password\n",
    "insecureAuthentication()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## secure way to store password\n",
    "- https://docs.python.org/3/library/hashlib.html\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "import uuid\n",
    "import hashlib, binascii\n",
    "\n",
    "def createSecurePassword(password, salt=None, round=100000):\n",
    "    if not salt:\n",
    "        salt = uuid.uuid4().hex\n",
    "    \n",
    "    \"\"\"\n",
    "    for i in range(round):\n",
    "        password = password+salt\n",
    "        password = hashlib.sha256(password.encode('utf-8')).hexdigest()\n",
    "    \"\"\"\n",
    "    # hashlib.pbkdf2_hmac(hash_name, password, salt, iterations, dklen=None)\n",
    "    dk = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), \n",
    "                        salt.encode('utf-8'), round)\n",
    "    password = binascii.hexlify(dk)\n",
    "    return \"%s:%s\"%(password, salt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "def secureRegistration():\n",
    "    # Prompt user to create account\n",
    "    username = input('Enter your username: ')\n",
    "    password = input('Enter your password: ')\n",
    "    secPass = createSecurePassword(password)\n",
    "    insert = 'insert into users (username, password) values (?, ?)'\n",
    "    cur.execute(insert, (username, secPass))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your username: jake\n",
      "Enter your password: password1\n"
     ]
    }
   ],
   "source": [
    "# register a user\n",
    "secureRegistration()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('john', 'password')\n",
      "('jake', \"b'c318988672d05094deaffce0148a49b1b43dfc89f3b8b75d251de60446dcecc5':5340a4af29574554997b0fe7a1ac670b\")\n"
     ]
    }
   ],
   "source": [
    "# check data\n",
    "for row in cur.execute('select * from users'):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "def secureAuthentication():\n",
    "    username = input('Enter your username: ')\n",
    "    password = input('Enter your password: ') \n",
    "    # use parameterized query\n",
    "    sqlSelect = 'select password from users where username = ?'\n",
    "    cur.execute(sqlSelect, (username,))\n",
    "    row = cur.fetchone()\n",
    "    if row:\n",
    "        # username exists\n",
    "        # check password hashes\n",
    "        hashpass = row[0]\n",
    "        hashedPass = hashpass[:hashpass.find(':')]\n",
    "        salt = hashpass[hashpass.find(':')+1:]\n",
    "        secPass = createSecurePassword(password, salt)\n",
    "        if hashpass == secPass:\n",
    "            print('Welcome to your kingdom, {}'.format(username))\n",
    "        else:\n",
    "            print('Wrong credentials. Try Again!')\n",
    "    else:\n",
    "        print('Wrong credentials. Try Again!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your username: jake\n",
      "Enter your password: password1\n",
      "Welcome to your kingdom, jake\n"
     ]
    }
   ],
   "source": [
    "secureAuthentication()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your username: jake' or '1' = '1\n",
      "Enter your password: adsfadsf\n",
      "Wrong credentials. Try Again!\n"
     ]
    }
   ],
   "source": [
    "# try the same SQL injection\n",
    "secureAuthentication()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}