notebooks/pypi_spam_url_blacklist_matching.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PyPi spam identification by url blacklist matching\n",
    "\n",
    "*19 Feb 2018*\n",
    "\n",
    "Following the [PyPi spam incident in February 2018](https://status.python.org/incidents/mgjw1g5yjy5j), this notebook aims to detect spam in the uploaded Python packages by matching the links contained in the description against blacklisted domain names.\n",
    "\n",
    "The PyPi metadata (`pypi-metadata-2018-02-19.parq`) for ~120000 Python packages  used in this analysis can be found in the project [README.md](https://github.com/rth/pypi-stats-viz).\n",
    "\n",
    "The hpHost list of blacklisted domain names can be downloaded from https://hosts-file.net/?s=Download. For this analysis, the `hosts.txt` file should be extracted from `hosts.zip`. See https://hosts-file.net/?s=classifications for more details about the domain classification.\n",
    "\n",
    "This notebook requires Python 3.5+. The dependencies can be installed with,\n",
    "```\n",
    "pip install numpy pandas dask toolz cloudpickle pyarrow urlextract\n",
    "```\n",
    "\n",
    "TLDR; this approach flags ~173 Python packages, most being false positive. So either PyPi currently contains almost no spam or other approaches should be tried instead. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from urllib.parse import urlparse\n",
    "from pathlib import Path\n",
    "\n",
    "import dask.dataframe as dd\n",
    "from dask.diagnostics import ProgressBar\n",
    "from urlextract import URLExtract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We start by loading the PyPi metadata and for each package extracting urls from the `description`, `home_page` and `summary` fields,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = dd.read_parquet(str(Path('..')/'data'/'pypi-metadata-2018-02-19.parq'), engine='pyarrow')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[                                        ] | 0% Completed |  0.1s"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/rth/.miniconda3/envs/pyM-index-env/lib/python3.6/site-packages/dask/dataframe/core.py:2067: UserWarning: `meta` is not specified, inferred from partial data. Please provide `meta` if the result is unexpected.\n",
      "  Before: .apply(func)\n",
      "  After:  .apply(func, meta={'x': 'f8', 'y': 'f8'}) for dataframe result\n",
      "  or:     .apply(func, meta=('x', 'f8'))            for series result\n",
      "  warnings.warn(msg)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[########################################] | 100% Completed | 58.6s\n"
     ]
    }
   ],
   "source": [
    "url_extractor = URLExtract()\n",
    "\n",
    "with ProgressBar():\n",
    "    df['text_all'] = (df.description + ' ' + df.home_page + ' ' + df.summary).astype('str')\n",
    "    df['extracted_urls'] = df.text_all.apply(url_extractor.find_urls)\n",
    "    df_s = df[['name', 'extracted_urls']].compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of processed PyPi packages:  128712\n"
     ]
    }
   ],
   "source": [
    "print('Number of processed PyPi packages: ', df_s.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>extracted_urls</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5602</th>\n",
       "      <td>dspam-milter</td>\n",
       "      <td>[https://travis-ci.org/whyscream/dspam-milter....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6336</th>\n",
       "      <td>easyshop.core</td>\n",
       "      <td>[(http://www.demmelhuber.net/shop), http://www...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2900</th>\n",
       "      <td>gpssim</td>\n",
       "      <td>[https://bitbucket.org/wjiang/gpssim]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10274</th>\n",
       "      <td>nester-edvan</td>\n",
       "      <td>[http://www.headfirstlabs.com]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7289</th>\n",
       "      <td>imaprelay</td>\n",
       "      <td>[hostname=imap.exchange.megacorp.com, hostname...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6586</th>\n",
       "      <td>vice.plone.outbound</td>\n",
       "      <td>[http://dev.plone.org/collective/browser/vice....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947</th>\n",
       "      <td>DBSync</td>\n",
       "      <td>[http://github.com/zhoubangtao/dbsync]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5476</th>\n",
       "      <td>LabelLib</td>\n",
       "      <td>[https://github.com/Fluorescence-Tools/LabelLib]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2239</th>\n",
       "      <td>pyeasy</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2816</th>\n",
       "      <td>loop_lista2201</td>\n",
       "      <td>[http://www.headfirstlabs.com]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name                                     extracted_urls\n",
       "5602          dspam-milter  [https://travis-ci.org/whyscream/dspam-milter....\n",
       "6336         easyshop.core  [(http://www.demmelhuber.net/shop), http://www...\n",
       "2900                gpssim              [https://bitbucket.org/wjiang/gpssim]\n",
       "10274         nester-edvan                     [http://www.headfirstlabs.com]\n",
       "7289             imaprelay  [hostname=imap.exchange.megacorp.com, hostname...\n",
       "6586   vice.plone.outbound  [http://dev.plone.org/collective/browser/vice....\n",
       "1947                DBSync             [http://github.com/zhoubangtao/dbsync]\n",
       "5476              LabelLib   [https://github.com/Fluorescence-Tools/LabelLib]\n",
       "2239                pyeasy                                                 []\n",
       "2816        loop_lista2201                     [http://www.headfirstlabs.com]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_s.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will flatten this nested DataFrame, to get a single package and URL pair per row,\n",
    "\n",
    "*Note*: it doesn't look like there is a simple vectorized way to do this in pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = []\n",
    "for idx, row in df_s.iterrows():\n",
    "    if not hasattr(row['extracted_urls'], '__len__'):\n",
    "        continue\n",
    "    for url in row['extracted_urls']:\n",
    "        res.append({'name': row['name'], 'url': url})\n",
    "        \n",
    "df_u = pd.DataFrame(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>547848</th>\n",
       "      <td>specio</td>\n",
       "      <td>https://travis-ci.org/paris-saclay-cds/specio....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335586</th>\n",
       "      <td>moses</td>\n",
       "      <td>https://github.com/l04m33/moses.git</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154246</th>\n",
       "      <td>django-cached-modelforms</td>\n",
       "      <td>models.py</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>407768</th>\n",
       "      <td>pantsbuild.pants.contrib.confluence</td>\n",
       "      <td>https://rbcommons.com/s/twitter/r/860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218735</th>\n",
       "      <td>effective-distance</td>\n",
       "      <td>https://github.com/benmaier/effective-distance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259259</th>\n",
       "      <td>google-cloud-trace</td>\n",
       "      <td>https://cloud.google.com/trace</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>453658</th>\n",
       "      <td>py_sonicvisualiser</td>\n",
       "      <td>http://www.sonicvisualiser.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260156</th>\n",
       "      <td>goprohero</td>\n",
       "      <td>https://github.com/joshvillbrandt/GoProController</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440255</th>\n",
       "      <td>postmen</td>\n",
       "      <td>https://github.com/postmen/postmen-sdk-python/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489967</th>\n",
       "      <td>python-lirc</td>\n",
       "      <td>[Debian](http://bugs.debian.org/cgi-bin/bugrep...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       name  \\\n",
       "547848                               specio   \n",
       "335586                                moses   \n",
       "154246             django-cached-modelforms   \n",
       "407768  pantsbuild.pants.contrib.confluence   \n",
       "218735                   effective-distance   \n",
       "259259                   google-cloud-trace   \n",
       "453658                   py_sonicvisualiser   \n",
       "260156                            goprohero   \n",
       "440255                              postmen   \n",
       "489967                          python-lirc   \n",
       "\n",
       "                                                      url  \n",
       "547848  https://travis-ci.org/paris-saclay-cds/specio....  \n",
       "335586                https://github.com/l04m33/moses.git  \n",
       "154246                                          models.py  \n",
       "407768              https://rbcommons.com/s/twitter/r/860  \n",
       "218735     https://github.com/benmaier/effective-distance  \n",
       "259259                     https://cloud.google.com/trace  \n",
       "453658                     http://www.sonicvisualiser.org  \n",
       "260156  https://github.com/joshvillbrandt/GoProController  \n",
       "440255  https://github.com/postmen/postmen-sdk-python/...  \n",
       "489967  [Debian](http://bugs.debian.org/cgi-bin/bugrep...  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_u.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can then exactract domain name from each URL,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def url_parse_netloc(url):\n",
    "    try:\n",
    "        return urlparse(url).netloc\n",
    "    except:\n",
    "        return float('nan')\n",
    "\n",
    "df_u['url_netloc'] = df_u.url.apply(url_parse_netloc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For instance the most popular domains linked to in Python packages on PyPi are as follows,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "url_netloc\n",
       "github.com                   162530\n",
       "                             117585\n",
       "pypi.python.org               31886\n",
       "img.shields.io                28411\n",
       "travis-ci.org                 27060\n",
       "rbcommons.com                 12628\n",
       "odoo-community.org            11313\n",
       "coveralls.io                  10106\n",
       "bitbucket.org                  7602\n",
       "badge.fury.io                  5726\n",
       "code.google.com                4722\n",
       "readthedocs.org                4494\n",
       "codecov.io                     4032\n",
       "www.gnu.org                    3584\n",
       "en.wikipedia.org               2888\n",
       "pypip.in                       2810\n",
       "raw.githubusercontent.com      2635\n",
       "runbot.odoo-community.org      2229\n",
       "docs.python.org                2096\n",
       "landscape.io                   2015\n",
       "Name: name, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_u.groupby('url_netloc').name.count().sort_values(ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we load hpHosts host file,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Domains in the blacklist:  724042\n"
     ]
    }
   ],
   "source": [
    "blacklist = pd.read_csv('hosts.txt', skiprows=27, sep='\\t', encoding='latin1', comment='#',\n",
    "                        names=['localhost', 'domain'])\n",
    "print('Domains in the blacklist: ', blacklist.shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and merge find the linked urls that belong to blacklisted domains,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "packages_flagged = (pd.merge(blacklist[['domain']], df_u,  how='inner', left_on='domain', right_on='url_netloc')\n",
    "                      .drop_duplicates())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Flagged 173 PyPI packages\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>domain</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>inlinestyler</td>\n",
       "      <td>www.campaignmonitor.com</td>\n",
       "      <td>http://www.campaignmonitor.com/css/</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>github-release-notifier</td>\n",
       "      <td>acme.com</td>\n",
       "      <td>https://acme.com/updated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>elektrika.openx</td>\n",
       "      <td>openx.org</td>\n",
       "      <td>http://openx.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>ns1cli</td>\n",
       "      <td>ns1.com</td>\n",
       "      <td>https://ns1.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>mailsnake</td>\n",
       "      <td>www.mailchimp.com</td>\n",
       "      <td>http://www.mailchimp.com/api/1.3/</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        name                   domain  \\\n",
       "103             inlinestyler  www.campaignmonitor.com   \n",
       "94   github-release-notifier                 acme.com   \n",
       "82           elektrika.openx                openx.org   \n",
       "147                   ns1cli                  ns1.com   \n",
       "122                mailsnake        www.mailchimp.com   \n",
       "\n",
       "                                     url  \n",
       "103  http://www.campaignmonitor.com/css/  \n",
       "94              https://acme.com/updated  \n",
       "82                      http://openx.org  \n",
       "147                      https://ns1.com  \n",
       "122    http://www.mailchimp.com/api/1.3/  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bad = packages_flagged.groupby(['name', 'domain']).url.apply(' , '.join).reset_index()\n",
    "\n",
    "# some of the domains in the blacklist are OK in the context of PyPi\n",
    "whitelist = ['mysite.com', 'addthis.com', 'www.addthis.com', 'www.academia.edu', 'www.google-analytics.com']\n",
    "bad = bad[~bad.domain.isin(whitelist)]\n",
    "print('Flagged %s PyPI packages' % bad.name.nunique())\n",
    "bad.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad.to_csv('bad.csv', sep='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The full results are available in https://pastebin.com/DNP4D4uj,  most of the flagged packages, while sometimes dubious, are mostly false positives. So either PyPi currently contains almost no spam or other approaches should be tried instead. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}