How to use the text that search with FTS by parameter in ASP.NET C#

Question

I coded a site with ASP.NET, C# and a SQL Server database. I want to search in database with full-text search by passing a query from C# to SQL Server. In summery, the problem occurs when I use parameters, but when use SQL query string, I get the correct results.

I must draw your attention to this point that my database language is Persian and I have to use NVarchar in FTS and use N for searching text like N'"text that want to search"' .

First I tried

SELECT
    TbSoore.IdSoore, TbSoore.NameSoore, TbAye.NumberAye, 
    TbAye.IdAye, TbAye.TextAye, TbAye.TextTarjome 
FROM
    TbAye 
INNER JOIN 
    TbSoore ON TbAye.IdSoore = TbSoore.IdSoore 
WHERE
    CONTAINS((TextTarjome, TextHadis), N'"متذکر شود"' )

in SQL Server directly and it worked.

Then use this code in C# and worked when I searched متذکر شود , too:

string forwardedSearchText = "N'\"" + Request.QueryString["SearchText"] + "\"'";//SearchText is entered by user in textbox.
sting forwardedSearchColumn;// This fill with checkboxes and it varies between 1 to 6 items.
strsql =
        $@"SELECT TbSoore.IdSoore, TbSoore.NameSoore, TbAye.NumberAye, 
        TbAye.IdAye, TbAye.TextAye, TbAye.TextTarjome
        FROM TbAye 
        INNER JOIN TbSoore ON TbAye.IdSoore = TbSoore.IdSoore 
        WHERE CONTAINS(({forwardedSearchColumn}),  " + forwardedSearchText + " )";

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(strcon))
{
    using (SqlCommand cmdSQL = new SqlCommand(strsql, con))
    {
        con.Open();
        dt.Load(cmdSQL.ExecuteReader());
    }

    ListViewSearchResultAye.DataSource = dt;
    ListViewSearchResultAye.DataBind();
}

But this code is not secure. I want to use a parameter to pass forwardedSearchText to SQL Server.

I changed my code to:

string forwardedSearchText = "N'\"" + Request.QueryString["SearchText"] + "\"'";//SearchText is entered by user in textbox.
sting forwardedSearchColumn;// This fill with checkboxes and it varies between 1 to 6 items.
strsql =
        $@"SELECT TbSoore.IdSoore, TbSoore.NameSoore, TbAye.NumberAye, 
        TbAye.IdAye, TbAye.TextAye, TbAye.TextTarjome
        FROM TbAye INNER JOIN TbSoore ON 
        TbAye.IdSoore = TbSoore.IdSoore WHERE 
        Contains(({forwardedSearchColumn}), @forwardedSearchText )";

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(strcon))
{
    using (SqlCommand cmdSQL = new SqlCommand(strsql, con))
    {
        cmdSQL.Parameters.AddWithValue("@forwardedSearchText", forwardedSearchText);
        con.Open();
        dt.Load(cmdSQL.ExecuteReader());
    }

    ListViewSearchResultAye.DataSource = dt;
    ListViewSearchResultAye.DataBind();
}

But when I search متذکر شود , I get this error:

Syntax error near '* شود*' in the full-text search condition 'N'"متذکر شود"''.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Syntax error near 'شود' in the full-text search condition 'N'"متذکر شود"''.

I try cmdSQL.Parameters.Add("@forwardedSearchText", SqlDbType.NVarChar).Value = forwardedSearchText;

and cmdSQL.Parameters.Add(new SqlParameter("@forwardedSearchText", forwardedSearchText));

instead of cmdSQL.Parameters.AddWithValue("@forwardedSearchText", forwardedSearchText);

and it made no difference.

Note: I want use Contain, Not FreeText.

What can I do to correct this problem?

Answer 1

Thank AlbertD.Kallal for his complete answer, his answer (and another answer and commands) helped me but my problem solved by this tip:

Despite the opinion of friends in commands, I have to use " both sides of Request.QueryString["SearchText"] in command.parameters.

So the correct code that works, is:

    strsql =
               $@"Select TbSoore.IdSoore, TbSoore.NameSoore, TbAye.NumberAye, TbAye.IdAye, TbAye.TextAye, TbAye.TextTarjome
               From TbAye INNER JOIN TbSoore ON TbAye.IdSoore = TbSoore.IdSoore
               Where Contains(({forwardedSearchColumn}), @forwardedSearchText )";
            }
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(strcon))
            {
                using
                    (SqlCommand cmdSQL = new SqlCommand(strsql, con))
                {
                    
                    cmdSQL.Parameters.Add("@forwardedSearchText", SqlDbType.NVarChar).Value =
                    "\"" + Request.QueryString["SearchText"] + "\"";
                    
                    con.Open();
                    dt.Load(cmdSQL.ExecuteReader());
                }
                ListViewSearchResultAye.DataSource = dt;
                ListViewSearchResultAye.DataBind();

            }