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 27, 2013, 9:59 AM

Post #26 of 76 (4008 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 (4007 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 (4005 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 (4002 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 (3997 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 (3995 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 (3989 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 (3986 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 (3986 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 (3984 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 (3979 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 (3970 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 (3961 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 (3959 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 (3955 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 (3954 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 (3948 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 (3944 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 (3941 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 (3940 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 (3934 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 (3933 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 (3932 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 (3928 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 (3924 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?

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