Prev: problem with column alias
Next: Distinct Row based on description count
From: nidaar on 29 Jul 2008 16:03
From a security point of view, is accepting wildcards like "%" in
input parameters of stored procedures against any best practices?

As an example, if a user defined function uses "Productname LIKE
@ProductName" in WHERE clause of a select statement, and a stored
procedure uses the user defined function while passing @ProductName
input parameter to the user defined function, is there any security
risks? Is there a better way to construct the search string while not
limiting the flexibility of using wildcards?

Thanks
From: --CELKO-- on 29 Jul 2008 17:18
>> From a security point of view, is accepting wildcards like "%" in input parameters of stored procedures against any best practices? <<

It should be fine. You are putting the pattern in a parameter and not
using dynamic SQL.
From: Erland Sommarskog on 29 Jul 2008 17:09
nidaar (nidaar(a)gmail.com) writes:
> From a security point of view, is accepting wildcards like "%" in
> input parameters of stored procedures against any best practices?
>
> As an example, if a user defined function uses "Productname LIKE
> @ProductName" in WHERE clause of a select statement, and a stored
> procedure uses the user defined function while passing @ProductName
> input parameter to the user defined function, is there any security
> risks? Is there a better way to construct the search string while not
> limiting the flexibility of using wildcards?

As long as you are only working with parameters and don't engange in
dynamic SQL there is no risk for SQL injection, if that is what you
have in mind.

There may be other security risks, like disclosure of information that the
user does not have right to see, but that is not as such affected by the
use of %.

The one thing I would have in mind with wildcard search is that there is
a big difference between initial wildcards and wildcards that comes
later. An initial wildcard does not go well with the index, so for larger
data volumes, you should prevent the user from using initial wildcards,
or at least lead initial wildcards into a code path on its own.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Plamen Ratchev on 29 Jul 2008 17:25
From security stand point it does not make a difference if you concatenate
the wildcards on the client and pass the whole search value as parameter, or
if you concatenate it in the stored procedure:

WHERE Productname LIKE '%' + @ProductName + '%'

For many applications I have used a screen drop down with values like
'Begins', 'Contains', 'Ends with', 'Equals' and then based on user selection
build the correct search pattern.


Plamen Ratchev
http://www.SQLStudio.com

From: nidaar on 29 Jul 2008 21:49
On Jul 29, 5:25 pm, "Plamen Ratchev" <Pla...(a)SQLStudio.com> wrote:
> From security stand point it does not make a difference if you concatenate
> the wildcards on the client and pass the whole search value as parameter, or
> if you concatenate it in the stored procedure:
>
> WHERE Productname LIKE '%' + @ProductName + '%'
>
> For many applications I have used a screen drop down with values like
> 'Begins', 'Contains', 'Ends with', 'Equals' and then based on user selection
> build the correct search pattern.
>
> Plamen Ratchevhttp://www.SQLStudio.com

Thanks for all the prompt replies. Much appreciated.
 |  Next  |  Last
Pages: 1 2
Prev: problem with column alias
Next: Distinct Row based on description count