Tech – Page 14 – Ryan Schulze

MySQL selecting IPs via CIDR

February 23, 2011December 15, 2017Ryancidr, inet_aton, inet_ntoa, ip, myysql, network

Quick little snippet here for selecting IPs from a database based off a CIDR subnet. First off a table structure with some test data:

mysql> desc tmp;
+-------+------------------+
| Field |     Type         |
+-------+------------------+
| ip1   | int(12) unsigned |  
| ip2   | varchar(15)      |
+-------+------------------+

mysql> select * from tmp;
+------------+-----------------+
| ip1        |             ip2 |
+------------+-----------------+
| 16909060   | 1.2.3.4         |
| 167904004  | 10.2.3.4        |
| 2915086080 | 173.192.175.0   |
| 2915086100 | 173.192.175.20  |
| 2915086335 | 173.192.175.255 |
+------------+-----------------+

mysql> desc tmp;

+-------+------------------+

| Field | Type |

+-------+------------------+

| ip1 | int(12) unsigned |

| ip2 | varchar(15) |

+-------+------------------+

mysql> select * from tmp;

+------------+-----------------+

| ip1 | ip2 |

+------------+-----------------+

| 16909060 | 1.2.3.4 |

| 167904004 | 10.2.3.4 |

| 2915086080 | 173.192.175.0 |

| 2915086100 | 173.192.175.20 |

| 2915086335 | 173.192.175.255 |

+------------+-----------------+

Now let’s say we want all IPs from the subnet 173.192.175.16/28, using a simple 173.192.175.% would provide false results since you don’t want the whole /24.

If your IP is stored as an unsigned int (good for you) than you can use this snippet to search for matching IPs:

select inet_ntoa(ip1) as ip1,ip2 from tmp where (ip1 & power(2, 32) - power(2, (32 - 28))) = inet_aton('173.192.175.16');

1	select inet_ntoa(ip1) as ip1,ip2 from tmp where (ip1 & power(2, 32) - power(2, (32 - 28))) = inet_aton('173.192.175.16');

If your IP is stored as a varchar (for whatever reason), the only difference is a inet_aton() around the IP field.

select inet_ntoa(ip1) as ip1,ip2 from tmp where (inet_aton(ip2) & power(2, 32) - power(2, (32 - 28))) = inet_aton('173.192.175.16');

1	select inet_ntoa(ip1) as ip1,ip2 from tmp where (inet_aton(ip2) & power(2, 32) - power(2, (32 - 28))) = inet_aton('173.192.175.16');

No matter which one you use, the result will be:

+----------------+----------------+
| ip1            |         ip2    |
+----------------+----------------+
| 173.192.175.20 | 173.192.175.20 |
+----------------+----------------+

+----------------+----------------+

| ip1 | ip2 |

+----------------+----------------+

| 173.192.175.20 | 173.192.175.20 |

+----------------+----------------+

Controlling SSH identities

September 30, 2010May 13, 2013Ryan1 Comment

SSH has a few strange undocumented “features”. One of which is the way it handles identities via agent and command line. It is possible to specify an identity file to use for ssh via the -i parameter (ssh -i identity_file $host). What the manpage doesn’t mention, is that the specified identity isn’t forced for the connection, it is just added to the list of possible identities.
To make matters worse, ssh tries the identities from the agent first. So if agent forwarding is enabled and valid for the destination the ssh command will never use the identity specified with -i. Why is this “bad”? Because the identity specified may be used for specific tasks with commands linked to them on the destination (e.g. automatic restarts, backups, …)

Sooo, as a solution I whipped up the following function as a workaround in my scripts, I add a function called “xssh”:

xssh() { #{{{
local identity="/root/.ssh/special_identity"
ssh-add -l &gt;/dev/null \
&amp;&amp; ssh -a ${USER}@localhost "ssh -o connecttimeout=10 -o stricthostkeychecking=no -i $identity ${@}" \
|| ssh -o connecttimeout=10 -o stricthostkeychecking=no -i $identity ${@}
} #}}}

xssh() { #{{{

local identity="/root/.ssh/special_identity"

ssh-add -l >/dev/null \

&& ssh -a ${USER}@localhost "ssh -o connecttimeout=10 -o stricthostkeychecking=no -i $identity ${@}" \

|| ssh -o connecttimeout=10 -o stricthostkeychecking=no -i $identity ${@}

} #}}}

I know it looks ugly, if it finds a key in the agent it makes a ssh connection to the current host with agent forwarding deactivated and then executes the ssh parameters passed. If no key is found in the ssh agent it does everything as normal.

XEN 3.4 with ipv6 routing

September 1, 2010May 13, 2013Ryanbash, ipv6, patch, routing, xen

Yes, there are a few postings out there about getting ipv6 routing running with XEN. But I’ll throw this online anyway since there are a few changes I had to make for it to work on my server. This text is intended for people who know their way around Linux and XEN so it will be a bit technical and won’t spell out every single step you have to make.

Most of the changes are based off scripts and information from BenV and wnagele (latter is interesting for me since I am also running XEN on a hetzner server). Have a look at the two links if anything is unclear. Now let’s start the fun 🙂

First of all we need IPv6 up and running on the host (dom0). Add the IP and gateway to your /etc/network/interfaces
This is what mine looks like:
iface eth0 inet6 static address 2a01:4f8:100:1123::2 netmask 64 gateway 2a01:4f8:100:1120::1 pre-up ip -6 route add 2a01:4f8:100:1120::1 dev eth0
Check if the IP address is responding to the outside world (e.g. with wiberg.nu/iptools.php), if everything looks ok, proceed …
Now we need to enable a few things to get routing and neighbor discovery running on the host (dom0). Edit your /etc/sysctl.conf and add/change these 2 entries (and while you are at it, set them with “sysctl -w” too):
net.ipv6.conf.all.forwarding=1 net.ipv6.conf.all.proxy_ndp=1

So, your host should by now be online with ipv6 and soon be able to route packets to it’s guests. By default XEN will only take care of IPv4 when a guest is created, so here is a small patchfile that adds support for IPv6: xen-ipv6-vif-route.patch. The patch changes vif-route and vif-common.sh, while these files may be in different places depending on your distribution, /etc/xen/scripts/ is where they can commonly be found. Download the patch to the directory with the scripts to be changed and execute a “patch -p0 < xen-ipv6-vif-route.patch” (vif-common.sh gets a few new IPv6 functions, and iptables now won’t try to change stuff for IPv6 IPs. vif-route changes are: ndp is enabled for the vif device and the route/neighbor IPv6 settings are set)

So, now that the scripts know how to setup all our IPv6 needs, we need to add the IPv6 IP to our guest settings (.cfg file typically found in /etc/xen/). What we want to change is the “vif” setting. Add the IPv6 IP of the guest to the IPv4 IP (just the IP without the trailing /network, space separated form the IPv4 IP):
vif = [ 'mac=B1:A3:3F:25:11:B8, ip=2a01:4f8:100:1123::5 188.40.34.101' ]

Now you can create the guest(domU) and add the IPv6 IP to the /etc/network/interfaces of the guest if you haven’t so already (it uses the host (dom0) as the gateway).
iface eth0 inet6 static address 2a01:4f8:100:1123::5 netmask 64 gateway 2a01:4f8:100:1123::2
Restart the networking on the guest (or reboot it) and you should now be able to ping the guest from the internet. See, easy wasn’t it 🙂

Script of the day – clean up stale .ssh/known_hosts

August 19, 2010May 13, 2013Ryanbash

This little script takes an IP or hostname as a parameter, and if there is an offending key in the .ssh/known_hosts it removes it and replaces it with the current valid one useful if you are moving/reinstalling a large amount of servers …

#!/bin/bash
#===============================================================================
# FILE: ssh-cleankey.sh
# USAGE: ./ssh-cleankey.sh 
#
# DESCRIPTION: deletes stale ssh known_hosts entries
#===============================================================================

# true or fasle
VERBOSE=false

#=== Exit codes ================================================================
# 1 - Not a valid IP or not reachable
#===============================================================================

#=== FUNCTION ================================================================
# NAME: print_help
# DESCRIPTION: Prints help and exits
#===============================================================================
print_help() { #{{{
echo "Usage: `basename $0` "
echo ""
echo "e.g. ./`basename $0` 1.2.3.4"
echo ""
exit 0
} #}}}

if [[ $# -eq 1 ]]
then
HOST="${1}"
else
print_help
fi

ping -w1 -c1 $HOST &gt;/dev/null 2&gt;&amp;1
if [[ $? != 0 ]]
then
$VERBOSE &amp;&amp; echo "ERROR: $HOST is either not a valid IP/hostname, or is not reachable via ping"
exit 1
fi

Check=$(ssh -o connecttimeout=10 -o stricthostkeychecking=no $HOST true 2&gt;&amp;1|grep -c "Offending key")
if [[ $Check -gt 0 ]]
then
$VERBOSE &amp;&amp; echo "$HOST is stale, updating known_hosts"
ssh-keygen -R $HOST &gt;/dev/null 2&gt;&amp;1
ssh -o connecttimeout=10 -o stricthostkeychecking=no $HOST true &gt;/dev/null 2&gt;&amp;1
else
$VERBOSE &amp;&amp; echo "$HOST is OK"
fi

exit 0

#!/bin/bash

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

# FILE: ssh-cleankey.sh

# USAGE: ./ssh-cleankey.sh

# DESCRIPTION: deletes stale ssh known_hosts entries

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

# true or fasle

VERBOSE=false

#=== Exit codes ================================================================

# 1 - Not a valid IP or not reachable

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

#=== FUNCTION ================================================================

# NAME: print_help

# DESCRIPTION: Prints help and exits

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

print_help() { #{{{

echo "Usage: `basename $0` "

echo ""

echo "e.g. ./`basename $0` 1.2.3.4"

echo ""

exit 0

} #}}}

if [[ $# -eq 1 ]]

then

HOST="${1}"

else

print_help

ping -w1 -c1 $HOST >/dev/null 2>&1

if [[ $? != 0 ]]

then

$VERBOSE && echo "ERROR: $HOST is either not a valid IP/hostname, or is not reachable via ping"

exit 1

Check=$(ssh -o connecttimeout=10 -o stricthostkeychecking=no $HOST true 2>&1|grep -c "Offending key")

if [[ $Check -gt 0 ]]

then

$VERBOSE && echo "$HOST is stale, updating known_hosts"

ssh-keygen -R $HOST >/dev/null 2>&1

ssh -o connecttimeout=10 -o stricthostkeychecking=no $HOST true >/dev/null 2>&1

else

$VERBOSE && echo "$HOST is OK"

exit 0

Checking a list of IPs against RBL

August 10, 2010March 12, 2011Ryanbash

This is more a reminder to myself than anything else … this is small snippet that takes a list of IPs and does a whois on all that aren’t in a RBL

Lets say we have al list of IPs in a file “iplist.txt”:
12.172.121.171 24.149.208.68 38.105.100.9 58.185.207.86 61.201.51.66 64.78.164.169

Snippet that checks the IPs (can of course be easily changed to check IPs that are IN a RBL)

for ip in $(cat iplist.txt)
do
test $(/usr/bin/rblcheck -qm ${ip}) &amp;&amp; continue || clear
whois $ip
printf "###########################\n##### %15s #####\n###########################\n" "${ip}"
read
done

for ip in $(cat iplist.txt)

test $(/usr/bin/rblcheck -qm ${ip}) && continue || clear

whois $ip

printf "###########################\n##### %15s #####\n###########################\n" "${ip}"

read

done