Exploit SQL Database Workflow with Pandas and iPython

Author:  Vitali Kremez

  • This iPython notebook creates and explores a SQL database of all known public exploits.

Source:

  • ​pandas: A library with data structures and data analysis tools. 
  • IPython notebook: An interface for writing and sharing Python code, text, and plots.
  • SQLite: An self-contained, server-less database that’s easy to set-up and query from Pandas.
  • Plotly: A platform for publishing interactive graphs from Python to the web.



PHP has the largest number of exploits in the SQL database.
Function:
(1) Creates and queries the SQL database with approx. 40,000 rows and 9 columns of public exploits;

(2) Creates a graph “Number of remote exploits by platform”;
[1] Windows OS has 3,340 remote exploits, the largest number of remote exploits in the SQL database.
[2] Linux OS has 825 remote exploits, the second largest number in the database

(3) Creates a graph “Number of webapps exploits by platform”;
[1] We have approx. 17,335 of PHP webapp exploits, the largest number of webapp exploits in the SQL database.
[2] We have only 1,498 asp webapp exploits, the second largest number in the database.

(4) Creates a graph “Number of local exploits by platform”;
[1] We have 1,561 of local Windows exploits, the largest number of local exploits in the SQL database.
[2] We have only 819 Linux local exploits, the second largest number in the database.

(5) Creates a graph “Number of Denial- of-Service (DOS) exploits by platform”;
[1] We have 2,845 of DOS Windows exploits, the largest number of DOS exploits in the SQL database.
[2] We have only 603 Linux DOS exploits, the second largest number in the database.

(6) Creates a graph “Number of exploits by platform”;
[1] We have approx. 17,590 of PHP exploits, the largest number of exploits in the SQL database.
[2] We have only 603 Linux exploits, the second largest number in the database.

(7) Queries the SQL database for Windows exploits;
We have 7,921 Windows exploits in the database.

(8) Creates a graph “Number of exploits by date”;
We have the largest number of exploits developed in 2010 among other years.

Windows has the largest number of DOS exploits among other platforms.

Picture

In [39]:
import pandas as pd
from sqlalchemy import create_engine # database connection
import datetime as dt
from IPython.display import display
In [40]:
import plotly.plotly as py # interactive graphing
from plotly.graph_objs import Bar, Scatter, Marker, Layout
In [13]:
disk_engine = create_engine('sqlite:///ExploitDB.db')
In [14]:
start = dt.datetime.now()
chunksize = 40000
j = 0
index_start = 1
In [15]:
for df in pd.read_csv('https://raw.githubusercontent.com/offensive-security/exploit-database/master/files.csv', chunksize=chunksize, iterator=True, encoding='utf-8'):
    
    df.index += index_start 

    j+=1
    print '{} seconds: completed {} rows'.format((dt.datetime.now() - start).seconds, j*chunksize)

    df.to_sql('data', disk_engine, if_exists='append')
    index_start = df.index[-1] + 1
4 seconds: completed 40000 rows
In [42]:
df = pd.read_sql_query('SELECT * FROM data', disk_engine)
print df
       index     id                                  file  \
0          1      1          platforms/windows/remote/1.c   
1          2      2          platforms/windows/remote/2.c   
2          3      3             platforms/linux/local/3.c   
3          4      4           platforms/solaris/local/4.c   
4          5      5          platforms/windows/remote/5.c   
5          6      6           platforms/php/webapps/6.php   
6          7      7           platforms/linux/remote/7.pl   
7          8      8            platforms/linux/remote/8.c   
8          9      9             platforms/windows/dos/9.c   
9         10     10           platforms/linux/remote/10.c   
10        11  37060      platforms/windows/dos/37060.html   
11        12     11              platforms/linux/dos/11.c   
12        13     12            platforms/linux/local/12.c   
13        14     13            platforms/windows/dos/13.c   
14        15     15              platforms/osx/local/15.c   
15        16     16           platforms/linux/remote/16.c   
16        17     17           platforms/windows/dos/17.pl   
17        18     18          platforms/linux/remote/18.sh   
18        19     19           platforms/linux/remote/19.c   
19        20     20       platforms/windows/remote/20.txt   
20        21     21            platforms/linux/local/21.c   
21        22     22            platforms/windows/dos/22.c   
22        23     23         platforms/windows/remote/23.c   
23        24     24           platforms/linux/remote/24.c   
24        25     25           platforms/linux/remote/25.c   
25        26     26          platforms/linux/remote/26.sh   
26        27     27          platforms/linux/remote/27.pl   
27        28     28         platforms/windows/remote/28.c   
28        29     29              platforms/bsd/local/29.c   
29        30     30        platforms/windows/remote/30.pl   
...      ...    ...                                   ...   
35465  35466  39213       platforms/php/webapps/39213.txt   
35466  35467  39214         platforms/linux/local/39214.c   
35467  35468  39215     platforms/windows/remote/39215.py   
35468  35469  39216        platforms/windows/dos/39216.py   
35469  35470  39217         platforms/linux/local/39217.c   
35470  35471  39218   platforms/windows/remote/39218.html   
35471  35472  39219      platforms/multiple/dos/39219.txt   
35472  35473  39220       platforms/windows/dos/39220.txt   
35473  35474  39221         platforms/win64/dos/39221.txt   
35474  35475  39222   platforms/multiple/remote/39222.txt   
35475  35476  39223       platforms/php/webapps/39223.txt   
35476  35477  39224    platforms/hardware/remote/39224.py   
35477  35478  39229         platforms/linux/dos/39229.cpp   
35478  35479  39230         platforms/linux/local/39230.c   
35479  35480  39231        platforms/asp/webapps/39231.py   
35480  35481  39232       platforms/windows/dos/39232.txt   
35481  35482  39233       platforms/windows/dos/39233.txt   
35482  35483  39234        platforms/php/webapps/39234.py   
35483  35484  39235  platforms/multiple/webapps/39235.txt   
35484  35485  39236   platforms/multiple/webapps/39236.py   
35485  35486  39237       platforms/php/webapps/39237.txt   
35486  35487  39238       platforms/php/webapps/39238.txt   
35487  35488  39239       platforms/php/webapps/39239.txt   
35488  35489  39240       platforms/php/webapps/39240.txt   
35489  35490  39242        platforms/windows/dos/39242.py   
35490  35491  39243       platforms/php/webapps/39243.txt   
35491  35492  39244       platforms/linux/local/39244.txt   
35492  35493  39245       platforms/php/webapps/39245.txt   
35493  35494  39246       platforms/php/webapps/39246.txt   
35494  35495  39248       platforms/php/webapps/39248.txt   

                                             description        date  \
0      Microsoft Windows WebDAV - (ntdll.dll) Remote ...  2003-03-23   
1          Microsoft Windows WebDAV - Remote PoC Exploit  2003-03-24   
2      Linux Kernel 2.2.x - 2.4.x ptrace/kmod Local R...  2003-03-30   
3      Sun SUNWlldap Library Hostname - Buffer Overfl...  2003-04-01   
4      Microsoft Windows RPC Locator Service - Remote...  2003-04-03   
5      WordPress <= 2.0.2 - (cache) Remote Shell Inje...  2006-05-25   
6      Samba 2.2.x - Remote Root Buffer Overflow Exploit  2003-04-07   
7            SETI@home Clients - Buffer Overflow Exploit  2003-04-08   
8             Apache HTTP Server 2.x Memory Leak Exploit  2003-04-09   
9                   Samba <= 2.2.8 - Remote Root Exploit  2003-04-10   
10            Microsoft Internet Explorer 11 - Crash PoC  2015-05-19   
11     Apache <= 2.0.44 (Linux) - Remote Denial of Se...  2003-04-11   
12     Linux Kernel < 2.4.20 - Module Loader Local Ro...  2003-04-14   
13         Chindi Server 1.0 - Denial of Service Exploit  2003-04-18   
14     Mac OS X <= 10.2.4 DirectoryService (PATH) Loc...  2003-04-18   
15         PoPToP PPTP <= 1.1.4-b3 - Remote Root Exploit  2003-04-18   
16     Xeneo Web Server 2.2.9.0 - Denial of Service E...  2003-04-22   
17     Snort <= 1.9.1 - Remote Root Exploit (p7snort1...  2003-04-23   
18     PoPToP PPTP <= 1.1.4-b3 - Remote Root Exploit ...  2003-04-25   
19     Microsoft Windows SMB - Authentication Remote ...  2003-04-25   
20           Qpopper 4.0.x - poppassd Local Root Exploit  2003-04-29   
21     Pi3Web 2.0.1 - Denial of Service - Proof of Co...  2003-04-29   
22     Real Server < 8.0.2 - Remote Exploit (Windows ...  2003-04-30   
23     Sendmail <= 8.12.8 prescan() BSD Remote Root E...  2003-04-30   
24     OpenSSH/PAM <= 3.6.1p1 - Remote Users Discover...  2003-04-30   
25     OpenSSH/PAM <= 3.6.1p1 - Remote Users Ident (g...  2003-05-02   
26     CommuniGate Pro Webmail 4.0.6 Session Hijackin...  2003-05-05   
27     Kerio Personal Firewall 2.1.4 - Remote Code Ex...  2003-05-08   
28     Firebird 1.0.2 FreeBSD 4.7-RELEASE - Local Roo...  2003-05-12   
29     Snitz Forums 3.3.03 - Remote Command Execution...  2003-05-12   
...                                                  ...         ...   
35465  WordPress Featured Comments Plugin Cross Site ...  2014-06-10   
35466  Linux Kernel <= 3.3.5 '/drivers/media/media-de...  2014-05-28   
35467  Konica Minolta FTP Utility 1.00 - CWD Command ...  2016-01-11   
35468     KeePass Password Safe Classic 1.29 - Crash PoC  2016-01-11   
35469               Amanda <= 3.3.1 - Local Root Exploit  2016-01-11   
35470  TrendMicro node.js HTTP Server Listening on lo...  2016-01-11   
35471  Adobe Flash BlurFilter Processing - Out-of-Bou...  2016-01-11   
35472  Adobe Flash - Use-After-Free When Rendering Di...  2016-01-11   
35473    Adobe Flash - Use-After-Free When Setting Stage  2016-01-11   
35474  Foreman Smart-Proxy Remote Command Injection V...  2014-06-05   
35475  ZeusCart 'prodid' Parameter SQL Injection Vuln...  2014-06-24   
35476    FortiGate OS Version 4.x - 5.0.7 - SSH Backdoor  2016-01-12   
35477  Grassroots DICOM (GDCM) 2.6.0 and 2.6.1 - Imag...  2016-01-12   
35478  Linux Kernel overlayfs - Local Privilege Escal...  2016-01-12   
35479  WhatsUp Gold 16.3 - Unauthenticated Remote Cod...  2016-01-13   
35480  Microsoft Windows devenum.dll!DeviceMoniker::L...  2016-01-13   
35481  Microsoft Office / COM Object DLL Planting wit...  2016-01-13   
35482        SevOne NMS <= 5.3.6.0 - Remote Root Exploit  2016-01-14   
35483  Manage Engine Applications Manager 12 - Multip...  2016-01-14   
35484  Manage Engine Application Manager 12.5 - Arbit...  2016-01-14   
35485  WordPress NextGEN Gallery <= 1.9.1 'photocrati...  2014-05-19   
35486  AtomCMS SQL Injection and Arbitrary File Uploa...  2014-07-07   
35487  xClassified 'ads.php' SQL Injection Vulnerability  2014-07-07   
35488  WordPress BSK PDF Manager Plugin 'wp-admin/adm...  2014-07-09   
35489                       NetSchedScan 1.0 - Crash PoC  2016-01-15   
35490     phpDolphin <= 2.0.5 - Multiple Vulnerabilities  2016-01-15   
35491  Amanda <= 3.3.1 - amstar Command Injection Loc...  2016-01-15   
35492     Roundcube 1.1.3 - Path Traversal Vulnerability  2016-01-15   
35493  mcart.xls Bitrix Module 6.5.2 - SQL Injection ...  2016-01-15   
35494  WordPress BSK PDF Manager Plugin 'wp-admin/adm...  2014-07-09   

                         author  platform     type  port  
0                        kralor   windows   remote    80  
1                     RoMaNSoFt   windows   remote    80  
2           Wojciech Purczynski     linux    local     0  
3                          Andi   solaris    local     0  
4                  Marcin Wolak   windows   remote   139  
5                          rgod       php  webapps     0  
6                     H D Moore     linux   remote   139  
7                       zillion     linux   remote     0  
8                Matthew Murphy   windows      dos     0  
9                         eSDee     linux   remote   139  
10               Garage4Hackers   windows      dos     0  
11               Daniel Nystram     linux      dos     0  
12                        KuRaK     linux    local     0  
13                  Luca Ercoli   windows      dos     0  
14                    Neeko Oni       osx    local     0  
15                     einstein     linux   remote  1723  
16                   Tom Ferris   windows      dos     0  
17                        truff     linux   remote     0  
18                 blightninjas     linux   remote  1723  
19                Haamed Gheibi   windows   remote   139  
20                    Xpl017Elz     linux    local     0  
21                         aT4r   windows      dos     0  
22             Johnny Cyberpunk   windows   remote   554  
23                        bysin     linux   remote    25  
24            Maurizio Agazzini     linux   remote     0  
25              Nicolas Couture     linux   remote     0  
26            Yaroslav Polyakov     linux   remote    80  
27                    Burebista   windows   remote     0  
28                          bob       bsd    local     0  
29                         None   windows   remote     0  
...                         ...       ...      ...   ...  
35465                 Tom Adams       php  webapps     0  
35466               Salva Peiro     linux    local     0  
35467                    TOMIWA   windows   remote    21  
35468   Mohammad Reza Espargham   windows      dos     0  
35469          Hacker Fantastic     linux    local     0  
35470  Google Security Research   windows   remote     0  
35471  Google Security Research  multiple      dos     0  
35472  Google Security Research   windows      dos     0  
35473  Google Security Research     win64      dos     0  
35474            Lukas Zapletal  multiple   remote     0  
35475              Kenny Mathis       php  webapps     0  
35476              operator8203  hardware   remote    22  
35477           Stelios Tsampas     linux      dos     0  
35478                   halfdog     linux    local     0  
35479           Matt Buzanowski       asp  webapps     0  
35480  Google Security Research   windows      dos     0  
35481  Google Security Research   windows      dos     0  
35482              @iamsecurity       php  webapps    80  
35483         Bikramaditya Guha  multiple  webapps  9090  
35484         Bikramaditya Guha  multiple  webapps     0  
35485                    SANTHO       php  webapps     0  
35486              Jagriti Sahu       php  webapps     0  
35487                Lazmania61       php  webapps     0  
35488           Claudio Viviani       php  webapps     0  
35489          Abraham Espinosa   windows      dos     0  
35490          WhiteCollarGroup       php  webapps    80  
35491          Hacker Fantastic     linux    local     0  
35492       High-Tech Bridge SA       php  webapps    80  
35493       High-Tech Bridge SA       php  webapps    80  
35494           Claudio Viviani       php  webapps     0  

[35495 rows x 9 columns]
In [ ]:
In [18]:
df = pd.read_sql_query("SELECT platform, COUNT(*) as 'num_remote' FROM data WHERE type LIKE '%remote%' GROUP BY platform ORDER BY 'num_remote'", disk_engine)
py.iplot([Bar(x=df.platform, y=df.num_remote)], filename='Number of remote exploits by platform')
Out[18]:
https://plot.ly/~vkremez/3.embed
In [20]:
df = pd.read_sql_query("SELECT platform, COUNT(*) as 'num_webapps' FROM data WHERE type LIKE '%webapps%' GROUP BY platform ORDER BY 'num_webapps'", disk_engine)
py.iplot([Bar(x=df.platform, y=df.num_webapps)], filename='Number of webapps exploits by platform')
Out[20]:
https://plot.ly/~vkremez/5.embed
In [21]:
df = pd.read_sql_query("SELECT platform, COUNT(*) as 'num_local' FROM data WHERE type LIKE '%local%' GROUP BY platform ORDER BY 'num_local'", disk_engine)
py.iplot([Bar(x=df.platform, y=df.num_local)], filename='Number of local exploits by platform')
Out[21]:
https://plot.ly/~vkremez/7.embed
In [22]:
df = pd.read_sql_query("SELECT platform, COUNT(*) as 'num_dos' FROM data WHERE type LIKE '%dos%' GROUP BY platform ORDER BY 'num_dos'", disk_engine)
py.iplot([Bar(x=df.platform, y=df.num_dos)], filename='Number of dos exploits by platform')
Out[22]:
https://plot.ly/~vkremez/9.embed
In [24]:
df = pd.read_sql_query("SELECT platform, COUNT(*) as 'num_exploits' FROM data GROUP BY platform ORDER BY 'num_exploits'", disk_engine)
py.iplot([Bar(x=df.platform, y=df.num_exploits)], filename='Number of exploits by platform')
Out[24]:
https://plot.ly/~vkremez/11.embed
In [35]:
df = pd.read_sql_query("SELECT platform, COUNT(*) as 'num_exploits' FROM data GROUP BY platform ORDER BY 'num_exploits'", disk_engine)
In [36]:
print df
          platform  num_exploits
0              aix            84
1          android            46
2              arm            15
3              asp          1508
4           atheos             1
5             beos             4
6              bsd            89
7          bsd_ppc             1
8          bsd_x86            14
9         bsdi_x86             3
10             cfm            56
11             cgi           692
12         freebsd            80
13     freebsd_x86            19
14  freebsd_x86-64             2
15       generator             9
16        hardware          1103
17           hp-ux            43
18         immunix             2
19             ios           135
20            irix            60
21            java           117
22             jsp           216
23       lin_amd64             9
24         lin_x86           230
25      lin_x86-64            28
26           linux          2352
27      linux_mips             9
28       linux_ppc             4
29     linux_sparc             2
..             ...           ...
31            mips             2
32        multiple          1949
33      netbsd_x86            10
34         netware            16
35          novell            39
36         openbsd            18
37     openbsd_x86             3
38             osx           273
39         osx_ppc            11
40         palm_os             5
41            perl             3
42             php         17590
43           plan9             1
44          python             4
45             qnx            10
46             sco            38
47         sco_x86             1
48             sh4             3
49         solaris           190
50   solaris_sparc            11
51     solaris_x86            10
52        system_z             1
53           tru64             6
54          ultrix             2
55            unix           304
56        unixware             4
57           win32           102
58           win64            16
59         windows          7921
60             xml            17

[61 rows x 2 columns]
In [16]:
df = pd.read_sql_query("SELECT file, COUNT(*) as 'num_windows' FROM data WHERE file LIKE '%windows%' GROUP BY file ORDER BY 'num_windows'", disk_engine)
In [17]:
print df
                                      file  num_windows
0           platforms/windows/dos/1000.cpp            5
1           platforms/windows/dos/10005.py            5
2           platforms/windows/dos/10062.py            5
3           platforms/windows/dos/10068.rb            5
4           platforms/windows/dos/10073.py            5
5          platforms/windows/dos/10091.txt            5
6          platforms/windows/dos/10092.txt            5
7           platforms/windows/dos/10100.py            5
8           platforms/windows/dos/10102.pl            5
9          platforms/windows/dos/10103.txt            5
10          platforms/windows/dos/10104.py            5
11           platforms/windows/dos/10106.c            5
12          platforms/windows/dos/10160.py            5
13          platforms/windows/dos/10163.pl            5
14           platforms/windows/dos/10164.c            5
15          platforms/windows/dos/10171.py            5
16         platforms/windows/dos/10176.txt            5
17         platforms/windows/dos/10190.txt            5
18         platforms/windows/dos/10204.txt            5
19         platforms/windows/dos/10208.txt            5
20         platforms/windows/dos/10210.txt            5
21         platforms/windows/dos/10221.txt            5
22         platforms/windows/dos/10223.txt            5
23         platforms/windows/dos/1024.html            5
24         platforms/windows/dos/1025.html            5
25          platforms/windows/dos/10257.py            5
26            platforms/windows/dos/1027.c            5
27          platforms/windows/dos/10303.py            5
28          platforms/windows/dos/10333.py            5
29         platforms/windows/dos/10343.txt            5
...                                    ...          ...
7891   platforms/windows/webapps/31994.txt            5
7892   platforms/windows/webapps/31995.txt            5
7893   platforms/windows/webapps/33330.txt            5
7894    platforms/windows/webapps/33428.py            5
7895    platforms/windows/webapps/33434.rb            5
7896   platforms/windows/webapps/33633.txt            5
7897     platforms/windows/webapps/34527.c            5
7898    platforms/windows/webapps/34817.rb            5
7899   platforms/windows/webapps/34852.txt            5
7900   platforms/windows/webapps/34924.txt            5
7901    platforms/windows/webapps/35039.rb            5
7902   platforms/windows/webapps/35529.txt            5
7903   platforms/windows/webapps/35593.txt            5
7904   platforms/windows/webapps/35982.txt            5
7905   platforms/windows/webapps/36262.txt            5
7906    platforms/windows/webapps/36580.rb            5
7907   platforms/windows/webapps/36861.txt            5
7908   platforms/windows/webapps/36960.txt            5
7909  platforms/windows/webapps/37059.html            5
7910  platforms/windows/webapps/37319.html            5
7911  platforms/windows/webapps/37320.html            5
7912   platforms/windows/webapps/37395.txt            5
7913   platforms/windows/webapps/37621.txt            5
7914   platforms/windows/webapps/38379.txt            5
7915   platforms/windows/webapps/38380.txt            5
7916   platforms/windows/webapps/38602.txt            5
7917   platforms/windows/webapps/38762.txt            5
7918    platforms/windows/webapps/38822.rb            5
7919    platforms/windows/webapps/9873.txt            5
7920    platforms/windows/webapps/9885.txt            5

[7921 rows x 2 columns]
In [38]:
df = pd.read_sql_query("SELECT date, COUNT(*) as 'num_exploit' FROM data GROUP BY date ORDER BY 'num_exploit'", disk_engine)
py.iplot([Bar(x=df.date, y=df.num_exploit)], filename='Number of exploits by date')
Out[38]:
https://plot.ly/~vkremez/13.embed

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: