Problem with login code

[Guest]

I am tring to create a very simple login page that asks for an email address
and password and compares the password entered to the password in the
accounts table to authenticate the user. I get the following error when
trying use executereader():

System.Data.SqlClient.SqlException: The column prefix 'asmussen@cableone'
does not match with a table name or alias name used in the query.

string mySelectQuery = "SELECT AccountID, Email, Password FROM Accounts
WHERE Email = " + Email.Text;

System.Data.SqlClient.SqlCommand myCommand = new
System.Data.SqlClient.SqlCommand(mySelectQuery, sqlConnection1);

sqlConnection1.Open();
myReader = myCommand.ExecuteReader();

if (Password.Text == myReader["Password"])
{
Session["Login"] = myReader["Email"];
Session["AccountID"] = myReader["AccountID"];
}
else
{
lblStatus.Text = "Login Failed";
}

myReader.Close();
sqlConnection1.Close();

Any ideas?
Thanks, Justin.

 

[Steve C. Orr [MVP, MCSD]]

Quotes need to go around the string parameter in your query.
So your first line needs to look like this:
string mySelectQuery = "SELECT AccountID, Email, Password FROM Accounts
WHERE Email = '" + Email.Text +"'";

 

[Mark Fitzpatrick]

Justin,
You may want to look into adding parameters to your query (check out
the SqlParameter object). When you create a string on the fly for a query
and pass it to a command object it's probably suject to a SQL Injection
Attack, which means a moderately skilled hacker could get all the user
accounts by adding some carefully crafted SQL statements into your Email
textbox and have them displayed for him right from your system.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

 

[Guest]

Thanks for the help guys. I put quotes around the variable but the results
remian the same. Why is the last part of the email get chopped off (should be
(e-mail address removed) not asmussen@cableone)?

I haven't tried using parameter yet, can you point to more info. or a
tutorial on using select parameters?

Thanks, Justin.

 

[James Thomas]

Try this:

In SQL:

CREATE PROC getLoginInfo
@Email nvarchar(50)
AS
SELECT
AccountID,
Email,
Password
FROM
Accounts
WHERE
Email = @Email

---
In code:
SqlCommand cmd = new SqlCommand();

cmd.CommandText = "getLoginInfo";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = new SqlConnection(<connection string>);

cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
cmd.Parameters["@Email"].Value = Email.Text

There's an easier way to do this, but it requires special casting when
an integer parameter is 0 that I would just assume not do.

However, it is done like this:
cmd.Parameters.Add("@Email", Email.Text);

Then read from your data reader as normal.

That may not compile but it will get you started anyway.

James