Selecting two columns for SqlCommand class

Question

I would like to find out whether there is a way to select two columns in the same table for a single SqlCommand which would be afterwards used for comparison between data in the two columns and data in two textBoxes:

Below are two strings what I use at the moment for SqlCommand class and would like to put them together:

String str1 = String.Format("SELECT * FROM [employeeAccount] WHERE [User Name] LIKE '{0}'", txtUserName.Text);
String str2 = String.Format("SELECT * FROM [employeeAccount] WHERE [Password] LIKE '{0}'", txtPassword.Text);

You are wide open for [sql injection attacks](http://en.wikipedia.org/wiki/SQL_injection). Please use bind parameters in any language you can. C# has them, use them. — nvoigt, Feb 06 '15 at 15:37

score 4 · Accepted Answer · answered Feb 06 '15 at 15:38

4

Just use AND in your sql query and sql-parameters to prevent sql-injection:

string sql = @"SELECT * FROM [employeeAccount] 
               WHERE [User Name] = @UserName
                 AND [Password]  = @Password";
using(var command = new SqlCommand(sql, con))
{
    con.Open();
    command.Parameters.AddWithValue("@UserName", txtUserName.Text);
    command.Parameters.AddWithValue("@Password", txtPassword.Text);
    // ...
}

answered Feb 06 '15 at 15:38

Tim Schmelter

450,073
74
686
939

Good answer, but shouldn't it be `OR`? And why do you pass `%` as the parameter's values? – stakx - no longer contributing Feb 06 '15 at 15:39
@stakx: i'm not sure, maybe. It's not that clear what OP is actually trying to achieve. According to the `%`: it was just a partly removed part of my answer. My first version contained the `LIKE` but it is redundant. – Tim Schmelter Feb 06 '15 at 15:39

score 1 · Answer 2 · edited May 23 '17 at 12:28

Few things to improve....

Do not use string concatenation/formatting to form SQL queries, you are prone to SQL injection. Parametrize your queries. Use SqlParameter
Important!. Do not use LIKE to compare against user name and password, you probably want exact match using =
You need to combine two conditions using AND operator.

So your code should look like:

using(SqlConnection connection = new SqlConnection("yourConnectionString"))
using (
    SqlCommand command =
        new SqlCommand(
            "SELECT * FROM [employeeAccount] WHERE [UserName] = @userName AND [Password] = @password",
            connection))
{
    command.Parameters.AddWithValue("@username", txtUserName.Text);
    command.Parameters.AddWithValue("@password", txtPassword.Text);
    connection.Open();
    //,... execute command
}

One last thing to add, do not store password text in database, instead store their hashes, see: How to store passwords *correctly*?

@fubo, I replied after during the same time, I don't think I can copy that much in one and half minute. By the way did you miss all the other details ? — Habib, Feb 06 '15 at 15:52

score 0 · Answer 3 · answered Feb 06 '15 at 15:38

Instead of

String str1 = String.Format("SELECT * FROM [employeeAccount] WHERE [User Name] LIKE '{0}'", txtUserName.Text);
String str2 = String.Format("SELECT * FROM [employeeAccount] WHERE [Password] LIKE '{0}'", txtPassword.Text);

Do

String str1 = String.Format("SELECT * FROM [employeeAccount] WHERE [User Name] LIKE '{0}', SELECT * FROM [employeeAccount] WHERE [Password] LIKE '{1}", txtUserName.Text, txtPassword.Text);

Selecting two columns for SqlCommand class

3 Answers3