Click here to Skip to main content
13,731,209 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
hi! i am new at .net and facing the following problem in which when i use the "Count(*)" i am able to login even if i dont enter anything,and when i use only" * after select " i am unable to login even if i provide the corrct username and password heri is my code.

//code

private void button1_Click(object sender, EventArgs e)
      {
          SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Mohammad Fahad\Documents\data.mdf;Integrated Security=True;Connect Timeout=30");
          SqlDataAdapter sda = new SqlDataAdapter("SELECT Count(*) FROM login WHERE user='" + textBox1.Text + "' AND pass ='" + textBox3.Text + "'", con);
          DataTable dt = new DataTable();
          sda.Fill(dt);
          if (dt.Rows.Count > 0)
          {
              this.Hide();
              Main ss = new Main();
              ss.Show();
          }
          else {
              MessageBox.Show("Please Check your usernmae and password");
          }
      }


What I have tried:

i have triend both of the ways and got undesirable results
i am newbie cant find whats the problem
Posted 4 days ago
Updated 4 days ago
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Notice that you are checking to see if there are any rows. SELECT COUNT(*) will always return a row.

The way you are passing in the username and password means that someone could hack your db by passing in SQL commands. You should use parameters and also a better way is to use the using statement. Something like
bool userIsValid = false;
using (SqlConnection sqlCon = new SqlConnection(connString)){
  sqlCon.Open();
  using (SqlCommand cmd = new SqlCommand("LoginUser", sqlCon)){
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@userid", txtUserId.Text);
    cmd.Parameters.AddWithValue("@password", txtPassword.Text);
    using (SqlDataReader dr = cmd.ExecuteReader()) {
      userIsValid = dr.HasRows;
    }
  }
}


And then have a Stored Procedure something like
CREATE PROCEDURE LoginUser
(
  @userid    NVARCHAR (50)
  @password  NVARCHAR (50)
)
AS
BEGIN
  SELECT * FROM users WHERE userid = @userid AND password = @password COLLATE SQL_Latin1_General_CP1_CS_AS -- to make password case sensitive
END


And if possible, encrypt or even hash the password field.
  Permalink  
Comments
4 days ago
   
i dont have the security issue my issue is if i dont use the count in query it wont login even if i enter correct user and password
‭011111100010‬ 4 days ago
   
If you use COUNT the way you have above, it will ALWAYS log you in.
4 days ago
   
ok but now i have removed count
and now code is

private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Mohammad Fahad\Documents\data.mdf;Integrated Security=True;Connect Timeout=30");
SqlDataAdapter sda = new SqlDataAdapter("select * from login where user ='" + textBox1.Text + "' and pass ='" + textBox3.Text + "'", con);
DataTable dt = new DataTable();
sda.Fill(dt);
if (dt.Rows.Count > 0)
{
this.Hide();
Main ss = new Main();
ss.Show();
}
else {
MessageBox.Show("Please Check your usernmae and password");
}
}

but now it doesnt logs in even i have give the correct username and password

i can send you screenshots on facebook or email
‭011111100010‬ 4 days ago
   
1. I don't believe you are actually using the correct username and password.
2. The way you are writing code is wrong and I suggest you use what I gave you.
3. Debug your code. Make sure to open the database and check what your data is. We can't do the debugging for you.
Richard Deeming 4 days ago
   
"i dont have the security issue"

Really?

Try entering the following credentials:
User: ' or 1 = 1 --
Password: (anything you like)

Does that let you log in?

Now try these credentials:
User: '; delete from login; --
Password: (doesn't matter)

Where have all your login records gone?!


Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
4 days ago
   
what i meant was i'm just practicing and i am a beginner
my actual problem is that the program isnt working it doesnt logs in when i enter correct username and password
i know there are security issues but first i want to correct the login problem
Richard Deeming 4 days ago
   
Fix the security issues first, especially the SQL Injection vulnerability.

Otherwise, you'll end up having to jump through a lot of hoops to fix issues with your queries that wouldn't be a problem if you were using parameters. :)
4 days ago
   
i'm not even asking u to debug it
what i was asking for a suggestion
and if i am writing the code in the wrong way
that is why i am asking what mistake i am doing
and i've checked the databse
user ha fahad stored in it and password ha fahad stored in it
‭011111100010‬ 4 days ago
   
I did give you a suggestion. But I can't tell you why it's not working. I can't see your database or your code or your machine.
4 days ago
   
i can send you screenshots if you'll provide me your email address or facebook id
‭011111100010‬ 4 days ago
   
No. Try the code I provided and then if it does not work, you have to debug it. Debugging is the most important skill you can have.
4 days ago
   
ok and thank you for your time
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Two things, in addition to what ‭011111100010‬ has said:
1) Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
DROP TABLE MyTable;
A perfectly valid "delete the table" command
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you? And on a login? That's just handing the keys to the burglars while you are blindfolded!
So go through your whole app, and fix every last one of those - or you will lose your DB at some point.

2) Never store passwords in clear text - it is a major security risk. There is some information on how to do it here: Password Storage: How to do it.[^]
  Permalink  
Comments
4 days ago
   
ok

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180920.1 | Last Updated 11 Oct 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100