Mysql connection to remote server in PHP

Question

I know this question has been asked several times before, but the solutions posted have not worked for me. I have tried all of them.
So here is my question

Q) I need to login to a @server using a username @username and password @password. On that server i have to use another password for mysql @sql_password and then start querying.

PUTTY
I start putty connect to @server, give @username and @password and i get connected. Then i issue mysql -p@sql_password and i'm connected to mysql. Then i can use databases and start my queries.
But Now I need to do this in PHP!

PHP

1. First method :-
   Use phpseclib to establish an SSH connection and then try connecting to mysql. Unfortunately when i issue the mysql -p@sql_password command my code just keeps running and i can see nothing on the screen. Now some solutions talked about using different syntaxes but nothing seems to work for me.
   Here is the simple code

   set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib');
   include('phpseclib/Net/SSH2.php');
   
   $server=@server;
   $username=@username;
   $password=@password;
   $sql_password=@sql_password;
   
   
   $ssh = new Net_SSH2($server);
   if (!$ssh->login($username, $password)) {
     exit('Login Failed');
   }
   
   echo $ssh->exec('whoami');
   
   
   echo $ssh->exec('mysql -p@sql_password');
   

2. Second Method
   use PHP Shell_exec command and plink.
   From the command line i can issue plink -ssh @username@@server -pw@password and it seems to work properly. From there onwards its the same as putty. Now when i try using shell_exec in php it does'nt seem to work. For instance echo shell_exec('plink'); gives me all the options of plink but when i run echo shell_exec ('plink -ssh @username@@server -pw@password'); it gives me nothing. I do a whoami to try and figure out somethings and i get nt/authority system ,while on command line i see my proper username.
   
   I am really lost here. Any help would be greatly appreciated.
   thanks

Answer 1

EDIT : my fisrt solution cannot work if you can't acces to the server:

Second solution, correct the code for ssh connection :

if (!$ssh->exec('mysql -u root -puserpass \r\n')) {
    exit('Login to MySQL Failed');
} else {
    echo "Login to MySQL Success";
}

No space between -p and the pass.

---

First solution :

I propose you this solution :

On your local machine

set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib');
include('phpseclib/Net/SSH2.php');

$server=@server;
$username=@username;
$password=@password;


$ssh = new Net_SSH2($server);
if (!$ssh->login($username, $password)) {
  exit('Login Failed');
}

echo $ssh->exec('php script.php');

On the server

script.php

mysql -p@sql_password

Then you avoid the security issue of showing the password of mysql in the local machine. I also recommend you to use public/private key for ssh.

Answer 2

You could do something like...

echo $ssh->exec('echo "select * from table where company_id=\"15\";" | mysql -u username -password=password database');

You could also do...

$ssh->write("mysql -p@sql_password\n");
echo $ssh->read("some prompt");
$ssh->write("SQL\n");

...etc...

This might also work:

$ssh->enablePTY();
$ssh->exec("mysql -p@sql_password");
$ssh->read("some prompt");
$ssh->write("SQL\n");