Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-02 : 13:51:59
|
So I am trying to upgrade an old system that uses string concatination (SQL Injection) to perform searches against the SQL DB.The code is massive.So now I am trying to convert this to a SPROC. Here is the thing: the way it is done at the front end is that if anyone enters a wildcard, it does a LIKE search. If not, then does a simple match search.I am wondering if there is a way I can do the same rather than having to check to see if the parameter contains a wild card then construct a different path to do a search if not, then do another type of query but without the LIKE.thoughts? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-02 : 15:34:49
|
You coulduse LIKE clause with or without a wild card. For example, -- this is perfectly valid and correctSELECT Customer FROM Tbl WHERE CUSTOMER LIKE 'Smith' It is good that you are revising the code to avoid SQL Injection and parameterizing the query. One thing to note though, is that when you do that, sometimes you can run into a problem that is often referred to as parameter sniffing. This happens when SQL Server generates an optimized execution plan based on one set of parameters, but that plan turns out to be completely inefficient for another set of parameters. As an example, consider the two queries below:SELECT Customer FROM Tbl WHERE Customer LIKE '%Smith';SELECT Customer FROM Tbl WHERE Customer LIKE 'Smith%' The query plan for the first select would not be able to use any index that may be on Customer column. If the query plan generated for that select is reused for the second query, it would miss out on the opportunity to use the index even though the second query could make use of such an index. If you run into that problem, you may find this article interesting read: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ |
|
|
|
|
|