bash – Page 6 – Ryan Schulze

How to build an efficient GeoIP SQL table

August 22, 2011April 10, 2012Ryan1 Commentbash, GeoIP, MySQL

This here is a very handy little script I threw together to generate a geoip.sql table for quickly determining which country a IP is from. I already hear you saying “Just convert the IP to an INT and use BETWEEN, how hard can it be”. And you are right, that works. And it may even be your easiest solution, but it just isn’t fast. And if you are planning on hammering the table with thousands of queries you are going to end up looking for something fast.

A while back I found a very interesting posting at www.jcole.us that described how to use Spacial Indexes together with MySQL’s GIS to speed up the queries. The posting has been online for a while and both it and the replies are worth reading.

All I did was make a small bash script to download the current “lite” version of GeoIP CSV file from maxmind.com, use the information from the posting to throw/transform it into a local database table and dump out a .sql file that can be easily imported into any other database. The script isn’t failproof though, it expects your user to be able to use mysql and have permission to create databases/tables and “load data local infile”.

generate_geoip_sql.sh

#!/bin/bash
#===============================================================================
#
#          FILE: generate_geoip_sql.sh
#
#         USAGE: ./generate_geoip_sql.sh
#
#   DESCRIPTION: Generates a optimized sql dump with geoipdata from maxmind.com
#
#         USAGE: import sql into a database
#
#  REQUIREMENTS: mysql database rights to create databases and LOAD DATA LOCAL INFILE
#
#       EXAMPLE:
#                 SELECT country_code,country_name
#                 FROM   geo_ip
#                 WHERE  MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('1.2.3.4'), 0)))
#
#                 SELECT country_code,country_name
#                 FROM   geo_ip
#                 WHERE  INET_ATON('1.2.3.4') 
#                 BETWEEN ip_from AND ip_to;
#
#        AUTHOR:  Ryan Schulze (rs), ryan@dopefish.de
#
#===============================================================================

set -o nounset                              # Treat unset variables as an error

CSVFile="GeoIPCountryWhois.csv"

if [[ -e ${CSVFile} ]]
  then
  echo -en "\n${CSVFile} already exists, download a newer version? [Y/n]: "
  read answer
  if [[ "$(echo $answer|tr "N" "n")" != "n" ]]
  then
    rm ${CSVFile}
  fi
fi

if [[ ! -e ${CSVFile} ]]
then
  tmpfile=$(mktemp)
  wget -O ${tmpfile} http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip
  unzip ${tmpfile} &amp;&amp; rm ${tmpfile}
fi

echo -e "\ngenerating geoip.sql"

mysql -e "create database if not exists temp"
mysql -D temp -e "drop table if exists geo_ip"

echo '
CREATE TABLE geo_ip
(
  id           INT UNSIGNED  NOT NULL auto_increment,
  ip_poly      POLYGON       NOT NULL,
  ip_from      INT UNSIGNED  NOT NULL,
  ip_to        INT UNSIGNED  NOT NULL,
  country_code CHAR(2)       NOT NULL,
  country_name CHAR(50)      NOT NULL,
  PRIMARY KEY (id),
  SPATIAL INDEX (ip_poly)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOAD DATA LOCAL INFILE "GeoIPCountryWhois.csv"
INTO TABLE geo_ip
FIELDS
  TERMINATED BY ","
  ENCLOSED BY "\""
LINES
  TERMINATED BY "\n"
(
  @ip_from_string, @ip_to_string,
  @ip_from, @ip_to,
  @country_code, @country_string
)
SET
  id      := NULL,
  ip_from := @ip_from,
  ip_to   := @ip_to,
  ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(
    /* clockwise, 4 points and back to 0 */
    POINT(@ip_from, -1), /* 0, top left */
    POINT(@ip_to,   -1), /* 1, top right */
    POINT(@ip_to,    1), /* 2, bottom right */
    POINT(@ip_from,  1), /* 3, bottom left */
    POINT(@ip_from, -1)  /* 0, back to start */
  ))),
  country_code := @country_code,
  country_name := @country_string
;
' | mysql --local-infile=1 -D temp

mysqldump --opt temp geo_ip &gt; geoip.sql

echo -e "\ndone generating geoip.sql"

#!/bin/bash

#===============================================================================

# FILE: generate_geoip_sql.sh

# USAGE: ./generate_geoip_sql.sh

# DESCRIPTION: Generates a optimized sql dump with geoipdata from maxmind.com

# USAGE: import sql into a database

# REQUIREMENTS: mysql database rights to create databases and LOAD DATA LOCAL INFILE

# EXAMPLE:

# SELECT country_code,country_name

# FROM geo_ip

# WHERE MBRCONTAINS(ip_poly, POINTFROMWKB(POINT(INET_ATON('1.2.3.4'), 0)))

# SELECT country_code,country_name

# FROM geo_ip

# WHERE INET_ATON('1.2.3.4')

# BETWEEN ip_from AND ip_to;

# AUTHOR: Ryan Schulze (rs), ryan@dopefish.de

#===============================================================================

set -o nounset # Treat unset variables as an error

CSVFile="GeoIPCountryWhois.csv"

if [[ -e ${CSVFile} ]]

then

echo -en "\n${CSVFile} already exists, download a newer version? [Y/n]: "

read answer

if [[ "$(echo $answer|tr "N" "n")" != "n" ]]

then

rm ${CSVFile}

if [[ ! -e ${CSVFile} ]]

then

tmpfile=$(mktemp)

wget -O ${tmpfile} http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip

unzip ${tmpfile} && rm ${tmpfile}

echo -e "\ngenerating geoip.sql"

mysql -e "create database if not exists temp"

mysql -D temp -e "drop table if exists geo_ip"

echo '

CREATE TABLE geo_ip

(

id INT UNSIGNED NOT NULL auto_increment,

ip_poly POLYGON NOT NULL,

ip_from INT UNSIGNED NOT NULL,

ip_to INT UNSIGNED NOT NULL,

country_code CHAR(2) NOT NULL,

country_name CHAR(50) NOT NULL,

PRIMARY KEY (id),

SPATIAL INDEX (ip_poly)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

LOAD DATA LOCAL INFILE "GeoIPCountryWhois.csv"

INTO TABLE geo_ip

FIELDS

TERMINATED BY ","

ENCLOSED BY "\""

LINES

TERMINATED BY "\n"

(

@ip_from_string, @ip_to_string,

@ip_from, @ip_to,

@country_code, @country_string

)

SET

id := NULL,

ip_from := @ip_from,

ip_to := @ip_to,

ip_poly := GEOMFROMWKB(POLYGON(LINESTRING(

/* clockwise, 4 points and back to 0 */

POINT(@ip_from, -1), /* 0, top left */

POINT(@ip_to, -1), /* 1, top right */

POINT(@ip_to, 1), /* 2, bottom right */

POINT(@ip_from, 1), /* 3, bottom left */

POINT(@ip_from, -1) /* 0, back to start */

))),

country_code := @country_code,

country_name := @country_string

;

' | mysql --local-infile=1 -D temp

mysqldump --opt temp geo_ip > geoip.sql

echo -e "\ndone generating geoip.sql"

How to install ruby 1.9.2 on Ubuntu 10.04

July 7, 2011May 13, 2013Ryan3 Commentsbash, Debian, LTS, ruby, Ubuuntu

The current LTS version of Ubuntu is 10.04 and the most current version of ruby it ships with is 1.9.1. Unfurtunately 1.9.1 wasn’t that great of a release and anyone using the 1.9 branch really should use the stable 1.9.2.

After doing a bit of researching I found some information on how the best approach to get ruby installed is. Downloading the source, compiling it and registering the installed version with the package manager.

The following little bash script takes care of installing ruby 1.9.2 on a ubuntu or debian based system (or any other version if you change the $Version variable in the script). The script just consolidates information found online and wraps it up into a nice bashscript

#!/bin/bash
#===============================================================================
#
#          FILE:  install_ruby_1.9.sh
# 
#         USAGE:  ./install_ruby_1.9.sh 
# 
#        AUTHOR: Ryan Schulze (rs), ryan@dopefish.de
#       CREATED: 07/07/2011 11:59:37 AM CDT
#===============================================================================

Version="1.9.2-p180"
GZFile="ruby-${Version}.tar.gz"
Download="http://ftp.ruby-lang.org/pub/ruby/1.9/${GZFile}"

if [[ "$(id -u)" != "0" ]]
then
	echo "You need root permission to execute this script"
	exit
fi

apt-get -q update
apt-get -qy upgrade
apt-get install -qy build-essential wget zlib1g-dev libssl-dev libffi-dev autoconf

cd /usr/local/src/
test -e ${GZFile} || wget ${Download}
tar -xzf ${GZFile}
cd ruby-${Version}

autoconf
./configure --with-ruby-version=${Version} --prefix=/usr --program-suffix=${Version} 
make
make install

mkdir -p /usr/lib/ruby/gems/${Version}/bin

update-alternatives \
	--install /usr/bin/ruby ruby /usr/bin/ruby${Version} $(echo ${Version//./}|cut -d- -f1) \
	--slave   /usr/share/man/man1/ruby.1.gz ruby.1.gz /usr/share/man/man1/ruby${Version}.1 \
	--slave   /usr/lib/ruby/gems/bin        gem-bin   /usr/lib/ruby/gems/${Version}/bin \
	--slave   /usr/bin/erb  erb  /usr/bin/erb${Version} \
	--slave   /usr/bin/irb  irb  /usr/bin/irb${Version} \
	--slave   /usr/bin/rdoc rdoc /usr/bin/rdoc${Version} \
	--slave   /usr/bin/ri   ri   /usr/bin/ri${Version} \
	--slave   /usr/bin/gem  gem  /usr/bin/gem${Version} \

update-alternatives --config ruby
update-alternatives --display gem &gt;/dev/null 2&gt;&amp;1 &amp;&amp; update-alternatives --remove-all gem

echo "[+] Done installing"
ruby -v

#!/bin/bash

#===============================================================================

# FILE: install_ruby_1.9.sh

# USAGE: ./install_ruby_1.9.sh

# AUTHOR: Ryan Schulze (rs), ryan@dopefish.de

# CREATED: 07/07/2011 11:59:37 AM CDT

#===============================================================================

Version="1.9.2-p180"

GZFile="ruby-${Version}.tar.gz"

Download="http://ftp.ruby-lang.org/pub/ruby/1.9/${GZFile}"

if [[ "$(id -u)" != "0" ]]

then

echo "You need root permission to execute this script"

exit

apt-get -q update

apt-get -qy upgrade

apt-get install -qy build-essential wget zlib1g-dev libssl-dev libffi-dev autoconf

cd /usr/local/src/

test -e ${GZFile} || wget ${Download}

tar -xzf ${GZFile}

cd ruby-${Version}

autoconf

./configure --with-ruby-version=${Version} --prefix=/usr --program-suffix=${Version}

make

make install

mkdir -p /usr/lib/ruby/gems/${Version}/bin

update-alternatives \

--install /usr/bin/ruby ruby /usr/bin/ruby${Version} $(echo ${Version//./}|cut -d- -f1) \

--slave /usr/share/man/man1/ruby.1.gz ruby.1.gz /usr/share/man/man1/ruby${Version}.1 \

--slave /usr/lib/ruby/gems/bin gem-bin /usr/lib/ruby/gems/${Version}/bin \

--slave /usr/bin/erb erb /usr/bin/erb${Version} \

--slave /usr/bin/irb irb /usr/bin/irb${Version} \

--slave /usr/bin/rdoc rdoc /usr/bin/rdoc${Version} \

--slave /usr/bin/ri ri /usr/bin/ri${Version} \

--slave /usr/bin/gem gem /usr/bin/gem${Version} \

update-alternatives --config ruby

update-alternatives --display gem >/dev/null 2>&1 && update-alternatives --remove-all gem

echo "[+] Done installing"

ruby -v

How to easily add colored text output in bash scripts

June 1, 2011September 27, 2014Ryan1 Commentbash, color, scripting

Here is small snippet that can give your shell scripts some nice output: colortext.sh As with the debug.sh script, just download it to the same directory as your own script and add it with

. colortext.sh

1	. colortext.sh

It contains one simple function called text with the syntax text “text to be output”. Color can be red, green, yellow, blue or grey. The function does not automatically add a linebreak to the putput, so pop a \n in there if you need it. I prefer using it together with printf for clean and easy color output.

Here are some examples of how the function can be used, and below the corresponding output:

. colortext.sh

echo "normal text"
text blue "blue text, "
text yellow "yellow text\n"

# Using it together with echo
echo -n "Status of script: [";text red "ERROR";echo "]"

# more elegant usage with printf
printf "Status of script: [%s]\n" $(text green "OK")

. colortext.sh

echo "normal text"

text blue "blue text, "

text yellow "yellow text\n"

# Using it together with echo

echo -n "Status of script: [";text red "ERROR";echo "]"

# more elegant usage with printf

printf "Status of script: [%s]\n" $(text green "OK")

Output:

normal text
blue text, yellow text
Status of script: [ERROR]
Status of script: [OK]

How to add debugging to shellscripts

May 4, 2011September 27, 2014Ryan1 Commentbash, debug

Debugging bash scripts is pretty straightforward, throwing around a couple echo and set -x quickly gives you what you need. But what if you want to add a nice breakpoint, debugging to lots of paces in the code or turn all debugging on or off at once? Then this little script I wrote is the right thing for you: debug.sh just download it to the same directory as your script and include it with the following line:

. debug.sh

1	. debug.sh

It contains 4 simple functions that will make your bash coding easier.
debug and breakpoint both print the argument with a timestamp to STDERR
You can turn off all the functions by adding a DEBUG=false into your code

Example:

#!/bin/bash
. debug.sh

debug_on
echo foo
debug_off

debug "I am a test"

breakpoint "wait for a bit"

echo done

#!/bin/bash

. debug.sh

debug_on

echo foo

debug_off

debug "I am a test"

breakpoint "wait for a bit"

echo done

Output:

+ echo foo
foo
+ debug_off
+ true
+ set +x
[16:37:47] I am a test
[16:37:47] -breakpoint- wait for a bit
press any key to continue

done

+ echo foo

foo

+ debug_off

+ true

+ set +x

[16:37:47] I am a test

[16:37:47] -breakpoint- wait for a bit

press any key to continue

done

Wireshark remote capturing

March 8, 2011May 13, 2013Ryanbash, remote, tcpdump, wireshark

yeah, this is real simple stuff, not really worth writing a script for it. but on the other hand it saves me from remembering how to do it every time I need it (which isn’t often). So here is a little script to setup remote capturing with wireshark.
All it basically does is ssh to the remote host and tcpdump sucking the output via stdout through the ssh connection to a local pipe, that is then used by wireshark to display the stream. Because of this you may want to make sure you aren’t capturing your own ssh data when doing this 😉

#!/bin/bash

destination="1.2.3.4" # server to run the remote capture on
filter="port 80"      # tcpdump filter options
interface="eth0"      # interface to listen on on remote server
opts="-n -p -s 0"     # other tcmpdump options

##### let the fun begin #####

mypipe="/tmp/remotecap.$$.cap"
mkfifo ${mypipe}

ssh root@${destination} "tcpdump ${opts} -i ${interface} -w - ${filter}" &gt; ${mypipe} &amp;
pipepid=$!

wireshark -k -N ntC -t a -i ${mypipe}

kill ${pipepid}
rm -f ${mypipe}

#!/bin/bash

destination="1.2.3.4" # server to run the remote capture on

filter="port 80" # tcpdump filter options

interface="eth0" # interface to listen on on remote server

opts="-n -p -s 0" # other tcmpdump options

##### let the fun begin #####

mypipe="/tmp/remotecap.$$.cap"

mkfifo ${mypipe}

ssh root@${destination} "tcpdump ${opts} -i ${interface} -w - ${filter}" > ${mypipe} &

pipepid=$!

wireshark -k -N ntC -t a -i ${mypipe}

kill ${pipepid}

rm -f ${mypipe}