CSharp Prepared Statement with MySQL does not work

Question

Hello folk, first I would like to tell you that I am new to CSharp and that is why I did not know that my way of creating the connection with MySQL was wrong, so because of that I had to change it with Prepared Statement. Please be kind!

THis is the method that I am trying to make it works.

[HttpPost]
    public ActionResult ValidatesLogin (User eUser)
    {
        var Email = eUser.Email;
        var Password = eUser.Password;
        try
        {
            MySqlCommand cmd = MySqlConn.cmd;
                cmd = new MySqlCommand(" from User " + "WHERE Email=@email " + "AND Password=@password",
                    MySqlConn.conn);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@email", username);
                cmd.Parameters.AddWithValue("@password", password);
                int result = (int)cmd.ExecuteReader();


            //encrypting the login user password 
            //the encrypter and decrypter must have same key which is 'kahat' below
            var encryptpassword = EncryptHelper.EncryptString(eUser.Password, "kahat");

            // Returns true when username and password match:
            if (result > 0)
            {
                System.Web.Security.FormsAuthentication.SetAuthCookie(eUser.Email, false);
                return RedirectToAction("Index", "Index", new { area = "Index" });

            }

            else
            {
                TempData["Message"] = "Login failed. Email or password supplied doesn't exist.";
                return View("Index");
            }
        }
        catch (Exception ex)
        {
            return ThrowJsonError(ex);
        }
    }

And this code below is my actual connection which works very well.

 [HttpPost]
        public ActionResult Validate(User eUser)
        {
            try
            {
                //Pull email and password from login page
                var Email = eUser.Email;
                var Password = eUser.Password;

                //Pull first email and password from database
                var currentUser = this.rpGeneric.Find<User>(" from User WHERE Email=:email ", new string[] { "email" }, new object[] { Email }).FirstOrDefault();

                //encrypting the login user password 
                //the encrypter and decrypter must have same key which is 'kahat' below
                var encryptpassword = EncryptHelper.EncryptString(eUser.Password, "kahat");
                //Comparing user password in login page and current password in db
                if (currentUser != null && encryptpassword.Equals(currentUser.Password, StringComparison.Ordinal) && currentUser.EmailConfirmed == true)
                {
                    System.Web.Security.FormsAuthentication.SetAuthCookie(eUser.Email, false);
                    return RedirectToAction("Index", "Index", new { area = "Index" });
                }
                else
                {
                    TempData["Message"] = "Login failed. Email or password supplied doesn't exist.";
                    return View("Index");
                }
            }

            catch (Exception ex)
            {
                return ThrowJsonError(ex);
            }
        }

Never store passwords in plain text in the database! Your question suggests you are doing this because you compare the users input directly with your query. https://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database — Philipp Grathwohl, Jul 25 '17 at 12:26
**Do not store passwords in plain text** or encrypt passwords, when the attacker gets the DB he will also get the encryption key. Just using a hash function is not sufficient and just adding a salt does little to improve the security. Iterate over an HMAC with a random salt for about a 100ms duration and save the salt with the hash. Use a function such as `ehash`, `PBKDF2`, `Bcrypt`, `passlib.hash` or similar functions. The point is to make the attacker spend a lot of time finding passwords by brute force. — zaph, Jul 25 '17 at 16:47

score 1 · Answer 1 · answered Jul 25 '17 at 12:24

1

Your SQL query is incomplete.

cmd = new MySqlCommand(" from User " + "WHERE Email=@email " + "AND Password=@password",
                MySqlConn.conn);

You aren't telling MySql what you want it to do, you need to add the "select " clause and the column names that you're selecting from the table:

cmd = new MySqlCommand("select [*],[column, column...] from User " + "WHERE Email=@email " + "AND Password=@password",
                MySqlConn.conn);

answered Jul 25 '17 at 12:24

Ortund

8,095
18
71
139

As an observation: you also don't need to concatenate your command string. `"select * from user where Email = @email and Password = @password` works just fine. – Ortund Jul 25 '17 at 12:25
Thank you very much for your answers but also I cannot understand why the cmd here: MySqlCommand cmd = MySqlConn.cmd; is underlined, it says object does not contain definition for cmd and no extension method cmd acception a first argument of type object – Antony Securo Jul 25 '17 at 12:32
I'm not sure what code you're referring to since I can't see that in your post. What that error means is that you're trying to reference something that doesn't exist. – Ortund Jul 25 '17 at 12:50

score 1 · Answer 2 · answered Jul 25 '17 at 12:25

1

Well, looks like you are missing the SELECT clause in your query unless it's a typo as pointed below

cmd = new MySqlCommand(" from User " + "WHERE Email=@email " + "
                        ^... Here

answered Jul 25 '17 at 12:25

Rahul

76,197
13
71
125

CSharp Prepared Statement with MySQL does not work

2 Answers2