# 1. Securing Database with Input Validation  
import sqlite3 
conn = sqlite3.connect("students.db") 
cursor = conn.cursor() 
cursor.execute("CREATE TABLE IF NOT EXISTS users (username TEXT, password  TEXT)") 
cursor.execute("INSERT INTO users VALUES ('admin', 'admin_password')") 
conn.commit() 
username = input("Enter username: ") 
password = input("Enter password: ") 
query = f"SELECT * FROM users WHERE username = '{username}' AND  password='{password}'" 
cursor.execute(query) 
# cursor.execute( 
# "SELECT * FROM users WHERE username = ? AND password = ?", (username, password) # ) 
result = cursor.fetchone() 
print(result) 
if result: 
    print("Login successful!") 
else:
    print("Invalid credentials!") 

# Output (Case 1) 
# Enter username: admin 
# Enter password: admin_password ('admin', 'admin_password') Login successful! 
# Output (Case 2) : SQL Injection
# Enter username: ' OR '1'='1' -- 
# Enter password: test 
# ('admin', 'admin_password') Login successful! 
# Output (Case 3 - Parameterized Query) -> Concept: Parameterized queries prevent SQL injection.
# Enter username: ' OR '1'='1' -- 
# Enter password: test 
# None 
# Invalid credentials!


# 2. Encrypting Sensitive Data in Database  
import sqlite3 
import bcrypt 
conn = sqlite3.connect("secure_users.db") 
cursor = conn.cursor() 
cursor.execute("CREATE TABLE IF NOT EXISTS users (username TEXT, password  TEXT)") 
conn.commit() 
username = input("Enter username: ") 
raw_password = input("Enter password: ") 
hashed_password = bcrypt.hashpw(raw_password.encode(), bcrypt.gensalt()) 
cursor.execute("INSERT INTO users VALUES (?, ?)", (username, hashed_password)) 
conn.commit() 
login_user = input("Username to login: ") 
login_pwd = input("Password to login: ") 
cursor.execute("SELECT password FROM users WHERE username = ?", (login_user,)) 
data = cursor.fetchone() 
print(data) 
if data and bcrypt.checkpw(login_pwd.encode(), data[0]): 
    print("Login successful!") 
else: 
    print("Login failed!")



# 3. Role-Based Access Control (RBAC) in Database  
import sqlite3 
conn = sqlite3.connect("rbac.db") 
cursor = conn.cursor() 
cursor.execute( 
"CREATE TABLE IF NOT EXISTS users (username TEXT, password TEXT, role TEXT)" ) 
cursor.execute("INSERT INTO users VALUES ('admin', 'admin_pwd', 'admin')") 
cursor.execute("INSERT INTO users VALUES ('john', 'john_pwd', 'student')") 
conn.commit() 
username = input("Enter username: ") 
password = input("Enter password: ") 
cursor.execute( 
"SELECT role FROM users WHERE username=? AND password=?", (username, password) )
user = cursor.fetchone() 
if user: 
    role = user[0] 
    print(f"Login successful! Your role is: {role}") 
    if role == "admin": 
        print("Access granted to view, add, delete and modify records.") 
    elif role == "student": 
        print("Access granted to view records.") 
    else: 
        print("Limited access.") 
else: 
    print("Invalid credentials!") 




# 4. Encrypting Entire DB Fields Using Fernet  
from cryptography.fernet import Fernet

# Generate key
key = Fernet.generate_key()
cipher = Fernet(key)

# Original database data
data = {
    "name": "John",
    "email": "john@gmail.com",
    "password": "12345"
}

# Encrypt all fields
encrypted = {
    k: cipher.encrypt(v.encode()).decode()
    for k, v in data.items()
}

print("Encrypted Data:", encrypted)

# Decrypt all fields
decrypted = {
    k: cipher.decrypt(v.encode()).decode()
    for k, v in encrypted.items()
}

print("Decrypted Data:", decrypted)


