Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    A few things:

    Parameters and SQL injection safe code does NOT allow a "parameter" for a column name. So, criteria can and should use a @Parmater, but for column names a different approach is required.

    You can still create injection safe code, and the approach is that in these cases (the column name is to be passed), then you simply test/check/verify that the column passed is legal. After all, you only going to have a few possible columns allowed here, right? Say 2-5.

    So, your code then becomes this:

            string forwardedSearchText = Request.QueryString["SearchText"];
        //SearchText is entered by user in textbox.
        string forwardedSearchColumn = "";// This fill with checkboxes and it varies between 1 to 6 items.


        List<string> LegalColumns = new List<string> { "Notes", "Descripiton", "ProductDesc"};
        string[] sCols = forwardedSearchColumn.Split(',');  // assume no spaces in this string

        foreach (string sCol in sCols) 
        {
            if (!LegalColumns.Contains(sCol)) 
                return;     // if columns don't verify - we exit
        }

        // if we get here, then only legal columns passed

        string 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 =  forwardedSearchText;
                con.Open();
                dt.Load(cmdSQL.ExecuteReader());
            }

    And note we don’t use "AddWith". We use add, and strong type the parameter.

    So, simply verify that columns passed are legal.

    To be fair, since the UI has check boxes for this, then it going to be "more difficult" to inject non legal columns or SQL. However, by verifying the columns are legal, then that removes the injection issues if a user were to mess with the check box values by use of browser debug tools before the post-back.

    In my experience, for indexed free text searching, then only a few columns are going to be free text indexed in such databases, and thus a simple list of legal columns in your code will suffice for the vast majority of such cases. Since we verify that all columns are legal, then we are free to inject such columns directly into the SQL query.

    Login or Signup to reply.
  2. 0

    When you are using query parameters, you do not need to quote/convert/format the values you put into the parameters.

    So in:

    string forwardedSearchText = "N'"" + Request.QueryString["SearchText"] + ""'";

    which is then used

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

    there will be double quoting of the search text and likely to interact badly.

    The value of the search text will be quoted twice (note the number of quotes in the error message. Just use

    cmdSQL.Parameters.Add(new SqlParameter("@forwardedSearchText", Request.QueryString["SearchText"]));

    The reason SQL query parameterisation is important is to let the database’s own code handle the necessary quoting/conversion/formatting rather than every programmer trying to do it (or forgetting) and likely getting it wrong. All the necessary escaping and quoting will be done by the database (either client or server side: really does not matter).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search