Solved

Connect to Mysql through PHP ssh2_tunnel ?

Posted on 2010-11-16
12
4,949 Views
Last Modified: 2013-11-13
OK, I've searched and searched on this one. I am creating a vb app that pipes over to php cli. The php cli handles database connections and what not without the need to install odbc drivers.  This works great.  Now I wanting to add ssh tunneling for security.  So here's the senorio...

I have a remote MySQL server located somewhere on the internet..
I'm useing php cli to connect to it..
Now I need to connect to it through an ssh tunnel..
All computers as well as the MySQl server are windows based...
Cygwin ssh server installed on the Mysql server and running

So having said that I've been looking at the ssh2_tunnel function in php.  I haven't found much documentation on it other than how to connect. Which I've done. The connections come back successful. So assuming the tunnel is created succesfully, I would like to connect to the to mysql through the tunnel. But using the tunnel resource does not work. Nor does pointing mysql_connect to localhost...

I've read in different places that you have to create a socket and bind to the tunnel. But can't find any documentation on that.. So really I'm just wanting an understanding of how to use ssh2_tunnel in php and if it's possible to use mysql_connect with it...

Here the code I'm looking at...

 
<?

$ssh_server = "4.4.4.4";     
$ssh_port = "22";
$remote_port = "3306";
$ssh_user = "username";
$ssh_pass = "passwword";


$ssh_conn = ssh2_connect($ssh_server, $ssh_port);
if($ssh_conn){echo "Connection Successful!\n";} 
else{echo 'Connection Failed...';die();}

$ssh_auth = ssh2_auth_password($ssh_conn, $ssh_user, $ssh_pass);
if($ssh_auth){echo "Authentication Successful!\n";} 
else{echo 'Authentication Failed...';die();}


$ssh_tunnel = ssh2_tunnel($ssh_conn, $ssh_server,$remote_port;
if ($ssh_tunnel){echo "Tunnel created\n";}
else{echo "Tunnel creation failed!!";die();}

//////////////////////////////////////////////////////
// EVERTHING comes back good to this point now I need to connect to mysql


$usr_mysql = "username";
$pwd_mysql = "passowrd";

// HAVE TRIED THIS
$host_mysql = "127.0.0.1";

// HAVE TRIED THIS
$host_mysql = $ssh_tunnel;


$cid_mysql = mysql_connect($host_mysql,$usr_mysql,$pwd_mysql);

if (mysql_error()) {print mysql_error();die();}

// errors out everytime


?>

Open in new window

0
Comment
Question by:Ben720
  • 7
  • 4
12 Comments
 
LVL 7

Expert Comment

by:Vimal DM
ID: 34156019
0
 

Author Comment

by:Ben720
ID: 34159590
I'm able to connect with the ssh2 functions just fine. And once connected I'm able to use the ssh2_tunnel function to create a php socket stream resource. But I'm stuck there.

I thought I would just be able to use the ssh2_tunnel resource in the mysql_connect function, but get the following error..

$ssh_server = "server address";     
$ssh_port = "22";
$remote_port = "3306";
$ssh_user = "username";
$ssh_pass = "password";

$ssh_conn = ssh2_connect($ssh_server, $ssh_port);
if($ssh_conn){echo "Connection Successful!;} 
else{echo 'Connection Failed...';die();}

$ssh_auth = ssh2_auth_password($ssh_conn, $ssh_user, $ssh_pass);
if($ssh_auth){echo "Authentication Successful!";} 
else{echo 'Authentication Failed...';die();}


$ssh_tunnel = ssh2_tunnel($ssh_conn, "localhost",$remote_port);
if ($ssh_tunnel){echo "Tunnel created;}
else{echo "Tunnel creation failed!!";die();}

$usr_mysql = "username";
$pwd_mysql = "password";

$cid_mysql = mysql_connect($ssh_tunnel,$usr_mysql,$pwd_mysql);
if (mysql_error()) {echo mysql_error;die();}

Open in new window


"Warning: mysql_connect() expects parameter 1 to be string, resource given"

Also I thought if a ssh tunnel was actually made by the function that I could just point mysql_connect to localhost (as if I used PuTTy to forward ports over a tunnel), but get the following error..

$ssh_server = "server address";     
$ssh_port = "22";
$remote_port = "3306";
$ssh_user = "username";
$ssh_pass = "password";

$ssh_conn = ssh2_connect($ssh_server, $ssh_port);
if($ssh_conn){echo "Connection Successful!;} 
else{echo 'Connection Failed...';die();}

$ssh_auth = ssh2_auth_password($ssh_conn, $ssh_user, $ssh_pass);
if($ssh_auth){echo "Authentication Successful!";} 
else{echo 'Authentication Failed...';die();}


$ssh_tunnel = ssh2_tunnel($ssh_conn, "localhost",$remote_port);
if ($ssh_tunnel){echo "Tunnel created;}
else{echo "Tunnel creation failed!!";die();}

$usr_mysql = "username";
$pwd_mysql = "password";
$host_mysql = "localhost";

$cid_mysql = mysql_connect($host_mysql,$usr_mysql,$pwd_mysql);
if (mysql_error()) {echo mysql_error;die();}

Open in new window


"Warning: mysql_connect(): [2002] A connection attempt failed because the connected party did not  (trying to connect via tcp://localhost:3306)"

"Warning: mysql_connect(): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond."

I'm starting to wonder if this is even possible. I've yet to find an example of what I'm trying to accomplish. I know I could use PuTTy's plink.exe to make a tunnel which I've done with WshShell.exec and it works just fine with my php cli script. But I'd much rather handle everything in php..
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34161729
From my reading, I have been able to identify a few potential problems with your code.  More importantly, I've come across a very good reason to not do this from within PHP - it takes some time to establish an SSH session.  If each script is lighting up its own session, that means every request on your site will have that couple second delay before anything starts happening.  

In any case, take a look here:

http://themetricsystem.rjmetrics.com/2009/01/06/php-mysql-and-ssh-tunneling-port-forwarding/

It is a good tutorial on how to set this up.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34161751
Also, remember that when you create an SSH tunnel, you're basically telling your server to take any local communication on port x and forward it via SSH to a remote system.  That means the localhost needs to be used as the host for your mysql_connect() request, not the resource returned from ssh_connect().  Also, the article mentions an oddity in MySQL, namely that a connection to "localhost" will skip the TCP stack altogether, opting for a local socket.  Using "127.0.0.1" forces the use of the stack, which allows SSH to capture the request and forward it properly.  I'm not sure how this behavior will translate to PHP, since PHP uses a separate library for MySQL interaction.  The "normal" MySQL library may still follow that pattern of behavior, which explains the error you received in your tests.
0
 

Author Comment

by:Ben720
ID: 34164022
Thanks for the replies. Unless I'm not understanding the article correctly the ssh tunnel is still not being created by php. The command  to create the tunnel "ssh -f -L 3307:127.0.0.1:3306 user@remote.rjmetrics.com sleep 60 >> logfile" is using the ssh program installed on the local system to create the tunnel rather than php and unless I'm not understanding correctly that would only work if i was trying to tunnel to the MySQL server from a linux box. The vb application is running from windows using php-win.exe as a pipe. As far as the time it takes to create a tunnel, that's not to much of an issue as the php script used is basically acting as a server. In other words the script is infinitely looping accepting commands from vb through the stdin and performing the appropriate function, meaning the mysql connection only has to be performed once as well as the ssh tunnel being created only once. I'm pretty much able to do the same thing as the article with PuTTy's plink command line ssh program for windows. But for simplicity sake and not having to use an external program to make the connection, I would like to handle the ssh connection as well as the tunnel completely in php.  

Having said that, that's why I have been looking at the ssh2 functions included in php. I came across the "ssh2_connect" and "ssh2_tunnel" functions and thought using these would do the same thing as issuing the ssh command ""ssh -f -L 3307:127.0.0.1:3306 user@remote.rjmetrics.com sleep 60 >> logfile"" with out the need of having a ssh program installed on the client.  I guess these functions are not what I was looking for or I don't understand how to use them.

So I guess to clarify the question I'm asking. Can php and only php create a port forwarding ssh tunnel without using an external ssh program (linux being ssh and windows being putty or plink)?  Or somehow use a resource the resource that is created with the ssh2_tunnel php function?

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34170505
You are correct - the majority of that article is about using an external application to build the tunnel.  After further review, I think that is going to your best, if not only, option.  I missed a particular comment at the end of that article:

A final note on PHP: there is a very poorly documented PHP extension called SSH2 that provides an interface for making SSH connections via PHP functions (eliminating the need for shell_exec and its brethren).  Unfortunately, however, the current version of this library does not allow you to specify a local port when opening an SSH tunnel.  This renders the library rather useless for the MySQL application, since the port used by the remote application is almost always in use by the local server.  For other applications of SSH tunneling, however, you may find it to be a more secure bet.

Without being able to bind the local side of the tunnel to an arbitrary port, you are stuck with the local 3306.  If you are running MySQL locally as well, that means you will be connecting to your local service, not the tunnel.  Even worse, I have had trouble finding any concrete examples of real world use, and describing the extension as "poorly documented" is being gracious.  The examples I have found either use an external tunnel like the previous article or use ssh_shell()/ssh_exec() to use remote clients for connecting to the target service.  So even without a local MySQL installation interfering, I have approximately zero evidence that the extension will work as expected in the context of tunnels.  All of my current environments do not have the SSH2 extension installed, so I can't even run my own tests at the moment.

I did happen to find a pay-for-license Windows component that might do the trick for you.  Their sample code implies they resolved the local port problem found in the PHP extension:  

http://www.example-code.com/php/sshTunnel_database.asp

Otherwise, my recommendation is to continue using an external application to create a persistent tunnel.  That is actually a bit more efficient than creating the tunnel with each execution, though it does increase your management necessities.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Ben720
ID: 34190826
thanks again for your reply.. I think you right about php. Not much I can do. However I did come across this article on php.net that suggest editing a few lines of source to make this usable.  I'm in the process of compiling this and testing.. Will post back if I can ever get it compiled...

article - http://pecl.php.net/bugs/bug.php?id=9612
0
 

Author Comment

by:Ben720
ID: 34261234
Ok, this maybe changing the direction of the original question but having a little trouble compiling php_ssh2.dll . I edited the source as the article suggest above and  I'm using vc9 to compile php and have pulled the needed libraries from http://pecl2.php.net/downloads/php-windows-builds/php-libs/VC9/x86/ for the shh2 extension. I'm able to compile php with shh2 as a static extension just fine but I'm wanting to compile ssh2 as a shared extension to give me the php_ssh2.dll file. It will compile as a static extension but when I change the configure options to "--with-ssh2=shared"  I get "libssh2_a.lib(openssl.obj) : error LNK2019: unresolved external symbol" errors looking for openssl links and to zlib links as well. I have the openssl libraries in place from the link in this post and have tried adding "--with-oppenssl=shared" to the configure command as well. Just kind of lost on this, everything compiles fine until I ask for the extensions to be shared. Has anybody had any success with this or advice on how to compile php_ssh2.dll?
0
 

Author Comment

by:Ben720
ID: 34262672
by the way the ssh2_tunnel function does accept five values now but still does not open a local port for port forwarding through the tunnel.  I looked at the article again and saw that I used newer Libraries when compiling, so I will recompile with the older versions mentioned in the article and report back.. Still only able to compile the ssh2 extension as static though.  I guess it really doesn't matter though if the function doesn't open a local port and forward through the tunnel... Anyhow will post my results after compiling with the older library versions...
0
 

Accepted Solution

by:
Ben720 earned 0 total points
ID: 34291839
Well, same thing with old libraries.  After thinking about it , I think this function is not what I want. I'm thinking is opens a port on the ssh server and forwarding to  another box rather than opening a port on the client and forwarding to some port on the ssh server. Maybe wrong about that or just not understand it at all, but those are my thoughts..

But on another note I was able to find a PHP class that was written using the php_ssh2.dll to perform what I need. This class opens a ssh connection then opens a port for tunneling using php only with no external ssh programs...


<?
/**
$ssh = new vpssh_core( array(
    'user' => 'username',
    'pass' => 'password',
    'host' => 'server.com',
    'port' => 22
));
$ls_binary = $ssh->which('ls');                         // Returns the string path of the command, if found, or false
echo $ssh->exec( "$ls_binary -lah" );                   // Returns the string results of the command
$result_file = $ssh->exec( "$ls_binary -lah", 'file' ); // Returns a filename which contains the results of the command
$result_fp = $ssh->exec( "$ls_binary -lah", 'fp' );     // Returns a rewound filepointer which contains the result of the command
**/

/** Example usage: Key Based Auth MySQL Proxy
$user_pass_config = array(
    'user' => 'username',
    'host' => 'server.com',
    'port' => 22,
    'kpub' => "ssh-dss ...",
    'kpri' => "-----BEGIN DSA PRIVATE KEY-----\n...\n-----END DSA PRIVATE KEY-----",
);
$local_proxy_port = 4444;
$remote_proxy_host = '127.0.0.1';
$remote_proxy_port = 3306;
$tunnel = new vpssh_tunnel( $user_pass_config );
$tunnel->proxy( $local_proxy_port, $remote_proxy_host, $remote_proxy_port );
// We now have a tcp server on 127.0.0.1 port 4444 which is proxying, over ssh, to 127.0.0.1 port 3306 on myserver.com
**/

/** Example usage: User/Pass Based Auth MySQL Proxy
$user_pass_config = array(
    'user' => 'username',
    'pass' => 'password',
    'host' => 'myserver.com',
    'port' => 22
);
$local_proxy_port = 4444;
$remote_proxy_host = '127.0.0.1';
$remote_proxy_port = 3306;
$tunnel = new vpssh_tunnel( $user_pass_config );
$tunnel->proxy( $local_proxy_port, $remote_proxy_host, $remote_proxy_port );
// We now have a tcp server on 127.0.0.1 port 4444 which is proxying, over ssh, to 127.0.0.1 port 3306 on myserver.com
**/

class vpssh_tunnel extends vpssh_core {

        var $tunnel = null;
        var $socket = null;

        function connect_to( $remote_host, $remote_port ) {
                if ( !$this->connect() ) return false;
                if ( $this->tunnel )
                        return true;
                $this->tunnel = fopen("ssh2.tunnel://$this->conn/$remote_host:$remote_port", 'r+');
                if ( !$this->tunnel || !is_resource( $this->tunnel ) ) {
                        if ( $this->debug )
                                echo "\tFailed Initializing Tunnel To $remote_host:$remote_port\n";
                        return false;
                }
                if ( $this->debug )
                        echo "\tTunnel To $remote_host:$remote_port Initialized\n";
                stream_set_blocking( $this->tunnel, false );
                return true;
        }

        function create_local_socket( $port ) {
                $this->socket = @stream_socket_server( "tcp://127.0.0.1:$port", $errno, $errstr );
                if ( !$this->socket ) {
                        if ( $this->debug ) 
                                echo "\tFailed creating socket server:\t$errstr ($errno)\n";
                        return false;
                }
                return true;
        }

        function proxy( $local_port, $remote_host, $remote_port ) {
                if ( !$this->connect_to( $remote_host, $remote_port     ) )
                        return false;
                if ( !$this->create_local_socket( $local_port ) )
                        return false;
                $conn = stream_socket_accept( $this->socket, -1 );
                stream_set_blocking( $conn, false );
                while( !feof($conn) && !feof($this->socket) ) {
                        // read from server to client
                        if ( strlen( $data = @fread( $this->tunnel, 4096 ) ) ) {
                                if ( false === fwrite( $conn, $data ) )
                                        break;
                        } else if ( strlen( $data = @fread( $conn, 4096 ) ) ) {
                                if ( false === fwrite( $this->tunnel, $data ) )
                                        break;
                        } else {
                                usleep( 5000 );
                        }
                }
                @fclose( $conn );
                @fclose( $this->tunnel );
                return true;
        }

        function vpssh_tunnel( $args=array() ) {
                $this->init( $args );
        }

        function __construct( $args=array() ) {
                $this->init( $args );
        }

        function init( $args=array() ) {
                parent::init( $args );
        }

}

class vpssh_core {

        var $host  = null; // SSH Host
        var $port  = 22;   // SSH Port
        var $user  = null; // Username

        var $pass  = null; // User Password

        var $kpub  = null; // Public Key
        var $kpri  = null; // Private Key
        var $kpwd  = null; // Key Password

        var $debug = true; // Whether we want debugging messages or not
        var $conn  = null; // Resource: ssh2 connection

        function rawexec( $command ) {
                if ( !$this->connect() ) return false;
                $res = ssh2_exec( $this->conn, $command, null );
                if ( !$res || !is_resource( $res ) ) {
                        if ( $this->debug )
                                echo "\tFailed Executing:\t$command\n";
                        return false;
                }
                return $res;
        }

        function exec( $command, $result='string' ) {
                if ( !$this->connect() ) return false;
                $res = $this->rawexec( $command );
                if ( !$res || !is_resource( $res ) )
                        return false;
                stream_set_blocking( $res, true );
                switch( $result ) {
                        case 'file':
                                $file = tempnam( '/tmp/', 'ssh-exec-' );
                                $fp = fopen( $file, 'w' );
                                while( !feof( $res ) )
                                        fwrite( $fp, fread( $res, 4096 ) );
                                fclose( $fp );
                                fclose( $res );
                                return $file;
                                break;
                        case 'fp':
                                $file = tempnam( '/tmp/', 'ssh-exec-' );
                                $fp = fopen( $file, 'w' );
                                unlink( $file );
                                while( !feof( $res ) )
                                        fwrite( $fp, fread( $res, 4096 ) );
                                fclose( $res );
                                rewind( $fp );
                                return $fp;
                                break;
                        default:
                                $rval = stream_get_contents( $res );
                                fclose( $res );
                                return $rval;
                                break;
                }
        }

        function which( $command ) {
                if ( !$this->connect() ) return false;

                $res = $this->exec( "which $command" );
                if ( $res )
                        return trim( $res );

                $paths = array(
                        '/bin',
                        '/usr/bin',
                        '/usr/local/bin',
                        '/sbin',
                        '/susr/bin',
                        '/susr/local/bin',
                );
                foreach( $paths as $path ) {
                        $res = $this->exec( "if [ -f $path/$command ]; then echo -n 'true'; else echo -n 'false'; fi" );
                        if ( !$res || $res == 'false' )
                                continue;
                        return "$path/$command";
                }
        }

        function connect() {
                if ( $this->conn )
                        return $this->conn;
                if ( !$this->host || !$this->port )
                        return false;
                if ( !$this->user )
                        return false;
                if ( !$this->pass && ( !$this->kpri && !$this->kpub ) )
                        return false;
                $this->conn = ssh2_connect( $this->host, $this->port );
                if ( !$this->conn ) {
                        $this->conn = null;
                        if ( $this->debug )
                                echo "Could not connect to $this->host:$this->port\n";
                        return false;
                }
                if ( $this->debug )
                        echo "Connected to $this->host:$this->port\n";

                if ( $this->pass ) {
                        $auth = ssh2_auth_password( $this->conn, $this->user, $this->pass );
                        if ( !$auth ) {
                                $this->conn = null;
                                if ( $this->debug )
                                        echo "\tCould not authenticate via password for user $this->user\n";
                                return false;
                        }
                        if ( $this->debug )
                                echo "\tLogged in, via password auth, as $this->user\n";
                        return true;
                } else {
                        $pufile = tempnam( '/tmp/', 'ssh-id-' );
                        $prfile = tempnam( '/tmp/', 'ssh-id-' );
                        file_put_contents( $pufile, $this->kpub );
                        file_put_contents( $prfile, $this->kpri );
                        if ( $this->kpwd ) {
                                $rval = ssh2_auth_pubkey_file( $this->conn, $this->user, $pufile, $prfile, $this->pwd );
                        } else {
                                $rval = ssh2_auth_pubkey_file( $this->conn, $this->user, $pufile, $prfile );
                        }
                        unlink( $pufile );
                        unlink( $prfile );
                        if ( !$rval ) {
                                $this->conn = null;
                                if ( $this->debug )
                                        echo "\tCould not authenticate via keyfiles for user $this->user\n";
                                return false;
                        }
                        if ( $this->debug )
                                echo "\tLogged in, via key auth, as $this->user\n";
                        return true;
                }
        }

        function vpssh_core( $args=array() ) {
                $this->init( $args );
        }

        function __construct( $args=array() ) {
                $this->init( $args );
        }

        function init( $args=array() ) {
                foreach( (array)$args as $idx => $val ) {
                        $this->$idx = $val;
                }
        }

}

?>

Open in new window

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 34298114
Very interesting.  The class looks to have resolved the local port issue using streaming contexts.  Otherwise, it continues using the ssh_* functions.  Have you been able to successfully test it?
0
 

Author Closing Comment

by:Ben720
ID: 35005253
none
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spacing 5 30
What is Python programming? 3 68
Jquery Autocomplete PHP script 3 21
two tables one button 11 19
This is about my first experience with programming Arduino.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now