Re: Perl Script connecting to Mysql works from command prompt, not from Apache

Thanks for your answers. here's the code and the results (I put them in as comments in the code):

#!/usr/bin/perl

# Loader.pl stripped down to MySQL connect error (15mar01)

use strict;
use CGI qw/:standard *table start_ul/;           # load standard CGI r
+outines
use CGI::Pretty;
use DBI;
use CGI::Carp qw /fatalsToBrowser/;

my %INI;
my $dbh = undef;

print "Content-Type:text/html\r\n\r\n";
print "<HTML>\n";
print "<HEAD>\n";
print "<TITLE>MySQL connect failure test</TITLE>\n";
print "</HEAD>\n";
print "<BODY BGCOLOR=\"white\">\n";

$INI{DBname} = 'Loader';
$INI{DBuser} = 'apache';
$INI{DBpass} = '';

print "opening database |$INI{DBname}|$INI{DBuser}|$INI{DBpass}|\n";
if ( $dbh = DBI->connect("DBI:mysql:$INI{DBname}",$INI{DBuser},$INI{DB
+pass}) ) {
   my $stmt = 'SELECT * FROM ipstat ORDER BY sortip';
   if ( my $sth = $dbh->prepare($stmt) ) {
      if ( my $rows = $sth->execute ) {
         print "<TABLE>\n";
         while ( my $dat = $sth->fetchrow_hashref ) {
            # output the line
            print "<TR>";
            print "<TD>$dat->{ip}</TD>";
            print "</TR>\n";
         } # while
         print "</TABLE>\n";
      } else {
         ERRreport("ERROR: Could not fetch rows from |$stmt| $@",1);
      }
   } else {
      ERRreport("ERROR: Could not prepare statement |$stmt| $@",1);
   }
   $dbh->disconnect();
} else {
   ERRreport("ERROR: Could not connect to |$INI{DBname}| as |$INI{DBus
+er}|$INI{DBpass}| err|$@|",1);
}
print "</BODY>\n";
print "</HTML>\n";

##################################
sub ERRreport {
   my $errmsg = $_[0];
   my $severity = $_[1];
   print PAGE "$errmsg<BR>\n";
   if ( $severity ) {
      print "<B>$errmsg</B><BR>\n";
   } else {
      print "$errmsg<BR>\n";
   }
}

#Running the above code from the command line produces the following o
+utput
# <HTML>
# <HEAD>
# <TITLE>MySQL connect failure test</TITLE>
# </HEAD>
# <BODY BGCOLOR="white">
# opening database |Loader|apache||
# <TABLE>
# <TR><TD>127.0.0.1</TD></TR>
# <TR><TD>128.0.0.1</TD></TR>
# </BODY>
# </HTML>

# Same code called from a form gives the folowing result
# <HTML>
# <HEAD>
# <TITLE>MySQL connect failure test</TITLE>
# </HEAD>
# <BODY BGCOLOR="white">
# opening database |Loader|apache||
# <B>ERROR: Could not connect to |Loader| as |apache|| err||</B><BR>
# </BODY>
# </HTML>
[download]

To AgentM 15apr2001 0030:

the Carp yielded no additional info, but thanks! It will be useful in future code
I'll check the error logs next.

To eejack 15apr01 0507:

Thanks! I created the security hole on purpose out of sheer desperation! I'll fix it later.
mysql.db has the following contents (again, bad security born of desperation):

#  host = %          Db= test    User=(none) select=Y ...
#  host = %          Db= Loader  User=apache select=Y ...
#  host = localhost  Db= Loader  User=apache select=Y ...
#  host = 127.0.0.1  Db= Loader  User=apache select=Y ...
[download]

This should not matter because it does work from the command prompt but not through apache.
Unless the system user (root in the case of command prompt and apache in the case of the browser call) is piggybacking something on the call to MySQL then the database permissions should be irrelevant.
I think the answer should be in something MySQL is doing. It must have some parameter such as "allow bozo (system) users to connnect = N", but I've found no reference to a variable of this type.
It would seem that it is the (system) user "apache", which has low priorities in the system is not allowed to connect even though it exists as a MySQL user.

The info at http://www/mysql.com/doc/P/r/Privilege_system.html is where I got the information in my original request for help. It was Ch 6.9 rather than 6.10 (so my info was older). Reading it gives me the same info. As you can see from both the mysql.user and mysql.db tables, the user apache without password is wide open for database Loader. This is borne out by the fact that the code works from the command prompt.

What would make a connect to a database, that works from the command prompt, fail from the browser? </CODE>

Comment on Re: Perl Script connecting to Mysql works from command prompt, not from Apache Select or Download Code

Replies are listed 'Best First'.
Re: Re: Perl Script connecting to Mysql works from command prompt, not from Apache by eejack (Hermit) on Apr 15, 2001 at 18:55 UTC
If your mysql.user table is like you originally wrote it. `Mysql info - select * from mysql.user yields: host = localhost user = root password = (none) host = % user = apache password = 77ddghws8w... host = localhost user = apache password = (none) host = 127.0.0.1 user = apache password = (none) host = % Db= test User=(none) select=Y ... host = % Db= Loader User=apache select=Y ... host = localhost Db= Loader User=apache select=Y ... host = 127.0.0.1 Db= Loader User=apache select=Y ...` [download] then perhaps mysql is picking which of the three differing apache it is using differently between the command line and browser... (I know what I want to say but it just isn't coming out properly) ... and that is messing up the permissions. Since mysql doesn't care who the actual user is, but only the hostname, database, username, and password you provide it in your connect, and it picks which available user it uses, you may be giving it too much opportunity to choose. You could try this.... `grant select, insert, update, delete on Loader.* to testuser@localhost identified by 'testpass';` [download] And then change your script to use username:testuser and password:testpass. This will give you just one user to worry about and cut down on the potential of mysql picking the wrong user. Another thing that could be happening is your browser connection is not acting as if it is from localhost. That is a reach, as I am sure people who know these things will point out to me...but I have worked on at least one setup where the mysql connection had to be set up as nobody@local.secondary.tld. To test around that you might want to specify localhost in your connect method. `$database = "Loader"; $hostname = "localhost"; $username = "testuser"; $password = "testpass"; $dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password)` [download] mysql assumes localhost and port 3306 if they are not specified btw, but it couldn't hurt to set them Also, you might want to look at http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html I am not super familiar with permissions and whatnot, but while mysql may not actually care who the actual user is connecting to it is, your file system does. According to the above mentioned article.... A MySQL client on Unix can connect to the mysqld server in two different ways: Unix sockets, which connect through a file in the file system (default `/tmp/mysqld.sock') or TCP/IP, which connects through a port number. Unix sockets are faster than TCP/IP but can only be used when connecting to a server on the same computer. Unix sockets are used if you don't specify a hostname or if you specify the special hostname localhost There is the possiblity that this file is set with permissions that do not allow apache to access it, but allow you(the user), to access it on the command line. Thanks... EEjack	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: Perl Script connecting to Mysql works from command prompt, not from Apache
by eejack (Hermit) on Apr 15, 2001 at 18:55 UTC

Mysql info - select * from mysql.user yields:
host = localhost user = root password = (none)
host = % user = apache password = 77ddghws8w...
host = localhost user = apache password = (none)
host = 127.0.0.1 user = apache password = (none)


host = %          Db= test    User=(none) select=Y ...
host = %          Db= Loader  User=apache select=Y ...
host = localhost  Db= Loader  User=apache select=Y ...
host = 127.0.0.1  Db= Loader  User=apache select=Y ...
[download]

Since mysql doesn't care who the *actual* user is, but only the hostname, database, username, and password you provide it in your connect, and it picks *which* available user it uses, you may be giving it too much opportunity to choose.

You could try this....

grant select, insert, update, delete
on Loader.*
to testuser@localhost
identified by 'testpass';
[download]

And then change your script to use username:testuser and password:testpass. This will give you just one user to worry about and cut down on the potential of mysql picking the wrong user.

Another thing that *could* be happening is your browser connection is not acting as if it is from localhost. That is a reach, as I am sure people who know these things will point out to me...but I have worked on at least one setup where the mysql connection had to be set up as nobody@local.secondary.tld.

To test around that you might want to specify localhost in your connect method.

$database = "Loader";
$hostname = "localhost";
$username = "testuser";
$password = "testpass";

$dbh = DBI->connect("DBI:mysql:$database:$hostname", $user, $password)
[download]

Also, you might want to look at http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html

I am not super familiar with permissions and whatnot, but while mysql may not actually care who the *actual* user is connecting to it is, your file system does.

According to the above mentioned article.... A MySQL client on Unix can connect to the mysqld server in two different ways: Unix sockets, which connect through a file in the file system (default `/tmp/mysqld.sock') or TCP/IP, which connects through a port number. Unix sockets are faster than TCP/IP but can only be used when connecting to a server on the same computer. Unix sockets are used if you don't specify a hostname or if you specify the special hostname localhost

There is the possiblity that this file is set with permissions that do not allow apache to access it, but allow you(the user), to access it on the command line.

Thanks...
EEjack

[reply]
[d/l]
[select]