helpers/analysis_paper.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sys\n",
    "sys.path.append(\"..\")\n",
    "from helpers.db_util import db, ReqResp, Req, DirectTest, ProbeTest, RetroTest, RedTest, Url, Site, Violation, Monitoring\n",
    "from helpers.util import parse_headers\n",
    "from helpers.analysis import common_errors, plot_errors, get_violation_details, get_direct_details, get_retro_details, get_violations, get_resp_details\n",
    "from mitmproxy.http import Headers\n",
    "from ast import literal_eval\n",
    "from dotenv import load_dotenv\n",
    "from tld import get_fld\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "load_dotenv()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# High level stats\n",
    "- Get all violations of each site\n",
    "- Ordered by site and by violation!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def connect(db_name):\n",
    "    db.init(db_name)\n",
    "    db.connect()\n",
    "\n",
    "# TODO: replace with your DB names; either run analysis on local servers or popular websites\n",
    "#db_name = \"results_local_2023_05_10\"\n",
    "db_name = \"results_popular_2023_05_10\"\n",
    "\n",
    "connect(db_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## General stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# General results on sites and origins\n",
    "sites = pd.DataFrame(Site().select().dicts())\n",
    "sites[\"scheme\"] = sites[\"origin\"].apply(lambda x: x.split(\"://\")[0])\n",
    "sites[\"same-scheme\"] = sites[\"scheme\"] == sites[\"org_scheme\"]\n",
    "display(sites.head())\n",
    "display(sites[\"reachable\"].value_counts().to_frame())\n",
    "\n",
    "# General results on URLs\n",
    "urls = pd.DataFrame(Url().select().dicts())\n",
    "display(urls.head())\n",
    "\n",
    "# SELECT count(id) as c, description  from url GROUP by description ORDER by c DESC\n",
    "\n",
    "mon = pd.DataFrame(Monitoring().select().dicts())\n",
    "display(mon.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Also how many sites have more than one hostname in our CrUX dataset and how many sites are reachable\n",
    "display(sites[\"site\"].value_counts().apply(lambda x: x/2).describe())\n",
    "\n",
    "# Reachable by scheme and original scheme (we test every hostname in CrUX both with http and https)\n",
    "# SELECT count(id) as c, reachable, org_scheme, split_part(origin, '://', 1) as scheme from site GROUP by reachable, org_scheme, split_part(origin, '://', 1) ORDER by c\n",
    "display(sites[[\"reachable\"]].value_counts())\n",
    "display(sites[[\"reachable\", \"scheme\", \"org_scheme\"]].value_counts())\n",
    "display(sites[[\"reachable\", \"same-scheme\"]].value_counts())\n",
    "# How many hosts are only reachable via HTTP (or HTTPS)\n",
    "sites[\"host\"] = sites[\"origin\"].apply(lambda x: x.split(\"://\")[1])\n",
    "display(sites.loc[sites[\"reachable\"]][\"host\"].nunique()) # Unique reachable hosts\n",
    "display(sites.loc[~sites[\"reachable\"]][\"host\"].nunique()) # Unique reachable hosts\n",
    "\n",
    "def which_scheme(col):\n",
    "    l = col.to_list()\n",
    "    result = \"\"\n",
    "    if \"http\" in l:\n",
    "        result += \"http\"\n",
    "    if \"https\" in l:\n",
    "        result += \"https\"\n",
    "    return result\n",
    "display(sites.loc[sites[\"reachable\"] == True].groupby(\"host\")[\"scheme\"].apply(which_scheme).value_counts().to_frame())\n",
    "display(urls[\"description\"].value_counts())\n",
    "display(mon[[\"susp\"]].value_counts())\n",
    "display(mon[[\"susp\"]].value_counts().sum())\n",
    "display(urls.describe())\n",
    "display(mon[[\"susp\", \"b_error\", \"a_error\"]].value_counts().to_frame())\n",
    "display(sites[[\"reachable\", \"error\"]].value_counts().to_frame())\n",
    "\n",
    "# How many hostnames are in CrUX twice?\n",
    "from tld import get_fld\n",
    "df = pd.read_csv(\"202302.csv\")\n",
    "origins = df.loc[df[\"rank\"] == 1000].head(1000)\n",
    "origins = pd.concat([origins, df.loc[df[\"rank\"] == 5000].head(4000)])\n",
    "origins = pd.concat([origins, df.loc[df[\"rank\"] == 1000000].head(5000)])\n",
    "origins[\"host\"] = origins[\"origin\"].apply(lambda x: x.split(\"://\")[1])\n",
    "origins[\"site\"] = origins[\"origin\"].apply(get_fld)\n",
    "\n",
    "print(f\"Origins that occur more than once (i.e., both http and https)\", origins[\"host\"].value_counts().to_frame().value_counts())\n",
    "display(origins[\"host\"].value_counts().to_frame().head(13))\n",
    "print(f\"Sites that occur several times:\", origins[\"site\"].value_counts().to_frame().value_counts())\n",
    "display(origins[\"site\"].value_counts().to_frame())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# General stats on requests and responses (Caution: slow!)\n",
    "c = db.execute_sql(\"SELECT * from reqresp LIMIT 1\")\n",
    "columns = [col.name for col in c.description]\n",
    "columns = [\"id\", \"real_url\", \"probe_id\", \"error\", \"msg\", \"resp_code\", \"resp_version\"]\n",
    "overview_data = {}\n",
    "for col in columns:\n",
    "    c = db.execute_sql(f\"SELECT COUNT(DISTINCT {col}) from reqresp\")\n",
    "    overview_data[col] = [c.fetchone()[0]]\n",
    "\n",
    "overview_data = pd.DataFrame(overview_data)\n",
    "display(overview_data.T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All eror messages with count\n",
    "c = db.execute_sql(\"SELECT COUNT(id) as c, error, msg, req_type from reqresp GROUP by error, msg, req_type ORDER by c DESC\")\n",
    "res = c.fetchall()\n",
    "error_data = pd.DataFrame(res, columns=[\"count\", \"error\", \"message\", \"req_type\"])\n",
    "display(error_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "def fix_message(msg):\n",
    "    try:\n",
    "        msg =  msg.split(\": \", maxsplit=1)[1]\n",
    "        msg = re.sub(\"Unexpected data from server: b'(.*)'\", \"Unexpected data from server: b'<somedata>'\", msg)\n",
    "    except IndexError:\n",
    "        pass\n",
    "    return msg\n",
    "\n",
    "def fix_error(error):\n",
    "    error = re.sub(\"Expected \\d+ bytes, received \\d+\", \"Expected N bytes, received M\", error)\n",
    "    error = re.sub(\"bytearray\\(b'.*\", \"bytearray(b'<somebytes>')\", error)\n",
    "    error = re.sub(\"Connect call failed \\(.*\\)\", \"Connect call failed\", error)\n",
    "    error = re.sub(\"unexpected server response: b'.*'\", \"unexpected server response: b'<somebytes>'\", error)\n",
    "    error = re.sub(\"unexpected server response: b\\\".*\\\"\", \"unexpected server response: b'<somebytes>'\", error)\n",
    "    error = re.sub(\"received \\d+ bytes, expected \\d+\", \"received N bytes, expected M\", error)\n",
    "    error = re.sub(\"last_stream_id:\\d+\", \"last_stream_id:N\", error)\n",
    "    error = re.sub(\"additional_data:.*\", \"additional_data:N\", error)\n",
    "    return error\n",
    "\n",
    "error_data[\"message_new\"] = error_data[\"message\"].apply(fix_message)\n",
    "error_data[\"error_new\"] = error_data[\"error\"].apply(fix_error)\n",
    "\n",
    "with pd.option_context(\"display.max_colwidth\", None):\n",
    "    error_agg = error_data.groupby([\"req_type\", \"message_new\", \"error_new\"])[\"count\"].sum().to_frame().sort_values(\"count\", ascending=False).reset_index()\n",
    "    #display(error_agg)\n",
    "    display(error_agg.loc[error_agg[\"req_type\"] == \"proxy-probe\"])\n",
    "    display(error_agg.loc[error_agg[\"req_type\"] == \"proxy-probe-failed\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# HTTP versions\n",
    "c = db.execute_sql(\"SELECT COUNT(id) as c, req_version, resp_version, req_type from reqresp GROUP by req_version, resp_version, req_type ORDER by c DESC\")\n",
    "res = c.fetchall()\n",
    "version_data = pd.DataFrame(res, columns=[\"count\", \"req_version\", \"resp_version\", \"req_type\"])\n",
    "display(version_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Status codes\n",
    "c = db.execute_sql(\"SELECT COUNT(id) as c, resp_code from reqresp GROUP by resp_code ORDER by c DESC\")\n",
    "res = c.fetchall()\n",
    "status_data = pd.DataFrame(res, columns=[\"count\", \"resp_code\"])\n",
    "display(status_data)\n",
    "\n",
    "# Status codes (with methods)\n",
    "c = db.execute_sql(\"SELECT COUNT(id) as c, resp_code, req_method from reqresp GROUP by resp_code, req_method ORDER by c DESC\")\n",
    "res = c.fetchall()\n",
    "status_method_data = pd.DataFrame(res, columns=[\"count\", \"resp_code\", \"req_method\"])\n",
    "display(status_method_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Error analysis? \n",
    "# error_counts = common_errors(db) # Caution: slow\n",
    "error_counts = {}\n",
    "import json\n",
    "error_counts_json = {}\n",
    "for key, value in error_counts.items():\n",
    "    error_counts_json[key] = value.describe().to_json()\n",
    "with open(f\"output/error_counts_{db_name}.json\", \"w\") as f:\n",
    "    json.dump(error_counts_json, f, indent=4)\n",
    "for c, entry in enumerate(error_counts):\n",
    "    if c > 5:\n",
    "        break\n",
    "    display(f\"Error: {entry}\", error_counts[entry])\n",
    "# plot_errors(error_counts) # Caution: slow"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## High level violation stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_violations, url_info = get_violations(db)\n",
    "print(len(test_violations))\n",
    "display(url_info.head())\n",
    "display(test_violations.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DB name: paper name\n",
    "name_mapping = {\n",
    "    'STS_header_after_upgrade_insecure_requests': 'STS after UIR',\n",
    "    'redirect_after_upgrade_insecure_requests': 'Redirect after HTTP-UIR',\n",
    "    'code_405_allow': 'Allow header present for 405',\n",
    "    'head_get_headers': 'HEAD and GET same headers',\n",
    "    'accept_patch_presence': 'Accept-Patch if PATCH supported',\n",
    "    'cookie_IMF_fixdate': 'Cookies use IMF-fixdate',\n",
    "    'content_length_same_head_get': 'Content-Length for HEAD=GET',\n",
    "    'post_invalid_response_codes': 'Forbidden status-codes for POST',\n",
    "    'code_304_headers': 'Same headers for 304 and 200',\n",
    "    'date_header_required': 'Date header required',\n",
    "    'expires_grammar': 'Experies ABNF',\n",
    "    'sts_header_http': 'STS not allowed for HTTP',\n",
    "    'duplicate_cookies': 'Duplicate cookie names',\n",
    "    'duplicate_fields': 'Duplicate headers',\n",
    "    'content_type_header_required': 'Content-Type header required',\n",
    "    'xfo_grammar': 'XFO ABNF',\n",
    "    'code_206_headers': 'Mandatory headers for 206',\n",
    "    'etag_grammar': 'Etag ABNF',\n",
    "    'cookie_grammar': 'Set-Cookie ABNF',\n",
    "    'access_control_allow_origin_grammar': 'ACAO ABNF',\n",
    "    'only_one_sts_header_allowed': 'Duplicate STS',\n",
    "    'duplicate_csp': 'Duplicate CSP',\n",
    "    'accept_patch_grammar': 'Accept-Patch ABNF',\n",
    "    'code_401_www_authenticate': 'WWW-Authenticate required for 401',\n",
    "    'content_length_1XX_204': 'Forbidden Content-Length for 1XX and 204',\n",
    "    'last_modified_grammar': 'Last-Modified ABNF',\n",
    "    'field_value_start_or_end_with_whitespace': 'Forbidden surrounding whitespace for fields',\n",
    "    'content_type_grammar': 'Content-Type ABNF',\n",
    "    'code_304_no_content': 'Forbidden content for 304',\n",
    "    'server_grammar': 'Server ABNF',\n",
    "    'date_grammar': 'Date ABNF',\n",
    "    'access_control_allow_credentials_grammar': 'ACAC ABNF',\n",
    "    'vary_grammar': 'Vary ABNF',\n",
    "    'content_length_same_304_200': 'Content-Length for 304=200',\n",
    "    'csp_grammar': 'CSP ABNF',\n",
    "    'duplicate_cookie_attribute': 'Cookies with duplicate attributes',\n",
    "    'age_grammar': 'Age ABNF',\n",
    "    'cache_control_grammar': 'Cache-Control ABNF',\n",
    "    'content_language_grammar': 'Content-Language ABNF',\n",
    "    'access_control_allow_methods_grammar': 'ACAM ABNF',\n",
    "    'permissions_policy_grammar': 'PermissionsPolicy ABNF',\n",
    "    'access_control_allow_headers_grammar': 'ACAH ABNF',\n",
    "    'sts_grammar': 'STS ABNF',\n",
    "    'xcto_grammar': 'XCTO ABNF',\n",
    "    'code_416_content_range': 'Content-Range required for 416',\n",
    "    'code_302_location': 'Location required for 302',\n",
    "    'duplicate_csp_ro': 'Duplicate CSP-RO',\n",
    "    'coop_grammar': 'COOP ABNF',\n",
    "    'code_407_proxy_authenticate': 'Proxy-Authenticate required for 407',\n",
    "    'code_415_unsupported_media_type': 'Missing required headers for 415',\n",
    "    'server_header_long': 'Overly long Server header',\n",
    "    'location_header_grammar': 'Location ABNF',\n",
    "    'content_length_grammar': 'Content-Length ABNF',\n",
    "    'access_control_max_age_grammar': 'ACMA ABNF',\n",
    "    'corp_grammar': 'CORP ABNF',\n",
    "    'allow_grammar': 'Allow ABNF',\n",
    "    'connection_grammar': 'Connection ABNF',\n",
    "    'code_301_location': 'Location required for 301',\n",
    "    'code_303_location': 'Location required for 303',\n",
    "    'response_directive_no_cache': 'Forbidden token form in no-cache directive',\n",
    "    'transfer_encoding_http11': 'TE forbidden for non HTTP/1.1 responses',\n",
    "    'sts_directives_only_allowed_once': 'Duplicate directives for STS',\n",
    "    'code_206_content_range': 'Content-Range required for 206',\n",
    "    'send_upgrade_101': 'Upgrade required for 101',\n",
    "    'coep_grammar': 'COEP ABNF',\n",
    "    'no_transfer_encoding_1xx_204': 'TE forbidden for 1XX and 204',\n",
    "    'range_grammar': 'Range ABNF',\n",
    "    'code_307_location': 'Location required for 307',\n",
    "    'close_option_in_final_response': 'Close Option in Final Response required',\n",
    "    # Direct tests\n",
    "    'reject_fields_contaning_cr_lf_nul': 'Illegal chars',\n",
    "    'code_400_after_bad_host_request': 'Bad host',\n",
    "    'reject_msgs_with_whitespace_between_startline_and_first_header_field': 'Illegal whitespace after startline',\n",
    "    'code_400_if_msg_with_whitespace_between_header_field_and_colon': 'Illegal whitespace in header name',\n",
    "    'allow_crlf_start': 'Allow CRLF prior to request line',\n",
    "    'code_501_unknown_methods': 'Unknown methods should result in 501',\n",
    "    'code_405_blocked_methods': 'Blocked methods should result in 405',\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use hosts and not sites\n",
    "# Use host (i.e., origin without scheme); some tests are inherently only applicable to HTTP or HTTPS and we (try to) test every hostname in both HTTP and HTTPS\n",
    "test_violations = test_violations.merge(url_info, left_on=\"url_id\", right_on=\"id\")\n",
    "test_violations[\"test_name\"] = test_violations[\"test_type\"] + \"_\" + test_violations[\"type\"] + \"_\" + test_violations[\"name\"]\n",
    "test_violations[\"New name\"] = test_violations[\"name\"].apply(lambda x: name_mapping[x])\n",
    "test_violations = test_violations.replace({\"directtest\": \"Direct\", \"probetest\": \"Probe\", \"retrotest\": r\"\\Multi{}\", \"STS after UIR\": r\"STS after UIR\\rlap{*}\"})\n",
    "\n",
    "\n",
    "violations_per_site = test_violations.groupby(\"site\")[\"test_name\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "sites_per_violation = test_violations.groupby([\"test_type\", \"type\", \"New name\"])[\"site\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "violations_per_site[\"unique\"] = violations_per_site[\"unique\"].apply(lambda x: sorted(x))\n",
    "sites_per_violation[\"unique\"] = sites_per_violation[\"unique\"].apply(lambda x: sorted(x))\n",
    "\n",
    "# Description is origin in both local and popular db\n",
    "violations_per_origin = test_violations.groupby(\"description\")[\"test_name\"].agg([\"unique\", \"nunique\"]).sort_values([\"nunique\", \"description\"], ascending=False)\n",
    "origins_per_violation = test_violations.groupby([\"test_type\", \"type\", \"New name\"])[\"description\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "violations_per_origin[\"unique\"] = violations_per_origin[\"unique\"].apply(lambda x: sorted(x))\n",
    "origins_per_violation[\"unique\"] = origins_per_violation[\"unique\"].apply(lambda x: sorted(x))\n",
    "\n",
    "\n",
    "violations_per_host = test_violations.groupby(\"host\")[\"test_name\"].agg([\"unique\", \"nunique\"]).sort_values([\"nunique\", \"host\"], ascending=False)\n",
    "hosts_per_violation = test_violations.groupby([\"test_type\", \"type\", \"New name\"])[\"host\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "violations_per_host[\"unique\"] = violations_per_host[\"unique\"].apply(lambda x: sorted(x))\n",
    "hosts_per_violation[\"unique\"] = hosts_per_violation[\"unique\"].apply(lambda x: sorted(x))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Group of potential dangerous/security relevant tests\n",
    "hot_d = [\"Bad host\"]\n",
    "\n",
    "hrs_p_d = ['Illegal chars', 'Illegal whitespace after startline', 'Illegal whitespace in header name']\n",
    "hrs_p = ['Forbidden Content-Length for 1XX and 204', 'Forbidden surrounding whitespace for fields', 'Forbidden content for 304', 'Content-Length ABNF', 'TE forbidden for non HTTP/1.1 responses', 'TE forbidden for 1XX and 204', 'Upgrade required for 101']\n",
    "\n",
    "sts_mitm = ['STS after UIR', 'Redirect after HTTP-UIR']\n",
    "sts_other = ['STS not allowed for HTTP', 'STS ABNF', 'Duplicate directives for STS']\n",
    "\n",
    "# Broken ABNF or used in HTTP while only allowed in HTTPS or similar, broken cookies can lead to strange behavior, STS with duplicate directives is invalid (although not breaking the ABNF)\n",
    "restrictive = ['XFO ABNF', 'CSP ABNF', 'PermissionsPolicy ABNF', 'COOP ABNF', 'CORP ABNF', 'COEP ABNF', 'Duplicate CSP', 'Duplicate CSP-RO']\n",
    "cors_issues = ['ACAO ABNF', 'ACAC ABNF', 'ACAM ABNF', 'ACAH ABNF', 'ACMA ABNF']\n",
    "\n",
    "# Only some are security relevant (such as duplicate STS, XFO, ...)\n",
    "duplicate_fields = ['Duplicate headers']\n",
    "# STS only one header (remove from table as it is a subset for duplicate fields?)\n",
    "dup_subsets = ['Duplicate STS']\n",
    "\n",
    "content_sniffing = ['Content-Type header required', 'Content-Type ABNF', 'XCTO ABNF']\n",
    "cookies = ['Set-Cookie ABNF', 'Duplicate cookie names', 'Cookies use IMF-fixdate', 'Cookies with duplicate attributes']\n",
    "\n",
    "\n",
    "# 1. Could confuse parsers, 2. indicates methods bypass?, 3. Connectior close/reuse issues?, 4/5. Duplicate CSP discouraged by the spec but can be good for security (not a subset of duplicate headers as allowed by the ABNF)\n",
    "unclear = ['Content-Length for HEAD=GET', 'Forbidden status-codes for POST', 'Connection ABNF']\n",
    "# 1. Bad for web security crawling studies, \n",
    "other = ['HEAD and GET same headers']\n",
    "\n",
    "dangerous = hot_d + hrs_p_d + hrs_p + sts_mitm + sts_other + restrictive + cors_issues + duplicate_fields + content_sniffing + cookies # + unclear + other\n",
    "print(len(set(dangerous)))\n",
    "print(dangerous)\n",
    "dangerous = dangerous + [r\"STS after UIR\\rlap{*}\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def replace_with_circle(val):\n",
    "    if val == 1:\n",
    "        return r'$\\bullet$'  # Filled circle symbol in LaTeX\n",
    "    elif val == 0:\n",
    "        return r'$\\circ$'    # Unfilled circle symbol in LaTeX\n",
    "    else:\n",
    "        return val\n",
    "if \"local\" in db_name:\n",
    "    with pd.option_context(\"display.max_colwidth\", None):\n",
    "        display(sites_per_violation.head())\n",
    "        sites_per_violation.to_latex(f\"output/sites_per_violation_{db_name}.tex\")\n",
    "        origins_per_violation.to_latex(f\"output/origins_per_violation_{db_name}.tex\")\n",
    "        hosts_per_violation.to_latex(f\"output/hosts_per_violation_{db_name}.tex\")\n",
    "        paper_df = sites_per_violation.reset_index()[[\"type\", \"New name\", \"nunique\"]]\n",
    "        paper_df = paper_df.rename({\"New name\": \"Rule Name\", \"nunique\": r\"\\#Hosts\", \"type\": \"Specification Level\"}, axis=1)\n",
    "        local_df = paper_df\n",
    "        \n",
    "        # For local use a different table/matrix, rows are rules, columns are servers, cells are whether the server is affected or not?\n",
    "        test_violations[\"dummy\"] = test_violations[\"site\"] + test_violations[\"New name\"]\n",
    "\n",
    "        paper_df = test_violations.pivot_table(values=\"dummy\", index=[\"test_type\", \"type\", \"New name\"],columns=\"site\", aggfunc=\"nunique\", margins=True).fillna(0).astype(int)\n",
    "        paper_df = paper_df.assign(sortkey=paper_df.index == ('All', \"\", \"\"))\\\n",
    "                        .sort_values(['sortkey','All'], ascending=[True, False])\\\n",
    "                        .drop('sortkey', axis=1)\n",
    "        paper_df.columns.name = None\n",
    "        paper_df.index = paper_df.index.rename([\"Test Type\", \"Specification Level\", \"Rule Name\"])\n",
    "        paper_df.applymap(replace_with_circle).to_latex(f\"output/matrix_{db_name}.tex\", escape=False)\n",
    "        \n",
    "        # Dangerous only\n",
    "        paper_df = test_violations.loc[test_violations[\"New name\"].isin(dangerous)].pivot_table(values=\"dummy\", index=[\"test_type\", \"type\", \"New name\"],columns=\"site\", aggfunc=\"nunique\", margins=True).fillna(0).astype(int)\n",
    "        paper_df = paper_df.assign(sortkey=paper_df.index == ('All', \"\", \"\"))\\\n",
    "                        .sort_values(['sortkey','All'], ascending=[True, False])\\\n",
    "                        .drop('sortkey', axis=1)\n",
    "        paper_df.columns.name = None\n",
    "        paper_df.index = paper_df.index.rename([\"Test Type\", \"Specification Level\", \"Rule Name\"])\n",
    "        paper_df.applymap(replace_with_circle).to_latex(f\"output/matrix_dangerous_{db_name}.tex\", escape=False)\n",
    "\n",
    "        \n",
    "else:\n",
    "     with pd.option_context(\"display.max_rows\", None):\n",
    "        display(hosts_per_violation.head())\n",
    "        sites_per_violation.reset_index()[[\"type\", \"New name\", \"nunique\"]].to_latex(f\"output/sites_per_violation_{db_name}.tex\", index=False)\n",
    "        origins_per_violation.reset_index()[[\"type\", \"New name\", \"nunique\"]].to_latex(f\"output/origins_per_violation_{db_name}.tex\", index=False)\n",
    "        paper_df = hosts_per_violation.reset_index()[[\"type\", \"New name\", \"nunique\"]]\n",
    "        paper_df = paper_df.rename({\"New name\": \"Rule Name\", \"nunique\": r\"\\#Hosts\", \"type\": \"Specification Level\"}, axis=1)\n",
    "        wild_df = paper_df\n",
    "        display(paper_df)\n",
    "        paper_df.to_latex(f\"output/hosts_per_violation_{db_name}.tex\", index=False, escape=False)\n",
    "        paper_df.loc[paper_df[\"Rule Name\"].isin(dangerous)].to_latex(f\"output/hosts_per_violation_dangerous_{db_name}.tex\", index=False, escape=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_df(df, rule_names, group_name):\n",
    "    df = df.loc[df[\"Rule Name\"].isin(rule_names)]\n",
    "    df[\"Group\"] = group_name\n",
    "    return df.set_index([\"Group\", \"Rule Name\"])\n",
    "\n",
    "if not \"local\" in db_name:\n",
    "    gdf = wild_df\n",
    "    # HTTP(S) issues\n",
    "    sts_g = create_df(gdf, sts_mitm + [r\"STS after UIR\\rlap{*}\"], \"\")\n",
    "    # Security Related Headers\n",
    "    sts_h_g = create_df(gdf, sts_other, \"STS\")\n",
    "    duplicate_g = create_df(gdf, duplicate_fields, \"Duplicates\")\n",
    "    cs_g = create_df(gdf, content_sniffing, \"MIME\")\n",
    "    restrictive_g = create_df(gdf, restrictive, \"Restrictive\")\n",
    "    cookie_g = create_df(gdf, cookies, \"Cookies\")\n",
    "    cors_g = create_df(gdf, cors_issues, \"CORS\")\n",
    "    # HRS primitives\n",
    "    hrs = hot_d + hrs_p_d + hrs_p\n",
    "    hrs_g = create_df(gdf, hrs, \"\")\n",
    "    res_table = pd.concat([sts_g,cookie_g,sts_h_g,duplicate_g,cs_g,restrictive_g,cors_g,hrs_g]).drop(columns=\"Specification Level\")\n",
    "    display(res_table)\n",
    "    res_table.to_latex(f\"output/dangerous_rules.tex\", escape=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Number of sites with at least one violation\", test_violations[\"site\"].nunique())\n",
    "print(\"Number of rules with a least one violating site\", test_violations[\"test_name\"].nunique())\n",
    "\n",
    "print(\"Number of hosts with at least one violation\", test_violations[\"host\"].nunique())\n",
    "print(\"Number of rules with a least one violating host\", test_violations[\"test_name\"].nunique())\n",
    "\n",
    "\n",
    "show_hosts = True\n",
    "if show_hosts and not \"local\" in db_name:\n",
    "    display(hosts_per_violation.head())\n",
    "    display(hosts_per_violation.tail(5))\n",
    "    display(violations_per_host.head(5))\n",
    "    display(violations_per_host.tail(5))\n",
    "else:\n",
    "    display(sites_per_violation.head())\n",
    "    display(sites_per_violation.tail(5))\n",
    "    display(violations_per_site.head(5))\n",
    "    display(violations_per_site.tail(5))\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Popular vs unpopular"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Popular vs unpopular\n",
    "if not \"local\" in db_name:\n",
    "    display(test_violations[\"bucket\"].value_counts().to_frame())\n",
    "    def calc_stats(df):\n",
    "        df = df.copy()\n",
    "        df.loc[\"test_name\", :] = df[\"test_type\"] + \"_\" + df[\"type\"] + \"_\" + df[\"name\"]\n",
    "        vph = df.groupby(\"host\")[\"test_name\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "        hpv = df.groupby([\"test_type\", \"type\", \"name\"])[\"host\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "        vph[\"unique\"] = vph[\"unique\"].apply(lambda x: sorted(x))\n",
    "        print(\"Number of hosts with at least one violation\", df[\"host\"].nunique())\n",
    "        print(\"Number of violations with a least one violating host\", df[\"test_name\"].nunique())\n",
    "        display(\"Violations per host\", vph[\"nunique\"].agg([\"count\", \"mean\", \"max\"]))\n",
    "        display(\"Host per violation\", hpv[\"nunique\"].agg([\"count\", \"mean\", \"max\"]))\n",
    "        return hpv, vph\n",
    "\n",
    "    print(\"Popular hosts:\")\n",
    "    calc_stats(test_violations.loc[test_violations[\"bucket\"] <= 5000])\n",
    "    print(\"Tail hosts:\")\n",
    "    calc_stats(test_violations.loc[test_violations[\"bucket\"] > 5000])\n",
    "\n",
    "    print()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Redbot results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Redbot results\n",
    "red, url_info = get_violations(db, redbot=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "red = red.merge(url_info, left_on=\"url_id\", right_on=\"id\")\n",
    "def fix_subject(row):\n",
    "    if \"offset-\" in row[\"subject\"]:\n",
    "        return row[\"extra\"]\n",
    "    else: \n",
    "        return row[\"subject\"]\n",
    "red[\"subject\"] = red.apply(fix_subject, axis=1)\n",
    "red[\"test_name\"] = red[\"violation\"] + \"_\" + red[\"subject\"] + \"_\" + red[\"name\"]\n",
    "red[\"test_name\"] = red[\"subject\"] + \"_\" + red[\"name\"]\n",
    "\n",
    "if not \"local\" in db_name:\n",
    "    violations_per_hostr = red.loc[red[\"violation\"] == \"BAD\"].groupby(\"host\")[\"test_name\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "    host_per_violationr = red.loc[red[\"violation\"] == \"BAD\"].groupby([\"test_name\"])[\"host\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "else:\n",
    "    violations_per_hostr = red.loc[red[\"violation\"] == \"BAD\"].groupby(\"site\")[\"test_name\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "    host_per_violationr = red.loc[red[\"violation\"] == \"BAD\"].groupby([\"test_name\"])[\"site\"].agg([\"unique\", \"nunique\"]).sort_values(\"nunique\", ascending=False)\n",
    "violations_per_hostr[\"unique\"] = violations_per_hostr[\"unique\"].apply(lambda x: sorted(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Number of host with at least one BAD note\", red.loc[red[\"violation\"] == \"BAD\"][\"host\"].nunique())\n",
    "print(\"Number of sites with at least one BAD note\", red.loc[red[\"violation\"] == \"BAD\"][\"site\"].nunique())\n",
    "print(\"Number of BAD notes with a least one violating host\", red.loc[red[\"violation\"] == \"BAD\"][\"test_name\"].nunique())\n",
    "\n",
    "display(host_per_violationr.head())\n",
    "display(host_per_violationr.tail())  # Specifications only broken rarely: might be of particular interest for attacks/critical issues?\n",
    "display(violations_per_hostr.head())\n",
    "display(violations_per_hostr.tail())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "red[\"host\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unique Host/BAD Note RedBot \n",
    "# Strange things: Etag grammar, last modified grammar; broken more often by factor 10 in comparison to our results?\n",
    "# Mostly grammar and repeated headers\n",
    "# Several tests are deprecated and thus excluded from our work (e.g., warning header and similar)\n",
    "red_overview = host_per_violationr[\"nunique\"].to_frame()\n",
    "display(red_overview.head())\n",
    "red_overview.to_latex(f\"output/red_overview_{db_name}.tex\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Unique notes per host (the same note is only counted once per host!)\n",
    "if not \"local\" in db_name:\n",
    "    unique_note_count_host = red.drop_duplicates(subset=[\"test_name\", \"host\"]).groupby(\"host\")[\"violation\"].value_counts().to_frame().rename(columns={\"violation\": \"Unique Note Count/Host\"}).reset_index().groupby(\"violation\").describe()\n",
    "else:\n",
    "    unique_note_count_host = red.drop_duplicates(subset=[\"test_name\", \"site\"]).groupby(\"site\")[\"violation\"].value_counts().to_frame().rename(columns={\"violation\": \"Unique Note Count/Host\"}).reset_index().groupby(\"violation\").describe()\n",
    "\n",
    "display(unique_note_count_host.head())\n",
    "#unique_note_count_host = unique_note_count_site.style.format(precision=2, escape=\"Latex\")\n",
    "unique_note_count_host.to_latex(f\"output/redbot_{db_name}.tex\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analyze individual tests\n",
    "- More details about how a rule is broken"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def check(row, header_name):\n",
    "    headers = row[\"resp_headers\"]\n",
    "    headers = parse_headers(headers)\n",
    "    if header_name != \"all\":\n",
    "        headers = headers.get_all(header_name)\n",
    "    return headers\n",
    "    \n",
    "\n",
    "def display_info(test_name, header_name, limit=1000):\n",
    "    probe = False\n",
    "    try:\n",
    "        df = get_violation_details(db, test_name, limit=limit)\n",
    "        print(test_name, df.iloc[0][\"type\"], df[\"site_id\"].nunique())\n",
    "        probe = True\n",
    "    except IndexError:\n",
    "        try:\n",
    "            df = get_retro_details(db, test_name, limit=limit)\n",
    "            print(test_name, df.iloc[0][\"type\"], df[\"site_id\"].nunique())\n",
    "        except IndexError:\n",
    "            df = get_direct_details(db, test_name, limit=limit)\n",
    "            print(test_name, df.iloc[0][\"type\"], df[\"site_id\"].nunique())\n",
    "    with pd.option_context(\"display.max_colwidth\", None):\n",
    "        display(df[\"extra\"].value_counts().to_frame().head(10))\n",
    "        display(df[[\"full_url\"]].value_counts().head(10).to_frame())\n",
    "        #display(df[[\"resp_body\"]].head())\n",
    "        if probe:\n",
    "            if header_name:\n",
    "                display(df[[\"extra\", \"req_method\", \"resp_code\", \"resp_version\", \"req_headers\", \"resp_headers\"]].apply(check, header_name=header_name, axis=1).value_counts().to_frame().head(20))\n",
    "            display(df[[\"req_version\", \"resp_version\"]].value_counts())\n",
    "            display(df[[\"resp_code\"]].value_counts())\n",
    "            display(df[[\"req_method\"]].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Manually analyze single tests\n",
    "names = [('range_grammar', \"range\"),\n",
    " ('code_301_location', \"location\"),\n",
    " ('response_directive_no_cache', \"cache-control\"),\n",
    " ('code_206_content_range', \"content-range\"),\n",
    " ('no_transfer_encoding_1xx_204', \"transfer-encoding\"),\n",
    " ('send_upgrade_101', \"upgrade\"),\n",
    " ('sts_directives_only_allowed_once', \"strict-transport-security\"),\n",
    " ('transfer_encoding_http11', \"transfer-encoding\")]\n",
    "names = [(\"accept_patch_grammar\", \"accept-patch\")]\n",
    "for name, header_name in names:\n",
    "    display_info(name, header_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Popular tests to analyze (affect more than 1000 hosts)\n",
    "names = [\n",
    "    #(\"STS_header_after_upgrade_insecure_requests\", \"strict-transport-security\"), # Reco: No STS header after UIR in HTTPS response; see below for how many hosts send STS at least once (2386)\n",
    "    #(\"redirect_after_upgrade_insecure_requests\", \"location\"), # Reco: Mostly no redirect (code 405, 403, ...), some redirects to HTTP; (252 hosts only reachable via HTTP, 152 only via HTTPS)\n",
    "    #(\"code_405_allow\", \"allow\", 10000),  # Req: Mostly no allow header at all; some empty allow header (although GET is allowed)\n",
    "    #(\"head_get_headers\", None, 10000),  # Reco: Different status codes (e.g., 206-200; 302-403); headers only in one (age, csp, referer-policy, content-type, set-cookie)\n",
    "    #(\"accept_patch_presence\", None, 1000), # Reco: Code 200 for PATCH, but no accept-patch header for OPTIONS\n",
    "    #(\"cookie_IMF_fixdate\", \"set-cookie\", 10000), # Reco: IMF fixdate; other dates used, e.g., with dashes (google!);\n",
    "    #(\"content_length_same_head_get\", None, 1000), # Req: if content-length set, has to be the same as get; only head has content-length (get has TE?); different CLs\n",
    "    (\"post_invalid_response_codes\", None, 1000), #Req: 206, 304, 416 not allowed in responses to POST; Mostly 206; probably header parsing of range has precedence, 304 if if-none-match, 416 if range in request; (order of header and method in processing code differs/method is ignored for certain headers?)\n",
    "    #(\"code_304_headers\", None, 1000), # Headers are missing for status code 304 (cc, cl, expires, vary, etag or different subsets)\n",
    "    ]\n",
    "for name, header_name, limit in names:\n",
    "    display_info(name, header_name, limit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grammar and similar tests to analyze\n",
    "names = [\n",
    "    # Cookie wrong date\n",
    "    #(\"expires_grammar\", \"expires\", 10000), # ABNF Should be a date but is 0, or -1, some incorrect dates (1 digit day)\n",
    "    #(\"xfo_grammar\", \"x-frame-options\", 1000), # ABNF incorrect syntax multiple values, deprecated syntax allow-from, allow-all, rare CSP syntax: e.g, None, *, ... (other headers)\n",
    "    #(\"etag_grammar\", \"etag\", 1000), # ABNF, missing quotes; mostly * with spaces or empty string\n",
    "    #(\"cookie_grammar\", \"set-cookie\", 1000), # ABNF, no/invalid name or missing value\n",
    "    #(\"access_control_allow_origin_grammar\", \"access-control-allow-origin\", 1000), # ABNF empty, several headers, or `undefined`, or URL with / instead of origin\n",
    "    #(\"accept_patch_grammar\", \"accept-patch\", 1000), # ABNF empty (not allowed)\n",
    "    #(\"last_modified_grammar\", \"last-modified\", 1000), # ABNF wrong date format various\n",
    "    (\"content_type_grammar\", \"content-type\", 1000), # ABNF Separation of parameters\n",
    "    #(\"server_grammar\", \"server\", 1000), # ABNF Separation of products (, or + instead of space)\n",
    "    #(\"date_grammar\", \"date\", 1000), # ABNF Incorrect dates, no GMT, missing colon, ... (typos? in manual formatting -> add IMF-date as default output to many libraries?!)\n",
    "    #(\"access_control_allow_credentials_grammar\", \"access-control-allow-credentials\", 1000), # ABNF mostly false (problem sometimes one has to omit a header entirely, sometimes there is a \"no\" option)\n",
    "    #(\"vary_grammar\", \"vary\", 1000), # ABNF incorrect separation (space or ; instead of ,) or empty list element\n",
    "    #(\"csp_grammar\", \"content-security-policy\", 1000), # ABNF invalid separation (,: and similar in wrong places), incorrect/no directive, incorrect spaces, everything in quotes\n",
    "    #(\"age_grammar\", \"age\", 1000), # ABNF list (parsing allows but against the spec); negative numbers, null\n",
    "    #(\"cache_control_grammar\", \"cache-control\", 1000), # ABNF mostly incorrect separation\n",
    "    #(\"content_language_grammar\", \"content-language\", 1000), # ABNF mostly incomplete language \"es-\"\n",
    "    #(\"access_control_allow_methods_grammar\", \"access-control-allow-methods\", 1000), # ABNF empty, wrong separation\n",
    "    #(\"permissions_policy_grammar\", \"permissions-policy\", 1000), # ABNF feature policy/iframe syntax, wrong separation (; instead of , CSP?), STS header\n",
    "    #(\"access_control_allow_headers_grammar\", \"access-control-allow-headers\", 1000), # ABNF empty\n",
    "    #(\"sts_grammar\", \"strict-transport-security\", 1000), # ABNF wrong separation, wrong quotes\n",
    "    #(\"xcto_grammar\", \"x-content-type-options\", 1000), # ABNF wrong quotes, ALLOW value\n",
    "    #(\"coop_grammar\", \"cross-origin-opener-policy\", 1000), # ABNF  cross-origin instead of unsafe-none (might be a better name?)\n",
    "    #(\"location_header_grammar\", \"location\", 1000), # ABNF Invalid (relative) hosts, template string and utf-8?\n",
    "    #(\"content_length_grammar\", \"content-length\", 1000), # ABNF list\n",
    "    #(\"access_control_max_age_grammar\", \"access-control-max-age\", 1000), # ABNF * or list\n",
    "    #(\"corp_grammar\", \"cross-origin-resource-policy\", 1000), # ABNF rollout, all three values together\n",
    "    #(\"allow_grammar\", \"allow\", 1000), # ABNF incorrect separation (Space instead of ,)\n",
    "    #(\"connection_grammar\", \"connection\", 1000), # ABNF Close &lt;br&gt;Content-Type:text/html (encoding issues + header not in newline but inserted; happened for others as well)\n",
    "    #(\"coep_grammar\", \"cross-origin-embedder-policy\", 1000), # ABNF two values\n",
    "    #(\"range_grammar\", \"range\", 1000), # ABNF empty\n",
    "\n",
    "]\n",
    "for name, header_name, limit in names:\n",
    "    display_info(name, header_name, limit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# HRS related tests to analyze\n",
    "names = [\n",
    "    # Wild\n",
    "    #(\"field_value_start_or_end_with_whitespace\", None, 1000), # Requirement Whitespace at end of header (if not stripped might lead to issues)\n",
    "    (\"code_304_no_content\", None, 1000), # Requirement Content with 304 (http2), if parsing stops after the code, HRS might occur!\n",
    "    #(\"transfer_encoding_http11\", \"transfer-encoding\", 1000), # Requirement TE header in H2 can lead to issues with conversion\n",
    "    #(\"send_upgrade_101\", \"all\", 1000), # Requirement 101 without upgrade might confuse \n",
    "    #(\"no_transfer_encoding_1xx_204\", \"transfer-encoding\", 1000), # Requirement TE with status code 100, might lead to issues\n",
    "    # Local \n",
    "    #(\"code_400_after_bad_host_request\", None, 1000), # D-Requirement HostOfTroubles attack?\n",
    "    #(\"reject_fields_containing_cr_lf_nul\", None, 1000), # D-Requirement Many parsing differences could occur!\n",
    "    #(\"code_400_if_msg_with_whitespace_between_header_field_and_colon\", None, 1000), # D-Requirement Parsing issues might occur\n",
    "    #(\"reject_msgs_with_whitespace_between_startline_and_first_header_field\", None, 1000), # D-Requirement Parsing issuse might occur\n",
    "]\n",
    "for name, header_name, limit in names:\n",
    "    display_info(name, header_name, limit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fixed analysis for HSTS test\n",
    "\n",
    "# only_one_sts_header_allowed\n",
    "# If more than one; check whether the same occurs multiple times or different ones appear!\n",
    "def check_hsts(row):\n",
    "    headers = parse_headers(row[\"resp_headers\"])\n",
    "    hsts = headers.get_all(\"strict-transport-security\")\n",
    "    len_hsts = len(hsts)\n",
    "    hsts = list(dict.fromkeys(hsts))\n",
    "    if len(hsts) == 1:\n",
    "        return f\"{len_hsts}: Same\"\n",
    "    else:\n",
    "        return f\"{len_hsts}: Diff: {hsts}\"\n",
    "df = get_violation_details(db, \"only_one_sts_header_allowed\", limit=1000)  \n",
    "display(df[[\"extra\", \"req_method\", \"resp_code\", \"resp_version\", \"resp_headers\"]].apply(check_hsts, axis=1).value_counts())\n",
    "\n",
    "\n",
    "# STS_header_after_upgrade_insecure_requests\n",
    "# Check if the site ever served an STS header!\n",
    "q = \"(select url_id from (select distinct url_id from probetest where name = 'STS_header_after_upgrade_insecure_requests' and violation = 'Breaks specification') as probe JOIN url ON probe.url_id = url.id)\"\n",
    "c = db.execute_sql(q)\n",
    "urls = pd.DataFrame(c.fetchall(), columns=[\"url_id\"])\n",
    "sts_df = pd.DataFrame()\n",
    "for url in urls[\"url_id\"].to_list():\n",
    "    q = \"select * from reqresp where resp_headers LIKE '%%(b''strict-transport-security'', b''%%' and url_id = %s\"\n",
    "    c = db.execute_sql(q, (url,))\n",
    "    sts = pd.DataFrame(c.fetchall())\n",
    "    if len(sts):\n",
    "        sts_df = pd.concat([sts_df, sts])\n",
    "violating = sts_df.drop_duplicates(subset=[2])\n",
    "if len(violating):\n",
    "    violating_urls = violating[2].to_list()\n",
    "    with pd.option_context(\"display.max_colwidth\", None):\n",
    "        display(violating[[2, 16]].head(2))\n",
    "else:\n",
    "    violating_urls = []\n",
    "s = pd.DataFrame(Site.select().dicts())\n",
    "u = pd.DataFrame(Url.select().dicts())\n",
    "d = u.merge(urls, left_on=\"id\", right_on=\"url_id\")\n",
    "d = s.merge(d, left_on=\"id\", right_on=\"site\")  \n",
    "print(f'Sites that do not serve STS for uir: {d[\"site_x\"].nunique()}, out of these ones that serve a STS header at least once: {d.loc[d[\"url_id\"].isin(violating_urls)][\"site_x\"].nunique()}') \n",
    "print(f'Hosts that do not serve STS for uir: {d[\"host\"].nunique()}, out of these ones that serve a STS header at least once: {d.loc[d[\"url_id\"].isin(violating_urls)][\"host\"].nunique()}') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f'Hosts that do not serve STS for uir: {d[\"host\"].nunique()}, out of these ones that serve a STS header at least once: {d.loc[d[\"url_id\"].isin(violating_urls)][\"host\"].nunique()}') "
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python",
   "pygments_lexer": "ipython3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}