hey guys, so i've made a very small db in sql and i created a very easy login form to connected to it like this:
protected void btnLogin_Click(object sender, EventArgs e)
{
try
{
SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=MyWebDB;Integrated Security=True");
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_Member where XUsername=@a and XPassword=@b", con);
sda.SelectCommand.CommandType = CommandType.Text;
sda.SelectCommand.Parameters.AddWithValue("@a",tbxUser.Text.ToLower().Trim());
sda.SelectCommand.Parameters.AddWithValue("@b",tbxPass.Text);
DataSet ds = new DataSet();
sda.Fill(ds);
if(ds.Tables[0].Rows.Count == 1)
{
Session.Add("let", "true");
Response.Redirect("services.aspx");
}
else
{
lblError.Visible = true;
}
}
catch
{
lblError.Text = "server down, please try again later.";
}
}
i jus dont understand why its not case sensitive! like if the password is "PASSWORD" and i enter "password" it still connects which it should not. any tots on that?????
By default SQL Server is case insensitive. So unless you've chosen a collation when you installed SQL Server
that is case sensitive, it doesn't matter if the app logs in as sa, SA, sA, or Sa.
Use a case sensitive collation to ensure case sensitivity try like following- e.g.
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_Member where XUsername=@a and XPassword=@b COLLATE SQL_Latin1_General_CP1_CS_AS", con);
VEBMAX
Member
48 Points
45 Posts
sql connections
May 24, 2012 11:17 AM|LINK
hey guys, so i've made a very small db in sql and i created a very easy login form to connected to it like this:
protected void btnLogin_Click(object sender, EventArgs e) { try { SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=MyWebDB;Integrated Security=True"); SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_Member where XUsername=@a and XPassword=@b", con); sda.SelectCommand.CommandType = CommandType.Text; sda.SelectCommand.Parameters.AddWithValue("@a",tbxUser.Text.ToLower().Trim()); sda.SelectCommand.Parameters.AddWithValue("@b",tbxPass.Text); DataSet ds = new DataSet(); sda.Fill(ds); if(ds.Tables[0].Rows.Count == 1) { Session.Add("let", "true"); Response.Redirect("services.aspx"); } else { lblError.Visible = true; } } catch { lblError.Text = "server down, please try again later."; } }i jus dont understand why its not case sensitive! like if the password is "PASSWORD" and i enter "password" it still connects which it should not. any tots on that?????
sql
karthicks
All-Star
31378 Points
5422 Posts
Re: sql connections
May 24, 2012 11:29 AM|LINK
hi, for case-sensitive check , use COLLATE Latin1_General_CS_AS
select * from tbl_Member where XUsername=@a and XPassword COLLATE Latin1_General_CS_AS =@b
i have not tested above. i hop it will work. if not write stored procedure with above query
Refer : http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/
http://www.codeproject.com/Articles/306549/Case-Sensitive-search-with-SQL
sql
Karthick S
mishra.bhupe...
Participant
1598 Points
378 Posts
Re: sql connections
May 24, 2012 11:36 AM|LINK
By default SQL Server is case insensitive. So unless you've chosen a collation when you installed SQL Server that is case sensitive, it doesn't matter if the app logs in as sa, SA, sA, or Sa.
Use a case sensitive collation to ensure case sensitivity try like following- e.g.
SqlDataAdapter sda = new SqlDataAdapter("select * from tbl_Member where XUsername=@a and XPassword=@b COLLATE SQL_Latin1_General_CP1_CS_AS", con);VEBMAX
Member
48 Points
45 Posts
Re: sql connections
May 24, 2012 11:39 AM|LINK
thank you ver much =)
sql