olvc89 olvc89 - 1 year ago 63
MySQL Question

Is it appropriate to use a shell command to download MySQL tables from within a C# program?

I am writing a C# program that needs to obtain data from a MySQL database in a REMOTE server. The internet connections that it will be using are extremely slow and unreliable, so I want to minimize the data that is being transferred.

The following shell command gets MySQL to store data from a certain table as a *.txt file in the LOCAL machine:

mysql.exe -u USERNAME -pPASSWORD -h REMOTE_SERVER_IP DB_NAME -e "SELECT * FROM table1" > C:/folder/file_name.txt

As of now, I am writing a C# program that will execute this command. HOWEVER, when executing this command from the Windows Command Prompt, I get a Warning that says "Using a password on the command line interface can be insecure." I have a few questions:

1- What kind of security risk is it referring to?

2- Does this risk still exist if you execute it from within another program?

3- Would any of y'all use the same approach? How does this compare with using a straight MySqlConnection and calling in SP's to store all of the data in RAM (and inserting it into the local database later), in terms of amounts of data transferred, speed and RAM usage? (In theory, of course, I don't expect anyone to have tried this specific comparison already)

4- Is the code on the following link the best for this? Isn't there something in the MySql library (.Net Framework) that will make it easier?

How to use mysql.exe from C#

I am also open to suggestions on changing my approach altogether, just in case...

EDIT: The alternate method I referred to in 3 uses the MySqlDataAdapter class, which stores the data in DataSets.

Answer Source

1 & 2

As you're passing password as CLI arguments, if they were displayed on screen, anyone can see your password. As easy as that.

Rest of points

It's not true that you would take all records into memory. If you use MySQL's IDataReader MySqlDataReader (i.e. you'll need to call MySqlCommand.ExecuteReader method) implementation, you can sequentially retrieve results from the database like an stream, thus, you can read each result in the result set one by one and store them in a file using a FileStream.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download