0

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.

Community
  • 1
  • 1
olvc89
  • 15
  • 6

4 Answers4

0

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.

Matías Fidemraizer
  • 63,804
  • 18
  • 124
  • 206
  • I've been looking into your MySqlDataReader solution and, although I haven't gotten to implementing it yet, it seems like it might actually be more efficient memory-wise. I'll have to test both methods in order to find out which one is faster with a slow connection. Thanks! For anyone reading this who may need a starting point with the DataReader class: [MSDN link with good explanation](https://msdn.microsoft.com/en-us/library/haa3afyz.aspx) – olvc89 Sep 09 '16 at 20:47
  • @olvc89 Trust me, you'll end up using an `IDataReader` ;P – Matías Fidemraizer Sep 09 '16 at 21:17
  • I added a new answer with the results of testing the methods. In short, you were correct, but the shell command does seem to have some uses. – olvc89 Oct 12 '16 at 21:45
0
  1. It will show your password in plain text either on the screen or in the console output or in memory.
  2. Yes since you need to store the password in plain text either on Disk or in memory
  3. If you are not that concerned about someone gaining access to your remote machine and steal the password without you knowing it, then its fine
  4. You can try Windows Native Authentication Plugin which you wouldn't need to store the password but instead it will use your current windows Login information to authenticate. (unless you are on Linux then forget about it)

It is pretty much the same idea as typing your password on any website without a mask (either dot or *). Whether or not that is a concern for you is for you to decide.

Steve
  • 11,696
  • 7
  • 43
  • 81
  • Thanks for the reply. Unfortunately, making the password secure is a pretty major concern. The Windows Native Authentication Plugin is also not an option because my program needs to be compatible with both Windows and Linux. – olvc89 Sep 09 '16 at 19:10
  • @olvc89 You will have to store it somewhere in this case then. I don't thin it is a major concern cuz for example the sql connection string is stored in plain text as well. – Steve Sep 09 '16 at 19:24
  • You are correct. I already have an encryption/decryption class, so I might just store an encrypted form and decrypt it in the program. Nice catch! BTW, what I meant is that security is really important, and I don't want the password to be accessible. – olvc89 Sep 09 '16 at 19:38
0

Why not connect to the DB the standard way (from w/i .Net like you can connect to an Oracle db for example) using MySqlConnection as shown here MySql Connection. Then, once you do that, you have no password concerns as this is in code. Then I think that I would handle the problem in a similar fashion (incrementally fetching data and storing locally - to get around the internet issue).

Community
  • 1
  • 1
Robert Green MBA
  • 1,834
  • 1
  • 22
  • 45
  • My question also requests a comparison between using a MySqlConnection and using this kind of shell command. If MySql provided a command for making this kind of dump file on the local machine, I wouldn't need a shell command. This part of the manual explains it: http://dev.mysql.com/doc/refman/5.7/en/select-into.html – olvc89 Sep 12 '16 at 18:04
0

So, I finally got around to properly coding and testing both methods (the shell command and the IDataReader), and the results were pretty interesting. In total, I tested a sample of my 4 heaviest tables six times for each method. The method of the shell command needed 1:00.3 minute on average, while the DataReader needed 0:56.17, so I'll go with that because of an overall (and pretty consistent) advantage of 4s.

If you look at the breakdown per step though, it seems that C# needed a full 8s to connect to the database (48.3s for downloading the tables vs the previous total). If you consider that the shell command was most likely establishing and closing a new connection for each table that was being downloaded, it seems to me that something in that process is actually quicker for connecting to the remote database. Also, for one of the tables, the shell command was actually faster by 2.9 seconds. For the rest of the tables, only one was more than 8 seconds slower under the shell command.

My recommendation for anyone in the future is to use the shell command if you're only obtaining a single, large table. For downloading multiple tables, the IDataReader is the more efficient choice, probably because it only establishes the connection once.

olvc89
  • 15
  • 6