Connect to a MySQL server over SSH in PHP

Question

I have my database on remote Linux machine, and I want to connect using SSH and PHP functions (I am currently using ssh2 library for that). I tried using mysql_connect, but it gives me can't access (although I have granted permission) when I tried using this function:

$connection = ssh2_connect('SERVER IP', 22);

ssh2_auth_password($connection, 'username', 'password');

$tunnel = ssh2_tunnel($connection, 'DESTINATION IP', 3307);

$db = mysqli_connect('127.0.0.1', 'DB_USERNAME', 'DB_PASSWORD', 
                         'dbname', 3307, $tunnel)
    or die ('Fail: '.mysql_error());

I got error "mysqli_connect() expects parameter 6 to be string, resource given". How can I resolve this?

Answer 1

SSH Tunnel Solution

Set up an SSH tunnel to your MySQL database server (through a Jumpbox proxy for security).

(A) GUI Tools

Depending on your requirements, you can use a GUI MySQL client with SSH Tunnelling support built-in such as Visual Studio Code Forwarding a port / creating SSH tunnel, TablePlus or use PuTTY to setup local port forwarding.

On macOS, I like Secure Pipes or TablePlus.

(B) Command Line

Step 1.

ssh -fNg -L 3307:10.3.1.55:3306 username@ssh-jumpbox.com 

The key here is the '-L' switch which tells ssh we're requesting local port forwarding.

I've chosen to use port 3307 above. All traffic on my local machine directed to this port will now be 'port-forwarded' via my ssh client to the ssh server running on the host at address ssh-jumpbox.com.

The Jumpbox ssh proxy server will decrypt the traffic and establish a network connection to your MySQL database server on your behalf, 10.3.1.55:3306, in this case. The MySQL database server sees the connection coming in from your Jumpbox' internal network address.

Local Port Forwarding Syntax
The syntax is a little tricky but can be seen as:

<local_workstation_port>:<database_server_addr_remote_end_of_tunnel>:<database_server_port_remote_end> username@ssh_proxy_host.com

If you're interested in the other switches, they are:

-f (go to background)
-N (do not execute a remote command)
-g (allow remote hosts to connect to local forwarded ports)

Private Key Authentication, add (-i) switch to above:

-i /path/to/private-key

Step 2.

Tell your local MySQL client to connect through your SSH tunnel via the local port 3307 on your machine (-h 127.0.0.1) which now forwards all traffic sent to it through the SSH tunnel you established in step 1.

mysql -h 127.0.0.1 -P 3307 -u dbuser -p passphrase

Data exchange between client and server is now sent over the encrypted SSH connection and is secure.

Security note
Don’t tunnel directly to your database server. Having a database server directly accessible from the internet is a huge security liability. Make the tunnel target address the internet address of your Jumpbox/Bastion Host (see example in step 1) and your database target the internal IP address of your database server on the remote network. SSH will do the rest.

Step 3.

Now connect up your PHP application with:

<?php
      $smysql = mysql_connect( "127.0.0.1:3307", "dbuser", "passphrase" );
      mysql_select_db( "db", $smysql ); 
?>

Credit to Chris Snyder's great article detailing ssh command line tunnelling for MySQL connectivity.

Answer 2

Unfortunately, the ssh2 tunnel offered by php doesn't seem able to handle a remote mysql connection as you cannot specify the local port to tunnel (it only works with port 22 or whatever ssh port your remote server is running on). My solution to this is to just open the tunnel via exec() operator and connect as usual from there:

exec('ssh -f -L 3307:127.0.0.1:3306 user@example.com sleep 10 > /dev/null');
$mysqli = new mysqli('127.0.0.1', 'user', 'password', 'database', '3307');

Answer 3

I was looking for the same thing, but I prefer not to need external commands and manage external processes. So at some point I thought, how hard can it be to write a pure PHP MySQL client which can operate on any PHP stream? It took me about half a day, based on the MySQL protocol documentation.

https://gist.github.com/UCIS/4e509915ed221660e58f5169267da004

You can use this with the SSH2 library or any other stream:

    $ssh = ssh2_connect('ssh.host.com');
    ssh2_auth_password($ssh, 'username', 'password');
    $stream = ssh2_tunnel($ssh, 'localhost', 3306);
    $link = new MysqlStreamDriver($stream, 'SQLusername', 'SQLpassword', 'database');
    $link->query('SELECT * FROM ...')->fetch_assoc();

It does not implement the complete mysqli API, but it should work with all plain-text queries. Please be careful if you decide to use this, I haven't thoroughly tested the code yet and the string escaping code has not been reviewed.

Answer 4

I believe that the reason I (and I suppose most people) have a problem getting this to work is because the user in the mysql server is set to only allow from "localhost" and not 127.0.0.1, the IP address of localhost.

I got this to work by doing the following steps:

Step 1: Allow 127.0.0.1 host for target user

SSH normally into your server, and log in with the mysql root user, then issue the command:

GRANT ALL ON yourdbname.* TO yourdbuser@127.0.0.1 IDENTIFIED BY 'yourdbpassword';

The key of course, is specifying 127.0.0.1 above.

Step 2: Start local SSH tunnel to MySQL

You can now start your local SSH tunnel to the remote MySQL server, like so:

ssh -vNg -L 33306:127.0.0.1:3306 sshuser@remotehost.com

-v makes ssh operate in verbose mode, which kind of helps to see what's happening. For example, you'll see debugging output like this in your terminal console when you attempt a connection:

debug1: client_input_global_request: rtype hostkeys-00@openssh.com want_reply 0
debug1: Connection to port 33306 forwarding to 127.0.0.1 port 3306 requested.

and output like this when you close the connection:

debug2: channel 2: is dead
debug2: channel 2: garbage collecting
debug1: channel 2: free: direct-tcpip: listening port 33306 for 127.0.0.1 port 3306, connect from 127.0.0.1 port 52112 to 127.0.0.1 port 33306, nchannels 3

-N makes ssh issue no commands, and just wait instead after establishing connection.

-g allows remote hosts to connect to local forwarded ports. Not completely sure if this is necessary but it might be useful for multiplexing multiple connections through the same SSH tunnel.

-L This is the main parameter that specifies the local port 33306 to connect to the remote host's local IP address 127.0.0.1 and the remote host's mysql port, usually 3306.

You can use whatever mechanisms / other parameters needed after this to connect through SSH to your remote host. In my case, I use key files configured in my ~/.ssh/config so I just need to specify user@host to get in.

Issuing the command like this runs SSH in the foreground, so I can easily close it with Ctrl + C. If you want to run this tunnel in a background process you can add -f to do this.

Step 3: Connect from PHP / other mysql compatible methods

The SSH tunnel running from above on your localhost will behave exactly like as if your mysql was running on 127.0.0.1. I use port 33306 (note the triple 3) which lets me run my local sql server at its normal port. You can now connect as you would normally do. The mysql command on the terminal looks like this:

mysql -h 127.0.0.1 -P 33306 -u yourmysqluser -p

where -P (capital P) specifies the port where your SSH tunnel's local end is accepting connections. It's important to use the 127.0.0.1 IP address instead of localhost because the mysql cli will try to possibly use the linux socket to connect.

For PHP connection strings, my data source name string (for my Yii2 config) looks like this:

'dsn' => 'mysql:host=127.0.0.1;dbname=yourdbname;port=33306',

Passwords, and usernames are specified as normal.

Answer 5

According to the docs, that last parameter is supposed to be a socket or pipe name, something like '/var/run/mysql/mysql.sock'. Since you're not connecting using a UNIX socket, that doesn't apply to you... so try just leaving it out.

Answer 6

Make sure that your username and password that you are connecting with has the right hostname permissions. I believe you can use '%' for a wildcard. Also if you are connecting to remote machine (which I would assume you are if you are trying to ssh into it) that is not on your local network you will have to forward the ports on your router where the server is for outside traffic to be able to connect to it.

http://www.lanexa.net/2011/08/create-a-mysql-database-username-password-and-permissions-from-the-command-line/

Answer 7

even i tried it by doing ssh both by root credentials and and public private key pair, but it allows me to conect through command line but not through php code. I tried by creating tunnel also(by using ssh2 functions),ans running shell commands from php code(system,exec etc), nothing worked. Finally i tried ssh2 function to execute shell command and it finally worked :) Here is code, if it helps you:----

$connection = ssh2_connect($remotehost, '22');
if (ssh2_auth_password($connection, $user,$pass)) { 
    echo "Authentication Successful!\n";
} else {
    die('Authentication Failed...');
}

$stream=ssh2_exec($connection,'echo "select * from zingaya.users where id=\"1606\";" | mysql');
stream_set_blocking($stream, true);
while($line = fgets($stream)) { 
    flush();
    echo $line."\n";
}

it worked for me try this if want to use php functions specifically.

Answer 8

When using ssh2 to connect, since there is no special function for php to use ssh2 to connect to mysql, we can only use the traditional socket and mysql protocol for data interaction.

When we use the ssh2_tunnel method, if the creation is successful, a socket object will be returned, then we can use this socket object for mysql data interaction. Of course, we need to understand the mysql protocol, which is the so-called "handshake packet"

......
$tunnel = ssh2_tunnel(....)
......

//we need to construct the mysql data packet and then use fwrite to transfer this packet to mysql host

fwrite($tunnel, MYSQLDATAPACKET)

when we use ssh2_connect method to connect the mysql host, it will return a MySQL handshake packet, it looks like this:

a 5.5.5-10.3.34-MariaDB-cll-lve�WfyP`uKW����RtscuF:/}J7umysql_native_password!��

here is the mysql handshake packet structure:

size(byte)  description
1           protocol version
n           server version
4           connection id
8           auth-plugin-data-part-1
1           filler
2           capability flags
1           character set
2           status flags
2           capability flags
1           length of auth-plugin-data
10          reserved
13          auth-plugin-data-part-2
n           auth-plugin name

so we should parse the byte array by using such structure.

After receving the Mysql handshake, which means we have connected the mysql host, and now we need to login mysql, so we need to construct the mysql send data packet

here is the data structure: (HandshakeResponse41)

4              capability flags, CLIENT_PROTOCOL_41 always set
4              max-packet size
1              character set
string[23]     reserved (all [0])
string[NUL]    username
  if capabilities & CLIENT_PLUGIN_AUTH_LENENC_CLIENT_DATA {
lenenc-int     length of auth-response
string[n]      auth-response
  } else if capabilities & CLIENT_SECURE_CONNECTION {
1              length of auth-response
string[n]      auth-response
  } else {
string[NUL]    auth-response
  }
  if capabilities & CLIENT_CONNECT_WITH_DB {
string[NUL]    database
  }
  if capabilities & CLIENT_PLUGIN_AUTH {
string[NUL]    auth plugin name
  }
  if capabilities & CLIENT_CONNECT_ATTRS {
lenenc-int     length of all key-values
lenenc-str     key
lenenc-str     value
   if-more data in 'length of all key-values', more keys and value pairs
  }

See Also: https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::HandshakeResponse41

Okay, so we have connected the mysql host successfully, and then you can query the database.

Here is the sample code for the Mysql Handshake and Mysql Socket Login:

function parseMysqlHandshakePack($hex_string)
{
    $dataField = {
        "protocol_version" => "",
        "server_version" => "",
        "thread_id" => "",
        "salt1" => "",
        "salt2" => "",
        "salt" => "",
    }
    
    $dataField["protocol_version"] = UtiliHelper::HexToInt(UtiliHelper::HexSub($hex_string,0,1));
    $dataField["server_version"] = UtiliHelper::HexToStr(UtiliHelper::HexSub($hex_string,1,7));
    $dataField["thread_id"] = UtiliHelper::HexToInt(UtiliHelper::HexSub($hex_string,8,4));
    $dataField["salt1"] = UtiliHelper::HexSub($hex_string,12,8);
    $dataField["salt2"] = UtiliHelper::HexSub($hex_string,39,12);
    
    $dataField["salt"] = $dataField["salt1"] . $dataField["salt2"];
    
    return $dataField;
}

function constructMysqlLoginPacket($username, $password, $database, $salt){
    $tags = [
        "power_tag" => "",
        "power_ext" => "",
        "max_length" => "",
        "charset" => "",
        "fill_pad" => "",
        "username" => "",
        "password" => "",
        "database" => "",
        "client_auth_plugin" => "",
        "payload" => ""
    ];
    $tags['power_tag'] = "8da2";
    $tags['power_ext'] = "0b00";
    $tags['max_length'] = "000000c0";
    $tags['charset'] = "08";
    $tags['fill_pad'] = "0000000000000000000000000000000000000000000000";
    $tags['client_auth_plugin'] = "6d7973716c5f6e61746976655f70617373776f726400";
    $tags["payload"] = "150c5f636c69656e745f6e616d65076d7973716c6e64";
    
    $tags['username'] = UtiliHelper::StrToHex($username)."0014";
    $tags['password'] = UtiliHelper::encryptionPass($password,$salt);
    $tags['database'] = UtiliHelper::StrToHex($database)."00";
    
    $message = "";
    foreach ($tags as $tagv){
        $message .= $tagv;
    }
    return UtiliHelper::IntToHex(strlen($message)/2)."01".$message;
}

the $salt parameter came from the Handshake phase, so you need to parse the mysql handshake message and get the salt.

The UtiliHelper is a custom class which is from a 3rd party party project in Github: https://github.com/gphper/PHPMysql/blob/master/src/UtiliHelper.php