How to get a value from a query and compare it with a string?

Question

Here is the schema of my Society Table:

Society(SocietyName, Email, Password, Status)

So basically I'm creating a login page in which user enters Email and password. If there is an email which matches the one in database then it checks that whether status is equal to president or faculty member or Student Affairs Office. Based on that , it redirects to different pages. Following is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication3 {
    public partial class WebForm1 : System.Web.UI.Page {
        MySql.Data.MySqlClient.MySqlConnection conn;
        MySql.Data.MySqlClient.MySqlCommand cmd;
        MySql.Data.MySqlClient.MySqlDataReader reader;
        String QueryStr;
        String name;

        protected void Page_Load(object sender, EventArgs e) { }
        protected void clicked(object sender, EventArgs e) {
            String ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["Webappconstring"].ToString();
            conn = new MySql.Data.MySqlClient.MySqlConnection(ConnString);
            conn.Open();
            String QueryStr2 = "";
            QueryStr = "";
            QueryStr = "Select * from the_society_circle.society WHERE Email= '" + Emailtxt.Text + "' And Psswd=' " + passwordtxt.Text + "'";
            cmd = new MySql.Data.MySqlClient.MySqlCommand(QueryStr, conn);
            reader = cmd.ExecuteReader();

            QueryStr2 = "Select Status from the_society_circle.society where Email = '" + QueryStr + "'";
            name = "";
            while (reader.HasRows && reader.Read()) {
                name = reader["Email"].ToString();
            }


            if ((QueryStr2== "president" || QueryStr2 == "faculty member") && reader.HasRows ) {
                    Session["Email"] = name;
                    Response.BufferOutput = true;
                    Response.Redirect("WebForm2.aspx", true);
            } else {
                Emailtxt.Text = "invalid user";
            }
            conn.Close();
            }    
        }
}

The problem is that if statement is never executed and it always prints invalid user.

PS: Im new to web development :D

Answer 1

As codemonkey already wrote, your condition will never come true.

You do the following: if ((QueryStr2== "president" || Quer... which evaluates to if (("Select Status from the_society_circle.society where Email = '" + QueryStr + "'"== "president" || Quer.... So you're comparing two different strings, which will never succeed.

---

I tried to refactor your code and came up with this (not tested, wrote from scratch):

1. First put your database-related code into a separate class (MySqlAccess) and dispose the database objects (put them into using-blocks which invokes Dispose() on leaving the block).
2. Don't use the user-inputs in your sql query directly. Remember "all input is evil". So better use parameterized-queries.
3. The reason your comparison failed was that you didn't execute your second query. Now the code executes just one query and returns the status of the user.

So to sum up:

Have SQL Injection and other malicious actions in mind. For example have a look at this article: http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx

And never store passwords as clear text in your database. That's the next thing you should care about. Edit your database to store the passwords as salted password hashes and just compare the hashes. For a starting point, have look at this article: http://www.codeproject.com/Articles/704865/Salted-Password-Hashing-Doing-it-Right

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MySql;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        private string _connectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            _connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["Webappconstring"].ToString();
        }

        protected void Clicked(object sender, EventArgs e)
        {
            string email = Emailtxt.Text;
            string password = passwordtxt.Text;

            var mysqlAccess = new MySqlAccess(_connectionString);
            string status = mysqlAccess.GetStatus(email, password);

            if (status == Constants.Status.PRESIDENT || status == Constants.Status.FACULTY_MEMBER)
            {
                Session["Email"] = email;
                Response.Redirect("WebForm2.aspx", true);
            }
            else
            {
                Emailtxt.Text = "invalid user";
            }
        }
    }

    internal class MySqlAccess
    {
        private readonly string _connectionString;

        public MySqlAccess(string connectionString)
        {
            _connectionString = connectionString;
        }

        public string GetStatus(string email, string password)
        {
            using (var conn = new MySqlConnection(_connectionString))
            {
                conn.Open();

                string query = "SELECT Status FROM the_society_circle.society WHERE Email=@Email AND Psswd=@Password;";
                using (var cmd = new MySqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@Email", email);
                    cmd.Parameters.AddWithValue("@Password", password);

                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows && reader.Read())
                        {
                            return reader["Status"].ToString();
                        }
                    }
                }
            }
            return string.Empty;
        }
    }

    internal class Constants
    {
        internal class Status
        {
            public const string PRESIDENT = "president";
            public const string FACULTY_MEMBER = "faculty member";
        }
    }
}

Answer 2

You set QueryString2 to this value

QueryStr2 = "Select Status from the_society_circle.society where Email = '" + QueryStr + "'";

It can never be one of the values you check for.