Query: security_headers_missing_headers

with available_headers as (
  select
    array_agg(header.key)
  from
    net_http_request,
    jsonb_each(response_headers) as header
  where
    url = $1
),
missing_headers as (
  select
    element
  from
    (
      select
        unnest(
          array [ 'Strict-Transport-Security',
          'Content-Security-Policy',
          'X-Frame-Options',
          'X-Content-Type-Options',
          'Referrer-Policy',
          'Permissions-Policy' ]
        )
      except
      select
        unnest(array_agg)
      from
        available_headers
    ) t (element)
)
select
  element as "Header",
  case
    when element = 'X-Content-Type-Options' then 'X-Content-Type-Options header with the ''nosniff'' value helps protect against mime type sniffing. Mime type sniffing attacks are only effective in specific scenarios where they cause the browser to interpret text or binary content as HTML. For example, if a user uploads an avatar file named xss.html and the web application does not set a Content-type header when serving the image, the browser will try to determine the content type and will likely treat xss.html as an HTML file. The attacker can then direct users to xss.html and conduct a Cross-Site Scripting attack.'
    when element = 'Strict-Transport-Security' then 'The HTTP Strict-Transport-Security (HSTS) response header helps to strengthens your TLS implementation by informing the browser that the site should only be accessed using HTTPS, nd any further attempts to access the site using HTTP should automatically redirect to HTTPS. Recommended value "Strict-Transport-Security: max-age=31536000; includeSubDomains".'
    when element = 'X-Frame-Options' then 'X-Frame-Options header helps to prevent Clickjacking attacks. The Deep Security Manager enforces the SAMEORIGIN value for this header, only allowing it to be embedded in web applications that are hosted on the same domain. Recommended value "X-Frame-Options: SAMEORIGIN".'
    when element = 'Permissions-Policy' then 'The Permissions Policy Header is an added layer of security that helps to restrict from unauthorized access or usage of browser/client features by web resources. This policy ensures the user privacy by limiting or specifying the features of the browsers can be used by the web resources. Permissions Policy provides a set of standard HTTP headers that allow website owners to limit which features of browsers can be used by the page such as camera, microphone, location, full screen etc.'
    when element = 'Content-Security-Policy' then 'Content Security Policy is an effective measure to protect your site from XSS attacks. By whitelisting sources of approved content, you can prevent the browser from loading malicious assets.'
    when element = 'Referrer-Policy' then 'The Referrer Policy HTTP header sets the parameter for amount of information sent along with Referrer Header while making a request. Referrer policy is used to maintain the security and privacy of source account while fetching resources or performing navigation. This is done by modifying the algorithm used to populate Referrer Header.'
  end as "Description"
from
  missing_headers;