perl search mysql - help

[brosskgm]

With being total new at this, for several month’s now to try and figure out how to open and search a database for the $id obtained from a web page and get the users email address and phone address associated with that id.

I have the script working now with out the search but it requires me having to hand code the perl script for each user. Would like to stream line the process to use a database.

In the database needing to search there is 3 tables, I need to search for the ticket_id and the username
that is their email address. The other field has their cell. But could be blank.

database tables

username: [email protected]
ticket_id: 1-YS25UHRN3N9D
phone: [email protected]

I was told putting the database login information in the cgi was bad, but I don’t know how to work it into
another file and access the file from perl or other way yet.

---

[php]
#!/usr/bin/perl -W

use CGI;
use CGI param;
use POSIX qw/strftime/;
use mysql;

database

$host = “localhost”;
$database = “users”;
$tablename = “tickets”;
$user = “username”;
$pw = “password”;

Obtain $id & $ip from web page.

$id = param(“id”);
$ip = param(“ip”);

message

1 = email

2 = phone

3 = both

$message = param(“send_me”);

Connect to database

$connect = Mysql->connect($host, $database, $user, $pw);

DB

$connect->selectdb($database);

MySQL QUERY

$myquery = “SELECT user_id FROM user”;

EXECUTE

$execute = $connect->query($myquery);

QUERY

$myquery = “SELECT ticket_id,username,phone FROM $tablename”;

#check if we have a matching $id == $ticket_id
if ($id =~ /$ticket_id/) {
#if we have a match open the file to write.
$file = “/var/log/$ticket_id_iptrace.txt”;
open(FILE, “>$file”);

  # lets put the time in the file.
  print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );

  # put the IP address and web page visited.
  print FILE " -- IP Address: $ENV{REMOTE_ADDR}\n\t Accessed your page: $ENV{HTTP_REFERER}\n------\n";
  print FILE " -- Here is the information we found:\n------\n";

  #close the file. Finished with the first part
  close(FILE);

  # Run the IP2Location script.
  system("./iptrace.sh $ENV{REMOTE_ADDR} json city>>/var/log/$ticket_id_iptrace.txt");

  # Lets send an email and or text with all the information.
  if($message =~ /1/) {
         system("mail -s 'Web Page visited' $username \< /var/log/$ticket_id_iptrace.txt");
  }
  if($message =~ /2/) {
          system("mail -s 'Web Page visited' $phone \< /var/log/$ticket_id_iptrace.txt");
  }
  if($message =~/3/) {
          system("mail -s 'Web Page visited' $username \< /var/log/$ticket_id_iptrace.txt");
          system("mail -s 'Web Page visited' $phone \< /var/log/$ticket_id_iptrace.txt");
  }

  exit();

}

“To Do” send a page or popup if the id is not valid.

$file = “/var/log/invalid_id_iptrace.txt”;
open(FILE, “>$file”);
print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILE " – IP Address: $ip\n\t Accessed unknown $id:\n------\n";
close(FILE);
system(“mail -s ‘Page visited Unknown’ [email protected] < /var/log/invalid_id_iptrace.txt”);
exit();
#end of script

[/php]