Accepting wildcards like % in input parameters of stored procedures

[nidaar]

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 "Productnam e 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

[--CELKO--]

Re: Accepting wildcards like % in input parameters of storedprocedure s

>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.

[Erland Sommarskog]

Re: Accepting wildcards like % in input parameters of stored procedures

nidaar (nidaar@gmail.c om) 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 "Productnam e 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@sommarsk og.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

[Plamen Ratchev]

Re: Accepting wildcards like % in input parameters of stored procedures

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

[nidaar]

Re: Accepting wildcards like % in input parameters of storedprocedure s

On Jul 29, 5:25 pm, "Plamen Ratchev" <Pla...@SQLStud io.comwrote:

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.c om

Thanks for all the prompt replies. Much appreciated.

[Eric Isaacs]

Re: Accepting wildcards like % in input parameters of storedprocedure s

According to this MSDN article you might consider escaping the
wildcard characters in a like clause.

FROM: http://msdn.microsoft.com/en-us/library/ms161953.aspx
-------------------------------------------------------------
LIKE Clauses
Note that if you are using a LIKE clause, wildcard characters still
must be escaped:
....
s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");
-------------------------------------------------------------

I suppose the logic for escaping these characters is that if you
request that the user supplies 3 characters for a like match in the
UI, they can't just key ___ or %%% and have it return all the results
still. That's a very minor example of SQL Injection where the results
returned might be unanticipated by the developers.

By escaping them with [%] or [_] it actually searches for text that is
like the real percent and underscore characters as opposed to the
wildcard affect they have in the LIKE clause.

-Eric Isaacs