473,388 Members | 873 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,388 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 36589
>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: BTR | 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: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.