Connect to remote MySQL database using SSH

[_Dron_]

Hello!
I need your help with Qt database and SSH. I want to establish a connection with remore MySQL database using SSH. For SSH I use libssh and the folloing code:

Settings settings; const Settings::SSH &ssh{settings.loadSSH()}; ssh_key key;
int code = ssh_pki_import_privkey_file("key.ossh", ssh.password.toStdString().c_str(), NULL, NULL, &key);
if(code != SSH_OK)
{
	_logger->logOnce("Bad private key!");
	return false;
}
 
ssh_options_set(_ssh, SSH_OPTIONS_HOST, ssh.host.toStdString().c_str());
code = ssh_connect(_ssh);
if(code != SSH_OK)
{
	_logger->logSshErrorOnce("Unable to connect to host!", _ssh);
	return false;
}
code = ssh_userauth_publickey(_ssh, ssh.login.toStdString().c_str(), key);
if(code != SSH_AUTH_SUCCESS)
{
	_logger->logSshErrorOnce("Unable to authorize!", _ssh);
	return false;
}
code = ssh_channel_open_forward(_channel, ssh.host.toStdString().c_str(), 22, "localhost", 3307);
if(code != SSH_OK)
{
	_logger->logSshErrorOnce("Unable to create ssh-channel!", _ssh);
	return false;
}

Settings settings; const Settings::SSH &ssh{settings.loadSSH()}; ssh_key key;
int code = ssh_pki_import_privkey_file("key.ossh", ssh.password.toStdString().c_str(), NULL, NULL, &key);
if(code != SSH_OK)
{
	_logger->logOnce("Bad private key!");
	return false;
}

ssh_options_set(_ssh, SSH_OPTIONS_HOST, ssh.host.toStdString().c_str());
code = ssh_connect(_ssh);
if(code != SSH_OK)
{
	_logger->logSshErrorOnce("Unable to connect to host!", _ssh);
	return false;
}
code = ssh_userauth_publickey(_ssh, ssh.login.toStdString().c_str(), key);
if(code != SSH_AUTH_SUCCESS)
{
	_logger->logSshErrorOnce("Unable to authorize!", _ssh);
	return false;
}
code = ssh_channel_open_forward(_channel, ssh.host.toStdString().c_str(), 22, "localhost", 3307);
if(code != SSH_OK)
{
	_logger->logSshErrorOnce("Unable to create ssh-channel!", _ssh);
	return false;
}

To copy to clipboard, switch view to plain text mode 

It works and there are no errors. BUT, what I should do next to transfer data from my Qt database to ssh tunnel? May be I need to use something in addition (classes, libs...)?
If I write after the code above this:

const Settings::Database &db{settings.loadDatabase()};
_db = QSqlDatabase::addDatabase("QMYSQL", "my_database");
_db.setPort(3307);
_db.setHostName("localhost");
_db.setDatabaseName("test_db");
_db.setUserName(db.login);
_db.setPassword(db.password);
 
_db.open();

const Settings::Database &db{settings.loadDatabase()};
_db = QSqlDatabase::addDatabase("QMYSQL", "my_database");
_db.setPort(3307);
_db.setHostName("localhost");
_db.setDatabaseName("test_db");
_db.setUserName(db.login);
_db.setPassword(db.password);

_db.open();

To copy to clipboard, switch view to plain text mode 

then Qt says that there is a connection error...
How to correctly make the connection? Help please.

PS I dont want to use QProcess and run external tunneler like plink.exe.

[anda_skoa]

Have you verified that the tunnel works?
E.g. by having your program open the tunnel and then connecting through it with a second program?

I guess the remote host can resolve its own hostname, but when using SSH on the commandline, the "target" for a forwarding to the login host itself is usually just "localhost".
Btw, the documentation for the sourcehost argument says "The numeric IP address of the machine from where the connection request originates."

Cheers,
_

[_Dron_]

I dont know works it or not.
I need to create SSH connection to database. HOW TO DO THIS?
Noticed that there is no information about it in internet. Like it is a military secret.
If somebody did this, please help! Give a manual, share your code...

[anda_skoa]

I dont know works it or not.

The first step of debugging a multi layered problem is to debug its single layers.

I need to create SSH connection to database. HOW TO DO THIS?

This is totally irrelevant until you have verified that the tunnel works.

Cheers,
_

[_Dron_]

Even if it works - how to put data from QtDatabase object into SSH tunnel? Then, how to extract data from SSH tunnel and put it into QtDatabase object and after that fetch an answer?

Have you ever written a SSH connection to remote db? If so, please show an example of your code.
Thanks.

[anda_skoa]

An SSH Tunnel is indistinguishable from the client and server perspective.

The client connects to its side of the tunnel like it would connect to the server,
The server gets connections from its side of the tunnel like it would from the clients.

And the tunnel doesn't care what it is transporting. It just accepts connections one one end, creates a connection on the other end and then passes data back and forth.

You can easily try this with a stand-alone SSH client that supports tunnels.

Cheers,
_

[jefftee]

In your example, you are trying to open a channel from localhost:3307 to remote:22. MYSQL isn't listening on port 22 on the remote, right? Assuming MYSQL is listening on its normal default port 3306, try this for line 22:

code = ssh_channel_open_forward(_channel, ssh.host.toStdString().c_str(), 3306, "localhost", 3307);

code = ssh_channel_open_forward(_channel, ssh.host.toStdString().c_str(), 3306, "localhost", 3307);

To copy to clipboard, switch view to plain text mode 

Hope that helps.

P.S. I should add that you have to write the code that listens on localhost:3307 and read/write data to/from the ssh channel you created. If you don't do this, there will be nobody listening on localhost:3307 and therefore when QSqlDatabase attempts to open the connection, it will fail, etc.

One option would be to scrap the libssh code altogether and just use QProcess to create the tunnel with the ssh command. If you do that, all you have to do is start the QProcess, then open the MYSQL connection using localhost:3307. The ssh command would be:

ssh -L <listening port>:<REMOTE_MYSQL_HOST>:<REMOTE_MYSQL_PORT> <SSH_USER>@<SSH_HOST>

That might be a little easier to implement if you're not comfortable creating the socket, listening for connections, then writing the glue code for the libssh channel you created, etc.