Solved

ASP.NET C# Datareader loop problem

Posted on 2007-03-18
5
285 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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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