SQL selecting multiple columns at once

Question

Hallo I am still new in this game. I am struggling to select the username and password from the database to verify it.

What this below mentioned code can do is ONLY select the username.

I need help to modify the select statement to select both username and password.

 public override int SelectUser(string username, ref User user)
 {
  int rc = 0;

    try
    {
        _sqlCon = new SQLiteConnection(_conStr);
        bool bRead = false;
        user = new User();

        _sqlCon.Open();

// This selection string is where I am struggling.

      string selectQuery = "SELECT * FROM Users WHERE [uUsername] = ' " + username + " ' ";

        SQLiteCommand sqlCmd = new SQLiteCommand(selectQuery, _sqlCon);
        SQLiteDataReader dataReader = sqlCmd.ExecuteReader();
        bRead = dataReader.Read();
        if(bRead == true)
        {
            user.Username = Convert.ToString(dataReader["uUsername"]);
            user.Password = Convert.ToString(dataReader["uPW"]);
            rc = 0;
        }// end if
        else
        {
            rc = -1;
        }// end else
        dataReader.Close();
    }// end try
    catch(Exception ex)
    {
        throw ex;
    }// end catch
    finally
    {
        _sqlCon.Close();
    }// end finally
    return rc;
}// end method

... AND Password = .... but this whole approach is wrong. Do not use string concatenations to build an sql command. Use Parameters — Steve, Jan 08 '17 at 14:26
What else must I do? The only thing I know is string concatenation. — Rhonwen Van Druten, Jan 08 '17 at 14:38
Yes I did, you said I must use parameters rather than string concatenation to build a sql command, but I do not know how to do that. — Rhonwen Van Druten, Jan 08 '17 at 14:43
And the answer shows how to use parameters. First, the query string contains a series of parameter placeholders (@name, @pass) then, after creating the command you fill the Parameters collection with a name (matching the placeholder), a type (to avoid misunderstandings on the value) and a value for the parameter. Everything is passed to the database engine that knows how to use the parameters value in executing your query text. This remove possible bugs in parsing (single quotes in values) and any possible injection as explained in the link below — Steve, Jan 08 '17 at 14:46

score 2 · Accepted Answer · edited May 23 '17 at 11:46

You add the AND logical operator to the WHERE first condition to get a double condition. However, this whole approach using string concatenations is wrong.
It is a well known source of bugs and a big security risk called Sql Injection

Instead you use a parameterized query like this

string selectQuery = @"SELECT * FROM Users 
                       WHERE [uUsername] = @name AND 
                             [uPw] = @pass";

SQLiteCommand sqlCmd = new SQLiteCommand(selectQuery, _sqlCon);
sqlCmd.Parameters.Add("@name", DBType.String).Value = username;
sqlCmd.Parameters.Add("@pass", DBType.String).Value = password;
SQLiteDataReader dataReader = sqlCmd.ExecuteReader();
bRead = dataReader.Read();
....

I assume that you have in the variable password the value to search for, change it to your actual situation.

Consider also that storing passwords in clear text inside a database is another security risk to avoid. The question Best way to store password in a database explains in great detail the reasons and the correct way to do it

SQL selecting multiple columns at once

1 Answers1