3

I have a sh script that works fine when I run it at command line with root user. This creates a database and a user with password.

$ ./create-database.sh dbname dbuser dbpass

    #!/bin/bash
exec 3>&1 4>&2
trap 'exec 2>&4 1>&3' 0 1 2 3
exec 1>log.out 2>&1
# Everything below will go to the file 'log.out':

# Functions
ok() { echo -e '\e[32m'$1'\e[m'; } # Green

EXPECTED_ARGS=3
E_BADARGS=65
MYSQL=`which mysql`

Q0="DROP DATABASE IF EXISTS $1;"
Q1="CREATE DATABASE IF NOT EXISTS $1;"
Q2="GRANT ALL ON $1.* TO '$2'@'localhost' IDENTIFIED BY '$3';"
Q3="FLUSH PRIVILEGES;"
SQL="${Q0}${Q1}${Q2}${Q3}"

if [ $# -ne $EXPECTED_ARGS ]
then
  echo "Usage: $0 dbname dbuser dbpass"
  exit $E_BADARGS
fi

$MYSQL -u root -pXXXXXXX -e "$SQL"

ok "Database $1 and user $2 created with a password $3"

The same file when I run using php it doesn't work. The PHP code is as below in the same folder as that of sh file.

    <?php

$db_name = $_GET['db_name'];
$db_user = $_GET['db_user'];
$db_pass = $_GET['db_pass'];

$output = shell_exec ("sh ./create-database.sh $db_name $db_user $db_pass");

echo "Output $output";

the PHP Script has 777 permissions and is running in an application owned by user pakerp

The log.out file gives me the following error

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

While the same script is working fine when I run at command line.

What am I missing. Please tell me.

Inazo
  • 488
  • 4
  • 15
Boxygen
  • 31
  • 4
  • Somtimes in debian you need to be root/sudo to connect with root user in MySQL/MariadDB. So the solution you need to create another user with privileges to do this actions – Inazo Sep 28 '20 at 09:34
  • `#!/bin/bash` in the script and `sh` in php – Ivan Sep 28 '20 at 09:44
  • Does the script work if you were to replace `$_GET`s with `$argv`s and ran it directly from the terminal ? – Hassan Sep 28 '20 at 09:52
  • This question was posted earlier today. Did you delete that one and post it again? Either way, you should really run the user data through [escapeshellcmd()](https://www.php.net/manual/en/function.escapeshellcmd.php) before adding them to `shell_exec()`, or you've basically opened up for anyone to run shell commands on your server. – M. Eriksson Sep 28 '20 at 10:04
  • @Ivan I am using both. but the error I am getting via PHP is Access Denied for 'user'@'localhost' but there is no error when I run the file from bash command line – Boxygen Sep 28 '20 at 20:23
  • The real question is.. why would you call the script from PHP? Use PDO/mysqli commands to call the queries within the PHP. And create an admin user in mysql for this purpose, as others have mentioned. – Oliver O'Neill Sep 28 '20 at 21:41
  • It appears that you have posted sensitive/private information. If that's the case, please reset your passwords and/or revoke API keys and tokens, as they are considered compromised when posted on the internet. – Samuel Liew Sep 29 '20 at 01:37
  • Then it looks like [this](https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost) – Ivan Sep 29 '20 at 06:23
  • @OliverO'Neill your question is correct. I tried using mysqli command too. But it gave the same error Connection failed: Access denied for user 'root'@'localhost'. It seems that if I try to access mysql as a root user and password via PHP it is not allowing me to connect either through mysqli OR sh file. I have given 777 permissions to this file to be on safe side. – Boxygen Sep 29 '20 at 18:36
  • @Boxygen yeah it's for the same reason. The root user is special, it's really locked down on how it can be used. You should be creating an admin user with just enough permissions to add/alter tables/schemas. Preferably a user per schema if you have multiple projects accessing the same DB. – Oliver O'Neill Sep 30 '20 at 00:16

2 Answers2

1

You might need to allow root on localhost or better use a non root user!

Seems the same as this issue

Erik
  • 86
  • 3
  • root is allowed on localhost as I can run the bash command from command line and successfully create the database. My problem is to get it done using PHP? – Boxygen Sep 28 '20 at 20:20
0

use one of following commands:

$MYSQL --host=localhost --user=root --password=XXXXXX -e $SQL

or

$MYSQL --host={{IP}} --user=root --password=XXXXXX -e $SQL

enter your machine ip instead of {{IP}}. if your machine is localhost, add 127.0.0.1 as IPv4 or ::1 as IPv6

Inazo
  • 488
  • 4
  • 15
AmirAli Esteki
  • 542
  • 3
  • 13
  • This command is not even working from command line. While mine is working from command line. I have problem to get it worked from php. It gives error ERROR 1698 (28000): Access denied for user 'root'@'localhost' when run from PHP file. I am missing some root privileges to get this done. How to Fix that? – Boxygen Sep 28 '20 at 19:56