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 exploitsthe 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 exploitsthe 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 exploitsthe 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')
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')
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')
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')
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')
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')

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s