Problem with login code

Discussion in 'ASP .Net' started by =?Utf-8?B?SnVzdGlu?=, Sep 14, 2004.

  1. 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.
     
    =?Utf-8?B?SnVzdGlu?=, Sep 14, 2004
    #1
    1. Advertising

  2. 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 +"'";

    --
    I hope this helps,
    Steve C. Orr, MCSD, MVP
    http://Steve.Orr.net




    "Justin" <> wrote in message
    news:...
    >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], Sep 14, 2004
    #2
    1. Advertising

  3. 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

    "Justin" <> wrote in message
    news:...
    >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.
     
    Mark Fitzpatrick, Sep 14, 2004
    #3
  4. 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
    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.

    "Justin" wrote:

    > 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.
     
    =?Utf-8?B?SnVzdGlu?=, Sep 15, 2004
    #4
  5. =?Utf-8?B?SnVzdGlu?=

    James Thomas Guest

    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

    Justin wrote:

    > I haven't tried using parameter yet, can you point to more info. or a
    > tutorial on using select parameters?
     
    James Thomas, Sep 15, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. William F. Robertson, Jr.
    Replies:
    0
    Views:
    451
    William F. Robertson, Jr.
    Jul 2, 2003
  2. Hermit Dave

    Forms Login Page Not Login Out

    Hermit Dave, Jan 12, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    487
    Hermit Dave
    Jan 13, 2004
  3. Nans
    Replies:
    2
    Views:
    20,115
    Martin Marinov
    Jun 17, 2004
  4. Pascal Blanchard
    Replies:
    0
    Views:
    254
    Pascal Blanchard
    Aug 17, 2004
  5. Pascal Blanchard
    Replies:
    1
    Views:
    286
    Pascal Blanchard
    Aug 18, 2004
Loading...

Share This Page