Clouds in my coffee | Brian Hare's personal blog.

CAT | Code Snippets

Oct/11

31

PHP: Searching every table in a database for a string

No comments · Posted by Brian Hare in Code Snippets

I recently wanted this ability in a PHP script I was writing, very similar to how you can search the whole database for a string using PHPMyAdmin. My first place I go when I have a problem is google,  but a google result returned no matches at that time, so I decided to write a quick method of my own.

Upon futher investigation, I did finally find this project: http://code.google.com/p/anywhereindb/. I didn’t take a close look into it but I saw it seemed to be a bit more polished and probably has better error checking and handling than mine does. I am still giving you my version though, as I feel it’s a bit more simple and grasps the key concepts.

The code was tested against PHP 5.3, other versions I have no idea if it will work. Note my user and pass for my database are server variables using the security method mention in this post: http://www.brianhare.com/wordpress/2011/02/18/hiding-mysql-passwords-in-php-using-apache-environment-variables/

 

 

<?php
$config['host'] = "localhost";
$config['user'] = $_SERVER['db_user'];
$config['pass'] = $_SERVER['db_pass'];
$database = "my_database";
$search = "billy";
 
mysql_connect($config['host'], $config['user'], $config['pass']);
mysql_select_db($database);
 
$SQL = "SHOW TABLES FROM $database";
$result = mysql_query($SQL) or die ("DB Error, could not list tables - MySQL Error: " . mysql_error());
 
while ($row = mysql_fetch_row($result)) {
	$table = $row[0];
	$SQL = "SELECT * FROM `$table`";
	$table_result = mysql_query($SQL) or die("<pre>". $SQL . "<br>MYSQL Error: " . mysql_error() . "</pre>");
	if(mysql_num_rows($table_result) < 1)
		continue;
	$array = mysql_fetch_assoc($table_result);
	$table_fields = array_keys($array);
	$SQL = "SELECT * FROM `$database`.`$table` WHERE (`" . implode("` LIKE '%$search%' OR `", $table_fields) . "` LIKE '%$search%');";
	$search_result = mysql_query($SQL) or die("<pre>". $SQL . "<br>MYSQL Error: " . mysql_error() . "</pre>");
	if(mysql_num_rows($search_result) < 1)
		continue;
 
	print "Search results for '$search' in table: $table <hr>";
	print "<table border='1' cellpadding='5'>";
	print"<tr>";
	foreach ($table_fields as $field) {
		print "<th>$field</th>";
	}
	print "</tr>";
 
	while($row = mysql_fetch_assoc($search_result)){
		echo "<tr>";
		foreach ($row as $field=>$value) {
			$style = (strpos($value, $search) === false) ? '' : 'style="background-color:lightgreen;"';
			echo "<td $style>$value</td>";
		}
		echo "</tr>";
	}
	echo "</table><br><br>";
}
?>

 

The most confusing part is probably the

WHERE (`" . implode("` LIKE '%$search%' OR `", $table_fields) . "` LIKE '%$search%');";

Where basically the implode function will expand the array that contains the table fields ($table_fields)and between each table it will add ` LIKE '%billy%' OR ` between each table field. However, you have to take into considerationthe first table field won't have a ` in front and the last one wont have the ` LIKE '%billy%', so those must also be appended. Perhaps the more inuitive thing to have done would of been to do it this way:

 

$SQL = "SELECT * FROM `$database`.`$table` WHERE (";
foreach($table_fields as $field) {
	$SQL .= "`$field` LIKE '%$search%' OR ";
}
$SQL = rtrim($SQL, " OR ");
$SQL .= ");";

	




No tags
Mar/11

2

Block TOR Exit Nodes Using BASH Script

No comments · Posted by Brian Hare in Code Snippets

I’ve always attempted to block TOR proxies from my server because the proxies can be abused and used to jump bans on some custom software that I host. Recently I found an official TOR blacklist for exit nodes located here: https://check.torproject.org/cgi-bin/TorBulkExitList.py.

I assume they require you to put in an IP address to give better results which exit nodes have access to your server. What isn’t really documented either is that you can also specify which port to check on as well by adding &port=###, where ### would be the port number you wish to see. This is greatly benefitical for me because the custom software runs on an irregular 9998 port.

Blacklists are great but they aren’t very useful unless you can actually use them on your server and block the IP addresses. Therefore, I wrote the following BASH script:

#!/bin/bash
 
IPTABLES_TARGET="DROP"
IPTABLES_CHAINNAME="TOR"
 
WORKING_DIR="/tmp/"
 
# get IP address of eth0 network interface
IP_ADDRESS=$(ifconfig eth0 | awk '/inet addr/ {split ($2,A,":"); print A[2]}')
 
if ! iptables -L "$IPTABLES_CHAINNAME" -n >/dev/null 2>&1 ; then			#If chain doesn't exist
	iptables -N "$IPTABLES_CHAINNAME" >/dev/null 2>&1				#Create it
fi
 
cd $WORKING_DIR
 
wget -q -O - http://proxy.org/tor_blacklist.txt -U NoSuchBrowser/1.0 > temp_tor_list1
sed -i 's|RewriteCond %{REMOTE_ADDR} \^||g' temp_tor_list1
sed -i 's|\$.*$||g' temp_tor_list1
sed -i 's|\\||g' temp_tor_list1
sed -i 's|Rewrite.*$||g' temp_tor_list1
 
wget -q -O - "https://check.torproject.org/cgi-bin/TorBulkExitList.py?ip=$IP_ADDRESS&port=80" -U NoSuchBrowser/1.0 > temp_tor_list2
wget -q -O - "https://check.torproject.org/cgi-bin/TorBulkExitList.py?ip=$IP_ADDRESS&port=9998" -U NoSuchBrowser/1.0 >> temp_tor_list2
sed -i 's|^#.*$||g' temp_tor_list2
 
iptables -F "$IPTABLES_CHAINNAME"
 
CMD=$(cat temp_tor_list1 temp_tor_list2 | uniq | sort)
 
for IP in $CMD; do
	let COUNT=COUNT+1
	iptables -A "$IPTABLES_CHAINNAME" -s $IP -j $IPTABLES_TARGET
done
 
iptables -A "$IPTABLES_CHAINNAME" -j RETURN
 
rm temp_tor*

It basically downloads the official blacklist and another blacklist that I found and extracts the IP addresses from the files, sorts them, and gets rid of any duplicates they may exist. Then it adds a DROP command to IPTABLES under the specified IPTABLES chain.

I suggest that you set this script to run hourly or daily depending on your needs with cron.

I also have written a custom progress bar to indicate how far along you are. The progress bar code and example using the TOR proxy blocker can be seen at this post: http://www.brianhare.com/wordpress/2011/03/02/bash-progress-bar/

No tags

Mar/11

2

BASH Progress Bar

2 Comments · Posted by Brian Hare in Code Snippets

Recently I rewrote one of my Bash Shell Scripts that blocks TOR proxy exit nodes. It does this by doing numerous DROPs in IPTABLES and because of this, it take 1 minute or so to go through all of them. I decided that it would be nice to have a progress bar display in the shell while it was running to give me an idea how far along it was (You can see the final script at the bottom of this post).

I started to look around for some BASH scripts that have a progress bar and I found 2 notable ones; the first one is called Bar and the second PV (Pipe Viewer). These were nice but I actually needed something that was more based on strictly elements in an array. I reused some code and then optimized it a bit and I got something that is not only very customizable but also will resize the progress bar depending on the window size, much like WGET’s progress bar. The code is here:

 
lib_progress_bar() {
	local current=0
	local max=100
	local completed_char="#"
	local uncompleted_char="."
	local decimal=1
	local prefix=" ["
	local suffix="]"
	local percent_sign="%"
	local max_width=$(tput cols)
 
	local complete remain subtraction width atleast percent chars
	local padding=3
 
	local OPTIND
 
	while getopts c:u:d:p:s:%:m:hV flag; do
		case "$flag" in
			c) completed_char="$OPTARG";;
			u) uncompleted_char="$OPTARG";;
			d) decimal="$OPTARG";;
			p) prefix="$OPTARG";;
			s) suffix="$OPTARG";;
			%) percent_sign="$OPTARG";;
			m) max_width="$OPTARG";;
 
			(h) lib_help;;
			(V) echo "$lib_script_name: version $Revision$ ($Date$)"; exit 0;;
			(*) lib_usage;;
		esac
	done
	shift $((OPTIND-1))
 
	current=${1:-$current}
	max=${2:-$max} 
 
	if (( decimal > 0 )); then
		(( padding = padding + decimal + 1 ))
	fi
 
	let subtraction=${#completed_char}+${#prefix}+${#suffix}+padding+${#percent_sign}
	let width=max_width-subtraction
 
	if (( width < 5 )); then
		(( atleast = 5 + subtraction ))
		echo >&2 "the max_width of ($max_width) is too small, must be atleast $atleast"
		return 1
	fi
 
    if (( current > max ));then
        echo >&2 "current value must be smaller than max. value"
        return 1
    fi
 
    percent=$(awk -v "f=%${padding}.${decimal}f" -v "c=$current" -v "m=$max" 'BEGIN{printf('f', c / m * 100)}')
 
    (( chars = current * width / max))
 
    # sprintf n zeros into the var named as the arg to -v
    printf -v complete '%0*.*d' '' "$chars" ''
    printf -v remain '%0*.*d' '' "$((width - chars))" ''
 
    # replace the zeros with the desired char
    complete=${complete//0/"$completed_char"}
    remain=${remain//0/"$uncompleted_char"}
 
    printf '%s%s%s%s %s%s\r' "$prefix" "$complete" "$remain" "$suffix" "$percent" "$percent_sign"
 
	if (( current >= max )); then
		echo ""
	fi
}
 
if [ ! -z $1 ] && [ $lib_script_name = "lib_main" ]; then
	"$1" "$2" "$3" "$4" "$5" "$6" "$7" "$8" "$9" "${10}" "${11}" "${12}" "${13}" "${14}" "${15}"
fi

Here are some examples to demostrate how it works:

# [#########################################..........................................] 50.0%
for i in {1..100}; do
	lib_progress_bar $i 100
done
# [@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@__________________________________________] 50.0%
for i in {1..100}; do
	lib_progress_bar -c '@' -u ' ' $i 100
done
#   (******************************                            ) 50 percent
for i in {1..754}; do
	lib_progress_bar -c '*' -u '-' -d 0 -p '   (' -s ')' -% ' percent' -m 75 $i 754
done
#|******.......|-- 50.00%
for i in {1..100}; do
	lib_progress_bar -c '*' -u '.' -d 2 -p '|' -s '|--' -% ' ' -m 25 $i 100
done
# [######################----------------------] 51.43%
for i in {1..1241}; do
	lib_progress_bar -d 2 -m 55 $i 1241
done


Finally, here is a real-world example showing how to use it for blocking TOR nodes:


#!/bin/bash
 
IPTABLES_TARGET="DROP"
IPTABLES_CHAINNAME="TOR"
 
WORKING_DIR="/tmp/"
 
# get IP address of eth0 network interface
IP_ADDRESS=$(ifconfig eth0 | awk '/inet addr/ {split ($2,A,":"); print A[2]}')
 
if ! iptables -L "$IPTABLES_CHAINNAME" -n >/dev/null 2>&1 ; then			#If chain doesn't exist
	iptables -N "$IPTABLES_CHAINNAME" >/dev/null 2>&1				#Create it
fi
 
cd $WORKING_DIR
 
wget -q -O - http://proxy.org/tor_blacklist.txt -U NoSuchBrowser/1.0 > temp_tor_list1
sed -i 's|RewriteCond %{REMOTE_ADDR} \^||g' temp_tor_list1
sed -i 's|\$.*$||g' temp_tor_list1
sed -i 's|\\||g' temp_tor_list1
sed -i 's|Rewrite.*$||g' temp_tor_list1
 
wget -q -O - "https://check.torproject.org/cgi-bin/TorBulkExitList.py?ip=$IP_ADDRESS&port=80" -U NoSuchBrowser/1.0 > temp_tor_list2
wget -q -O - "https://check.torproject.org/cgi-bin/TorBulkExitList.py?ip=$IP_ADDRESS&port=9998" -U NoSuchBrowser/1.0 >> temp_tor_list2
sed -i 's|^#.*$||g' temp_tor_list2
 
iptables -F "$IPTABLES_CHAINNAME"
 
CMD=$(cat temp_tor_list1 temp_tor_list2 | uniq | sort)
UBOUND=$(echo "$CMD" | grep -cve '^\s*$')
 
for IP in $CMD; do
	let COUNT=COUNT+1
	lib_progress_bar $COUNT $UBOUND
	iptables -A "$IPTABLES_CHAINNAME" -s $IP -j $IPTABLES_TARGET
done
 
iptables -A "$IPTABLES_CHAINNAME" -j RETURN
 
rm temp_tor*

	




No tags

Theme Design by devolux.nh2.me

Clouds in my coffee

Welcome to my blog. Things will be changing a lot while I settle in.
To top