#!/usr/bin/perl # PerlBill - Incoming Email Management # email.cgi # # Use of this script requires an appropriate license from perlbill.com # if you have not purchased this software or have received it from a # source other than perlbill.com - please report it to abuse@perlbill.com # # Path to CONF.CGI $DATA_PATH = "/srv/www/htdocs/web1/html/cgi-bin/perlbill/include"; use lib "/srv/www/htdocs/web1/html/cgi-bin/perlbill/include/lib/mods"; # No Further Editing Required! use CGI qw(:standard); use DBI(); use Crypt::RC4; BEGIN { $Class::Date::WARNINGS=0; } use Class::Date; require "$DATA_PATH/conf.cgi"; require "$DATA_PATH/lang/en.inc"; # No further editing required $dbh = dbh_connect(); sub dbh_connect { open(DBINFO, "<$DATA_PATH/lib/dbaccess.db") or die "could not open database file"; my $dbstring = ; close(DBINFO);open(DBINFO, "<$DATA_PATH/lib/dbpass.db") or die "could not open database password file"; my $cdbpass = ; close(DBINFO);($dbhost,$dbname,$dbuser,$key) = split(/\|/, $dbstring);$string = "PdEsaA3B" . $key; $dbpass = RC4( $string, $cdbpass ); $dbh= DBI->connect("DBI:mysql:$dbname:localhost","$dbuser","$dbpass") or die("Could not connect to the database user $dbhost $dbuser"); return $dbh;}sub script_error{my $error="@_";print "Content-type: text/html\n\n";print qq~PerlBill Error

 

PerlBill: Script Error
   
Perlbill was unable to launch due to the following errors:

$error



 

~; exit; } use MIME::Parser; use Mail::Address; use MIME::Entity; use MIME::Body; use Data::Dumper; $parser = new MIME::Parser; $parser->ignore_errors(1); $parser->output_to_core(1); my $MIME_entity = $parser->parse(\*STDIN); my $error = ($@ || $parser->last_error); $header = $MIME_entity->head; $subject = $header->get('Subject') || "no subject"; $cto = $header->get('To'); $from = $header->get('From'); @to_addresses = Mail::Address->parse($cto); @from_addresses = Mail::Address->parse($from); my $address; if (@to_addresses) { $to = $to_addresses[0]->address(); } else { exit; } if (@from_addresses) { $address = $from_addresses[0]->address(); } else { exit; } if ($MIME_entity->parts > 0) { for (my $i=0;$i<$MIME_entity->parts;$i++) { my $subEntity = $MIME_entity->parts($i); #~~ # Get the $body of the email #~~ my $ignore_plain = "0"; my $ignore_html = "0"; $ishtml = "1" if $subEntity->mime_type eq 'text/html'; $ishtml = "0" if $subEntity->mime_type eq 'text/plain'; #~~ # This is a multipart/alternative message if ($subEntity->mime_type eq 'multipart/alternative' && !$ignore_all ) { for (my $j=0;$j<$subEntity->parts;$j++) { my $subSubEntity = $subEntity->parts($j); if ($subSubEntity->mime_type eq 'text/plain' && $ignore_plain == "0" && !$ignore_all) { if (my $io = $subSubEntity->open("r")) { while (defined($_=$io->getline)) { $_ =~ s/"/\"/g; $body .= $_; } $ignore_all = 1; last; } } } } #~~ # Test / Plain Email Body if ($subEntity->mime_type eq 'text/plain' && $ignore_plain == "0" && !$ignore_all) { if (my $io = $subEntity->open("r")) { while (defined($_=$io->getline)) { $_ =~ s/"/\"/g; $body .= $_; } $io->close; $ignore_html = 1; $ignore_all = 1; last; } } #~~ # Text / HTML Email Body if ($subEntity->mime_type eq 'text/html' && $ignore_html == "0" && !$ignore_all) { if (my $io = $subEntity->open("r")) { while (defined($_=$io->getline)) { $_ =~ s/"/\"/g; $body .= $_; } $io->close; $ignore_plain = 1; $ignore_all = 1; } } $id++; } } else { $body = join "", @{$MIME_entity->body}; } $to = $1 if $to =~ /<(\S+)>/; if ($from =~ /<(\S+)>/) { $newfrom = $1 } else { $newfrom = $from } chomp($newfrom); #~~ # Remove JavaScript From BODY #~~ $body =~ s#]*>.*?]*>#NOTE: This email contained Javascript, which was removed upon arrival#ig; $body =~ s#document.cookie##gi; #~~ # Remove FORM tags #~~ $body =~ s#
#<\;form>\;#gi; $body =~ s#
#<\;\/form>\;#gi; #~~ if ($from =~ /<(\S+)>/) { $newfrom = $1 } else { $newfrom = $from } $body =~ s/"/\"/g; my $statement = 'SELECT * FROM settings'; my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { my $setting = $ref->{'setting'}; $global{$setting} = $ref->{'value'}; } $sth->finish; if ((!$subject) || ($subject eq "")) { email( To => "$newfrom", From => "$ticketad", Subject => "Help Desk Response", Body => "Please send your request with a subject line intact, if you are replying to a staff response, please keep the subject line intact for tracking purposes. If this is a new request, please give it a suitable subject.\n\nThank you" ); exit; } &get_time(); $epre = $global{'epre'}; if ((!$subject) || ($subject eq "")) { my $ibody .= "Please resend your request with a subject line intact, if you "; $ibody .= "are replying to a staff response, please keep the subject line intact for tracking purposes. If this is a new request, please give it a suitable subject line."; $ibody .= "\n\n"; $ibody .= "Thank You."; email( To => "$newfrom", From => "$global{'adminemail'}", Subject => "Your Response ", Body => "$ibody"); exit; } #~~ # Remove JavaScript From BODY #~~ $body =~ s#]*>.*?]*>#NOTE: This email contained Javascript, which was removed upon arrival#ig; #~~ # Check to see if the FROM address has been banned #~~ my ($alias, $domain) = split(/\@/, $newfrom); my $statement = qq|SELECT * FROM blocked_email WHERE (address = "$domain" OR address = "$newfrom")|; my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; if ($sth->rows > 0) { exit; } # It found a result, so exit the parsing $sth->finish; #~~ # Is this a response? #~~ if ($subject =~ /\{$epre-/gi) { $callid = $1 if $subject =~ /\{(\S+)\}/; $callno = $callid; $callno =~ s/$epre-//g; $statement = 'SELECT username,email,status,category,subject FROM calls WHERE id = ?'; $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($callno) or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $user = $ref->{'username'}; $callemail = $ref->{'email'}; $status = $ref->{'status'}; $category = $ref->{'category'}; $subj = $ref->{'subject'}; } if ($status eq "CLOSED") { $dbh->do(qq|UPDATE calls SET status = "OPEN" WHERE id = "$callno"|); } $callemail = lc($callemail); $newfrom = lc($newfrom); if ($callemail ne $newfrom) { my $ibody .= "Sorry, you seem to have sent this help desk response to a call which "; $ibody .= "is not owned by you. If you believe this to be a mistake, please check "; $ibody .= "that you are sending this response from the email address used when logging this call.\n\n"; $ibody .= "Thank You."; email( To => "$newfrom", From => "$global{'adminemail'}", Subject => "\{$callid\} Help Desk Submission ", Body => "$ibody"); exit; } $sth = $dbh->prepare( "INSERT INTO notes VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" ) or die "couldnt prepare statement"; $sth->execute( "NULL", "0", "1", "NULL", $callno, $user, $hdtime, $body, "", "" ) or die "Couldnt execute statement"; $time = time(); $dbh->do(qq|UPDATE calls SET active = "$time" WHERE id = "$callno"|); $statement = qq|UPDATE calls SET aw = "1" WHERE id = "$callno"|; $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; $statement = qq|SELECT * FROM staff WHERE access LIKE "%$category::%" OR access LIKE "%GLOB::%" OR access="admin"|; $sth = $dbh->prepare($statement)or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { if ($ref->{'notify'} == "1") { my $cbody .= "There is a new email response made by the user in request $callid. \n"; $cbody .= "\nCall Details\n"; $cbody .= "---------------------------------------\n"; $cbody .= "\tResponse by......: $newfrom\n"; $cbody .="\tCategory.........: $category\n"; $cbody .= "\tSubject..........: $subj\n"; $cbody .= "---------------------------------------\n"; $cbody .= "\n\nThank You."; email( To => "$ref->{'email'}", From => "$global{'adminemail'}", Subject => "Help Desk Response (USER)", Body => "$cbody"); } } $sth->finish; exit; } else { #~~ # Log a New Ticket #~~ $statement = qq|SELECT * FROM em_forwarders WHERE address = "$to"|; $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $category = $ref->{'category'}; $requirereg = $ref->{'req_reg'}; $send_res = $ref->{'respond'}; $fromemail = $ref->{'address'}; } if ($sth->rows == "0") { $dbody .= "Sorry you have sent this email to an un-assigned address which has been setup with PerlBill\n\n"; $dbody .= "The address ($to) must be setup in the perlbill admin in order to receive incoming submissions"; $dbody .= "Thank You."; email( To => "$newfrom", From => "$global{'adminemail'}", Subject => "E-Mail Error", Body => "$dbody"); exit; } $sth->finish; if ($requirereg == "1") { $current_time = time(); $statement = 'SELECT value FROM settings WHERE setting = "floodwait"'; $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $global{'floodwait'} = $ref->{'value'}; } $sth->finish; $statement = 'SELECT username, email, url, fname, lname FROM users WHERE email = ?'; $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute($newfrom) or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $username = $ref->{'username'}; $name = $ref->{'fname'} . ' ' . $ref->{'lname'}; $url = $ref->{'url'}; $email = $ref->{'email'}; my $newtime = $ref->{'lcall'}; $newtime = $newtime + $global{'floodwait'}; exit if $newtime > $current_time; } if ($sth->rows == "0") { my $abody .= "Sorry, you do not seem to have an account at the help desk. \n"; $abody .= "To submit requests via e-mail you must register. To do so please visit:\n\n"; $abody .= "$global{'baseurl'}/$mainfile\n\n"; $abody .= "Thank You."; email( To => "$newfrom", From => "$fromemail", Subject => "Your Email Submission", Body => "$abody"); exit; } $sth->finish; $subject =~ s/\n//g; $body =~ s/\r//g; $body =~ s/\n/
/g if $ishtml == "0"; $body =~ s/\n//g if $ishtml == "1"; $mbody = $dbh->quote($body); $msubject = $dbh->quote($subject); $current_time = time(); my $dsth = $dbh->prepare( "INSERT INTO calls VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ); $dsth->execute( "NULL", "OPEN", $username, "$newfrom", "3", $category, $subject, $body, $hdtime, "Unowned", "NULL", "em", $current_time, $current_time, $current_time, "", "1", "0" ); $trackno = $dbh->{'mysql_insertid'}; my $statement = 'SELECT * FROM ticket_fields WHERE (name = "email" OR name = "e-mail") LIMIT 1'; my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { my $sth = $dbh->prepare( "INSERT INTO call_fields VALUES ( ?, ?, ?, ? )" ) or die $DBI->errstr; $sth->execute( "NULL", $trackno, $ref->{'id'}, "$newfrom" ) or die $DBI->errstr; } notify_tech(); if ($send_res == "1") { $ibody = ""; open (MAILNEWTPL,"$global{'data'}/include/tpl/newticket.txt"); while () { lang_parse(); if ($_ =~ /\{*\}/i) { s/\{name\}/$name/g; s/\{baseurl\}/$global{'baseurl'}/g; s/\{subject\}/$subject/g; s/\{description\}/$body/g; s/\{mainfile\}/client\.cgi/g; s/\{date\}/$hdtime/g; } $ibody .= "$_"; } email( To => "$newfrom", From => "$fromemail", Subject => "\{$epre-$trackno\} RE: $subject", Body => "$ibody"); } } elsif ($requirereg == "0") { $statement = 'SELECT username, fname, lname, url FROM users WHERE email = ?'; $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute( $newfrom ) or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $username = $ref->{'username'}; $name = $ref->{'fname'} . ' ' . $ref->{'lname'}; $url = $ref->{'url'}; $fname = $ref->{'fname'}; } if ($sth->rows == "0") { if (!$username) { $username = "Unregistered"; } if (!$name) { $name = "Unregistered"; } if (!$url) { $url = "Unregistered"; } } $sth->finish; $subject =~ s/\n//g; $body =~ s/\r//g; $body =~ s/\n/
/g if $ishtml == "0"; $body =~ s/\n//g if $ishtml == "1"; $mbody = $dbh->quote($body); $msubject = $dbh->quote($subject); $current_time = time(); my $dsth = $dbh->prepare( "INSERT INTO calls VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ); $dsth->execute( "NULL", "OPEN", $username, "$newfrom", "3", $category, $subject, $body, $hdtime, "Unowned", "NULL", "em", $current_time, $current_time, $current_time, "", "1", "0" ); $trackno = $dbh->{'mysql_insertid'}; my $statement = 'SELECT * FROM ticket_fields WHERE (name = "email" OR name = "e-mail") LIMIT 1'; my $sth = $dbh->prepare($statement) or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { my $sth = $dbh->prepare( "INSERT INTO call_fields VALUES ( ?, ?, ?, ? )" ) or die $DBI->errstr; $sth->execute( "NULL", $trackno, $ref->{'id'}, "$newfrom" ) or die $DBI->errstr; } notify_tech(); if ($send_res == "1") { $ibody = ""; open (MAILNEWTPL,"$global{'data'}/include/tpl/newticket.txt"); while () { lang_parse(); if ($_ =~ /\{*\}/i) { s/\{name\}/$fname/g if defined $fname; s/\{name\}/n\/a/gi if !$fname; s/\{subject\}/$subject/g; s/\{baseurl\}/$global{'baseurl'}/g; s/\{description\}/$mbody/g; s/\{date\}/$hdtime/g; s/\{mainfile\}/client\.cgi/g; } $ibody .= "$_"; } close(MAILNEWTPL); email( To => "$newfrom", From => "$fromemail", Subject => "\{$epre-$trackno\} RE: $subject", Body => "$ibody"); } } } $parser->filer->purge(); sub notify_tech { $statement = 'SELECT * FROM staff WHERE access LIKE "%' . "$category" . '::%" OR access LIKE "%GLOB::%" OR access="admin"'; $sth = $dbh->prepare($statement)or die "Couldn't prepare statement: $DBI::errstr; stopped"; $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; while(my $ref = $sth->fetchrow_hashref()) { $cabody = ""; if ($ref->{'notify'} eq "1") { $cabody = "There is a new help desk submission. \n"; $cabody .= "\nCall Details\n"; $cabody .= "-----------------------------------------\n"; $cabody .= "\tLogged by....: $newfrom\n"; $cabody .= "\tSubject......: $subject\n\n"; $cabody .= "\n"; $cabody .= "-----------------------------------------\n"; $cabody .= "\n\nLogin URL: $global{'baseurl'}/staff.cgi\n\n"; $cabody .= "\n\nThank You."; email( To => "$ref->{'email'}", From => "$global{'adminemail'}", Subject => "E-Mail Submission", Body => "$cabody"); } } $sth->finish; } sub lang_parse { if ($_ =~ /\%*\%/) { s/\%(\S+)\%/$LANG{$1}/g; } } 1;