I have a table containing products. I need to make a query finding all the matching results to an user-input value. I am using SqlParameter
for the insertion of the inputs.
SqlCommand findProcutsByPattern = new SqlCommand(
"SELECT *" +
" FROM [Products]" +
" WHERE ProductName LIKE @pattern", connection);
findProcutsByPattern.Parameters.AddWithValue("@pattern", '%' + pattern + '%');
The problem comes when the user input string contains '_' or '%' as they're being interpreted as special characters. In the other hand, considering this:
Command objects use parameters to pass values to SQL statements or
stored procedures, providing type checking and validation. Unlike
command text, parameter input is treated as a literal value, not as
executable code.
I shouldn't have such problems. Do I need to replace/escape all the '_' and '%' in the input string or is there a more elegant solution. I want the input to be considered as a literal.
I have a few records in the table which include special characters in the name(N_EW, NEW, N%EW, N"EW, N'EW). Specifying , ", and ' as the input works fine(considers them as literals).
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…