Making SQL password query case sensitive using COLLATE

Question

I am using the following SQL code in my VB in VS2013. I want to create a login form using a database of users stored into a UserList. However The query is not case sensitive. How do I change my query string to use COLLATE or any other case sensitive comparison

Dim Check As String = _
   "SELECT COUNT(*) AS Expr1 FROM UserList HAVING (Username = '" & _
    _UsernameTextBox.Text & "') AND ([Password]= '" & _PasswordTextBox.Text & _
    "') AND (UserType = '" & User.ToString & "')"

    With search
        .CommandText = Check
        .Connection = cn
        If .ExecuteScalar() = 1 Then
            Me.Hide()
            If User = "Trader" Then

                Trader.Show()
            ElseIf User = "Broker" Then
                Broker.Show()
            ElseIf User = "Corporate" Then
                Corporate.Show()
            ElseIf User = "System" Then
                SystemManager.Show()


            End If
        Else : MsgBox("IncorrectInput")
        End If`

You should never store the password unhashed. Store the password as a salted hash (eg PBKDF2), and your problem doesn't occur. — Mark Rotteveel, Jun 27 '14 at 06:44
I suggest you do some research, there are a lot of websites explaining PBKDF2 for .NET. — Mark Rotteveel, Jun 27 '14 at 06:58

Stefan Steiger · Accepted Answer · 2014-07-15T08:16:37.517

   "SELECT COUNT(*) AS Expr1 FROM UserList 
HAVING (Username = @username)  
AND ([Password] COLLATE Latin1_General_CS_AS = @password) 
AND (UserType = @usertype)

"

Apart from the fact that you don't have your password stored and compared with a slow salted cryptographic hash function (=non-reversible encryption), your query is also vulnerable to SQL-injection (when I use a username like "Jean le Rond d'Alambert" or just "d'Alambert".

Another bug is that when you save the password as plain text, say e.g. (n)varchar(32), I can enter a password that is longer than that (e.g. a sentence) ==> bug

Given you're writing a financial application ("broker", "corporate"), SQL-injection is an intolerable security risk.

You can for example MD5-hash your password (cheap & dirty): master.dbo.fn_varbintohexstr(HashBytes('MD5', 'test'))

You have a "System.Data.SqlClient.SqlCommand", there you can add a System.Data.SqlClient.SqlCommand

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        //
        // Description of SQL command:
        // 1. It selects all cells from rows matching the name.
        // 2. It uses LIKE operator because Name is a Text field.
        // 3. @Name must be added as a new SqlParameter.
        //
        using (SqlCommand command = new SqlCommand(
        "SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection))
        {
        //
        // Add new SqlParameter to the command.
        //
        command.Parameters.Add(new SqlParameter("Name", dogName));
        //
        // Read in the SELECT results.
        //
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            int weight = reader.GetInt32(0);
            string name = reader.GetString(1);
            string breed = reader.GetString(2);
            Console.WriteLine("Weight = {0}, Name = {1}, Breed = {2}",
            weight,
            name,
            breed);
        }
        }
    }

If you do it right from the start, then you don't have to change anything later.

But how then Do i specify where to get the query parameters from? — Joey Pinto, Jun 27 '14 at 06:54
security is not an issue for me as yet because it's only a game I am building. Once I have it functional, Security will be my next priority — Joey Pinto, Jun 27 '14 at 07:00
Is there a way to modify my existing code or just aplly the collate property to the entire table? — Joey Pinto, Jun 29 '14 at 10:48
@Frankenstein: SELECT * FROM TABLE WHERE (condition) group by xy order by abc COLLATE Latin1_General_CS_AS — Stefan Steiger, Jul 14 '14 at 08:33

Making SQL password query case sensitive using COLLATE

1 Answers1