SSH and sudo connection to MySQL instance from C#

Question

I am trying to write a program that will allow me to interact with a remote database that is on the local server within an Ubuntu Virtual Machine. Below are the commands I can run in the windows terminal to connect to the database, and these are essentially the same commands I want to execute in the program in order to communicate with the database.

>ssh user@192.168.0.60
user@192.168.0.60's password: *********

>sudo mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 101
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

I am able to establish the ssh connection easily, I can execute a command like ls and see the contents of the current directory. But when I try to execute a command like sudo mysql the program seems to enter a loop - I am guessing because it is prompted for the password but I don't know how to provide the password when it is prompted.

I'll add the connection code below. I really just need to be able to run select statements with the connection to retrieve data from the MySQL database.

using System;
using Renci.SshNet;


namespace DBconnector
{
    class Program
    {
        private static string username = "user";
        private static string password = "password";
        private static string host = "192.168.0.60";

        static void Main(string[] args)
        {
            SshClient client = new SshClient(host, 22, username, password);
            client.Connect();
            if (!client.IsConnected)
            {
                Console.WriteLine("Connection Error: Failed");
                System.Environment.Exit(1);
            }

            SshCommand dir = client.RunCommand("...some command..."); // This is where I want to run 'sudo mysql'

            // and here I need to be able to change DB and select from tables

            Console.WriteLine(dir.Result);
        }
    }
}

Thank you!

There are MySQL libraries like EntityFramework(.Core) or something worse haha, there's absolutely no need to connect via SSH. — misticos, Dec 16 '19 at 18:16
the mysql connector for dot net also can ssh https://dev.mysql.com/doc/connector-net/en/connector-net-ssh-tunneling.html — nbk, Dec 16 '19 at 18:17
The instructions here may help: https://mysqlconnector.net/tutorials/connect-ssh/ — Bradley Grainger, Dec 16 '19 at 21:00

Martin Prikryl · Answer 1 · 2019-12-16T19:33:54.680

For your literal question, see:

Providing input/subcommands to a command (cli) executed with SSH.NET SshClient.RunCommand

Though, automating sudo this way is a bad practice:

Allowing automatic command execution as root on Linux using SSH

I believe you will actually want to tunnel the MySQL connection through an SSH:

(unless your database server actually allows a direct connection)

score 0 · Accepted Answer · answered Dec 20 '19 at 16:19

I just wanted to update this thread with the solution that worked for me. I ended up using a mysql connector as suggested above.

using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Net.NetworkInformation;

namespace DBconnector
{
    class DBConnect
    {
        public MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        private void Initialize()
        {
            Console.WriteLine("Initializing Connection...");
            server = "192.168.0.60";
            database = "devDB";
            uid = "user";
            password = "password";
            string connectionString;
            connectionString = "SERVER="+server+";" + "UID="+uid+";" + "DATABASE="+database+";" + "PASSWORD="+password+";";

            connection = new MySqlConnection(connectionString);
        }


        /* Sample select statement from a table in the DB */
        public List<string> SelectFBOEntries(String statement)
        {
            string query = statement;
            List<string> response = new List<string>();
            if (this.OpenConnection())
            { 
                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    response.Add(dataReader["ID"] + "");
                    response.Add(dataReader["Name"] + "");
                    response.Add(dataReader["Age"] + "");
                    response.Add(dataReader["DOB"] + "");
                    response.Add(dataReader["ZIP"] + "");
                }
                dataReader.Close();
                this.CloseConnection();
                return response;
            }
            else
            {
                return response;
            }
        }

        public bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error Closing Connection");
                return false;
            }
        }

SSH and sudo connection to MySQL instance from C#

2 Answers2