All materials
app.py
pyapp.py
import os
import sqlite3
import requests
from flask import Flask, request, render_template_string, jsonify, g
app = Flask(__name__)
DATABASE = '/app/data/guides.db'
TOURISM_API_URL = os.environ.get('TOURISM_API_URL', 'http://tourism-portal:3000/api/tourist-data')
def get_db():
db = getattr(g, '_database', None)
if db is None:
db = g._database = sqlite3.connect(DATABASE)
db.row_factory = sqlite3.Row
return db
@app.teardown_appcontext
def close_connection(exception):
db = getattr(g, '_database', None)
if db is not None:
db.close()
def init_db():
db = sqlite3.connect(DATABASE)
db.execute('''
CREATE TABLE IF NOT EXISTS guides (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
license_number TEXT NOT NULL,
nationality TEXT,
phone TEXT,
employment_status TEXT DEFAULT 'active',
credentials TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
db.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
role TEXT DEFAULT 'staff',
is_active INTEGER DEFAULT 1,
last_login DATETIME,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
count = db.execute('SELECT COUNT(*) FROM guides').fetchone()[0]
if count == 0:
guides = [
('Dorji Wangchuk', 'GL-2024-001', 'Bhutanese', '+975-17-123456', 'active', 'Cultural Heritage Guide, Trekking Guide Level 2'),
('Kinley Zam', 'GL-2024-002', 'Bhutanese', '+975-17-234567', 'active', 'Cultural Heritage Guide'),
('Tashi Dorji', 'GL-2023-015', 'Bhutanese', '+975-17-345678', 'active', 'Trekking Guide Level 3, Bird Watching Specialist'),
('Pema Lhamo', 'GL-2024-003', 'Bhutanese', '+975-17-456789', 'active', 'Cultural Heritage Guide, Festival Specialist'),
('Sangay Tshering', 'GL-2022-008', 'Bhutanese', '+975-17-567890', 'inactive', 'Cultural Heritage Guide'),
]
for g_data in guides:
db.execute('INSERT INTO guides (name, license_number, nationality, phone, employment_status, credentials) VALUES (?, ?, ?, ?, ?, ?)', g_data)
users = [
('tshering.pem', 'pbkdf2:sha256:admin_hash_placeholder', 'admin', 1),
('dorji.operator', 'pbkdf2:sha256:operator_hash_placeholder', 'staff', 1),
('karma.former', 'pbkdf2:sha256:former_hash_placeholder', 'admin', 1),
('sonam.transferred', 'pbkdf2:sha256:transferred_hash_placeholder', 'admin', 1),
]
for u in users:
db.execute('INSERT INTO users (username, password_hash, role, is_active) VALUES (?, ?, ?, ?)', u)
db.commit()
db.close()
init_db()
INDEX_TEMPLATE = '''
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Guide Management System</title>
<style>
body { font-family: Georgia, serif; margin: 0; padding: 20px; background: #faf8f5; }
.header { background: #8b4513; color: white; padding: 20px; margin: -20px -20px 20px; }
.header h1 { margin: 0; }
form { margin: 20px 0; }
input { padding: 8px; border: 1px solid #ccc; border-radius: 4px; width: 250px; }
button { padding: 8px 16px; background: #8b4513; color: white; border: none; border-radius: 4px; cursor: pointer; }
.info { background: white; padding: 15px; border-radius: 6px; margin: 10px 0; border: 1px solid #ddd; }
</style></head>
<body>
<div class="header"><h1>Guide Management System</h1><p>Bhutan Tourism Council - Guide Licensing and Credentials</p></div>
<div class="info"><h2>Guide Credential Lookup</h2>
<form action="/lookup" method="GET">
<input type="text" name="name" placeholder="Enter guide name...">
<button type="submit">Lookup</button>
</form></div>
</body></html>
'''
LOOKUP_TEMPLATE = '''
<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Lookup Results</title>
<style>
body { font-family: Georgia, serif; padding: 20px; background: #faf8f5; }
.header { background: #8b4513; color: white; padding: 20px; margin: -20px -20px 20px; }
table { width: 100%%; border-collapse: collapse; background: white; }
th, td { padding: 10px; text-align: left; border-bottom: 1px solid #ddd; }
th { background: #8b4513; color: white; }
a { color: #8b4513; }
</style></head>
<body>
<div class="header"><h1>Lookup Results</h1></div>
<p><a href="/">Back</a></p>
<p>Results for: {{ query }}</p>
{% if results %}
<table><tr><th>Name</th><th>License</th><th>Status</th><th>Credentials</th></tr>
{% for r in results %}<tr><td>{{ r.name }}</td><td>{{ r.license_number }}</td><td>{{ r.employment_status }}</td><td>{{ r.credentials }}</td></tr>{% endfor %}
</table>
{% else %}<p>No guides found.</p>{% endif %}
</body></html>
'''
@app.route('/')
def index():
return render_template_string(INDEX_TEMPLATE)
# VULNERABLE: XSS in credential lookup -- user input rendered without escaping
@app.route('/lookup')
def lookup():
name = request.args.get('name', '')
db = get_db()
results = db.execute(
"SELECT name, license_number, employment_status, credentials FROM guides WHERE name LIKE ?",
(f'%{name}%',)
).fetchall()
# Intentional: rendering raw query back into page without escaping
return render_template_string(LOOKUP_TEMPLATE, results=results, query=name)
# Internal API -- no authentication required
# Called by tourism-portal and operations-platform
# This API bypasses the main authentication system
@app.route('/api/guides')
def api_guides():
db = get_db()
guides = db.execute('SELECT id, name, license_number, phone, employment_status, credentials FROM guides').fetchall()
return jsonify({'guides': [dict(g) for g in guides]})
@app.route('/api/guides/<int:guide_id>')
def api_guide(guide_id):
db = get_db()
guide = db.execute('SELECT * FROM guides WHERE id = ?', (guide_id,)).fetchone()
if guide:
return jsonify(dict(guide))
return jsonify({'error': 'Guide not found'}), 404
# Cross-portal API call -- fetches tourist data from tourism-portal without auth
@app.route('/api/tourist-assignments')
def tourist_assignments():
try:
resp = requests.get(TOURISM_API_URL, timeout=5)
tourist_data = resp.json()
return jsonify(tourist_data)
except Exception as e:
return jsonify({'error': str(e)}), 500
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5000)