# SQL i Python Workshop - Materialer
## Forberedelse af miljø
### Ubuntu Server opsætning
```bash
# Installer SQLite og Python
sudo apt update
sudo apt install sqlite3 -y
# Opret en mappe til workshoppen
mkdir ~/sql_workshop
cd ~/sql_workshop
```
## Del 1: Grundlæggende SQL
### Oprettelse af en testdatabase
```bash
# Start SQLite med en ny database
sqlite3 security_test.db
```
#### SQL-kommandoer til oprettelse af database-skema
```SQL
-- Opret en tabel til brugere
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
password TEXT NOT NULL,
email TEXT,
is_admin BOOLEAN DEFAULT 0
);
-- Opret en tabel til logfiler
CREATE TABLE logs (
id INTEGER PRIMARY KEY,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
user_id INTEGER,
action TEXT,
ip_address TEXT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- Indsæt testdata
INSERT INTO users (username, password, email, is_admin) VALUES
('admin', 'password123', '
[email protected]', 1),
('alice', 'securepass', '
[email protected]', 0),
('bob', 'bobspassword', '
[email protected]', 0);
-- Gem og afslut med .exit - vigtigt husk punktum .exit
```
### Inspection af database og data
Start med at tilgå databasen igen
```bash
# Start SQLite
sqlite3 security_test.db
```
prøv: `.help`
```sql
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> .help
```
Læs om de forskellige mulige kommandoer, find og afprøv kommandoen for:
- List names and files of attached databases
- List names of tables matching LIKE pattern TABLE
- Show the CREATE statements matching PATTERN
>[!question] Spørgsmål/opgaver:
> Hvordan ser du hvilke tables din database indeholder og hvilken CREATE statement som har skabt disse?
>[!tip]- Svar
>![[Pasted image 20250324002341.png]]
### Grundlæggende SQL-forespørgsler (øvelser)
```sql
-- Hent alle brugere
SELECT * FROM users;
-- Filtrer data
SELECT username, email FROM users WHERE is_admin = 1;
-- Opdater data
UPDATE users SET password = 'newpassword' WHERE username = 'alice';
-- Slet data
DELETE FROM users WHERE username = 'bob';
```
## Del 2: Python og SQLite
### Grundlæggende Python-script til database adgang
```python
#!/usr/bin/python3
# file: basic_db.py
import sqlite3
# Opret forbindelse til databasen
conn = sqlite3.connect('security_test.db')
cursor = conn.cursor()
# Udfør en forespørgsel
cursor.execute("SELECT * FROM users")
users = cursor.fetchall()
# Vis resultater
print("Alle brugere:")
for user in users:
print(f"ID: {user[0]}, Brugernavn: {user[1]}, Email: {user[3]}, Admin: {user[4]}")
# Luk forbindelsen
conn.close()
```
#### CRUD-operationer i Python
```python
#!/usr/bin/python3
# file: crud_operations.py
import sqlite3
def create_connection(db_file):
"""Opret en databaseforbindelse til SQLite databasen"""
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except sqlite3.Error as e:
print(e)
return conn
def add_user(conn, user):
"""Tilføj en ny bruger til users tabellen"""
sql = '''INSERT INTO users(username, password, email, is_admin)
VALUES(?,?,?,?)'''
cur = conn.cursor()
cur.execute(sql, user)
conn.commit()
return cur.lastrowid
def update_user(conn, user):
"""Opdater brugerdata baseret på bruger-id"""
sql = '''UPDATE users
SET username = ?,
password = ?,
email = ?,
is_admin = ?
WHERE id = ?'''
cur = conn.cursor()
cur.execute(sql, user)
conn.commit()
def delete_user(conn, id):
"""Slet en bruger baseret på bruger-id"""
sql = 'DELETE FROM users WHERE id=?'
cur = conn.cursor()
cur.execute(sql, (id,))
conn.commit()
def select_all_users(conn):
"""Hent alle brugere fra databasen"""
cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows = cur.fetchall()
return rows
def main():
database = "security_test.db"
# Opret forbindelse
conn = create_connection(database)
if conn is not None:
# Tilføj en ny bruger
user = ('charlie', 'charliepass', '
[email protected]', 0)
user_id = add_user(conn, user)
print(f"Tilføjet bruger med ID: {user_id}")
# Opdater en bruger
updated_user = ('charlie', 'strongerpass', '
[email protected]', 1, user_id)
update_user(conn, updated_user)
print(f"Opdateret bruger med ID: {user_id}")
# Vis alle brugere
users = select_all_users(conn)
for user in users:
print(user)
# Slet en bruger
delete_user(conn, user_id)
print(f"Slettet bruger med ID: {user_id}")
# Vis alle brugere igen efter sletning
users = select_all_users(conn)
for user in users:
print(user)
conn.close()
if __name__ == '__main__':
main()
```
## Del 3: Sikkerhedsperspektiver
### Usikker kode (sårbar over for SQL-injektion)
```python
#!/usr/bin/python3
# file: insecure_login.py
import sqlite3
def insecure_login(username, password):
conn = sqlite3.connect('security_test.db')
cursor = conn.cursor()
# USIKKER MÅDE: Direkte indsættelse af brugerinput i SQL-forespørgslen
query = f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
print(f"Executing query: {query}") # Til demonstration
cursor.execute(query)
user = cursor.fetchone()
conn.close()
if user:
return f"Login success! Welcome {user[1]}"
else:
return "Login failed: Invalid credentials"
# Test med legitim login
print(insecure_login("alice", "securepass"))
# Test med SQL-injektion
print(insecure_login("anything' OR '1'='1", "anything"))
```
#### Sikker kode (beskyttet mod SQL-injektion)
```python
#!/usr/bin/env python3
# file: secure_login.py
import sqlite3
def secure_login(username, password):
conn = sqlite3.connect('security_test.db')
cursor = conn.cursor()
# SIKKER MÅDE: Brug af parameteriserede forespørgsler
query = "SELECT * FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))
user = cursor.fetchone()
conn.close()
if user:
return f"Login success! Welcome {user[1]}"
else:
return "Login failed: Invalid credentials"
# Test med legitim login
print(secure_login("alice", "securepass"))
# Test med SQL-injektion (vil ikke virke nu)
print(secure_login("anything' OR '1'='1", "anything"))
```
### Øvelse 1: Oprettelse af simpel database
1. Opret en database med en tabel til lagring af netværkshændelser
2. Tabellen skal have felter til: id, timestamp, event_type, source_ip, destination_ip, port, protocol, severity
### Øvelse 2: Python-script til databaser
1. Skriv et Python-script der kan indsætte netværkshændelser i databasen
2. Implementér en funktion der kan søge i databasen efter bestemte IP-adresser