?
Solved

ASP.NET C# Datareader loop problem

Posted on 2007-03-18
5
Medium Priority
?
286 Views
Last Modified: 2012-05-05
Hello,

i am trying to make my own membership system in asp.net 2.0 c# (i am using MY SQL)

public void btnSignUp_OnClick(object sender, EventArgs e)
      {
            // CREATE THE CONNECTION
            MySqlConnection con = new MySqlConnection(connectionString);
            try
            {
                  // CREATE THE COMMAND
                  string sql = "SELECT * FROM tbl_user";
                  
                  // CONFIGURE THE CONNECTION
                  MySqlCommand cmd = new MySqlCommand(sql, con);
                  
                  // OPEN THE DATABASE CONNECTION
                  con.Open();

                  // RUN THE QUERY
                  MySqlDataReader reader = cmd.ExecuteReader();

                  // PARSE THE RESULTS
                  while (reader.Read() == true)
                  {
                        object objusername = reader["user_name"];
                        object objpassword = reader["user_password"];

                        // IF USERNAME ALREADY EXISTS
                        if (txtNewUsername.Text == objusername.ToString())
                        {
                              lblSignUpStatus.Text = "Username already taken.";
                        }      
                        

                        // IF USERNAME DOES NOT EXIST
                        if (txtNewUsername.Text != objusername.ToString())
                        {
                              lblSignUpStatus.Text = "you can use this one, its not being used.";
                        }      
                        
                  }
                  // CLOSE THE READER
                  reader.Close();
            }
            finally
            {
                  // CLOSE THE DATABASE CONNECTION
                  con.Close();
            }      
      }

when the submit button is pressed the code tried to find if the username the customer has chosen already exists in the database, if it does already exist then it will show the message "username already taken".

if the username does not exist then the customer is shown an ok message.

the problem here is that there is 2 usernames in the table and when i chose the 2nd username in the table, it shows the message that it is ok to use. if i choose the first username in the table, it wont.

if i take away this

// IF USERNAME DOES NOT EXIST
                        if (txtNewUsername.Text != objusername.ToString())
                        {
                              lblSignUpStatus.Text = "you can use this one, its not being used.";
                        }      

the problem no longer occurs, but i need the code above to validate wether the username can be used or not.

can anyone help?
thanks
0
Comment
Question by:thomasmutton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Edwin_C
ID: 18746338
The problem of the code is that even it finds a match, the while loop continues and hence the second if has a chance to show the "OK" message to overwrite the "error" message.  My recommendation is to find existing record directly rather than getting all records and then search one by one because it is very ineffective.

string sql = "SELECT count(*) FROM tbl_user WHERE user_name=@username";
MySqlCommand cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@username", txtNewUsername.Text);
con.Open();
int count = (int)cmd.ExecuteScalar();
con.Close();
if (count>0) {
  lblSignUpStatus.Text = "Username already taken.";
}
else {
 lblSignUpStatus.Text = "you can use this one, its not being used.";
}

Hope that helps

Edwin

0
 

Author Comment

by:thomasmutton
ID: 18746612
hello, i see what you are doing there but i seemed to have solved this by.

// CREATE THE COMMAND
                        string sql = "SELECT * FROM tbl_user WHERE user_name = '" + txtNewUsername.Text + "'";

                        // CONFIGURE THE CONNECTION
                        MySqlCommand cmd = new MySqlCommand(sql, con);

                        // OPEN THE DATABASE CONNECTION
                        con.Open();

                        // RUN THE QUERY
                        MySqlDataReader reader = cmd.ExecuteReader();

                        // PARSE THE RESULTS
                        if (reader.HasRows == true)
                        {
                              lblSignUpStatus.Text = "Username already taken.";                        
                        }
                        else if (reader.HasRows == false)
                        {
                              lblSignUpStatus.Text = "you can use this one, its not being used.";
                              SaveNewUser();
                        }

                        // CLOSE THE READER
                        reader.Close();

selecting the row where the username is = to what the user chose and then asking if that row exists or not.

it seems to work fine.
0
 
LVL 16

Expert Comment

by:Edwin_C
ID: 18746633
Yes it also works but you have to watch for SQL injection attack in your approach.  Imagine what will happen if someone entered "nobody; DELETE * FROM tbl_user;" in txtNewUsername.  It will get the records in your table deleted.  Using parameters can avoid this.

Also, you are not going to read the record data from your database and so I think it is unnecessary to create an datareader object in this case.

That is my little opinion.
0
 
LVL 16

Accepted Solution

by:
Edwin_C earned 2000 total points
ID: 18746637
Sorry! The correct attack string should be " nobody'; DELETE * FROM tbl_user; SELECT ' ".  Anyway I hope you can see what I mean.
0
 

Author Comment

by:thomasmutton
ID: 18746984
hello edwin and thankyou for your advice. I will try to fix the SQL injection. i will give you the points as you are the only person to have helped me. thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question