473,320 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Accepting wildcards like % in input parameters of stored procedures

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
Jul 29 '08 #1
5 36564
>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.
Jul 29 '08 #2
nidaar (ni****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jul 29 '08 #3
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

Jul 29 '08 #4
On Jul 29, 5:25*pm, "Plamen Ratchev" <Pla...@SQLStudio.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.com
Thanks for all the prompt replies. Much appreciated.
Jul 30 '08 #5
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
Jul 31 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: zlatko | last post by:
I use a stored procedure that is calling several other stored procedure which update or append values in several tables. All of them are stored procedures with input parameters by which they filter...
2
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures...
11
by: Bã§TãRÐ | last post by:
I have been working on this particular project for a little over 2 weeks now. This product contains between 700-900 stored procedures to handle just about all you can imagine within the product. I...
3
by: Zlatko | last post by:
A question concerning Access Project with SQL Server: I use a stored procedure that is calling several other stored procedure which update or append values in several tables. All of them are...
5
by: Rob Wire | last post by:
For the code below, how could I add an item in the drop down lists for both company and location to be an "All" selection that would send to the stored proc. spRptAttachments a value of "%" so...
0
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how...
3
by: .Net Sports | last post by:
I'm able to find info for using input parameters for stored procedures to do inserts and update type SQL functions in asp.net (vb.net), but I need to find something that will use input parameters...
45
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
3
by: LataChavan | last post by:
I have tried to look for a solution to the problem of sending parameters to stored procedures through crystal report. Following is the code: Now what happens is that if i do not apply the logon...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.