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

[gary kuipers]

I am having problems connecting to MySQL from Perl scripts that are run from the apache web server. These problems don't exist when running the same scripts from the command prompt. It's almost certainly not a Perl problem, but someone may have run into this problem and may be able to help.

Pertinent information:
----------------------
Mysql info - select * from mysql.user yields:
host = localhost user = root password = (none) (yes, I know I have to change this)
host = % user = apache password = 77ddghws8w...
host = localhost user = apache password = (none)
host = 127.0.0.1 user = apache password = (none)

Apache httpd.conf, section 2 has:
User apache
Group apache
ServerName localhost
<Directory "/usr/www/loader/cgi-bin">
AllowOverride None
Options ExecCGI
Order allow,deny ( I think this makes everything very liberal)
Allow from all
</Directory>

There is no (apparent) mysql.cnf file in the system

Observed Behaviour:
-------------------
mysql (from root) ==> mysql> and behaves appropriately (e.g. use mysql is allowed, use mydb is also OK, and can select from mydb.mytable)
mysql -u apache ==> mysql> and behaves appropriately (e.g. use mysql is denied but use mydb is ok, also can select from mydb.mytable)
in the perl script:
if ( $dbh = DBI->connect("DBI:mysql:$INI{DBname}",$INI{DBuser},$INI{DBpass}) )
where $INI{DBuser} = 'root' and $INI{DBpass} is null
==>
if executed from command prompt ==> displays DB information (i.e. evaluates as true)
if executed from web browser ==> evaluates as false

script $who=`whoami`; print "$who"; ==> reports "apache" as the user, therefore:

if ( $dbh = DBI->connect("DBI:mysql:$INI{DBname}",$INI{DBuser},$INI{DBpass}) )
where $INI{DBuser} = 'apache' and $INI{DBpass} is (the login password for apache)
==>
if executed from command prompt ==> displays DB information (i.e. evaluates as true)
if executed from web browser ==> evaluates as false

the same is true if password is left blank
the same result if the $who variable is used instead of $INI{DBuser}

Observation: Users that can connect to mysql from the command prompt and scripts run from the command prompt can access the MySQL databases, whereas those run from the apache web server cannot connect.

Presumption: The error that is being generated by the DBI->connect() statement is a MySQL error, not a system error. The $@ variable is null in this case.

Thoughts:
----------
It should not be a linux permissions issue: we are asking a process to access ITS files (dbs). It demonstrates the ability to do so from the command prompt and from the script run from a command prompt.

It can't be that mysql "rescues" a bad login from prompt(in this case mysql -u apache) by substituting the user from which it was executed, because an apache login bombed when trying to "use mysql", so mysql clearly believed the user was "apache".

It is not a MySQL password issue, as shown by the fact that command prompt "mysql -u apache" works, as does the execution of a script from the command prompt even though it has no password in the DBI->connect() statement.

The password being blank for apache from the command prompt did not matter. This means that the user table is using one of the more specific apache@localhost or apache@'127.0.0.1' entries rather than the general apache@% This further implies that a login into MySQL with user name "apache" and no password should work.

Is it only the user table that allows access? Per the MySQL documentation 6.9: "The user table scope fields determine whether to allow or reject incoming connections.", so it would appear so.

Is this access based on the logged in user (in this case "apache" from whe web server) or is it based on the information supplied to the DBI->connect() statement? The incoming CGI request is user system user apache (as shown by the 'whoami' in the script. Additionaly the specified user is apache (in the DBI->connect() statement). Since both are "apache" and a password is (presumably) not required ...?

Stumped!

[AgentM]

We really don't have enough info to go on. For future reference, it's best to include some relevant lines of code. A couple of tips:
• use CGI::Carp qw/fatalsToBrowser/; More likely than not, you'll be able to read off the error if there is one.
• MySQL is interested in the login and password passed to it and little else. Thus, using user 'apache' to login shouldn't be a problem unless that user is restricted access to some libs (unlikely).
• Consider that you might be banging your head against the wall but dealing with the wrong issues. Could the error have something else to do with the script.
• Check the MySQL as well as the Apache error_logs for messages that might help.
Perhaps next time you could be a little more descriptive than "evaluates to true" but, if you try out some of my tips above, it's unlikely that you won't find out what's going on.


AgentM Systems nor Nasca Enterprises nor Bone::Easy nor Macperl is responsible for the comments made by AgentM. Remember, you can build any logical system with NOR.

[eejack]

Howdy, A couple of things...
First I would change your security - you have it set up so anyone can connect to your database from remote using your listed username and password for apache. Essentially the "%" allows for any host to connect...(Assuming you haven't blocked port or implemented other security).

Second - *IF* it is not a code problem, and because you are asking about what other tables might be involved, it just could be a mysql privilege issue. These are the typical relevant tables in database mysql


mysql> show tables;                              
+-----------------+
| Tables in mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)


For a look at the mysql docs on priviledges.. http://www.mysql.com/doc/P/r/Privilege_system.html

Specifically, look at your db table, which contains the priviledges based on a combination of user/host/database.

Also, if you post some code I'm sure it would help everyone be more helpful..:)

EEjack

[gary kuipers]

AHA! The httpd/error_log reports the following:
Sun Apr 15 01:12:49 2001 kk.pl: DBI->connect(Loader) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) at /usr/www/loader/cgi-bin/kk.pl line 26

So the problem is a sockets problem
(Question: why would a command prompt execution use a different socket from the apache execution??)

Using find / -name mysql.sock yields it in /var/lib/mysql/mysql.sock. Since this is not where the error is I looked to see how mysql is configured by typing mysqladmin version. This is the result:


mysqladmin  Ver 8.18 Distrib 3.23.35, for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software
+,
and you are welcome to modify and redistribute it under the GPL licens
+e

Server version          3.23.35
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 29 min 46 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 6  Flush tables: 1  
+Open tables: 0 Queries per second avg: 0.001

Clearly the server is configured correctly, so the problem must be one step pack, in the DBI or the DBD::mysql

I'm still hunting for where the /tmp/mysql.sock is specified, but I thought I'd give you this update.

[Yohimbe]

/tmp/myslq.sock will be what the DBI::DBD was compiled with. Had this problem too. I just symlinked /tmp/mysql.sock to the other one.
--
Jay "Yohimbe" Thorne, alpha geek for UserFriendly

[gary kuipers]

Dear Jay:

Thanks! I was going nuts trying to figure out where that /tmp/mysql.sock was coming from. One small piece of further help please: How do I create the symlink? I've done this before and my recollection is that it is a simple command 2-4 characters long but that its name is not intuitive. My documentation shows that words with *link* in them tend to be include files for C programs. My RH 6.1 Bible does not mention symlink nor symbolik link nor alias, etc. for about a dozen different words I thought of.

Do you know if the people in charge of DBI/DBD have any plans to make this a parameter? My recollection is that both MySQL and DBI use /tmp/mysql.sock. I had to go throiugh this process before just to het mysql to work from the command prompt. I have no idea where the /var/... socket came from.

Regards,
Gary

[gary kuipers]

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>

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 ...

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>

[eejack]

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 ...

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';

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 [e-mail].

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)

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

[gary kuipers]

Thank You! Merci! Gracias! Danke! Arigato!

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

created the appropriate symlink and apache is now communicating properly with MySQL.

Thank you all again!