# Practical 4 – Database Security
# Install: pip install bcrypt cryptography
import sqlite3
import bcrypt
from cryptography.fernet import Fernet

# 1. Parameterized login query prevents SQL injection
with sqlite3.connect("students.db") as db:
    db.execute("CREATE TABLE IF NOT EXISTS users (username TEXT, password TEXT)")
    db.execute("INSERT INTO users VALUES (?, ?)",
               ("admin", "admin_password"))
    username = input("Username: ")
    password = input("Password: ")
    result = db.execute(
        "SELECT username FROM users WHERE username=? AND password=?",
        (username, password),
    ).fetchone()
    print("Login successful!" if result else "Invalid credentials!")

# 2. Store a password hash and verify login
with sqlite3.connect("secure_users.db") as db:
    db.execute("CREATE TABLE IF NOT EXISTS users "
               "(username TEXT PRIMARY KEY, password BLOB)")
    username = input("Register username: ")
    password = input("Register password: ").encode()
    hashed = bcrypt.hashpw(password, bcrypt.gensalt())
    db.execute("INSERT OR REPLACE INTO users VALUES (?, ?)", (username, hashed))
    login_user = input("Login username: ")
    login_password = input("Login password: ").encode()
    row = db.execute("SELECT password FROM users WHERE username=?",
                     (login_user,)).fetchone()
    print("Login successful!" if row and bcrypt.checkpw(login_password, row[0])
          else "Login failed!")

# 3. Role-based access control with parameterized SQL
with sqlite3.connect("rbac.db") as db:
    db.execute("CREATE TABLE IF NOT EXISTS users "
               "(username TEXT PRIMARY KEY, password TEXT, role TEXT)")
    db.executemany("INSERT OR IGNORE INTO users VALUES (?, ?, ?)", [
        ("admin", "admin_pwd", "admin"), ("john", "john_pwd", "student")
    ])
    username = input("Username: ")
    password = input("Password: ")
    user = db.execute(
        "SELECT role FROM users WHERE username=? AND password=?",
        (username, password),
    ).fetchone()
    if user:
        print("Role:", user[0])
        print("Access granted." if user[0] == "admin"
              else "Access granted to view records.")
    else:
        print("Invalid credentials!")

# 4. Encrypt and decrypt database-like fields with Fernet
cipher = Fernet(Fernet.generate_key())
data = {"name": "John", "email": "john@example.com", "password": "12345"}
encrypted = {key: cipher.encrypt(value.encode()).decode()
             for key, value in data.items()}
decrypted = {key: cipher.decrypt(value.encode()).decode()
             for key, value in encrypted.items()}
print("Encrypted:", encrypted)
print("Decrypted:", decrypted)

# What this code does: demonstrates parameterized database queries, password
# hashing, role checks, and encryption of sensitive fields.
