How can I use individual words from user input into a SQL 'WHERE CONTANS' query in C#?

Question

This is my first asked question on here, and I can't find the answer so apologies if I've missed it somewhere.

I'm currently building a search function in a HR Portal that I'm developing (ASP.NET MVC), and while I've managed to get it all set up to query the SQL Server database I've created, it currently works using a LIKE query, e.g.:

public List<Detail> Search(List<string> Information)
{
    StringBuilder Buildsql = new StringBuilder();
    Buildsql.Append("select * from UH_QA.dbo.Answers where ");

    foreach (string value in Information)
    {
        Buildsql.AppendFormat("(Question like '%{0}%') and ", value);
    }

    string datasql = Buildsql.ToString(0, Buildsql.Length - 5);

    return QueryList(datasql);
}

But in order to make sure the search function is fool proof, I want to use a query like this:

SELECT * 
FROM UH_QA.dbo.Answers 
WHERE CONTAINS(Question, '"Where" OR "do" OR "I" OR "put" OR "my" OR "phone"')

Any advice on how I might be able to go about changing what I've already got to split the string input by the user and then insert the individual words into the query?

My thoughts where to use value.Split(' ') to split the string by whitespace, but I'm open to suggestions.

Would `value.RemoveSpecialCharacters.Split(' ')` (like this: https://stackoverflow.com/questions/1120198/most-efficient-way-to-remove-special-characters-from-string) prevent SQL injection I'm not too worried about SQL injection though for this instance as it's only an internal system, and the questions and answers in the table are backed up as a .csv file. But I would like to learn how to avoid injection as I'm relatively new to programming. — Harry Vella-Thompson, Apr 19 '20 at 06:46
"I'm not too worried about SQL injection though for this instance as it's only an internal system" - famous last words! Use a parameterised query not string concatentation — Mitch Wheat, Apr 19 '20 at 06:47
CONTAINS requires a full text index, does your table have one? — Martin Staufcik, Apr 19 '20 at 06:47
Yeah, the table does have a full text index. When I run a `CONTAINS` query in SQL Server Management Studio it works perfectly. — Harry Vella-Thompson, Apr 19 '20 at 06:49
I have added an answer using `LIKE` instead `CONTAINS` because I am not familiar with `CONTAINS`. I did use parametrized queries to avoid SQL injection. You could adapt it to use `CONTAINS` if needed — Cleptus, Apr 19 '20 at 06:57
@HarryVella-Thompson Regarding your `value.RemoveSpecialCharacters` comment: No, that method is not safe to avoid SQLi because the injection could be encoded one or many times. The safest way to avoid it is using parameters (it is the database that handles it). — Cleptus, Apr 19 '20 at 07:02

score 0 · Answer 1 · edited Apr 19 '20 at 07:29

You would achieve it by:

Changing the signature of QueryList so it allows an variable number of parameters.
Changing your foreach block to both populate an list of parameters and build the SQL. Do note that this would get rid of the SQL injection vulnerabilities.
In your QueryList method add the parameters to the command.

public List<Detail> Search(List<string> Information)
{
    StringBuilder Buildsql = new StringBuilder();
    Buildsql.Append("select * from UH_QA.dbo.Answers where ");

    List<SqlParameter> queryParameters = new List<SqlParameter>();

    for (int i = 0; i < Information.Count; i++)
    {
        if (!string.IsNullOrEmpty(Information[i])) 
        {
            queryParameters.Add(new SqlParameter("@p" + i.ToString(), SqlDbType.VarChar) {
                Value = Information[i]
            });

            if (i > 0) 
            {
                Buildsql.Append(" OR ");
            }

            Buildsql.AppendFormat("Question like '%' + @p{0} + '%'", i);
        }
    }

    return QueryList(datasql, queryParameters);
}

public List<Detail> QueryList(string query, IEnumerable<SqlParameter> parameters) {
    // Do use the `parameters` when accessing the database
}

Thank you for this, but now I'm having the issue _The name 'Information' does not exist in the current context._ I've tried adding it as an object in various contexts but it doesn't seem to satisfy all three instances. Am I missing something obvious? A `using` directive or something? — Harry Vella-Thompson, Apr 19 '20 at 07:13
It does exist in the code of your question: `public List Search(List Information)` Editing the answer to make the code more clear (on block of code) — Cleptus, Apr 19 '20 at 07:16

How can I use individual words from user input into a SQL 'WHERE CONTANS' query in C#?

1 Answers1