1

I have successfully connect to the My VPS using phpscelib library.Now i want to connect to my existing database.Please help me for this ?

<?php
set_include_path(get_include_path() . PATH_SEPARATOR . 'phpseclib');

include('Net/SSH2.php');


$ssh = new Net_SSH2('192.ccc.ccc.ccc');
if (!$ssh->login('ccc', 'cccc')) {
exit('Login Failed');
}

echo $ssh->exec("I need to put MySql commands here");
?>
underscore
  • 6,495
  • 6
  • 39
  • 78

2 Answers2

4

First, wouldn't it be better to allow remote access for that user to mysql? However, I don't know your reasons.

The most common an transparent way would be create a ssh tunnel. This can be done in two different ways. If the mysql port (3306) isn't open on the mysql machine, you'll need a reverse ssh tunnel which has to be opened by the remote machine. Log into the mysql machine and issue the following command:

ssh -R 12345:localhost:3306 user@php_machine -N

If the mysql port is open on the remote machine then the tunnel can be opened by the php machine:

ssh -f user@mysql_machine -L 12345:mysql_machine:3306 -N

Regardless of the way the tunnels has been created, the PHP application can now just use PDO and connect to localhost port 12345.

$pdo = new PDO('mysql:host=localhost;port=12345;dbname=test', $user, $password);

All traffic will get crypted through the tunnel.

If you just want to issue a couple of simple commands you might use the following alternative.

The simplest but unsecure way would be to use the following command:

echo $ssh->exec('mysql -uUSER -pPASSWORD DATABASE -e "SQL COMMAND"');

This is insecure because other users on the system could see the password.

You can workaround the security issue using expect. expect is a program which can pass the password to mysql in a more secure way. Make sure that expect is installed on the remote system. Here comes an example using the SHOW TABLES command on database test:

include('Net/SSH2.php');

$ssh = new Net_SSH2('192.xxx.xxx.xxx');
if (!$ssh->login('ssh_user', 'ssh_password')) {
exit('Login Failed');
}

echo $ssh->exec('expect <<EOF
# disable command output
log_user 0
# start the mysqldump process
spawn mysql -uTHE_USER -p test -e "SHOW TABLES"
# wait for the password prompt
expect "password:"
# send the password (mind the \r)
send "THE_PASSWORD\r"
# enable output again
log_user 1
# echo all outout until the end
expect eof
EOF
');

To further understand what's going, I've recently wrote a my blog article about that.

hek2mgl
  • 152,036
  • 28
  • 249
  • 266
  • i want to connect to the mysql and begin some inserting,updating CUID process .What is this ? – underscore Aug 20 '13 at 02:22
  • This is an example how to issue a command.. I'm using the `SHOW TABLES` command on the database test. – hek2mgl Aug 20 '13 at 02:23
  • bash: -c: line 0: syntax error near unexpected token `(' bash: -c: line 0: `mysql – underscore Aug 20 '13 at 02:48
  • Hmmm. I've tested it, it works.. Maybe you have a copy/paste problem.. However I guess you want something like a reverse ssh tunnel instead.. – hek2mgl Aug 20 '13 at 02:50
  • my password contain ( simple that's the issue.i was connect to the server from localhost using above command – underscore Aug 20 '13 at 02:56
  • and i need a pvt,pub key authentications process also.it's already in phphseclib library – underscore Aug 20 '13 at 02:56
  • The ssh tunnel would being transparent for the php application. No need for phpseclib (unless you want to issue other commands than mysql) – hek2mgl Aug 20 '13 at 02:58
  • @ఠ_ఠ Have added an example for ssh tunneling – hek2mgl Aug 20 '13 at 03:06
  • can i run this command using php ssh -R 12345:localhost:3306 user@php_machine? – underscore Aug 20 '13 at 03:08
  • @ఠ_ఠ The command must be issued on the remote machine. This is to workaround firewall restrictions unless the port 3306 is open. If the port is open you can use a forward ssh tunnel to the machine, what can be done with php – hek2mgl Aug 20 '13 at 03:10
  • let me say.our script have in every single machine and those are connecting to the remote mysql. – underscore Aug 20 '13 at 03:13
  • 1
    You don't need to use expect with phpseclib. Just do $ssh->write('...') and then $read->('...') where the parameter passed to read is what you are expecting. It can take the form of a string literal or a regex. Example of regex: http://phpseclib.sourceforge.net/ssh/examples.html#sudo – neubert Aug 20 '13 at 03:18
  • I have updated my post a little bit. All depends on whether the port 3306 on the mysql machine is open or not – hek2mgl Aug 20 '13 at 03:18
  • @neubert This first looked like a nice idea, but it appeared unstable for me. – hek2mgl Aug 20 '13 at 03:32
  • @ఠ_ఠ Any news about port 3306 on the remote machine? You can find that out by issue `nmap -p 3306 mysql_machine` – hek2mgl Aug 20 '13 at 03:33
  • @hek2mgl - I've never had any problems with it.. maybe post your code in a separate question and we can figure out what's up with it? – neubert Aug 20 '13 at 03:34
  • @neubert http://pastebin.com/JAevqNTY. It hangs. That's because a part of the login message is still in buffers. – hek2mgl Aug 20 '13 at 03:36
  • @hek2mgl i should run any commands using php.please help me with it ? i can't open CMDS in every local pcs(those are in different locations ) and create tunnels and so on – underscore Aug 20 '13 at 03:37
  • 1
    what is `CMDS`? what is `pcs`? – hek2mgl Aug 20 '13 at 03:38
  • i have 30 local machines and one remote server(VPS).we are going to install wamp in every single machine and run our php script there.so i need to connect to the Remote server using ssh.we don't have static IPs with it . – underscore Aug 20 '13 at 03:43
  • 1
    @hek2mgl - the problem is that you didn't add a "\n" before the end of your command. eg. `$ssh->write("mysql -uroot -p test -e 'SHOW TABLES'");` should be `$ssh->write("mysql -uroot -p test -e 'SHOW TABLES'\n");`. I mean, when you type a command in the terminal you normally hit enter don't you? You don't just type the command and not hit enter and expect Linux to magically run it do you? – neubert Aug 20 '13 at 04:18
  • @neubert I've tryed that - even with a new line - It does not working. (I think it is a timing issue). Using `expect` works stable for me. – hek2mgl Aug 20 '13 at 09:46
  • @hek2mgl - maybe it's a different case than your regex is expecting than. You could try doing `$ssh->setTimeout(1)` to have it return back what it has after a minute. The way I figured out it was the "\n" for me was that I did `define('NET_SSH2_LOGGING', 3)`. – neubert Aug 20 '13 at 12:50
  • @neubert phpseclib hasn't an API documentation. Just this example based one. grhhh.. Hoewever I think our dicussion is a little bit off topic here.. I just should never told about that expect stuff. As it is misleading. ssh tunnel is the way to go here. If you like we can continue our discussion in the [PHP chat room](http://chat.stackoverflow.com/rooms/11/php). Just address a message to me there – hek2mgl Aug 20 '13 at 12:54
2

short and simple

echo $ssh->exec('echo "select * from table where company_id=\"15\";" | mysql -u username -password=password database');
Abhishek Goel
  • 18,785
  • 11
  • 87
  • 65