CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: DBI:
Perl MySQL help with code.

 



brosskgm
User

Feb 26, 2013, 3:18 PM

Post #1 of 76 (14516 views)
Perl MySQL help with code. Can't Post

I have been working on this for some time and with not really knowing Perl it’s been extremely difficult.

It does seem to be connecting to MySQL because there has not been any of those errors.

I can’t seem to search the database for the value needed.

A short PHP header sends the information the perl script processes.

Is there anyone that can help fix this perl code. I can even send a little by paypal if it’s not to much. The script is working now as long as I don’t try to use the mysql but I would have to hand code each new setup if I want to add more people to it.

The php code could be on any server, the perl will only be on one machine is why I don't put it all in the php page.

I only have 5 people using the setup, so it’s not all that critical, but I have been getting asked by others that would like to use it for their web site.

Before I do that I would like it to stream line it to use a database. Database already exists and can already have data entered by a form page.

#### PHP code that is already in use.


Code
<?php 
$ip = $_SERVER['REMOTE_ADDR']; // Client IP address

if(isset($_POST['referer'])) {
$l_sReferer = trim($_POST['referer']);
} elseif (isset($_SERVER['HTTP_REFERER'])) {
$l_sReferer = base64_encode($_SERVER['HTTP_REFERER']);
} else {
$l_sReferer = "";
}

$id = '1-YS25UHRN3N9D'; // Client id
$pg = '1'; // 1=email, 2=text, 3=email/text
$url = "http://192.168.4.4/cgi-bin/log_it.cgi"; // back-end url
echo '[removed][removed]';
?>


Echo is removed by forum but that part works.

I have tried many different samples of code, tried changing the declaration and param but still can not get it to search the database and retrieve the information on the user.

The database has


Code
username:  user-email-address 
subscr_id: 1-YS25UHRN3N9D
phone: 123456789/domain-to-send-text


########## Perl code…


Code
#!/usr/bin/perl -w 
use warnings;
use strict;
use diagnostics;
use CGI qw/param/;
use POSIX qw/strftime/;
use DBI;

print "ContentType: text/html\n\n";

my $host = "remote_host";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "pw";

# Obtain items from web page.
my $id = param("id");
my $ip = param("ip");
my $pg = param("pg");

#
# This seems to have problem
# From MySQL database
my $username = param("username");
my $subscr_id = param("subscr_id");
my $phone = param("phone");
##################
# to be re-worked to make file handling better
#
my $file = "_iptrace.txt";
my $filea = "/tmp/";
my $fileb = $subscr_id;
my $filec = ".txt";
my $filed = "/tmp/invalid_id_iptrace.txt";
my $phone;

# message
# 1 = email
# 2 = phone text message
# 3 = both
my $message = param("pg");

# Is not working. Needs to be changed/removed etc...
#
# my $dbh = DBI->connect('DBI:mysql:$database, $host','$user','$pw')
# or die "Connection Error: $DBI::errstr\n";
#
# my $sql = "SELECT subscr_id,username FROM $tabelname WHERE subscr_id = 1";
# my $sth = $dbh->prepare($sql);
# $sth->execute
# or die "SQL Error: $DBI::errstr\n";
# my @row = $sth->fetchrow_array();
# print "subscr_id\n";
#
# $sth = $dbh->finish();
#
#

if ($id eq 'subscr_id') {
#if we have a match open the file to write.
open(FILE, '>'."/tmp/$subscr_id\$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>>/tmp/$subscr_id$file");

# Lets send an email and or text with all the information.
# Send email
if($message == 1) {
system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file");
}
# Send text
if($message == 2) {
system("mail -s 'Web Page visited' $filea$fileb \< /tmp/$subscr_id$file");
}
# Send email and text message
if($message == 3) {
system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file");
system("mail -s 'Web Page visited' $filea$fileb \< /tmp/$subscr_id$file");
}
print "Content-type: text/javascript\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
# $dbh->disconnect();
exit();
}
#
# "To Do" send a page or popup if the id is not valid.
# for now send me the notice..
#

$filed = "/tmp/invalid_id_iptrace.txt";
open (FILED, '>'."$filed") or die $!;
print FILED strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILED " -- IP Address: $ip\n\t Access Page: $ENV{HTTP_REFERER} \n From id: $id with :pg as $pg\n------\n";
print FILED " -- Database ID: $subscr_id \n------\n";
close(FILED);
system("mail -s 'Page visited Unknown ID' admin\@domain.com \< $filed");
print "Content-type: text/javascript\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";

exit();
#end of script



FishMonger
Veteran / Moderator

Feb 26, 2013, 4:03 PM

Post #2 of 76 (14511 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Simply saying the script doesn't work is a poor problem description. You should explain in what way it's failing and what errors/warnings it produces.

There are a number of things I'd change, but the initial problem with the database code is in your connection statement. Using single quotes around the vars will prevent interpolation which means that you're passing $user and $pw as literal strings rather than passing the values that those vars hold.


Code
my $dbh = DBI->connect("DBI:mysql:$database:$host" , $user, $pw)  
or die "Connection Error: $DBI::errstr\n";



(This post was edited by FishMonger on Feb 26, 2013, 4:05 PM)


brosskgm
User

Feb 26, 2013, 5:18 PM

Post #3 of 76 (14505 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Sorry, I don't know why it's failing other than it's not getting information from the database .

The subscr_id is in the database exactly as it's writen. I even put the subscr_id in the failed message to send me the id and it's not showing is how I figure it's not reading the database at all. I'm getting an error
in the subscr_id only - below.

I also tried what you posted and even hard coded the database and connection information. I don't know perl enough to know what else to do.

The database works fine when accessed from a php page on our system. Just this perl one doesn't and it's the only script trying to access the database is why it's hard for me. All the scripts I've found on the internet have all failed. I'm guessing I should just go to using flat files and give up trying to get this working.

$subscr_id is a table name in the database. I've tried the connect lines many different ways with what was on the internet. Upper case DBI lower case dbi and so on. At least 5 or 10 different setups.

Error


Code
[Tue Feb 26 16:26:10 2013] [error] [client 192.168.4.4] Use of uninitialized value $subscr_id in concatenation (.) or string at, referer: http://192.168.4.4/test.php 
[Tue Feb 26 16:26:10 2013] [error] [client 192.168.4.4] \t/var/www/cgi-bin/log_it.cgi line 98 (#1), referer: http://192.168.4.4/test.php



lightspd
Novice

Feb 26, 2013, 6:10 PM

Post #4 of 76 (14499 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

What are you trying to do with the data? You open a connection, run a query, then. What happens if you do something like?


Code
while (my @row = $sth->fetchrow_array()) 
{
my $subscr_id = $row[0];
my $username = $row[1];
print "subscr_id: $subscr_id\n username: $username\n";
}



brosskgm
User

Feb 26, 2013, 6:57 PM

Post #5 of 76 (14494 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

Passes up the if (id eq $subscr_id) and goes to the invalid and puts the error log

[Tue Feb 26 18:53:30 2013] [error] [client 192.168.4.4] Use of uninitialized value $subscr_id in concatenation (.) or string at, referer: http://192.168.4.4/test.php
[Tue Feb 26 18:53:30 2013] [error] [client 192.168.4.4] \t/var/www/cgi-bin/log_it.cgi line 109 (#1), referer: http://192.168.4.4/test.php


brosskgm
User

Feb 26, 2013, 7:00 PM

Post #6 of 76 (14493 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

All I'm wanting to do with the data from the database is verify the user. If they are not in the database go to the else invalid and send me the notice. If they are valid send them the IP2Location information by email, or text, or both.

username is email address
subscr_id should be the id in the php header
phone is their phone number@cellphone company domain for text message.


brosskgm
User

Feb 26, 2013, 7:03 PM

Post #7 of 76 (14490 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

It's working now but only if I hard code each person in the perl file. Just trying to add the database lookup so I won't have to hard code if others start using this. There is only 5 people using it now so hard coding was easy, but if I get more than that the file will get bloated.


lightspd
Novice

Feb 26, 2013, 7:07 PM

Post #8 of 76 (14488 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Ok then, try this.

Change:


Code
if ($id eq 'subscr_id')



Code
my @row = $sth->fetchrow_array(); 
my $subscr_id = $row[0];

if($id eq $subscr_id)



brosskgm
User

Feb 26, 2013, 7:24 PM

Post #9 of 76 (14481 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

Have to shut down. I'll be back in about 9 hours.


FishMonger
Veteran / Moderator

Feb 26, 2013, 8:50 PM

Post #10 of 76 (14479 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

If $subscr_id is uninitialized, then you have a problem with your php script, since that's where its value is supposedly come from.


brosskgm
User

Feb 27, 2013, 6:46 AM

Post #11 of 76 (14469 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

That's the funny part. I tried this before also. When it gets declared as you mentioned to try it gives this in the error log. It also did not go to the fail after the removal of the ' if($id eq $subscr_id)


Code
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] "my" variable $subscr_id masks earlier declaration in same scope at, referer: http://192.168.4.4/test.php 
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] \t/var/www/cgi-bin/log_it.cgi line 57 (#1), referer:
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] Execution of /var/www/cgi-bin/log_it.cgi aborted due to compilation errors (#2), referer: http://192.168.4.4/test.php
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] (F) With "strict subs" in use, a bareword is only allowed as a, referer: http://192.168.4.4/test.php
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] subroutine identifier, in curly brackets or to the left of the "=>", referer: http://192.168.4.4/test.php
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] symbol. Perhaps you need to predeclare a subroutine?, referer: http://192.168.4.4/test.php


******************************

In the last message

The $subscr_id is the database, $id is from the PHP and it's received fine.

If I remove everything about the database access and just hard code the id, email address's and so on, it works fine.


FishMonger
Veteran / Moderator

Feb 27, 2013, 7:29 AM

Post #12 of 76 (14463 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


Quote
The $subscr_id is the database, $id is from the PHP and it's received fine.

It's unclear from that verbiage if the value of $subscr_id is supposed to be passed in from the php script or hard coded in the perl script.


Code
my $subscr_id = param("subscr_id");

That statement, along with the fact that it's not being assigned elsewhere, tells us that you expect its value to come from the calling php script and the following error message you posted told me that the value was not passed in by the php script which is why you received this error:

Quote
[Tue Feb 26 16:26:10 2013] [error] [client 192.168.4.4] Use of uninitialized value $subscr_id in concatenation (.) or string at, referer: http://192.168.4.4/test.php


Adding:

Code
my $subscr_id = $row[0];

as lightspd suggests without removing the prior statement is the cause of this error:


Quote
[Wed Feb 27 06:32:55 2013] [error] [client 192.168.4.4] "my" variable $subscr_id masks earlier declaration in same scope at, referer: http://192.168.4.4/test.php


You should output all of the vars that are being used in the database calls to verify that they hold what you expect. I suspect (actually expect) that they do not, which would be why the db code is failing.


FishMonger
Veteran / Moderator

Feb 27, 2013, 7:36 AM

Post #13 of 76 (14461 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Please take note of these suggested changes to the use statements and make them in your script.


Code
#!/usr/bin/perl 

use warnings FATAL => 'all';
use strict;
use diagnostics;
use CGI;
use POSIX qw/strftime/;
use DBI;
use CGI::Carp qw(fatalsToBrowser); # remove this when the script is complete and put into production



lightspd
Novice

Feb 27, 2013, 7:41 AM

Post #14 of 76 (14460 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Ok, let's go through your code and fix some things

change theses, with the param you are saying you expect them from PHP, but you want them from the DB, right?


Code
my $username;  
my $subscr_id;
my $phone;


You are also trying to use data from the database before you even select it.
my $fileb = $subscr_id; <--- there

So, move all your DB stuff, to the above those variables. Then put the returned data, into them. You will also, need to select phone if you want that from the DB.

while (my @row = $sth->fetchrow_array())
{
$subscr_id = $row[0];
$username = $row[1];
}

Then apply your conditions to them. Which I would suggest changing to an if/else as apposed to just if's you have. Remove the exit statements, I enclose your failed condition in an else.


brosskgm
User

Feb 27, 2013, 7:43 AM

Post #15 of 76 (14460 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

In the code I comment # this seems to be wrong I'm not getting $subscr_id from PHP, it's the name of the database table.

I tried it

my $subscr_id; and it complained, if I removed it and set as in the code provided it complained it was not declared.

In the database table there is 4 tables

ID
username
subscr_id
phone

The subscr_id is exactly what is being sent by $id in the php code listed in the posting. the username does have my email address in it, the phone table is empty since it could be empty.

I was trying to get the code to compare $id with $subscr_id and if they match according to 1, 2 or 3 send email or text message, if no match fail.


brosskgm
User

Feb 27, 2013, 7:50 AM

Post #16 of 76 (14458 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

I'm working the suggested changes.

It does have an else after the if, just didn't say else, it's the fail notice, I'll add else { .... } in.

I'm not sure I'm understanding this.

The while (my@row is below all the DB connect etc.


brosskgm
User

Feb 27, 2013, 8:00 AM

Post #17 of 76 (14454 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

Small error not related to most the changes.

I was putting else on the if statements in the if's and I'm not sure how to tell it to continue if not true. continue; was tossing an error.


if($id eq "$ubscr_id")
{

if(message eq "1")
{
do this
}
else
{
continue;
}
if(messge eq "2")
{

ets........


lightspd
Novice

Feb 27, 2013, 8:14 AM

Post #18 of 76 (14447 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

I'll clarify my suggestion. instead of this:


Code
if(blah eq blah) 
{

}
if(blah eq blah2)


Try this

Code
if($msg == 1) 
{
print "message 1"; <-- example
}
elsif($msg == 2)
{
do something else
}
elsif($msg == 3)
{
do something
}
else
{
No conditions met.
}



FishMonger
Veteran / Moderator

Feb 27, 2013, 8:23 AM

Post #19 of 76 (14441 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Please post your updated script so we can get a better idea of what changes you made.

If I were writing this script, I'd use a hash ref for the returned db data.

example:

Code
# db related vars 
my $host = "remote_host";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "pw";

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pw,
{RaiseError => 1})
or die "Connection Error: $DBI::errstr\n";

my $sql = "SELECT subscr_id, username FROM $tablename WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $subscriber = $sth->fetchrow_hashref;

$sth->finish();


$subscriber would be a hash reference with a structure of:


Code
$subscriber = { 
subscr_id => '1-YS25UHRN3N9D',
username => 'user-email-address'
}



(This post was edited by FishMonger on Feb 27, 2013, 8:26 AM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 8:25 AM

Post #20 of 76 (14439 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
I'll clarify my suggestion. instead of this:


Code
if(blah eq blah) 
{

}
if(blah eq blah2)


Try this

Code
if($msg == 1) 
{
print "message 1"; <-- example
}
elsif($msg == 2)
{
do something else
}
elsif($msg == 3)
{
do something
}
else
{
No conditions met.
}



Personally, I'd use a dispatch table instead of the if/elsif/elsif/else block


brosskgm
User

Feb 27, 2013, 8:29 AM

Post #21 of 76 (14438 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

Ok, all changed, this is getting closer. I run it, no more errors at all. Nothing happens. Error log is empty, the access log has

192.168.4.4 - - [27/Feb/2013:08:24:15 -0800] "GET /test.php HTTP/1.1" 200 144
192.168.4.4 - - [27/Feb/2013:08:24:15 -0800] "GET /cgi-bin/log_it.cgi?pg=1&id=1-YS25UHRN3N9D&ip=192.168.4.4 HTTP/1.1" 500 773

It didn't fail this time, but didn't do anything else. This is the first time I saw the information in the access logs.


brosskgm
User

Feb 27, 2013, 8:31 AM

Post #22 of 76 (14436 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I forgot to add what you posted above the if statements.

I'll work on that ad test again. I'll also post the updated.


brosskgm
User

Feb 27, 2013, 9:05 AM

Post #23 of 76 (14427 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

@lightspd - I just realized there was two here. Here is the script so far. It gives no errors in the logs. The access logs give the two lines listed.


Code
#!/usr/bin/perl 
use warnings FATAL => 'all';
use strict;
use diagnostics;
use CGI;
use POSIX qw/strftime/;
use DBI;
use CGI::Carp qw(fatalsToBrowser); # remove this when the script is complete and put into production

print "ContentType: text/html\n\n";

my $host = "remotehost";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "password";

# Obtain items from web page.
my $id = param("id");
my $ip = param("ip");
my $pg = param("pg");
my $username;
my $subscr_id;
my $phone;
my $file = "_iptrace.txt";
my $filea = "/tmp/";
my $fileb = $subscr_id;
my $filec = ".txt";
my $filed = "/tmp/invalid_id_iptrace.txt";

# message
# 1 = email
# 2 = phone
# 3 = both
my $message = param("pg");

my $dbh = DBI->connect("DBI:mysql:$database:$host" , $user, $pw)
or die "Connection Error: $DBI::errstr\n";

my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = 1";
my $sth = $dbh->prepare($sql);
$sth->execute
or die "SQL Error: $DBI::errstr\n";

while (my @row = $sth->fetchrow_array())
{
my $subscr_id = $row[0];
my $username = $row[1];
print "subscr_id: $subscr_id\n username: $username\n";
}
$sth = $dbh->finish();

if ($id eq "$subscr_id")
{
#if we have a match open the file to write.
open(FILE, '>'."$filea$subscr_id$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>>/tmp/$subscr_id$file");

# Lets send an email and or text with all the information.
# Send email

if($msg == 1)
{
system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file");
# print "message 1"; <-- example
}
elsif($msg == 2)
{
system("mail -s 'Web Page visited' $filea$fileb \< /tmp/$subscr_id$file");
# do something else
}
elsif($msg == 3)
{
system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file");
system("mail -s 'Web Page visited' $filea$fileb \< /tmp/$subscr_id$file");
# do something
}
else
{
;
# No conditions met.
}

print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
else
{
# "To Do" send a page or popup if the id is not valid.
$filed = "/tmp/invalid_id_iptrace.txt";
open (FILED, '>'."$filed") or die $!;
print FILED strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILED " -- IP Address: $ip\n\t Access Page: $ENV{HTTP_REFERER} \n From id: $id with :pg as $pg\n------\n";
print FILED " -- Database ID: $subscr_id \n------\n";
close(FILED);
system("mail -s 'Page visited by Unknown ID' admin\@domain.com \< $filed");
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
}
exit();
#end of script



lightspd
Novice

Feb 27, 2013, 9:20 AM

Post #24 of 76 (14426 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

A couple changes.

Move this:

Code
my $file = "_iptrace.txt";  
my $filea = "/tmp/";
my $fileb = $subscr_id;
my $filec = ".txt";
my $filed = "/tmp/invalid_id_iptrace.txt";


to after your $dbh->finish. Or at least move my $fileb, I say the whole thing just to keep them together.

Second, change this.

Code
while (my @row = $sth->fetchrow_array())   
{
$subscr_id = $row[0];
$username = $row[1];
}



FishMonger
Veteran / Moderator

Feb 27, 2013, 9:42 AM

Post #25 of 76 (14422 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post

I'd change this:

Code
while (my @row = $sth->fetchrow_array())    
{
$subscr_id = $row[0];
$username = $row[1];
}


to this:

Code
my ($subscr_id, $username) = $sth->fetchrow_array;


and make sure the prior declarations for those vars get removed.

I see an issue with this line:

Code
my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = 1";


Based on what you previously stated, subscr_id is '1-YS25UHRN3N9D' not '1', so that select statement shouldn't return any data.

Change the select statement to use a placeholder and pass $id in the execute statement as I showed in my prior post.


brosskgm
User

Feb 27, 2013, 9:59 AM

Post #26 of 76 (4997 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

To look like this?

my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = $id";


FishMonger
Veteran / Moderator

Feb 27, 2013, 10:02 AM

Post #27 of 76 (4996 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Other thinks to note:

1) ->finish is a statement ($sth) handle method, not a database ($dbh) handle method.

2) You're using 3 separate vars for the value passed in under the 'pg' param.

line 21:

Code
my $pg = param("pg");

line 35:

Code
my $message = param("pg");

lines 74, 79 and 84 : use to $msg when referring to that value.

There are other issues I see, but we'll take it 1 step at a time.


FishMonger
Veteran / Moderator

Feb 27, 2013, 10:04 AM

Post #28 of 76 (4994 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
To look like this?

my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = $id";


No

It should look like this:

Code
my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = ?";


And the execute statement should look like this:

Code
$sth->execute($id);



brosskgm
User

Feb 27, 2013, 10:06 AM

Post #29 of 76 (4991 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I changes $msg to message because it complained and I caught that.

When I commented out the $username & $subscr_id and ran it, it complained

[Wed Feb 27 10:03:14 2013] [error] [client 192.168.4.4] [Wed Feb 27 10:03:14 2013] logger2.cgi: Global symbol "$subscr_id" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 34., referer: http://192.168.4.4/test.php


brosskgm
User

Feb 27, 2013, 10:15 AM

Post #30 of 76 (4986 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

When I put my $username; & my $subscr_id back in and run it it tells me my masks earlier declaration.


FishMonger
Veteran / Moderator

Feb 27, 2013, 10:16 AM

Post #31 of 76 (4984 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


Quote
When I commented out the $username & $subscr_id and ran it, it complained

I assume you're referring lines 22 & 23.

The error message refers to line 34, but version you posted doesn't use $subscr_id anywhere near that line. What version did you run?


(This post was edited by FishMonger on Feb 27, 2013, 10:16 AM)


brosskgm
User

Feb 27, 2013, 10:26 AM

Post #32 of 76 (4978 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

I found what was causing that error. It was the $fileb = $subscr_id so I took it out and removed $fileb from the message area for now.

Now my $id = param("id"); is giving me undefined routine.


brosskgm
User

Feb 27, 2013, 10:30 AM

Post #33 of 76 (4975 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

I found that error, I put back

use CGI qw/param/;

and that error when away.

Now it's telling me "finish" can't be found VIA package DBI::db


FishMonger
Veteran / Moderator

Feb 27, 2013, 10:31 AM

Post #34 of 76 (4975 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


Quote
Now my $id = param("id"); is giving me undefined routine.


Please post the exact error message and line it's referring to as well as any related code.


(This post was edited by FishMonger on Feb 27, 2013, 10:32 AM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 10:32 AM

Post #35 of 76 (4973 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


Quote
Now it's telling me "finish" can't be found VIA package DBI::db


Did you read my prior post relating to the finish statement?

Read this: http://search.cpan.org/~timb/DBI-1.623/DBI.pm#finish


(This post was edited by FishMonger on Feb 27, 2013, 10:34 AM)


brosskgm
User

Feb 27, 2013, 10:39 AM

Post #36 of 76 (4968 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Sorry, I didn't catch that. I will read that now.

The exact error

[Wed Feb 27 10:38:13 2013] [error] [client 192.168.4.4] [Wed Feb 27 10:38:13 2013] logger2.cgi: Can't locate object method "finish" via package "DBI::db" at /var/www/cgi-bin/log_it.cgi line 58., referer: http://192.1687.4.4/test.php


brosskgm
User

Feb 27, 2013, 10:58 AM

Post #37 of 76 (4959 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

I'm confused about this part. In previous you mentioned to put that line in, the link said not to. I did comment that finish line out.

I was getting the declaration error on if($id eq $subscr_id); until I put if($id eq '$subscr_id'); I don't know if that's correct to do.

Then $subscr_id at the bottom is now tossing the error that's just a bit different. I don't know if perl checks the complete script first or as it runs, but this is the fail section if $id does not equal $subscr_id

[Wed Feb 27 10:51:15 2013] [error] [client 192.168.4.4] [Wed Feb 27 10:51:15 2013] log_it.cgi: Use of uninitialized value $subscr_id in concatenation (.) or string at /var/www/cgi-bin/log_it.cgi line 147., referer: http://www.192.168.4.4/test.php


(This post was edited by brosskgm on Feb 27, 2013, 10:59 AM)


brosskgm
User

Feb 27, 2013, 11:28 AM

Post #38 of 76 (4950 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I took the line out in the last else that had $subscr_id and that removed the last of the errors.

I do get the failed notice by email now.


lightspd
Novice

Feb 27, 2013, 11:33 AM

Post #39 of 76 (4948 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

post the code you are currently using.


FishMonger
Veteran / Moderator

Feb 27, 2013, 11:47 AM

Post #40 of 76 (4944 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

There's no need/reason to compare $id and $subscr_id. Instead, you should test $username.

I'd also put the db code into a subroutine.


Code
# retrieve user from the db 
my $username = get_user($id);

if ( $username ) {
....
....
....
}


sub get_user {

my $id = shift;
my $host = "remotehost";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "password";

my $dbh = DBI->connect("DBI:mysql:$database:$host" , $user, $pw,
{ RaiseError => 1} )
or die "Connection Error: $DBI::errstr\n";

# no need to select subscr_id since we already know that value
# and will be using it to retrieve the "username", if the id is found
my $sql = "SELECT username FROM members WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $username = $sth->fetchrow_array;

$sth->finish();
$dbh->disconnect;

return $username; # returns the 'username' or undef if not found in the db
}


Since you've made a number of changes to the script, we'll need to see your current version so we can point out other changes that it might need.


(This post was edited by FishMonger on Feb 27, 2013, 11:48 AM)


brosskgm
User

Feb 27, 2013, 11:48 AM

Post #41 of 76 (4943 views)
Re: [lightspd] Perl MySQL help with code. [In reply to] Can't Post


Code
#!/usr/bin/perl 
use warnings FATAL => 'all';
use strict;
use diagnostics;
use CGI qw/param/;
use POSIX qw/strftime/;
use DBI;
use CGI::Carp qw(fatalsToBrowser); # remove this when the script is complete and put into production

print "ContentType: text/html\n\n";

my $host = "remotehost";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "password";

# Obtain items from web page.
my $id = param("id");
my $ip = param("ip");
my $pg = param("pg");
## End items from web page

my $phone;
my $file = "_iptrace.txt";
my $filea = "/tmp/";
my $filec = ".txt";
my $filed = "/tmp/invalid_id_iptrace.txt";

# message
# 1 = email
# 2 = phone
# 3 = both
my $message = param("pg");

my $dbh = DBI->connect("DBI:mysql:$database:$host" , $user, $pw)
or die "Connection Error: $DBI::errstr\n";

my $sql = "SELECT subscr_id,username FROM members WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);

$sth->execute($id);

my ($subscr_id, $username) = $sth->fetchrow_array;

#$sth = $dbh->finish();

if ($id eq '$subscr_id')
{
#if we have a match open the file to write.
open(FILE, '>'."$filea$subscr_id$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>>/tmp/$subscr_id$file");

# Lets send an email and or text with all the information.
# Send email

if($message == 1)
{
system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file");
# print "message 1"; <-- example
}
elsif($message == 2)
{
system("mail -s 'Web Page visited' $filea$subscr_id\.txt \< /tmp/$subscr_id$file");
# do something else
}
elsif($message == 3)
{
system("mail -s 'Web Page visited' $username \< /tmp/$subscr_id$file");
system("mail -s 'Web Page visited' $phone \< /tmp/$subscr_id$file");
# do something
}
else
{
;
# No conditions met.
}
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
else
{
# "To Do" send a page or popup if the id is not valid.
$filed = "/tmp/invalid_id_iptrace.txt";
open (FILED, '>'."$filed") or die $!;
print FILED strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILED " -- IP Address: $ip\n\t Access Page: $ENV{HTTP_REFERER} \n From id: $id with :pg as $pg\n------\n";
# print FILED " -- Database ID: \n------\n";
close(FILED);
system("mail -s 'Page visited Unknown' admin\@domain.com \< $filed");
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
exit();
#end of script



(This post was edited by brosskgm on Feb 27, 2013, 11:49 AM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 11:57 AM

Post #42 of 76 (4937 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


Code
if ($id eq '$subscr_id')


The single quotes are preventing var interpolation. Remove those quotes.

That's not the only problem, but we'll start there.


brosskgm
User

Feb 27, 2013, 12:08 PM

Post #43 of 76 (4933 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Ok, when I do I get the following error.

[Wed Feb 27 12:06:52 2013] [error] [client 192.168.4.4] [Wed Feb 27 12:06:52 2013] log_it.cgi: Use of uninitialized value $subscr_id in string eq at /var/www/cgi-bin/log_it.cgi line 53., referer: http://192.168.4.4/test.php


(This post was edited by brosskgm on Feb 27, 2013, 12:19 PM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 12:15 PM

Post #44 of 76 (4930 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Then that means that the sql query didn't return the data you expected.

Dump out $id and $subscr_id to see exactly what the hold.


brosskgm
User

Feb 27, 2013, 12:17 PM

Post #45 of 76 (4929 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

How do I do that?, mysqldump?

I'm still working on the file names etc. to clean it up. I was having issues trying to name /tmp/$subscr_id.txt until I saw that I only needed to use /tmp/$subscr_id\.txt so that will change soon.


(This post was edited by brosskgm on Feb 27, 2013, 12:19 PM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 12:21 PM

Post #46 of 76 (4923 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

The var names used for the filename and path need to be fixed.

Var names should reflect what they hold. Something more like this:

Code
my $iptrace = "_iptrace.txt"; 
my $invalid_iptrace = "/tmp/invalid_id_iptrace.txt";
my $dir = "/tmp";
my $ext = ".txt";


Those first 2 could be improved, but I think you get the idea.


brosskgm
User

Feb 27, 2013, 12:21 PM

Post #47 of 76 (4922 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

In the failed email message - I used to have $subscr_id after the ------ but it never printed anything.

Wednesday February 27, 2013 - 11:25 AM PST
-- IP Address: 192.168.4.4
Access Page: http://192.168.4.4/test.php
From $id: 1-YS25UHRN3N9D with :$pg as 1
------


FishMonger
Veteran / Moderator

Feb 27, 2013, 12:22 PM

Post #48 of 76 (4921 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


Code
use Data::Dumper; 

print Dumper $id, $subscr_id;



brosskgm
User

Feb 27, 2013, 12:30 PM

Post #49 of 76 (4917 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Ok, I'll add what you just mentioned.

Here is the dump

CREATE TABLE `members` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`id` varchar(255) NOT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` VALUES(12, 'admin@domain.com', 'I-YS25UHRN3N9D', 'NULL');


(This post was edited by brosskgm on Feb 27, 2013, 12:31 PM)


brosskgm
User

Feb 27, 2013, 12:36 PM

Post #50 of 76 (4913 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I'm still getting the undeclared error for $subscr_id at if($id eq $subscr_id);
after removing the single quotes.

Were do I put
print Dumper $id, $subscr_id;

Will this print to the logs?


FishMonger
Veteran / Moderator

Feb 27, 2013, 12:42 PM

Post #51 of 76 (4667 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Your database doesn't have a 'subscr_id' field, which is why it's not returning the data you expect. In fact, if you used the db code that I provided, it would have told you about that problem.

Also, having 2 fields with the same name but differing in case and datatype is a REALLY BAD idea. You should alter your table and rename the 'id' field to 'subscr_id';


(This post was edited by FishMonger on Feb 27, 2013, 12:46 PM)


brosskgm
User

Feb 27, 2013, 1:03 PM

Post #52 of 76 (4653 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I created a new db and changed that id field to subscr_id

Same undeclared error in the perl code.


brosskgm
User

Feb 27, 2013, 1:06 PM

Post #53 of 76 (4651 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

What one? I put most of what you posted

I was having several things thrown in.


brosskgm
User

Feb 27, 2013, 1:09 PM

Post #54 of 76 (4648 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

New dump, same undeclared error

Here is the dump

CREATE TABLE `members` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`subscr_id` varchar(255) NOT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` VALUES(12, 'admin@domain.com', 'I-YS25UHRN3N9D', 'NULL');


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:22 PM

Post #55 of 76 (4641 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
What one? I put most of what you posted

I was having several things thrown in.


This is the db code I was referring to, which was posted at 8:23am PST.


Code
# db related vars  
my $host = "remote_host";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "pw";

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pw,
{RaiseError => 1})
or die "Connection Error: $DBI::errstr\n";

my $sql = "SELECT subscr_id, username FROM $tablename WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $subscriber = $sth->fetchrow_hashref;

$sth->finish();


If you run your select statement in the mysql cli, it will generate a warning. Since your script is not redirecting warnings to the browser, that message was never displayed with the rest of the output.

The adjustments that I suggested, in particular the { RaiseError => 1} portion would have raised that warning to a fatal error which would then have been displayed in the browser and added to the web server error log.


brosskgm
User

Feb 27, 2013, 1:28 PM

Post #56 of 76 (4637 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

But with out the finish since it errors?


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:29 PM

Post #57 of 76 (4636 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
New dump, same undeclared error

Here is the dump

CREATE TABLE `members` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`subscr_id` varchar(255) NOT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` VALUES(12, 'admin@domain.com', 'I-YS25UHRN3N9D', 'NULL');


If you compare the 'subscr_id' field here with the id passed in from the php script, you'll see that they don;t match.

id = 1-YS25UHRN3N9D
subscr = I-YS25UHRN3N9D

One of them begins with the number 1 and the other begins with the uppercase I letter.


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:31 PM

Post #58 of 76 (4632 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
But with out the finish since it errors?


Your finish statement errors out because it's wrong. Mine is correct and doesn't error.


brosskgm
User

Feb 27, 2013, 1:32 PM

Post #59 of 76 (4629 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Dam, that's odd, I even tagged and copied that sucker too. I'll make the change.


brosskgm
User

Feb 27, 2013, 1:39 PM

Post #60 of 76 (4627 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Wow!!!!

Ok, that part is working!!! Ya, but when it's the wrong $id it give the undeclared error for $subscr_id at
if($id eq $subscr_id)


FishMonger
Veteran / Moderator

Feb 27, 2013, 1:46 PM

Post #61 of 76 (4623 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

That's part of the reason I said that you shouldn't be comparing those 2 vars. Just check $username.


Code
if ( $username )


An undefined var checked in scalar context like this returns true or false.

If you want it more verbose, you could write it as:

Code
if ( defined $username )


EDIT:
The db code I reference a few posts back was not the one I actually wanted. I just scrolled to the wrong post when searching for the version I meant to refer to..


(This post was edited by FishMonger on Feb 27, 2013, 1:55 PM)


brosskgm
User

Feb 27, 2013, 1:54 PM

Post #62 of 76 (4616 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I tagged and copied yours here above it still tosses the finish error and when the $id is not matching it still tosses the following errors - first couple lines copied.


Code
[Wed Feb 27 13:44:21 2013] [error] [client 192.168.4.4] [Wed Feb 27 13:44:21 2013] log_it.cgi: Can't locate object method "finish" via package "DBI::db" at /var/www/cgi-bin/log_it.cgi line 51., referer: http://192.168.4.4/test.php 
[Wed Feb 27 13:46:37 2013] [error] [client 192.168.4.4] [Wed Feb 27 13:46:37 2013] log_it.cgi: Global symbol "$subscr_id" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 64., referer: http://192.168.4.4/test.php



FishMonger
Veteran / Moderator

Feb 27, 2013, 1:57 PM

Post #63 of 76 (4613 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Are you using:

Code
$dbh->finish;


or

Code
$sth->finish;



Please post the lines that those error messages refer to.


(This post was edited by FishMonger on Feb 27, 2013, 1:59 PM)


brosskgm
User

Feb 27, 2013, 2:00 PM

Post #64 of 76 (4609 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

$sth->finish(); as you have posted above. I tagged and copied all of it and pasted it in.


brosskgm
User

Feb 27, 2013, 2:05 PM

Post #65 of 76 (4606 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

Found it, I still had the one that didn't work about it.

The comment out was on the line above it.


brosskgm
User

Feb 27, 2013, 2:08 PM

Post #66 of 76 (4605 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

This if ( defined $username )
replaces if($id eq $subscr_id) ?

When I do it tosses all the way down on all of them. Even that the $id is wrong.

Code
[Wed Feb 27 14:09:12 2013] [error] [client 192.168.4.4] [Wed Feb 27 14:09:12 2013] log_it.cgi: Global symbol "$username" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 65., referer: http://192.168.4.4/test.php 
[Wed Feb 27 14:09:12 2013] [error] [client 192.168.4.4] [Wed Feb 27 14:09:12 2013] log_it.cgi: Global symbol "$subscr_id" requires explicit package name at /var/www/cgi-bin/log_it.cgi line 70., referer: http://192.168.4.4/test.php



(This post was edited by brosskgm on Feb 27, 2013, 2:13 PM)


FishMonger
Veteran / Moderator

Feb 27, 2013, 2:10 PM

Post #67 of 76 (4603 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post


In Reply To
This if ( defined $username )
replaces if($id eq $subscr_id) ?


Yes, but my first preference would be:

Code
if ( $username )



brosskgm
User

Feb 27, 2013, 2:14 PM

Post #68 of 76 (4598 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

same error about the global symbol all the way down.


FishMonger
Veteran / Moderator

Feb 27, 2013, 2:20 PM

Post #69 of 76 (4593 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

You need to post your code.


brosskgm
User

Feb 27, 2013, 2:36 PM

Post #70 of 76 (4584 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

 

Code
#!/usr/bin/perl 
use warnings FATAL => 'all';
use strict;
use diagnostics;
use CGI qw/param/;
use POSIX qw/strftime/;
use DBI;
use CGI::Carp qw(fatalsToBrowser); # remove this when the script is complete and put into production
use Data::Dumper;

print "ContentType: text/html\n\n";

my $host = "remote_host";
my $database = "dbase";
my $tablename = "tname";
my $user = "user";
my $pw = "password";

# Obtain items from web page.
my $id = param("id");
my $ip = param("ip");
my $pg = param("pg");
#
# End web page items
#
my $phone;
my $iptrace = "_iptrace.txt";
my $invalid_iptrace = "/tmp/invalid_id_iptrace.txt";
my $dir = "/tmp/";
my $ext = ".txt";

# message
# 1 = email
# 2 = phone
# 3 = both
my $message = param("pg");

my $dbh = DBI->connect("DBI:mysql:$database:$host", $user, $pw,
{RaiseError => 1})
or die "Connection Error: $DBI::errstr\n";
my $sql = "SELECT subscr_id, username FROM $tablename WHERE subscr_id = ?";
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my $subscriber = $sth->fetchrow_hashref;
$sth->finish();

print Dumper $id, $subscr_id;

# if ($id eq $subscr_id)
if ( $username )
{
#if we have a match open the file to write.
open(FILE, '>'."$dir$subscr_id$ext");

# 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>> $dir$subscr_id$ext");

# Lets send an email and or text with all the information.
# Send email

if($message == 1)
{
system("mail -s 'Web Page visited' $username \< $dir$subscr_id$ext");
# print "message 1"; <-- example
}
elsif($message == 2)
{
system("mail -s 'Web Page visited' $phone \< $dir$subscr_id$ext");
# do something else
}
elsif($message == 3)
{
system("mail -s 'Web Page visited' $username \< $dir$subscr_id$ext");
system("mail -s 'Web Page visited' $phone \< $dir$subscr_id$ext");
# do something
}
else
{
;
# No conditions met.
}
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
else
{
# "To Do" send a page or popup if the id is not valid.
my $filed = "$invalid_iptrace";
open (FILED, '>'."$filed") or die $!;
print FILED strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILED " -- IP Address: $ip\n\t Access Page: $ENV{HTTP_REFERER} \n From id: $id with :pg as $pg\n------\n";
close(FILED);
system("mail -s 'Page visited Unknown' admin\@domian.com \< $filed");
print "ContentType: text/html\n\n";
print "<!--\n";
print "var x;\n";
print "//-->\n";
exit();
}
exit();
#end of script



FishMonger
Veteran / Moderator

Feb 27, 2013, 2:56 PM

Post #71 of 76 (4577 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

You need to be careful when applying changes; you need to make sure that you adjust all related code that would be affected by the change you made.

If you fetch the data as a hash ref, then you need to update all of the related vars to the hash ref. Or, fetch the data as an array and keep the related vars a is.


change:

Code
my $subscriber = $sth->fetchrow_hashref;


to:

Code
my ($subscr_id, $username) = $sth->fetchrow_array;



brosskgm
User

Feb 27, 2013, 3:26 PM

Post #72 of 76 (4574 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Wow!!, thank you so much!!, I do try to tag/copy/paste as carefully as I can. Especially since I have no idea about much of this.

Is there something I can do for you and lightspd ? This was a great help. I'd been trying to get this working for better than a couple months.

Thanks so much.
Bob


FishMonger
Veteran / Moderator

Feb 28, 2013, 7:14 AM

Post #73 of 76 (4553 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

The best 2 things you can do for those of us that help you is to give your honest appreciation for our time and learn how to do this on your own so that you can repay this effort of ours by helping/teaching others.


brosskgm
User

Mar 4, 2013, 6:34 AM

Post #74 of 76 (4508 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

I was trying to improve the code a bit the other day and ran into a slight snag. It is working as I was wanting, but it's creating two errors that I'm having a problem figuring out. I was trying to eliminate the need to tell it to send 1, 2 or 3. I added an extra table to the database to handle the cell sms address after their cell number and also to create a log file.

It seems to be acting like it's processing all the if and elsif but still working as I was hoping.

It still writes all the files and only sends the correct one. (Not a big deal, but it's an error.)

2nd) I'm now getting a mail error in the logs, but it's sending the mail just fine.

Error:

Code
[Mon Mar 04 04:51:52 2013] [error] [client 192.168.4.4] Send options without primary recipient specified., referer: http://192.168.4.4/ 
[Mon Mar 04 04:51:52 2013] [error] [client 192.168.4.4] Usage: mail -eiIUdEFntBDNHRV~ -T FILE -u USER -h hops -r address -s SUBJECT -a FILE -q FILE -f FILE -A ACCOUNT -b USERS -c USERS -S OPTION users, referer: http://192.168.4.4/




Code
    if ( $username ) 
{
#if we have a match open the file to write.
open(FILEB, '>>'."$dirlog$subscr_id$extb"); # log file
# lets put the time in the file.
print FILEB strftime("%A %B %d, %Y - %I:%M %p %Z ", localtime(time) );
# put the IP address and web page visited.
print FILEB " -- IP Address: $ENV{REMOTE_ADDR}\n";
#close the file. Finished with the first part
close(FILEB);
# Run the IP2Location script.
# Lets send an email and or text with all the information.

# Send email and text

if($subscr_provider ne " " && $subscr_email ne " ")
{
# do something
# open email & text files for write
open(FILE, '>'."$dir$subscr_id$ext"); # email file
open(FILEC, '>'."$dir$subscr_id$text"); # text message file
# lets put the time in the file
print FILE strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
print FILEC strftime("%A %B %d, %Y - %I:%M %p %Z ", 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";
print FILEC " -- IP Address: $ENV{REMOTE_ADDR} Accessed your page\n";
close(FILE);
close(FILEC);
# Run the IP2Location script.
system("./iptrace.sh $ENV{REMOTE_ADDR} json city>> $dir$subscr_id$ext");
# were done send the notices
system("mail -s 'Web Page visited' $subscr_email \< $dir$subscr_id$ext");
system("mail -s 'Web Page visited' $subscr_phone$subscr_provider \< $dir$subscr_id$text");
}
elsif($subscr_provider eq " " && $subscr_email ne " ") # send email only
{
# open email file for write
open(FILE, '>'."$dir$subscr_id$ext"); # email file
# 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
close(FILE);
# Run the IP2Location script.
system("./iptrace.sh $ENV{REMOTE_ADDR} json city>> $dir$subscr_id$ext");
system("mail -s 'Web Page visited' $subscr_email \< $dir$subscr_id$ext");
}
elsif($subscr_provider ne " " && $subscr_email eq " ") # text message only
{
# open text file for write
open(FILEC, '>'."$dir$subscr_id$text"); # text message file
# lets put the time in the file
print FILEC strftime("%A %B %d, %Y - %I:%M %p %Z\n", localtime(time) );
# put the ip address that accessed the web site
print FILEC " -- IP Address: $ENV{REMOTE_ADDR} Accessed your page\n";
# We don't run IP2Location for text. Makes them 5 messages long.
# were done lets send the notice.
system("mail -s 'Web Page visited' $subscr_phone$subscr_provider \< $dir$subscr_id$text");
}



FishMonger
Veteran / Moderator

Mar 4, 2013, 8:11 AM

Post #75 of 76 (4502 views)
Re: [brosskgm] Perl MySQL help with code. [In reply to] Can't Post

The error message is telling you that your syntax for the mail command is incomplete. You should read the man page for the mail command to learn how it should be used. I don't use it and don't want to guess on its usage.

It would be better to use one of Perl's email modules instead of doing the system call.
MIME::Lite - http://search.cpan.org/~rjbs/MIME-Lite-3.029/lib/MIME/Lite.pm

It would also be more efficient to not use those external files as a holding place for the email data that you're sending. Instead store that info in a perl var.


brosskgm
User

Mar 4, 2013, 1:42 PM

Post #76 of 76 (5828 views)
Re: [FishMonger] Perl MySQL help with code. [In reply to] Can't Post

Ok thanks,

More work to do.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives