My Random Blog

So at work we wanted to create a centralized syslog server. I had a great idea of why don't we log all user commands run on all servers into mysql. I knew about the bash-paranoia patch so that gave me the idea of using that as my basis for my mysql patch. This should apply to both 3.0 and 3.1 even with all the all the latest security patches included.

So first lets download the paranoia patch

wget http://e133.enemy.cx/xSH-paranoia/download/bash-paranoia.patch

Using that and compiling bash with ---enable-paranoia will allow you to log all user commands to syslog.

So now lets download my patch now

wget http://zcentric.com/bash-mysql.patch

Now if you want to apply it to bash 3.1 I will give a little howto on how to do that. All in 1 step

You want to create the following table in a mysql database

CREATE TABLE `logs` (
`rowid` mediumint(8) NOT NULL auto_increment
`host` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
`user` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
`as_user` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
`ip` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
`ut_line` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
`command` text character set utf8 collate utf8_unicode_ci NOT NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Now that mysql table is made lets install bash and patch it and all that good stuff

wget http://ftp.gnu.org/gnu/bash/bash-3.1.tar.gz
tar zxfv bash-3.1.tar.gz
cd bash-3.1
wget http://e133.enemy.cx/xSH-paranoia/download/bash-paranoia.patch
wget http://zcentric.com/bash-mysql.patch
patch -p0 < bash-paranoia.patch
patch -p1 < bash-mysql.patch
autoconf
./configure --enable-paranoia --prefix=/usr
make

Now the config file to let bash know where the mysql server is. Now if the mysql server is down bash shouldn't crash or anything.

So you have to create a file /etc/bash.conf and use the following lines.

SERVER=192.168.0.10
USER=username
PASS=password
DB=dbName

Now you should be able to like run /path/to/bash-3.1/bash and it should bring you to a new shell that is now the bash version with mysql. You should now be able to type commands it it will log to mysql!

You can then run make install if you wish to install

So you have a whole table full of members or places with latitude and longitude's associated with them. Just replace the $lat and $lon with the center point you want to find distances from. You can also change the distance<=10 to a number you want to search from. This will limit your results to all results that are under 10 miles from the starting point

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC

So I am writing this series to help others that might stumble upon this entry as a starting point for building a good web application from the start. Let me tell you that there is nothing worse then building up a web application just to figure out you need to make a change that requires a change in 8 other sections.

So a bit of background. I am the lead developer for Photoblog. Before doing Photoblog, I had around 5-6 years of PHP experience. I made many mistakes and have learned a lot from them. So please use this and the others I write to learn from my mistakes.

So my first and foremost advice is to create a wrapper class around the database driver. If you were one of the poor souls that didn't do this and wanted to move from php's mysql to their mysqli class then you had to change a ton of mysql_* commands. So that is where the benefits of a wrapper class comes in. When you need to make some changes, you just do it in the wrapper class.

Basically to make a simple mysql query this is what I do

$DB = new Database;
$DB->connect();
$DB->query("SELECT * FROM table WHERE id=1 LIMIT 1");
$DB->fetch_array();

Do you see the benefits to that? You might at first think that it is overkill to do this but its really not. You can do so much with a wrapper class. If you want to run an explain on each query or find the time to complete each query you just add it in the query() function in the wrapper class.

Doing this will save you time in the end.

I use to write sql queries like this

mysql_query("query here") or die(mysql_error());

So now I just do

$DB->query("query here");

In that query() function I can create my own error page if sql errors out. For Photoblog what I do is if the user is an admin it shows the SQL query and the error. If its a normal user it will email the admins the SQL error and just show a error page that the user can understand and say a email has been sent out.

So as you can see there are major benefits to creating a wrapper class for mysql. This is just the tip of the iceberg of what you can do with a wrapper class.