#!/usr/bin/env python
# coding: utf-8

# # Sqlite database
# <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>
# 
# - https://www.sqlite.org/
# - C-based, one of the most used embedded database (zero configuration)
# 
# ## SQL basics
# - Structured Query Language
# - case insensitive language; usually written in uppercase
# - let's you or program use SQL-based databases such as SQLite, MySQL, MSSQL, PostgreSQL, etc.
# - most important basic statents to learn: CRUD
# - C: create (database, table, create and insert records)
# - R: retrieve/read data
# - U: update data
# - D: delete data
# - http://www.w3schools.com/sql/default.asp
# 
# ## sqlite browser
# - GUI-based sqlite db explorer
# - makes it easy to see data and learn SQL
# - http://sqlitebrowser.org/

# ## sqlite3 module
# - python3 provides sqlite3 library to work with sqlite database
# - https://docs.python.org/3/library/sqlite3.html
# - SQLite natively supports the following types: NULL, INTEGER, REAL, TEXT, BLOB 
# 
# |SQLite type|Python type|
# | ---|---|
# | NULL | None |
# | INTEGER | int |
# | REAL | float |
# | TEXT | str |
# | BLOB | bytes |
# 
# ## in memory db example

# In[1]:


import sqlite3
# connect to the memory database
con = sqlite3.connect(":memory:")

# create a table
con.execute("create table person(fname, lname)")


# In[2]:


# fill the table with data
persons = [('Hugo', 'Boss'), ('Calvin', 'Klien')]
con.executemany("insert into person(fname, lname) values (?, ?)", 
                persons)


# In[3]:


# print the table contents
for row in con.execute("select rowid, fname, lname from person"):
    print(row)


# In[4]:


print("I just deleted", con.execute("delete from person where rowid=1").rowcount, 
      "rows")


# ## db file example
# ### create database, create table and insert data into table

# In[5]:


import sqlite3
# create connection
conn = sqlite3.connect('example.db')
# create cursor object
cur = conn.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS students (
                firstName text, 
                lastName text, 
                test1 real, 
                test2 real,
                average real,
                grade text
                )
            """)


# In[6]:


query = """ INSERT INTO students (firstName, lastName, 
            test1, test2) values (?, ?, ?, ?)
        """
cur.execute(query, ('John', 'Smith', 99, 95.5))


# In[7]:


cur.execute(query, ('Michael', 'Jordan', 50, 65))


# In[8]:


# save/commit the changes to the db
conn.commit()
# close the database if done
conn.close()


# ### open database, read and update table

# In[9]:


import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()


# In[10]:


cur.execute('SELECT * FROM students where rowid = 1')
row = cur.fetchone() # returns one row as tuple if rowid with value 1 exists
print(row)


# In[11]:


for col in row:
    print(col)


# In[12]:


cur.execute('SELECT rowid, * FROM students')
rows = cur.fetchall()
print(type(rows))


# In[13]:


for row in rows:
    print(row)


# <strong>update table</strong>

# In[14]:


for row in rows:
    avg = (row[3] + row[4])/2
    # grade = ?
    cur.execute('update students set average=? where rowid=?', (avg, row[0]))


# In[15]:


cur.execute('select * from students')
print(cur.fetchall())


# In[16]:


# commit changes and close connection
conn.commit()
conn.close()


# ## SQL Injection Vulnerability
# - how not to write sql query in programs

# In[17]:


import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()

cur.execute("""CREATE TABLE IF NOT EXISTS users (
                username text unique, 
                password text 
                )
            """)


# In[18]:


# Prompt user to create account
username = input('Enter your username: ')
password = input('Pick a password: ')


# In[23]:


# bad passwords
# insecure way to create sql statements
sqlinsert = "insert into users (username, password) values ('{0}', '{1}')".format(username, password)
print(sqlinsert)
cur.execute(sqlinsert)


# In[24]:


# check database
conn.commit()
for row in cur.execute('select * from users'):
    print(row)


# ### what is wrong with the above codes?

# ### authenticate users and SQL injection attack

# In[25]:


# Prompt user to create account
def insecureAuthentication():
    username = input('Enter your username: ')
    password = input('Pick a password: ')
    sqlSelect = "select * from users where username = '{0}' \
                    and password = '{1}'".format(username, password)
    cur.execute(sqlSelect)
    row = cur.fetchone()
    if row:
        print('Welcome {}, this is your kingdom!'.format(row[0]))
    else:
        print('Wrong credentials. Try Again!')
        


# In[26]:


insecureAuthentication()


# In[30]:


# sql injection; authenticate without using password
insecureAuthentication()


# ## secure way to store password
# - https://docs.python.org/3/library/hashlib.html
# 

# In[31]:


import uuid
import hashlib, binascii

def createSecurePassword(password, salt=None, round=100000):
    if not salt:
        salt = uuid.uuid4().hex
    
    """
    for i in range(round):
        password = password+salt
        password = hashlib.sha256(password.encode('utf-8')).hexdigest()
    """
    # hashlib.pbkdf2_hmac(hash_name, password, salt, iterations, dklen=None)
    dk = hashlib.pbkdf2_hmac('sha256', password.encode('utf-8'), 
                        salt.encode('utf-8'), round)
    password = binascii.hexlify(dk)
    return "%s:%s"%(password, salt)


# In[32]:


def secureRegistration():
    # Prompt user to create account
    username = input('Enter your username: ')
    password = input('Enter your password: ')
    secPass = createSecurePassword(password)
    insert = 'insert into users (username, password) values (?, ?)'
    cur.execute(insert, (username, secPass))
    


# In[34]:


# register a user
secureRegistration()


# In[35]:


# check data
for row in cur.execute('select * from users'):
    print(row)


# In[ ]:


conn.commit()


# In[36]:


def secureAuthentication():
    username = input('Enter your username: ')
    password = input('Enter your password: ') 
    # use parameterized query
    sqlSelect = 'select password from users where username = ?'
    cur.execute(sqlSelect, (username,))
    row = cur.fetchone()
    if row:
        # username exists
        # check password hashes
        hashpass = row[0]
        hashedPass = hashpass[:hashpass.find(':')]
        salt = hashpass[hashpass.find(':')+1:]
        secPass = createSecurePassword(password, salt)
        if hashpass == secPass:
            print('Welcome to your kingdom, {}'.format(username))
        else:
            print('Wrong credentials. Try Again!')
    else:
        print('Wrong credentials. Try Again!')


# In[37]:


secureAuthentication()


# In[39]:


# try the same SQL injection
secureAuthentication()


# In[ ]:


conn.commit()
conn.close()


# In[ ]: