HawaiiDragon
asked on
Check Password against database
I am trying to create a log in for a page. My goal is to have the user hit’s the submit then the information entered on the username and password textboxes are checked against the table “Account_Info” with the collum names Email and Password. Now here comes the tricky part. If the username does not exist the Username and Password are saved to the database and the panel dissipates. If the Username is correct but the password is wrong it propts you to enter the correct password. AND if the user is new and new information is saved then the panel dissipates.
here is my code so far...
here is my code so far...
protected void btnSubmitUserInfo_Click(object sender, EventArgs e)
{
if (textBox1.Text.Trim().Length <= 0)
{
Label1.Visible = true;
Label1.Text = "Please enter User Name.";
// MessageBox.Show("Please enter User Name and Password.", "Message", MessageBoxButtons.OK);
}
else if (textBox2.Text.Trim().Length <= 0)
{
Label1.Visible = true;
Label1.Text = "Please enter Password.";
}
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["strDBCourseStandard"].ConnectionString;
con.Open();
string slelectString = "Select Email, Password from Account_Info where Email = '" + textBox1.Text + "' and Password = '" + textBox2.Text + "'";
SqlCommand cmd = new SqlCommand();
String strResult = String.Empty;
strResult = (String)cmd.ExecuteScalar();
con.Close();
if (strResult.Length > 0)
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["strDBCourseStandard"].ConnectionString;
con.Open();
SqlCommand cmdinsert = new SqlCommand();
cmdinsert.CommandText = "update Account_Info set Email =" + textBox1.Text + "
string strInsert = "Insert into Account_Info (Email, Password) Values ("textBox1.Text +"," + textBox2.Text);
}
}
What are you looking for?
ASKER
How to insert in to the database and also check the database to see if it exists.
you can use ExecuteNonQuery on the SQLCommand to persist
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
and subsequently use the ExecuteReader to verify it's been persisted?
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
and subsequently use the ExecuteReader to verify it's been persisted?
you can do like below..
select count(*) from account_info where email=your email and password=your password.
take the count
if the count =0 then you can display the error message as user name or password not matching etc...
if the count=1 then it is successfull login.
so you can save the log and continue.
select count(*) from account_info where email=your email and password=your password.
take the count
if the count =0 then you can display the error message as user name or password not matching etc...
if the count=1 then it is successfull login.
so you can save the log and continue.
ASKER
not enough detail..... would it be?
System.Data.SqlClient.SqlC onnection con = new System.Data.SqlClient.SqlC onnection( );
con.ConnectionString = ConfigurationManager.Conne ctionStrin gs["strDBC ourseStand ard"].Conn ectionStri ng;
con.Open();
SqlCommand cmdinsert = new SqlCommand();
cmdinsert.CommandText = "Insert into Account_Info (Email, Password) Values ("textBox1.Text +"," + textBox2.Text);
cmdinsert.ExecuteNonQuery( );
System.Data.SqlClient.SqlC
con.ConnectionString = ConfigurationManager.Conne
con.Open();
SqlCommand cmdinsert = new SqlCommand();
cmdinsert.CommandText = "Insert into Account_Info (Email, Password) Values ("textBox1.Text +"," + textBox2.Text);
cmdinsert.ExecuteNonQuery(
yes correct, that's for persisting.
using(SQLConnection conn = new SQLConnection(connString))
{
SQLCommand cmd = new SQLCommand(conn);
cmd.CommandText = "Select count(*) from Acount_Info where [Email] = <Value> AND [Password] = <value>"
SqlDataReader myReader = cmd.ExecuteReader();
while(myReader.Read())
{
// Verify if it's here and data is correct
}
myReader.Close();
}
using(SQLConnection conn = new SQLConnection(connString))
{
SQLCommand cmd = new SQLCommand(conn);
cmd.CommandText = "Select count(*) from Acount_Info where [Email] = <Value> AND [Password] = <value>"
SqlDataReader myReader = cmd.ExecuteReader();
while(myReader.Read())
{
// Verify if it's here and data is correct
}
myReader.Close();
}
Please don't store passwords in plain text. Hash them (with or without a salt, a salt is better ofcourse). Then use a hash function to compare passwords. This is how its done in a professional way.
Regards Marten
Regards Marten
That's true. You should never store plain passwds. Encode/Encrypt them properly. And for verification you will have Encode/Encrypt the passwd and compare against that of DB.
You also shouldn't allow user input to be used directly in a query. Instead do something like:
SqlCommand cmd = new SqlCommand("SELECT Email, Password FROM Account_Info WHERE Email = @Email and Password = @password")
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = textBox1.Text;
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = textBox2.Text;
ASKER
getting error on ExecuteScalar(); (It states that the connection has not been established... but it has on the third line)......
System.Data.SqlClient.SqlC onnection con = new System.Data.SqlClient.SqlC onnection( );
con.ConnectionString = ConfigurationManager.Conne ctionStrin gs["strDBC ourseStand ard"].Conn ectionStri ng;
con.Open();
SqlCommand cmd = new SqlCommand("SELECT Email, Password FROM Account_Info WHERE Email = @Email and Password = @password");
cmd.Parameters.Add("@email ", SqlDbType.Char).Value = textBox1.Text;
cmd.Parameters.Add("@passw ord", SqlDbType.Char).Value = textBox2.Text;
cmd.ExecuteScalar();
con.Close();
System.Data.SqlClient.SqlC
con.ConnectionString = ConfigurationManager.Conne
con.Open();
SqlCommand cmd = new SqlCommand("SELECT Email, Password FROM Account_Info WHERE Email = @Email and Password = @password");
cmd.Parameters.Add("@email
cmd.Parameters.Add("@passw
cmd.ExecuteScalar();
con.Close();
try
{
using(var con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["strDBCourseStandard"].ConnectionString))
{
con.Open();
SqlCommand cmd = new SqlCommand("SELECT Email, Password FROM Account_Info WHERE Email = @Email and Password = @password", con);
cmd.Parameters.Add("@email", SqlDbType.Char).Value = textBox1.Text;
cmd.Parameters.Add("@password", SqlDbType.Char).Value = textBox2.Text;
cmd.ExecuteScalar();
con.Close();
}
}
catch(Exception e)
{
//...
}
Er, rather, the con.Close() is no longer needed within the using statement as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
finally block will throw a compile error as con is limited to using block. isn't it
No, it's been initialized outside of the try block.
Ah, but you'd need to remove the "var" before con in the using statement. I'm apparently awesome at typos!
the sql portion i e 'SqlCommand cmd = new SqlCommand("SELECT Email, Password ...' ought to go against a stored proc imho.
It enhances security, speeds up the database and is considered good practice.
create a proc, that accepts username and pwd. let it do the logic in adding or verifying the user and check responce from the stored proc.
the security enhancement lies in ONLY granting execute to the proc. the underlying tables is not directly accesible for the iis worker process account. this redimes any attempt to sql inject, and also controls the damnage of a compromised application/iis server.
These simple guidelines makes a vast impact on security for your application.
//Marten
It enhances security, speeds up the database and is considered good practice.
create a proc, that accepts username and pwd. let it do the logic in adding or verifying the user and check responce from the stored proc.
the security enhancement lies in ONLY granting execute to the proc. the underlying tables is not directly accesible for the iis worker process account. this redimes any attempt to sql inject, and also controls the damnage of a compromised application/iis server.
These simple guidelines makes a vast impact on security for your application.
//Marten