Параметризованный запрос ASP.NET C# ничего не возвращает

Question

Хорошо, я новичок в параметризованных запросах. Я понимаю, почему вы должны использовать их и все, но я не могу найти ни один ресурс, который показывает правильный путь, или, по крайней мере, тот, который показывает правильный путь, который действительно работает.

Поэтому мой вопрос о том, верен ли мой код. Он компилируется и работает просто отлично, но он ничего не возвращает в gridview.

 protected void SearchButton_Click(object sender, EventArgs e)
{
    string searchBoxValue = SearchBox.Text;
    string columnNameValue = ColumnName.SelectedValue;
    columnNameValue.ToLower();

    SqlCommand searchCommand = new SqlCommand();
    searchCommand.Connection = connection;
    searchCommand.CommandText = "select firstname AS FirstName,lastname AS LastName, zipcode as ZipCode, phone AS Phone, email AS Email, cancersurvivor AS CancerSurvivor, ethnicity AS Ethnicity from registrants where @columnname = @searchterm";

    SqlParameter columnParam = new SqlParameter();
    columnParam.ParameterName = "@columnname";
    columnParam.Value = columnNameValue;

    SqlParameter searchBoxParam = new SqlParameter();
    searchBoxParam.ParameterName = "@searchterm";
    searchBoxParam.Value = searchBoxValue;

    searchCommand.Parameters.Add(columnParam);
    searchCommand.Parameters.Add(searchBoxParam);

    UpdateTable(searchCommand);

}

Функция UpdateTable принимает объект SqlCommand, а затем использует объект DataAdapter для выполнения команды, заполняет объект DataTable, затем устанавливает источник данных gridview в объект данных и связывает его.

Как я уже говорил, я действительно ищу правильный способ сделать это? мне нужна хранимая процедура для этого? Я смущен всем этим и почему это не работает.

Answer 1

Вы не можете параметризовать @columnname, Это должен быть литерал в вашем запросе.

Ваше заявление

select 
 /* .... */
from registrants where @columnname = @searchterm

вернет все строки из registrants если значения параметров оказываются одинаковыми или нет строк в противном случае.

Он не будет смотреть и видеть, если у вас есть столбец с таким именем и посмотреть, если @searchterm существует в нем.

Чтобы сделать это безопасным способом, вам необходимо проверить, что columnNameValue соответствует одному из белого списка допустимых имен столбцов (поскольку вы должны знать возможные имена столбцов в этой таблице) и объединить его в свой запрос. Не объединять неподтвержденный пользовательский ввод. как тогда вы открываете себя до SQL-инъекции.

Таким образом, вы можете реализовать что-то вроде

using System.Linq;

protected void SearchButton_Click(object sender, EventArgs e)
{
    string columnNameValue = ColumnName.SelectedValue.ToLower();

    var validColumnNames = new string[] { "firstname", "lastname", "zipcode" };

    if (!validColumnNames.Contains(columnNameValue))
    {
        throw new Exception("Unexpected column name " + columnNameValue);
    }

    /* ... code omitted */

    searchCommand.CommandText = "select firstname AS FirstName,lastname AS LastName, zipcode as ZipCode, phone AS Phone, email AS Email, cancersurvivor AS CancerSurvivor, ethnicity AS Ethnicity from registrants where " + columnNameValue + " = @searchterm";

    /* ... code omitted */
}

Answer 2

Цель параметризованной команды - предотвратить внедрение SQL. Вы не можете параметризовать имя столбца, sql примет его как строку.

protected void SearchButton_Click(object sender, EventArgs e)
{
    string searchBoxValue = SearchBox.Text;
    string columnNameValue = ColumnName.SelectedValue;
    columnNameValue.ToLower();

    SqlCommand searchCommand = new SqlCommand();
    searchCommand.Connection = connection;
    //Put the column name directly in the request, but use a parameter for the search value
    searchCommand.CommandText = "select firstname AS FirstName,lastname AS LastName, zipcode as ZipCode, phone AS Phone, email AS Email, cancersurvivor AS CancerSurvivor, ethnicity AS Ethnicity from registrants where " + columnNameValue  + " = @searchterm";

    /* No need for this part
    SqlParameter columnParam = new SqlParameter();
    columnParam.ParameterName = "@columnname";
    columnParam.Value = columnNameValue;
    */

    SqlParameter searchBoxParam = new SqlParameter();
    searchBoxParam.ParameterName = "@searchterm";
    searchBoxParam.Value = searchBoxValue;

    //searchCommand.Parameters.Add(columnParam);
    searchCommand.Parameters.Add(searchBoxParam);

    UpdateTable(searchCommand);

}

Answer 3

Ваша проблема в том, как вы пытаетесь сделать имя столбца в качестве параметра. Вы хотите изменить запрос в целом, чтобы отразить, по какому столбцу вы хотите фильтровать. Попробуйте следующее:

protected void SearchButton_Click(object sender, EventArgs e)
{
    string searchBoxValue = SearchBox.Text;
    string columnNameValue = ColumnName.SelectedValue;
    columnNameValue.ToLower();

    SqlCommand searchCommand = new SqlCommand();
    searchCommand.Connection = connection;
    searchCommand.CommandText = String.Format("select firstname AS FirstName,lastname AS LastName, zipcode as ZipCode, phone AS Phone, email AS Email, cancersurvivor AS CancerSurvivor, ethnicity AS Ethnicity from registrants where {0} = @searchterm",columnNameValue);

    SqlParameter searchBoxParam = new SqlParameter();
    searchBoxParam.ParameterName = "@searchterm";
    searchBoxParam.Value = searchBoxValue;

    searchCommand.Parameters.Add(columnParam);
    searchCommand.Parameters.Add(searchBoxParam);

    UpdateTable(searchCommand);

}

Answer 4

Если вы хотите, чтобы это работало, вам нужно динамически построить SQL-стату и выполнить с процедурой sp_executesql внутри процедуры следующим образом:

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2012.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

Вы по-прежнему можете использовать параметризованные запросы и не подвергать себя SQL-инъекции.

Источник здесь. Еще одна очень полезная ссылка это.