1#!/usr/local/bin/perl 2# Please note that some MySQL experience is required to use this script. 3# 4# You must have the appropriate tools installed for MySQL and Perl to "talk" 5# to each other... specifically perl dbi and DBD::mysql 6# 7# You must set up the following table in your mysql database: 8# 9# +----------+-------------+------+-----+---------+-------+ 10# | Field | Type | Null | Key | Default | Extra | 11# +----------+-------------+------+-----+---------+-------+ 12# | nickname | varchar(40) | YES | | NULL | | 13# | email | varchar(40) | YES | | NULL | | 14# | birthday | varchar(40) | YES | | NULL | | 15# +----------+-------------+------+-----+---------+-------+ 16# 17# I suggest you set up a separate user in the mysql database to use this script 18# with only permission to SELECT from this database. 19# </paranoia> 20# 21# In the script you must replace the following variables with your information: 22# 23# $d = database name 24# $u = user login for database 25# $p = user password 26# 27# if you choose to make this accessible by users on a user-list only, create 28# a text file called "users" in your home .irssi directory, add the nicknames 29# of users you wish to give access in this format: 30# 31# PrincessLeia2 32# R2D2 33# Time 34# 35# AND uncomment the 3 sections indicatated in the script 36# 37# I never created an interface to add new nicknames, email, and birthday, 38# so you will need to manually insert this information into the database 39# 40# This script allows a user to search the database by using the command ~search nickname 41# (in channel, or in pm) it will respond with a private message. It will match full and 42# partial nicknames while it does it's search (if you search for 't' it will give you 43# results of any nicknames with a 't' i nthem) 44# 45# Personally, I run this in an ircbot, as the owner of this script cannot use 46# the ~search command themselves 47# 48# 49# ... That's about it, enjoy! 50# 51 52use strict; 53use Irssi; 54use DBI; 55use vars qw($VERSION %IRSSI); 56 57$VERSION = "1.0"; 58%IRSSI = ( 59 authors => 'PrincessLeia2', 60 contact => 'lyz\@princessleia.com ', 61 name => 'emaildb', 62 description => 'a script for accessing an email mysql database through irc', 63 license => 'GNU GPL v2 or later', 64 url => 'http://www.princessleia.com/' 65); 66 67 68# uncomment the following commented (and replace '/home/user' with your home directory) lines for user restricted access. 69# 70# open ( LIST, "</home/lyz/.irssi/users" ) or die "can't open users:$!\n"; 71# chomp( @user = <LIST> ); 72# close LIST; 73 74my $d = ('database'); 75my $u = ('user'); 76my $p = ('password'); 77 78 79sub event_privmsg { 80my ($server, $data, $nick, $mask, $target) =@_; 81my ($target, $text) = $data =~ /^(\S*)\s:(.*)/; 82 if ($text =~ /^~search */i ) { 83 84# Uncomment the following commented lines for user restricted access 85# foreach $person (@user) { 86# if ($nick =~ /^$person$/i) { 87 88 my ($nickname) = $text =~ /^~search (.*)/; 89 90 my $dbh = DBI->connect("DBI:mysql:$d","$u","$p") 91 or die "Couldn't connect to database: " . DBI->errstr; 92 my $sth = $dbh->prepare("SELECT * FROM 13th where nickname like \"\%$nickname\%\";") 93 or die "Cant prepare statement: $dbh->errstr\n"; 94 my $rv = $sth->execute 95 or die "cant execute the query: $sth->errstr\n"; 96if ($rv >= 1) { 97 my @row; 98 while ( @row = $sth->fetchrow_array( ) ) { 99 my $n = "$row[0]\n"; 100 my $e = "$row[1]\n"; 101 my $b = "$row[2]\n"; 102 $server->command ( "msg $nick Nickname : $n" ); 103 $server->command ( "msg $nick Email : $e" ); 104 $server->command ( "msg $nick Birthday : $b" ); 105} 106} 107else { 108 $server->command ( "msg $nick Sorry, No Results Match Your Query\n" ); 109 } 110 111# Uncomment the following commented lines for user restricted access 112#} 113#} 114 115} 116} 117 118Irssi::signal_add('event privmsg', 'event_privmsg'); 119