SqlDataReader & an SQL statement problem

Original issue went unanswered (well, unsolved) Using returned value from SqlDataSource1 so I'm trying a different route. Instead of writing to Profile, I'm going with Sessions.

Here's my issue and some code.

The select command won’t let me conditional Where LoginId = the value of the login textbox and Where LoginPwd = value of the password text box.

The query when tested does return 164 when I enter in a test user's username and Password into the Query Bulder's "Test Query" but when I put those values into the WHERE area in the Select, it says “no TESTUSERNAME column” “no TESTUSERPASSWORD” column.

I have to use WHERE (LoginId = LoginId) AND (LoginPwd = LoginPwd) (the generic DB column names) to get it to work…BUT the output to the Textbox3 is 223, which is the item in the first column for the last record row in the DB. It’s supposed to be the userID associated with the Login : Password combo.

WHERE (LoginId = “ + Session[“name”] + “) AND (LoginPwd = “ + Session[“password”] + “ LoginPwd) doesn’t work (the session variables are filled above the SQL clause by the items in the textboxes see below code).

protected void Button1_Click(object sender, EventArgs e)

{

//set session login name and password from textboxs

Session["name"] = TextBox1.Text;

Session["password"] = TextBox2.Text;

//connect to the DB

SqlConnection cnn =

new SqlConnection(@"data source=companysssqldb;Initial Catalog=MyTestingDBname;Integrated Security=True");

//here is the command to return, hopefully, the userID associated with the login name and password

SqlCommand cmd = new SqlCommand("SELECT DISTINCT UserId FROM [User] WHERE (LoginId = LoginId) AND (LoginPwd = LoginPwd)", cnn);

//start connection and do read

cnn.Open();

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

TextBox3.Text = Convert.ToString(dr.GetInt32(0)); //writes to textbox what was returned by the reader

Session["userid"] = TextBox3.Text; //sets the value of the session userid to the textbox value

}

//Pack up and go home

dr.Close();

cnn.Close();

}

How can I get the select command, which works in the query builder, to output to the data reader the User ID associated with that username and userpassword combination instead of just jumping down to the last row

NOTE: If I remove the While and just do one dr.Read(); it returns "1", the first column. I'm somehow not getting the query to associate the userId row.....

Please, any help would end this 4-day pain and let me get on to actually designing the web app instead of working on the plumbing. Thank you all for taking the time to read this and respond.

-Corby-

Answer this question

SqlDataReader & an SQL statement problem

Logan1337
I tried that, along with trying using the Sessino["name"] and Sessino["password"]. It gave me the same "No TESTUSERNAME column" "No TESTUSERPASSWORD column" error message.

Even if I hard code into that select statement a working username/password combo, for example userId 155 is tied to login name : bob with password : iscool, it still isn't outputting 155 like it should.
Adam Shipp

You can use Integration services to create a map from your old db into the new database.
Amry
You never want to do this:

SqlCommand cmd = new SqlCommand("SELECT DISTINCT UserId FROM [User] WHERE (LoginId ='"+TextBox1.Text + ") AND (LoginPwd='" + TextBox2.Text +")", cnn);

Here is an old, but still valid post on the risk of Sql Injection:

SQL Injection Attacks - Parameterized Queries - Regular Expressions - ASP.NET Security Best Practices

Regards,

Dave
Haluk Yildirim

I just want to make sure that you're not using ASP.Net 2.0 before we make a complicated solution that is given for free in 2.0.
Gem Lode Ranch
Just wanted to update this thread for any other newbie out there.

The above suggestions for using the builtin Membership aspect of Visual Studio 2005 seems to be the way for me to go. It seems, as I tend to do, I was trying to reinvent the wheel.

This article, http://aspnet.4guysfromrolla.com/articles/120705-1.aspx, not only explained the entire Membership setup in VS2005, but after some minor adjustments, I was able to create with ease a login page, new user creation page, and more. I was also able to update my existing SQL database to take advantage fo the Membership and Rolls by following the tutorial. Good stuff.

From there, I was also able to set a temperary Profile.Username = to the username stored in the database ( accessible via User.Identity.Name) and POW, my app is working smoothly. I'm next going to move on to getting my app to key off of Session["name"] instead of the Profile.Username.

Thank you everyone for your help and I hope this post will help out some other new guy in the future.

-Corby-
Mujdat Dinc
SqlCommand cmd = new SqlCommand("SELECT DISTINCT UserId FROM [User] WHERE (LoginId = LoginId) AND (LoginPwd = LoginPwd)", cnn);

This should be

SqlCommand cmd = new SqlCommand("SELECT DISTINCT UserId FROM [User] WHERE (LoginId ='"+TextBox1.Text + ") AND (LoginPwd='" + TextBox2.Text +")", cnn);
JodyT

Corby,

The reason you are getting all rows is because your command statement will always be true. You are not actually putting in the id and password provided by the user.

However, it doesn't look like you are hashing passwords, but instead storing your user passwords in a database in clear text. That is a huge security problem.

Before you go any further with this application, I recommend reading the ASP.NET 2.0 Security Best Practices Document on MSDN:

http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnpag2/html/PAGPractices0001.asp

The whole article is a must read, but there is information there on how to protect sensitive data in the database and how to protect passwords.

If you are using ASP.NET 2.0, I recommend using the membership features built into it as opposed to rolling your own. The membership features help you will all the logon and security features. Check out Scott Guthrie's blog post for a whole list of resources on not only membership, but the security of your application.

http://weblogs.asp.net/scottgu/archive/2006/02/24/438953.aspx

Security is so important that you really want to understand the proper way of implementing it in your applications before you go any further.

Regards,

Dave
Sutty
I am using 2.0 with Visual Studio 2005.

The reason I am not using the built-in membership is because we already have an existing database (sql server 2003) with user info. I can't really figure out how to get the data from our existing db into the Visual Studio 2005 built-in Membership portion.

Is there a way to somehow get the data out of the existing database and into the built-in 2.0 Membership thing you discuss with ease

I do not have the time, nor the resources, to take the 1000+ current users in the SQL db and port them by hand into ASP 2.0's built-in feature.

Any advice on this, specifically because of the excellent security points brought up by previous posters above, would be greatly appreciated!

Thanks guys/gals!
-Corby-
SqlDataReader & an SQL statement problem

Answer this question