PDO via SSH2 in PHP?

Question

I'm trying to access a remote MySQL Database which is only reachable locally (localhost).

My Code looks like this:

$connection = ssh2_connect('IP to Server', 22);

    if (ssh2_auth_password($connection, 'User', 'Password')) {
        echo "Authentication Successful!\n";
    } else {
        die('Authentication Failed...');
    }

    $tunnel = ssh2_tunnel($connection, '127.0.0.1', 3306);

    try {
        $this->_connection = new PDO($dsn, $config['login'], $config['password'], $flags);
        $this->connected = true;
        if (! empty($config['settings'])) {
            foreach ($config['settings'] as $key => $value) {
                $this->_execute("SET $key=$value");
            }
        }
    } catch (PDOException $e) {
        throw new MissingConnectionException(array(
                'class' => get_class($this),
                'message' => $e->getMessage()
        ));
    }

The script is successful till $tunnel. The PDO connection fails with "Connection refused". I think PDO is trying to connect to MAMP? The $dsn variable looks like "mysql:host=127.0.0.1;port=3306;dbname=mydb".

How can I tell MAMP, that 127.0.0.1 is on the remote server connected through SSH?

Thank you!

score 3 · Answer 1 · edited Jun 28 '21 at 17:48

As you already noted, your script is attempting to connect to your local MySQL server instead of the remote one. In order for PDO to establish a connection with the remote server, you could establish an SSH tunnel and forward a local port to the server port. As shown here https://brettrawlins.com/blog/mysql-ssh-tunnel.

To run the ssh command in php: shell_exec(ssh code here)

You might want to see: Connect to a MySQL server over SSH in PHP

However as noted in there as well, this method is relatively slow as you will have to create a new tunnel each time you query the database, making your queries take quite a bit longer. I suppose for a development environment it's a valid option since you don't have a static IP, but I don't see this working in production.

If you are trying to do this for the sake of encryption, I would recommend connecting to your database with SSL. However note that SSL for PDO might not be supported by all PHP versions.

Thank you - I think a more stable solution would be the SSL connection. Just for understanding: is the SSH solution as secure as I think it is? Or ist it just a lot of work and overload? — mittererr, Jun 10 '15 at 14:55
From a security standpoint: If your code ever leaks, it would have your SSH login credentials hard coded into it. Which is not exactly what you are looking for, even if you are using public key authentication, which you should. From a practical standpoint: If you want to encrypt your database traffic, that's what SSL is for. — wittyweasel, Jun 10 '15 at 17:41

PDO via SSH2 in PHP?

1 Answers1

Linked