C#•16mo ago

❔ Error on my try and catch

It says that the variable 'ex' is declared but never used and how do i make the username and password more secure?

 [HttpPost]
    public IActionResult AppUserLogin(AppUser user)
    {
        string connectionString = "server=db4free.net;database=rpxxxxx;uid=rpxxxx;pwd=rpxxxxx;";
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            string sql = "SELECT Email,Password FROM AppUser WHERE Email= '" + user.Email + "' AND password = '" + user.Password + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.ExecuteNonQuery();
            TempData["Msg"] = "Successfully login!";
            connection.Close();
        }
        catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password";
        }


        return RedirectToAction("Main");

    }

 [HttpPost]
    public IActionResult AppUserLogin(AppUser user)
    {
        string connectionString = "server=db4free.net;database=rpxxxxx;uid=rpxxxx;pwd=rpxxxxx;";
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            string sql = "SELECT Email,Password FROM AppUser WHERE Email= '" + user.Email + "' AND password = '" + user.Password + "'";
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.ExecuteNonQuery();
            TempData["Msg"] = "Successfully login!";
            connection.Close();
        }
        catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password";
        }


        return RedirectToAction("Main");

    }

22 Replies

Philémon•16mo ago

To make it more secure, i know that you should definitly use Query parameters and not string concatenations

Buddy•16mo ago

$sqlinjection

MODiX•16mo ago

Always parameterize queries! https://i.imgur.com/uePzr0S.jpeg Do not concatenate the query, example:

// Do NOT do this
string query = "SELECT * FROM users WHERE username='" + UserName + "';";
...

// Do NOT do this
string query = "SELECT * FROM users WHERE username='" + UserName + "';";
...

But instead always parameterize your queries. Look up the documentation for said Database library. If you are using System.Data.SqlClient refer to this https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-7.0#examples

Imgur

Philémon•16mo ago

Indeed your Exception ex is not used, i dont think this warning prevents you from running the app but maybe you could consider logging the exception, or writing it to the console with Console.WriteLine ?

catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password";
            Console.WriteLine(ex);
        }

catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password";
            Console.WriteLine(ex);
        }

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

rip the youtube i follow is bad @>@

Buddy•16mo ago

SQL Injection is VERY dangerous Will lead to data breaches, and destruction.

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

i remember my teacher covered that but i always seems to forget thanks it works now the applications is able to run blobthumbsup

will work on my code to prevent sql injection thank you so much ^^

Philémon•16mo ago

No worries ☺️

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

Using the way you say I should code it using this way

string query = "SELECT * FROM users WHERE username =' " @ username  +" ';

string query = "SELECT * FROM users WHERE username =' " @ username  +" ';

The @ username is from the html asp-for I think

Philémon•16mo ago

Its not the @ for the html asp for, i dont think so there is a good example of the use of parameters on the link above

string commandText = "UPDATE Sales.Store SET Demographics = @demographics WHERE CustomerID = @ID;";

string commandText = "UPDATE Sales.Store SET Demographics = @demographics WHERE CustomerID = @ID;";

And when you create your command :

SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
command.Parameters.AddWithValue("@demographics", demoXml);

SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;
command.Parameters.AddWithValue("@demographics", demoXml);

Accord•16mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

like this? but there is an error at varchar. checked my database datatype is varchar(50) but before i change anything. my code doesnt work. how can i troubleshoot?

[HttpPost]
    public IActionResult AppUserLogin(AppUser Emails, AppUser Passwords)
    {
        string? connectionString = _configuration.GetConnectionString(ConnectionStringKey);
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            //string sql = "SELECT Email,Password FROM AppUser WHERE email= '" + Emails + "' AND password = '" + Passwords + "'";
            string sql = "SELECT Email,Password FROM AppUser WHERE email = @Emails AND password = @Passwords";
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.Parameters.Add("@Emails",SqlDbType.VarChar());
            command.Parameters["@Emails"].Value = Emails;      
            command.Parameters.Add("@Passwords", SqlDbType.VarChar());
            command.Parameters["@Passwords"].Value = Passwords;
            command.ExecuteNonQuery();
            TempData["Msg"] = "Successfully login!";
            connection.Close();
        }
        catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password" + ex.Message;
        }
        return View("AppUserLogin");

[HttpPost]
    public IActionResult AppUserLogin(AppUser Emails, AppUser Passwords)
    {
        string? connectionString = _configuration.GetConnectionString(ConnectionStringKey);
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            //string sql = "SELECT Email,Password FROM AppUser WHERE email= '" + Emails + "' AND password = '" + Passwords + "'";
            string sql = "SELECT Email,Password FROM AppUser WHERE email = @Emails AND password = @Passwords";
            MySqlCommand command = new MySqlCommand(sql, connection);
            command.Parameters.Add("@Emails",SqlDbType.VarChar());
            command.Parameters["@Emails"].Value = Emails;      
            command.Parameters.Add("@Passwords", SqlDbType.VarChar());
            command.Parameters["@Passwords"].Value = Passwords;
            command.ExecuteNonQuery();
            TempData["Msg"] = "Successfully login!";
            connection.Close();
        }
        catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password" + ex.Message;
        }
        return View("AppUserLogin");

Philémon•16mo ago

The ExecuteNonQuery method doesn't work for scalar result i guess this could be one of the problems You are trying to Select a row in the database, right ? ExecuteNonQuery is used for queries like update, delete and add I think what you are looking for is ExecuteReader

Philémon•16mo ago

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

Retrieving Data Using a DataReader - ADO.NET

Learn how to retrieve data using a DataReader in ADO.NET with this sample code. DataReader provides an unbuffered stream of data.

Philémon•16mo ago

But there are other problems too, you should make use of "using" statements when you create your connection so that the garbage collector can Dispose it after you are finished using it Also, when you say that your code doesn't work, what is the error that you see ? Something must be displayed i guess

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

i think is because i didnt create a data access layer before i do the api 🤔 https://www.c-sharpcorner.com/article/crud-operation-with-asp-net-core-2-1-mvc-using-ado-net/ But when i at this step my code like faced issue.

CRUD Operation With ASP.NET Core 2.1 MVC Using ADO.NET

In this article you will learn about CRUD Operation with ASP.NET Core 2.1 MVC using ADO.NET.

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

this one seems to cant be used not sure why also having red lines @>@ even tho

public IEnumerable<AppUser> GetAllUser()
        {
            List<AppUser> lstCustomer = new List<AppUser>();

            string? connectionString = _configuration.GetConnectionString(ConnectionStringKey);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();
                SqlDataReader sdr = connection.ExecuteReader();

                while (sdr.Read())
                {
                    AppUser Customer = new AppUser();

                    AppUser.Id = sdr["Id"].ToString();
                    AppUser.FirstName = sdr["FirstName"].ToString();
                    AppUser.LastName = sdr["LastName"].ToString();
                    AppUser.Email = sdr["Email"].ToString();


                    lstCustomer.Add(Customer);
                }
                connection.Close();
            }
            return lstCustomer;
        }

public IEnumerable<AppUser> GetAllUser()
        {
            List<AppUser> lstCustomer = new List<AppUser>();

            string? connectionString = _configuration.GetConnectionString(ConnectionStringKey);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {

                connection.Open();
                SqlDataReader sdr = connection.ExecuteReader();

                while (sdr.Read())
                {
                    AppUser Customer = new AppUser();

                    AppUser.Id = sdr["Id"].ToString();
                    AppUser.FirstName = sdr["FirstName"].ToString();
                    AppUser.LastName = sdr["LastName"].ToString();
                    AppUser.Email = sdr["Email"].ToString();


                    lstCustomer.Add(Customer);
                }
                connection.Close();
            }
            return lstCustomer;
        }

Philémon•16mo ago

Where is your sql command tho? Data access layer is not mandatory It is a good practice but not having one doesn't prevent your code from running

𝔳𝔞𝔪𝔭𝔲𝔯𝔯OP•16mo ago

i forget about it @>@

   [HttpPost]
    public IActionResult AppUserLogin(AppUser user)
    {
        string? connectionString = _configuration.GetConnectionString(ConnectionStringKey);
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            string sqlLogin = "SELECT Email,Password FROM AppUser WHERE Email= @Email AND password @Password ";
            MySqlCommand command = new MySqlCommand(sqlLogin, connection);
            command.Parameters.AddWithValue("@Email", user.Email);
            command.Parameters.AddWithValue("@Password", user.Password);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    if (ModelState.IsValid)
                    {
                        user.Id = reader["Id"].ToString();
                        //user.Email = usr.Email;
                        TempData["Msg"] = "Successfully login!";
                    }
                }
            }
            else
            {
                TempData["msg"] = "Unexpected Database Error";
            }
            connection.Close();
        }
        catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password" + ex.Message;
        }
        //if (ModelState.IsValid)
        //{

        //}
        //else
        //{
        //    TempData["msg"] = "Invalid information entered!";
        //}


        return RedirectToAction("Main");

    }

   [HttpPost]
    public IActionResult AppUserLogin(AppUser user)
    {
        string? connectionString = _configuration.GetConnectionString(ConnectionStringKey);
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            string sqlLogin = "SELECT Email,Password FROM AppUser WHERE Email= @Email AND password @Password ";
            MySqlCommand command = new MySqlCommand(sqlLogin, connection);
            command.Parameters.AddWithValue("@Email", user.Email);
            command.Parameters.AddWithValue("@Password", user.Password);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    if (ModelState.IsValid)
                    {
                        user.Id = reader["Id"].ToString();
                        //user.Email = usr.Email;
                        TempData["Msg"] = "Successfully login!";
                    }
                }
            }
            else
            {
                TempData["msg"] = "Unexpected Database Error";
            }
            connection.Close();
        }
        catch (Exception ex)
        {
            TempData["Msg"] = "Incorrect email or password" + ex.Message;
        }
        //if (ModelState.IsValid)
        //{

        //}
        //else
        //{
        //    TempData["msg"] = "Invalid information entered!";
        //}


        return RedirectToAction("Main");

    }

but the reader["Id"].ToString() and command.ExecuteReader() having red underline

Philémon•16mo ago

I've seen some things that could be coded better, so maybe you can change your code towards something more like this : https://paste.mod.gg/ptnqnnqqkrtx/0 Keep in mind that i did not try it since i don't have your app on my computer. Maybe there are little things to change, but hopefully this will give you a better idea of how to make your code successfully run 😁

BlazeBin - ptnqnnqqkrtx

A tool for sharing your source code with the world!

Philémon•16mo ago

And you are using an ExecuteReader for only one value (the User id), this is not optimal, you could run ExecuteScalar if you just want the Id. If you want more than just one value, then ExecuteReader is a good fit !

Accord•16mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Error on my try and catch