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.

 

First page Previous page 1 2 3 4 Next page Last page  View All


brosskgm
User

Feb 26, 2013, 3:18 PM

Post #1 of 76 (12128 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 (12123 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 (12117 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 (12111 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 (12106 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 (12105 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 (12102 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 (12100 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 (12093 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 (12091 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 (12081 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 (12075 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 (12073 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 (12072 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 (12072 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 (12070 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 (12066 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 (12059 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 (12053 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 (12051 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 (12050 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 (12048 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 (12039 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 (12038 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 (12034 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.

First page Previous page 1 2 3 4 Next page Last page  View All
 
 


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

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