vcherubini
Programmer
Hey guys.
I just got done writing a script that allows a user to search a database, but this one gets down and allows the user to specify what tables and fields to search under.
Right now, you can select multiple tables to search in the database, and search a field below that table.
i.e., if you have a database called forums, and there was a threads field, and below that threads field, you could search a subject field, or something like that.
These scripts are not made to be used by the public, but rather by an admin of a database.
Here they are.
[tt]
# search.pl
#!E:/perl/bin/perl
#----
# made by Vic Cherubini, 2001, krs-one@cnunited.com
# Easily search an entire database on a server.
# Please keep all of this content in here. You can give this out as much as you like
# Any suggestions, tips, or addons you have made, I am always greatful.
# Email them to me at krs-one@cnunited.com
# Thanks!
#----
use strict;
use Mysql;
print "Content-type:text/html\n\n";
# $usedb = use this database as the database to search
my $usedb = "cnunited";
my $host = "localhost";
my $user = "vikter";
my $pass = "morpheus";
my $dbh = Mysql->connect($host,$usedb,$user,$pass);
my @dbs = $dbh->listdbs() unless ($usedb);
my @tables;
push @dbs,$usedb unless (!$usedb);
print "<html><head><title>search</title>\n";
print "</head><body>\n";
print "<form action=\"results.pl\" method=\"post\">\n";
print "<table border=0 width=100% bgcolor=black cellspacing=1>\n";
print "<tr><td width=100% bgcolor=silver style=\"padding:5px\">\n";
print "<b>Search Text:</b> <input type=\"text\" name=\"query\"></td></tr></table><br><br>\n";
print "<table border=0 width=100% cellspacing=1 bgcolor=black>\n";
for my $i (0..$#dbs) {
$dbh->selectdb($dbs[$i]);
push (@tables,$dbh->listtables($dbs[$i]));
print "<tr><td width=100% bgcolor=gray><input type=\"radio\" name=\"database\" value=\"$dbs[$i]\"><b>".$dbs[$i]."</b></td></tr>\n";
print "<tr><td width=100% style=\"padding-left:20px;\" bgcolor=silver>\n";
for my $j (0..$#tables) {
my $query = "SELECT * FROM $tables[$j]";
my $sth = $dbh->query($query);
my $intnumrows = $sth->numrows();
my $intnumfields = $sth->{NUM_OF_FIELDS};
my @arrfields;
for (my $k=0; $k<$intnumfields; $k++) {
push @arrfields,$sth->{NAME}->[$k];
}
print "<input type=\"checkbox\" name=\"table[$j]\" value=\"$tables[$j]\"><small>$tables[$j] ($intnumrows)</small><br>\n";
print " ";
my $k=0;
foreach my $fields (@arrfields) {
if ($k>11) {
$k=0;
print "<br>";
}
print "<input type=\"radio\" name=\"table[$j]field[$k]\" value=\"$fields\">$fields";
$k++;
}
print "<br>";
}
print "</td></tr>\n";
@tables = ();
}
print "</table><br>\n";
print "<input type=\"submit\">\n";
print "</form></body></html>\n";
[/tt]
And
[tt]
# results.pl
#!E:/perl/bin/perl
#----
# made by Vic Cherubini, 2001, krs-one@cnunited.com
# Easily search an entire database on a server.
# Please keep all of this content in here. You can give this out as much as you like
# Any suggestions, tips, or addons you have made, I am always greatful.
# Email them to me at krs-one@cnunited.com
# Thanks!
#----
use strict;
use Mysql;
print "Content-type:text/html\n\n";
check_method();
my %FORM = parse_form();
check_query($FORM{'query'});
print "Search on database: <b>".$FORM{'database'}."</b> with query \"".$FORM{'query'}."\".<br><br>";
my $host = "localhost";
my $user = "vikter";
my $pass = "morpheus";
my $dbh = Mysql->connect($host,$FORM{'database'},$user,$pass);
my @arrtables = $dbh->listtables([$FORM{'database'}]);
for my $i (0..$#arrtables) {
my $query = "SELECT * FROM ". $FORM{'table['.$i.']'}."";
my $sth = $dbh->query($query);
my $intnumfields = $sth->{NUM_OF_FIELDS};
for (my $j=0; $j<$intnumfields; $j++) {
my $field = $FORM{'table['.$i.']field['.$j.']'};
my $sqlquery = "SELECT * FROM ".$FORM{'table['.$i.']'}." WHERE ". $FORM{'table['.$i.']field['.$j.']'} ." LIKE '%".$FORM{'query'}."%'";
unless (!defined($FORM{'table['.$i.']field['.$j.']'})) {
my $sth2 = $dbh->query($sqlquery);
my $numrows = $sth2->rows();
print "Table: <b>".$FORM{'table['.$i.']'}."</b><br>";
print " Field: <b>".$FORM{'table['.$i.']field['.$j.']'}."</b><br>";
for (my $k=0; $k<$numrows; $k++) {
my $hashref = $sth2->fetchrow_hashref();
$hashref->{$field} =~ s/\</</g;
$hashref->{$field} =~ s/\>/>/g;
if (get_result_size($hashref->{$field}) >= 128) {
$hashref->{$field} = get_snippet($hashref->{$field});
}
my $temp += $k + 1;
print " $temp. <i><small>".$hashref->{$field}."</small></i><br>";
}
print "Found $numrows results.<br>";
print "<hr>";
}
}
}
sub parse_form {
my ($buffer,$pair,$name,$value,%FORM,@pairs);
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs){
($name, $value) = split(/=/, $pair);
$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
if ($FORM{$name}) {
$FORM{$name} = "$FORM{$name}, $value";
} else {
$FORM{$name} = $value;
}
}
return %FORM;
}
sub check_method {
if ($ENV{'REQUEST_METHOD'} eq "GET"
{
print "This page only allows POST request methods";
die;
}
}
sub get_result_size {
my $result = shift;
return length($result);
}
sub get_snippet {
my $text = shift;
return substr($text,0,128)." . . . ";
}
sub check_query {
my $query = shift;
if (!$query) {
print "You need to specify a query to search for.";
die;
}
}
[/tt]
Save the first on as search.pl, and the other one as results.pl.
I just wrote these today, so I am sure they are pretty bug filled, but, if anyone wants to play around with them and report back what you like and don't like, I am always appreciative.
I am sure the forum sages could pick them apart piece by piece, and thats cool, it can only help.
In case those don't work, you can download them here:
and in case that does not work, you can always email me at krs-one@cnunited.com, and I can email them to you.
Thanks a lot and have fun messing with the scripts, I think you will like them.
-Vic vic cherubini
krs-one@cnunited.com
====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash
====
I just got done writing a script that allows a user to search a database, but this one gets down and allows the user to specify what tables and fields to search under.
Right now, you can select multiple tables to search in the database, and search a field below that table.
i.e., if you have a database called forums, and there was a threads field, and below that threads field, you could search a subject field, or something like that.
These scripts are not made to be used by the public, but rather by an admin of a database.
Here they are.
[tt]
# search.pl
#!E:/perl/bin/perl
#----
# made by Vic Cherubini, 2001, krs-one@cnunited.com
# Easily search an entire database on a server.
# Please keep all of this content in here. You can give this out as much as you like
# Any suggestions, tips, or addons you have made, I am always greatful.
# Email them to me at krs-one@cnunited.com
# Thanks!
#----
use strict;
use Mysql;
print "Content-type:text/html\n\n";
# $usedb = use this database as the database to search
my $usedb = "cnunited";
my $host = "localhost";
my $user = "vikter";
my $pass = "morpheus";
my $dbh = Mysql->connect($host,$usedb,$user,$pass);
my @dbs = $dbh->listdbs() unless ($usedb);
my @tables;
push @dbs,$usedb unless (!$usedb);
print "<html><head><title>search</title>\n";
print "</head><body>\n";
print "<form action=\"results.pl\" method=\"post\">\n";
print "<table border=0 width=100% bgcolor=black cellspacing=1>\n";
print "<tr><td width=100% bgcolor=silver style=\"padding:5px\">\n";
print "<b>Search Text:</b> <input type=\"text\" name=\"query\"></td></tr></table><br><br>\n";
print "<table border=0 width=100% cellspacing=1 bgcolor=black>\n";
for my $i (0..$#dbs) {
$dbh->selectdb($dbs[$i]);
push (@tables,$dbh->listtables($dbs[$i]));
print "<tr><td width=100% bgcolor=gray><input type=\"radio\" name=\"database\" value=\"$dbs[$i]\"><b>".$dbs[$i]."</b></td></tr>\n";
print "<tr><td width=100% style=\"padding-left:20px;\" bgcolor=silver>\n";
for my $j (0..$#tables) {
my $query = "SELECT * FROM $tables[$j]";
my $sth = $dbh->query($query);
my $intnumrows = $sth->numrows();
my $intnumfields = $sth->{NUM_OF_FIELDS};
my @arrfields;
for (my $k=0; $k<$intnumfields; $k++) {
push @arrfields,$sth->{NAME}->[$k];
}
print "<input type=\"checkbox\" name=\"table[$j]\" value=\"$tables[$j]\"><small>$tables[$j] ($intnumrows)</small><br>\n";
print " ";
my $k=0;
foreach my $fields (@arrfields) {
if ($k>11) {
$k=0;
print "<br>";
}
print "<input type=\"radio\" name=\"table[$j]field[$k]\" value=\"$fields\">$fields";
$k++;
}
print "<br>";
}
print "</td></tr>\n";
@tables = ();
}
print "</table><br>\n";
print "<input type=\"submit\">\n";
print "</form></body></html>\n";
[/tt]
And
[tt]
# results.pl
#!E:/perl/bin/perl
#----
# made by Vic Cherubini, 2001, krs-one@cnunited.com
# Easily search an entire database on a server.
# Please keep all of this content in here. You can give this out as much as you like
# Any suggestions, tips, or addons you have made, I am always greatful.
# Email them to me at krs-one@cnunited.com
# Thanks!
#----
use strict;
use Mysql;
print "Content-type:text/html\n\n";
check_method();
my %FORM = parse_form();
check_query($FORM{'query'});
print "Search on database: <b>".$FORM{'database'}."</b> with query \"".$FORM{'query'}."\".<br><br>";
my $host = "localhost";
my $user = "vikter";
my $pass = "morpheus";
my $dbh = Mysql->connect($host,$FORM{'database'},$user,$pass);
my @arrtables = $dbh->listtables([$FORM{'database'}]);
for my $i (0..$#arrtables) {
my $query = "SELECT * FROM ". $FORM{'table['.$i.']'}."";
my $sth = $dbh->query($query);
my $intnumfields = $sth->{NUM_OF_FIELDS};
for (my $j=0; $j<$intnumfields; $j++) {
my $field = $FORM{'table['.$i.']field['.$j.']'};
my $sqlquery = "SELECT * FROM ".$FORM{'table['.$i.']'}." WHERE ". $FORM{'table['.$i.']field['.$j.']'} ." LIKE '%".$FORM{'query'}."%'";
unless (!defined($FORM{'table['.$i.']field['.$j.']'})) {
my $sth2 = $dbh->query($sqlquery);
my $numrows = $sth2->rows();
print "Table: <b>".$FORM{'table['.$i.']'}."</b><br>";
print " Field: <b>".$FORM{'table['.$i.']field['.$j.']'}."</b><br>";
for (my $k=0; $k<$numrows; $k++) {
my $hashref = $sth2->fetchrow_hashref();
$hashref->{$field} =~ s/\</</g;
$hashref->{$field} =~ s/\>/>/g;
if (get_result_size($hashref->{$field}) >= 128) {
$hashref->{$field} = get_snippet($hashref->{$field});
}
my $temp += $k + 1;
print " $temp. <i><small>".$hashref->{$field}."</small></i><br>";
}
print "Found $numrows results.<br>";
print "<hr>";
}
}
}
sub parse_form {
my ($buffer,$pair,$name,$value,%FORM,@pairs);
read(STDIN, $buffer, $ENV{'CONTENT_LENGTH'});
@pairs = split(/&/, $buffer);
foreach $pair (@pairs){
($name, $value) = split(/=/, $pair);
$name =~ tr/+/ /;
$name =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$value =~ tr/+/ /;
$value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
if ($FORM{$name}) {
$FORM{$name} = "$FORM{$name}, $value";
} else {
$FORM{$name} = $value;
}
}
return %FORM;
}
sub check_method {
if ($ENV{'REQUEST_METHOD'} eq "GET"
{print "This page only allows POST request methods";
die;
}
}
sub get_result_size {
my $result = shift;
return length($result);
}
sub get_snippet {
my $text = shift;
return substr($text,0,128)." . . . ";
}
sub check_query {
my $query = shift;
if (!$query) {
print "You need to specify a query to search for.";
die;
}
}
[/tt]
Save the first on as search.pl, and the other one as results.pl.
I just wrote these today, so I am sure they are pretty bug filled, but, if anyone wants to play around with them and report back what you like and don't like, I am always appreciative.
I am sure the forum sages could pick them apart piece by piece, and thats cool, it can only help.
In case those don't work, you can download them here:
and in case that does not work, you can always email me at krs-one@cnunited.com, and I can email them to you.
Thanks a lot and have fun messing with the scripts, I think you will like them.
-Vic vic cherubini
krs-one@cnunited.com
====
Knows: Perl, HTML, JavScript, C/C++, PHP, Flash
====