Я пытаюсь параметризовать поисковый запрос, который использует ПОДОБНОЕ ключевое слово с подстановочным знаком. Исходный sql имеет динамический sql как это:
"AND JOB_POSTCODE LIKE '" + isPostCode + "%' "
Таким образом, я попробовал это вместо этого, но я получаю FormatException:
"AND JOB_POSTCODE LIKE @postcode + '%' "
Править: Я предполагаю, что FormatException не будет прибытием из SQL-сервера, который вот - CE, так согласно просьбе, то, как я установил параметр в своем коде C#. Параметр устанавливается в коде как это:
command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode;
Я также попробовал:
"AND JOB_POSTCODE LIKE @postcode"
с
command.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = isPostCode + "%";
но это не возвращает результатов. Кто-либо может советовать, как использовать параметры в этом поиске sql?
Короткий ответ заключается в том, что вы должны поместить подстановочный знак в значение параметра, а не в CommandText. т.е.
не то:
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode%"
это:
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode";
sqlCommand.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postCode + "%";
Длинный ответ здесь:
Я вернулся и сократил свой код до самого необходимого, чтобы я мог публиковать это здесь, и при этом я обнаружил, что последний метод, который я попробовал в своем исходном вопросе, действительно работает. Должно быть, что-то не так в моем тестировании. Итак, вот краткое изложение с полным запущенным кодом:
Исходный динамический sql, уязвимый для внедрения sql:
//Dynamic sql works, returns 2 results as expected,
//but I want to use parameters to protect against sql injection
string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE '"
+ postCode + "%'";
return Database.fGetDataSet(sqlCommand,
iiStartRecord,
iiMaxRecords,
"JOBVISIT");
Первая попытка использования параметра дает ошибку:
//This syntax with a parameter gives me an error
//(note that I've added the NVarChar length as suggested:
//System.FormatException : @postcode : G20 -
//Input string was not in a correct format.
//at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings()
//at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor,
// Boolean& isBaseTableCursor)
string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode
+ '%'";
sqlCommand.Parameters.Add("@postcode",
SqlDbType.NVarChar,
10).Value = postCode;
return Database.fGetDataSet(sqlCommand, iiStartRecord, iiMaxRecords, "JOBVISIT");
Второй метод действительно работает:
///This syntax with a parameter works, returns 2 results as expected
string postCode = "G20";
sqlCommand.CommandText = "SELECT * FROM JOB WHERE JOB_POSTCODE LIKE @postcode";
sqlCommand.Parameters.Add("@postcode", SqlDbType.NVarChar).Value = postCode
+ "%";
return Database.fGetDataSet(sqlCommand, iiStartRecord, iiMaxRecords, "JOBVISIT");
Спасибо за все введенные, и извините за оригинальный вводящий в заблуждение вопрос ...
Использование:
"AND JOB_POSTCODE LIKE '" + isPostCode + "%' "
... означает, что строка создается до того, как она будет добавлена в динамический SQL. Это сделано для того, чтобы вам не нужно было указывать параметр в списке параметров для sp_executesql / EXEC, в отличие от этого:
"AND JOB_POSTCODE LIKE @postcode + '%' "
... Пожалуйста, опубликуйте больше вопросов.
Пожалуйста опубликуйте свой полный пример (включая внешний клиентский код, в котором вы собираете свой вызов). И второй, и третий варианты должны работать, если вы правильно передаете параметр.
Это возвращает соответствующие результаты в SQL Server 05 (также работает в пакете SP), поэтому кажется, что последнее, что вы пробовали, должно было сработать. Но у меня нет испытательного стенда для Compact Edition, так что, возможно, это имеет значение (мне было бы любопытно узнать, так ли это и почему).
declare @p1 nvarchar(50)
set @p1 = 'f' -- initial value passed from your app
set @p1 = @p1 + '%' -- edit value for use with LIKE
select * from JOB
where JOB_POSTCODE like @p1
РЕДАКТИРОВАТЬ:
Какую версию SQLCE вы используете ? Можете ли вы сказать, действительно ли значения ваших параметров передаются из вашего кода в БД? Я просмотрел этот учебник MSDN и смог получить желаемые результаты, по крайней мере, в конструкторе запросов Visual Studio. (разница только в том, что я использую VS 2008 и SQL Server Compact 3.5). См. Раздел «Создание нового запроса»; Я смоделировал таблицу с некоторыми данными, и этот запрос сработал так, как вы предполагали.
SELECT JobID, PostCode, OtherValue
FROM Job
WHERE (PostCode LIKE @p1 + '%')
Как я уже сказал, я этого не сделал. Я пишу код для вызова запроса, но он работал внутри дизайнера. Другими словами, «это работает на моей машине».