MySQL and Python

As you will find out by reading this post, I like to give my projects nicknames that can easily be turned into acronyms. Maybe its due to my days spent in the Navy or maybe I’m just a little crazy at times. This post involves a project I refer to as PEB, once again I will reveal what it stands for when it is complete. Unlike POL I expect PEB to be complete in the coming weeks. I am more excited about PEB than any of my other projects only because it has been rattling around in my brain for years and is finally coming to fruition. On to the meat of this post.

 

The backend for PEB relies on 3 specific components, Ubuntu Server, Python 2.7, and MySQL DB. I’m not going to do a walkthrough of installing Ubuntu server, there are tons of guides on the web and it’s not all that difficult. So once we get our server up and running we need to do a bit of maintenance

 sudo apt-get update –y && sudo apt-get upgrade -y 

 

Let it do its thing for a few minutes, always a good idea to give it a reboot after an update just in case

 sudo shutdown –r now

 

Ok now we can get started with the guts of our server. First let’s make sure Python is ready to go, there is a good chance that python is already installed on your system, let’s check

 python -V

If you get an output similar to

 Python 2.7.12 

you are good to go, if not let’s get Python installed along with a few other items we may need.

 sudo apt-get install python

Once this completes let’s get PIP installed. PIP is the python package manager used for installing most dependencies you will end up needing.

 sudo apt install python-pip 

For some reason, at least for me, it never installs the most recent version of PIP. So let’s fix that.

 sudo pip install --upgrade pip 

Ok, so Python is ready to go, the other part of this project entails using Python to get data to a MySQL database. We need two things, the MySQL Python Connector and a MySQL Database with a table. Let’s start with the MySQL Connector, at one point this could be installed directly from PIP, but that has changed so we have to do it manually. Go to http://dev.mysql.com/downloads/file/?id=465651 and download the connector. I chose to download it on my Windows machine and the SCP it over with MobaXterm. You can very well just use wget. Now we have it on the server, time to install it.

 sudo dpkg -i mysql-connector-python_2.1.4-1ubuntu16.04_all.deb 

Now we need a MySQL DB to put all that awesome useful data into, time to install and configure.

 sudo apt-get install mysql-server 

While it is installing it will ask you to set a root password, make it something strong, you know the drill. Once the install has completed let’s add a bit more security to our MySQL instance, this part is optional but highly recommended. It will ask you a few questions about the security settings of MySQL. Setting things such as remote access by root to false is generally a good idea. You can decide when it comes to the other options.

 sudo mysql_secure_installation 

So we’ve got MySQL running and secure, let’s get it setup to handle our data. First thing, log in.

 mysql -p -u root 

Great, lets create a Database

 create database newdbname; 

Select the Database so we can create a table in the database

 use newdbname; 

We need a table in that database

 CREATE TABLE newtablename (field1 INT(13), field2 VARCHAR(255) CHARACTER SET utf8mb4, field3 VARCHAR(255) CHARACTER SET utf8mb4); 

Good, now how do we go about seeing this Database? I prefer to use a GUI based tool called My SQL Workbench, it’s great for new users as it gives you the ability to use a tabbed interface to run different queries remotely. At this point you’re saying “But we disable remote root access?”. That is a correct statement, we need to make a few changes to allow remote access to the database from a non root user. Since we are still in MySQL let’s get it set first. We are going to add a new user and give it permissions for remote access. This can be done in one line of code.

 GRANT ALL PRIVILEGES ON newdbname.* To 'user'@'%' IDENTIFIED BY 'password'; 

Let break this down so you can properly fill in the required fields.

newdbname.* = This identifies the DB we want to gives access to  along with all of the tables within it which is identified by the wildcard character

‘user’@’%’ = This identifies which user and from where they can connect. In this case replace user with the desired username. The % character represents any remote IP address.

IDENTIFIED BY ‘password’ = This sets the password for the user, you know the drill.

At this point we need to flush the permissions so that MySQL knows what the hell is going on, after the permissions flush exit MySQL.

 flush privileges; exit; 

The last step to gaining remote access to your MySQL install is to comment out the Bind Address in mysqld.conf. If we don’t comment this out only users on the localhost will be able to access the database, by default the bind address is set to 127.0.0.1. So let’s comment it out.

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 

Once in the file you will see the bind address, go ahead and hit insert and add a # to the beginning of the line. Let’s save and quit vi, hit the escape key, select shift + semicolon, WQ and enter.

 

At this point you should be able to open MySQL Workbench and remotely access your server and get to work. Have fun and good luck. Let me know if I missed something or if you find this useful.

Capanlysis: An adventure in automated packet analysis 

Capanlysis: An adventure in automated packet analysis 

Recently I started a personal project I call “POL”, I’m not going to divulge what the acronym stands for at this point, but will in the future when I deem the project complete. One of the requirements of the project was the ability to automate PCAP data analysis. I didn’t necessarily know what information I was looking for in the PCAP but needed a starting point. I like my analysis to be visually based and somewhat simple to use. So after many nights of searching the net for the perfect application, I stumbled upon Capanalysis.

While it is not exactly perfect and could use some refinement, it fit my needs. The gist of Capanlysis is that you capture network traffic on the same server that you are running Capanalysis on or upload a PCAP and it automatically parses the data and displays it in many types of graphs and charts. It got me started down the road for my investigations.

I found that the Geo display and Flows section for Capanalysis were the most helpful. There is a section that is supposed to display the WhoIs data for an IP address, but it doesn’t look as though the code has been implemented yet, which was a bit disappointing but not a deal breaker. For my project I had to do some customization of how Capanlysis gets the data. It is designed to have PCAP files uploaded through their web interface or captured via the CLI on the server. Neither of these options suited my automation needs, so I created it. It wasn’t very difficult, I simply needed to find out what the system did when a PCAP was uploaded.

Turns out that Capanlysis uploads the file to

/opt/capanalysis/ds_1/new/ 

and then renames it and moves it to

/opt/capanalysis/ds_1/raw

I have a rsysnc job running that pulls down my PCAP file from a remote server and places them in a set directory and then removes the files from the source

*/15 * * * *  rsync -Pav -e 'ssh -i /home/user/.ssh/id_rsa' root@x.x.x.x:/dump_files/ /dump_files --remove-source-files

I remove the files from the source because the capture is running on a VPS with limited storage and frankly I don’t have the need for them in the cloud once they have been moved. The I have  a cron job that runs every 16 minutes that move the PCAP’s into the Capanalysis directory for processing. A fairly simple cronjob

*/16 * * * * mv /dump_files/* /opt/capanalysis/ds_1/new/

.

Making this work was not at all difficult but I was really surprised that I was unable to find something open source that accomplished my goals. While it is lacking in some Bella’s and whistles it is a great tool to begin an investigation of captured traffic. Let me know if I missed something or if you find this useful.