How to execute a MySQL command using shell script from PHP page?

Question

I want to execute a shell script from php page which will execute a MySQL command.

To do this I followed the way shown here: https://stackoverflow.com/a/8055745/2117868

Here is my sqlscript.sh

#!/bin/sh
sudo wget -t 50 -O /tmp/update.sql http://example.com/update.sql
if [ $? -eq 0 ]; then
    mysql -h "localhost" -u "root" "-pXXXXXXXX" "database-name" < "/tmp/update.sql"
    if [ $? -eq 0 ]; then
        sudo rm /tmp/update.sql
        echo "200"
    else
        echo "502"
    fi
else
    echo "404"
fi

And my php page is runscript.php

<?php
    shell_exec("sudo /path/to/script/sqlscript.sh");
?>

Now, when I'm calling the sqlscript.sh from the server console or php page runscript.php it works perfectly and returns 200 as expected.

But when I take MYSQL user and password in ~/.my.cnf so I don't have to put it on the command-line at all:

[client]
user = root
password = XXXXXXXX

And added credentials in sudoer to execute this script without password.

User_Alias WWW_USER = www-data
Cmnd_Alias WWW_COMMANDS_SQL = /path/to/script/sqlscript.sh
WWW_USER ALL = (ALL) NOPASSWD: WWW_COMMANDS_SQL

Here is my sqlscript.sh

#!/bin/sh
sudo wget -t 50 -O /tmp/update.sql http://example.com/update.sql
if [ $? -eq 0 ]; then
    mysql -h "localhost" "database-name" < "/tmp/update.sql"
    if [ $? -eq 0 ]; then
        sudo rm /tmp/update.sql
        echo "200"
    else
        echo "502"
    fi
else
    echo "404"
fi

Now, when I'm calling the sqlscript.sh from the server console it works perfectly and returns 200 as expected. But if I call it from the php page

http://example.com/runscript.php

it returns 502 as it couldn't execute the SQL command.

Update:

Here is the Apache error.log

--2014-12-05 10:51:55--  http://example.com/update.sql
Resolving example.com (example.com)... 162.144.71.XXX
Connecting to example.com (example.com)|162.144.71.XXX|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 146 [text/x-sql]
Saving to: `/tmp/update.sql'

     0K                                                       100% 10.9M=0s

2014-12-05 10:51:55 (10.9 MB/s) - `/tmp/update.sql' saved [146/146]

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

So, please help me to solve the issue.

This is probably a permission issue. you're running php with web server user, and in console with a different user. to analyze the problem you should check apache logs, and see (maybe also edit your question with it) the exact error message. — Volkan Ulukut
– Volkan Ulukut, Commented Dec 5, 2014 at 9:10
This is clearly a permission issue. The user who executes the PHP script and therefore your bash script (and mysql), is not allowed to access my.cnf file. — PauloASilva
– PauloASilva, Commented Dec 5, 2014 at 9:50
Hi, @VolkanUlukut thanks for you support. I updated the question adding the Apache error log. But why this script is causing this permission error? — kuttumiah
– kuttumiah, Commented Dec 5, 2014 at 10:01
You don’t need root privileges for the commands you are executing. — Gumbo
– Gumbo, Commented Dec 5, 2014 at 10:32

oori · Accepted Answer · 2015-07-31 01:05:23Z

1

Solved by adding the --defaults-extra-file option.
mysql --defaults-extra-file=~/.my.cnf ...

Or with absolute path (might be required via apache, depends on server config):
mysql --defaults-extra-file=/absolute/path/to/.my.cnf ...

answered Jul 31, 2015 at 1:05

oori

5,7511 gold badge34 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kuttumiah Over a year ago

Thanks for your answer. I'll check and let you know.

Don Djoe · Accepted Answer · 2015-02-26 03:54:11Z

0

Have you tried this?

In your shell script, instead of

mysql -h "localhost" "database-name" < "/tmp/update.sql"

use

mysql -h "localhost"-u root -ppassword "database-name" < "/tmp/update.sql"

Basically, try to pass MySQL username and password inside your shell script. Of course you can put them into variables/configs/whatever to obsecure it

MySQL 28000 error showed that your script was able to attempt to login as a root but because there was no parameter password, it failed

answered Feb 26, 2015 at 3:54

Don Djoe

7051 gold badge10 silver badges21 bronze badges

2 Comments

kuttumiah Over a year ago

Thanks for your answer. Yes, I've tried it. And it's at the beginning of my question that this way is working. But I don't want to write password directly in the script. That's what I wanted.

Don Djoe Over a year ago

Apologies for not reading the question properly. For some reason I mis-read the original script. Have you tried to either put .my.cnf into www-data home folder instead of your user's home folder? Alternatively set the apache user into your user (which arguably not preferred by some people)

Collectives™ on Stack Overflow

How to execute a MySQL command using shell script from PHP page?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related