SQL Query with String Builder in C#-CodePudding

I am very new to C#, and I ran into some issues when I try to perform a sql query with stringbuilder.

Here is part of the table named :

| ID   | Name      | InternalID |
|:----:|:---------:| :---------:|
| 1    | Emory Lu  | 84765      |
| ...  | ...       | ...        |
| 8    | John Smith| 52455      |
| 9    | John smith| 49         |
| ...  | ...       | ...        |

Notice that John Smith are assigned two different InternalID (string type) in this table. In the second record, his last name started with the lowercase "s".

I performed an easy sql query to select an employee's InternalID (string type) when inputting their name：

public DataTable findInternalID(string name)
{
    ProductionRecordConnection.Open();
    DataTable result = new DataTable();
    OleDbCommand bdCommand = ProductionRecordConnection.CreateCommand() as OleDbCommand;
    StringBuilder commandTextBuilder = new StringBuilder();

    // SELECT InternalID FROM EmployeeList WHERE Name = "(name)"
    commandTextBuilder.AppendLine("SELECT InternalID ");
    commandTextBuilder.AppendLine("FROM EmployeeList ");
    commandTextBuilder.AppendLine($"WHERE Name LIKE '%{name}%'");

    bdCommand.CommandText = commandTextBuilder.ToString();
    _dataAdapter = new OleDbDataAdapter(bdCommand);
    _dataAdapter.Fill(result);

    ProductionRecordConnection.Close();
    return result;

}

This block of code works fine for employees only have one record, but for employees with multiple records, it will fill all InternalID records and output at the same time, due to this line:

commandTextBuilder.AppendLine($"WHERE Name LIKE '%{name}%'");

So I tried to change the LIKE keyword into "=" :

commandTextBuilder.AppendLine($"WHERE Name = '%{name}%'");

but this gives me a mistake.

How can I edit my code, so when inputting "John Smith" only outputs "52455" and inputting "John smith" only outputs "49".

Under the main function, I wrote a piece of code to test this function:

  string name = "John Smith"; // or John smith
  DataTable result = _accessManager.findInternalID(name);
         
  String message = result.Rows[0][0].ToString(); // Or ROWS[1][0]
  MessageBox.Show(message);

BTW, I used MS Access :)

CodePudding user response：

For starters, you should never build a SQL query by concatenating user provided data into the query string. Doing so makes you vulnerable to SQL Injection. Instead, you should use query parameters to pass user provided data to your query.

A rewrite of your original function to use parameters might look something like this:

public DataTable findInternalID(string name)
{
    ProductionRecordConnection.Open();
    DataTable result = new DataTable();
    OleDbCommand bdCommand = ProductionRecordConnection.CreateCommand() as OleDbCommand;
    StringBuilder commandTextBuilder = new StringBuilder();

    // SELECT InternalID FROM EmployeeList WHERE Name = "(name)"
    commandTextBuilder.AppendLine("SELECT InternalID ");
    commandTextBuilder.AppendLine("FROM EmployeeList ");
    commandTextBuilder.AppendLine("WHERE Name LIKE @name");

    bdCommand.CommandText = commandTextBuilder.ToString();
    bdCommand.Parameters.AddWithValue("name", $"%{name}%");
    _dataAdapter = new OleDbDataAdapter(bdCommand);
    _dataAdapter.Fill(result);

    ProductionRecordConnection.Close();
    return result;
}

As for getting a case sensitive query, I can't say that I've actually done that. It looks like COLLATE should work, assuming that your database is SQL Server.

commandTextBuilder.AppendLine("SELECT InternalID ");
commandTextBuilder.AppendLine("FROM EmployeeList ");
commandTextBuilder.AppendLine("WHERE Name = @name COLLATE Latin1_General_CP1_CS_AS");

Or, perhaps casting to varbinary(MAX) to compare the bytes?

commandTextBuilder.AppendLine("SELECT InternalID ");
commandTextBuilder.AppendLine("FROM EmployeeList ");
commandTextBuilder.AppendLine("WHERE CAST(Name as varbinary(MAX)) = CAST(@name AS varbinary(MAX))");

Also, as one last note, you don't really need to use a StringBuilder at all. You could just use a verbatim string with the @ identifier if you want to format the statement across multiple lines.

string commandText = @"SELECT InternalId
FROM EmployeeList
WHERE Name = @name COLLATE Latin1_General_CP1_CS_AS";

CodePudding user response：

You just need case sensitive search, try this one!

commandTextBuilder.AppendLine($"WHERE Name = '{name}' COLLATE Latin1_General_CS_AS ");

CodePudding user response：

You still have the % characters in the exact name search. Those are used in the LIKE query in your first example to indicate wildcard matches.

commandTextBuilder.AppendLine($"WHERE Name = '%{name}%'");

Instead, try the following, which will perform and exact string match.

commandTextBuilder.AppendLine($"WHERE Name = '{name}'");