0
else if(updated_password == confirm_password)
{
   using (var old_connection = new MySqlConnection("server=localhost;user id=" + old_user + ";database=DB;password=" + old_pass))
   {
     MySqlCommand old_cmd = new MySqlCommand("ALTER USER'" + old_user + "'@'localhost' IDENTIFIED BY'" + confirm_password + "'", old_connection);
     old_cmd.ExecuteNonQuery();
     //old_connection.Close();
     MessageBox.Show("Password changed successfully.", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
     //old_connection.Open();
   }
}

How do I prevent SQL injection from occurring in the following code? How do I recode it?

I have tried to use the common SQL injection techniques like x' OR 'x'='x in the username and password but the programs immediately returns an error. So those SQL injection only apply when you using the MySQLCommand and NOT the MySQLConnection?

user2864740
  • 60,010
  • 15
  • 145
  • 220
JoeyB
  • 123
  • 6
  • 3
    Short answer: Parameters. – ProgrammingLlama May 17 '20 at 15:59
  • Have a look here:: [SqlCommand.Parameters Property](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-3.1). There are a lot of tutorials on this – Barns May 17 '20 at 16:00
  • @John thanks for the most direct answer. But I have no idea what that means. Can you show me an example using my code? – JoeyB May 17 '20 at 16:01
  • I'm afraid I'm about to go to bed, but if you search for "mysql parameters c#" you should get some decent results. – ProgrammingLlama May 17 '20 at 16:04
  • @Joey, Did you read the content of the other links provided? – Srinika Pinnaduwage May 17 '20 at 16:05
  • @Srinika its only been 4minutes. I am still reading..... – JoeyB May 17 '20 at 16:06
  • there where you make a concatenation you put @variable name see here https://dev.mysql.com/doc/connector-net/en/connector-net-programming-prepared-preparing.html and because of ypur usrdefined variable you need also https://stackoverflow.com/questions/47128232/prepared-statement-conflicting-with-user-variables – nbk May 17 '20 at 16:06
  • @nbk what does the parameters do exactly to prevent injection? – JoeyB May 17 '20 at 16:08
  • @Barns okay so I tried entering 'OR '1'='1 into the password but the applications sends an error. Does this mean MySql already embedded this into their code? Because that normally bypasses the correct passowrd – JoeyB May 17 '20 at 16:13
  • @rsjaffe okay so I tried entering 'OR '1'='1 into the password but the applications sends an error. Does this mean MySql already embedded this into their code? Because that normally bypasses the correct passowrd – JoeyB May 17 '20 at 16:16
  • when you conact the text all is code, when replace the parameters with data, they are only text, and so they won't be interpreted as sql code – nbk May 17 '20 at 16:31
  • @nbk okay so I tried entering 'OR '1'='1 and x' OR 1=1;# into the password but the applications sends an error. Does this mean MySql already embedded this safety feature into their code? Because that normally bypasses the correct password – JoeyB May 17 '20 at 16:33
  • 1
    no that is why the prepared statements with poarameters exist. I linked the official mysql page to show that this has to be done every time, no excuses. But see also the seocd link that is vital for your code to work. – nbk May 17 '20 at 16:35
  • @nbk thank you. How would I test the application to be sure everything is safe? – JoeyB May 17 '20 at 16:39
  • @user2864740 I have tried to use the common SQL injection techniques like x' OR 'x'='x in the username and password but the programs immediately returns an error. So those SQL injection only apply when you using the MySQLCommand and NOT the MySQLConnection? – JoeyB May 17 '20 at 17:26
  • That’s not SQL Injection. Hopefully the title will avoid the needless river.. – user2864740 May 17 '20 at 17:26
  • @user2864740 then what is? – JoeyB May 17 '20 at 17:28
  • Or maybe I tried to focus the question too much, assuming a basic search was done *first*. Revert as needed. – user2864740 May 17 '20 at 17:31
  • @user2864740 lolololol, its literally just one line of code! I understand it now! – JoeyB May 17 '20 at 17:35
  • You don't have to worry overmuch about malicious data in your connection strings. If they're bogus you'll get an exception, not wrecked data. That being said, I would never, even once in a hundred years, accept a database username or password from a web user without validating it carefully, – O. Jones May 17 '20 at 17:38
  • You have to trust my and microsoft, that they did the right thing, nothing is absolutly secure, that fact of life. And please pemenber never to save passwords as clear text. only hashed values which you comapare. – nbk May 17 '20 at 17:51
  • @nbk the password entered by the user is not stored in the db. It is rather stored in a string in the code. And gets erased when they exit the session or close the application so it's not saved anywhere in the code. – JoeyB May 17 '20 at 18:11
  • your command says alter user and confirmed password, so.... – nbk May 17 '20 at 18:20
  • @nbk that is used to update the old password of the user. So the user was created when setting up mysql and I added the user. The user is not stored in a table. – JoeyB May 17 '20 at 18:33
  • @nbk so once the password is updated the user cannot log in on the application with the old password. The user as to first input the updated password to connect to the db. The new password is stored in a string which gets erased when the session is over or closez the application – JoeyB May 17 '20 at 18:37
  • yes i understand, still i dodn't see this a lot,. usually you make your own login table, so that the user can't login in to the database directly. – nbk May 17 '20 at 18:41
  • @nbk there is only going to be a single user that will have access to the db. So it's redundant to make a table of users like you said, when there's only one person/user that's going to be accessing the db. And the user will also have all privileges to the root db. – JoeyB May 17 '20 at 18:44

0 Answers0