Hi all,

This is my 1st post here. I need your opinion regarding one scenario. I have a custom search page which searches data from a SQL database table based upon the search criteria submitted by the user in my web site. My site is an asp.net 2.0 web site using SQL Server as database using a 3 tier architecture.

(UI- DomainObject(with get set properties) - BusinessLayer - DataAccessLayer - DataBase)

I am able to see 2 ways to do this :

1st Way :

In the code behind page I am building a search string for the selected criteria and passing it to my stored procedure as a parameter. Below is my stored procedure. My Searchquery comes something like ' [columnname] like ''abc%'''

@SearchString as varchar(500)

declare @strQuery as varchar(1000)

WHERE  ' + @SearchString

Execute(@strQuery)

2nd Way :

I dont want to build my search string in the code behind. I want to set the search values to my domain object and I want to pass an object to my data access layer with getters and setters. Then I want to access the values in my stored procedure as parameters and then build the query logic in the stored procedure and execute.

My project consists of huge number of users and sometimes needs to access data from multiple tables. So I want a best approach with maximum flexibility for the programmer for change requests in the search page and it should have good performance.

Please suggest me which approach is the best approach in the above.

Is there any SQL Query injection threat to my application.

Thanks,

Subbu

Hi Subbu

The second approach sounds good.
I would also use a get-param using for searches like (search.aspx?s=mysearch), so you can deeplinking your queries.

I try to avoid writing SQL and wouldn't use stored procedures.

Use a good ORM. Something that supports LINQ, such as LINQ to SQL, NHibernate or LLBL Gen Pro.

Once you start using LINQ and messing around with expression trees there is no going back.

LINQ, ORM and NHibernate are better solutions. I would prefer Linq

Answering his questions, 2nd appraoch is good

And sql injection threats are reduced to 60% by using Stored procedures. Proven fact

Hi Peter Bucher,

 Thanks for your reply.

Problem with the second approach is, I am working on a project where "Change Requests" are expected.  So whenever a change request comes like adding a new field in the search panel then I need to modify my "dll" for adding new properties. Also I need to modify my stored procedure. I am just worried about changing dll and SP for each change request. (Because I have nearly 25 search screens in my project)

In my 1st approach I only need to change my code behind file. (Only one file change). So which way is better?

Thanks,

N.V.Subrahmanyam

AliSufyanButt:

And sql injection threats are reduced to 60% by using Stored procedures. Proven fact

That is a riduculous claim. How can you possibly put a percentage on this?

SQL injection threats are caused by un-parameterized queries and can occur in stored procedures just as readily. It occurs more often in inline SQL because it's easier for the "lazy developer" to do. LINQ to SQL does not use stored procedures and has no risk of SQL injection in this way, as it has parameterized type-safe queries.

Getting back to answering the question of the post, you should look into using LINQ to SQL. If a change request comes in, you would simply change your database, rebuild your datacontext and away you go.

Hi JontyMC,

I am sorry if I had asked a ridiculous question. I do not know about LINQ. So I tried to solve my problem in that way. I will look into LINQ to SQL concept and try to use it for my project. Anyways thanks for the reply.

 Subbu

Your question was fine. I was referring to the claim by Ali Sufyan Butt that "sql injection threats are reduced to 60% by using Stored procedure", which is incorrect and misleading.

You'll be glad you looked into LINQ to SQL, it will save a lot of unncessary coding. There is no need to write data access code anymore as there are many great ORM's available to use.

Dear Jonty,

I was also comparing inline queries and Stored procedures. If you are making same parametrized Stored procedure then there is no reason to make a stored procedure except it is precompiled.

I claimed it on my personal experience. :) I can easily hack, normal sites with sql injections. But the sites using Stored procedures are less prone to Sql Injection Hack attacks

It's not necassarily stored procedures that prevent SQL injection, but the fact that the developer took the time to use parameters rather than string concatenation.

I would go with Option 1.  This is by far the best method.

Option 2 means having a DLL installed into your SQL database (which sucks)

LINQ-2-SQL is great for prototyping, other then that I wouldn't (and don't) use it

Oh, and if your only going to allow specfic colums to be 'searchable', put an index on them, else enable Full-Text indexing on your DB.

Its a common sense. Depends on how you interepret the statements. Why would you use stored procedures, to make query execution faster. To add security use parameters. Simple.

But even parameters can be hacked. But difficult to hack parametrized stored procedures.

Hi Subbu,

absolutely not a ridiculous question, and those answering it with "use an ORM such as LINQ to SQL" are just answering your question partly. Whatever ORM you are using, you will always have the same problems building a user interface with a standardized way of passing your search-criteria ("less than", "greater than" , "equal", "between", etc.) forward to the next layer and I'm very surprised that no ORM producer has taken their product further, building UI-components (voluntary) adressing these issues (they probably have although I havent noticed it). This would for sure be the best way to draw users to their ORM. Look at all 4GL products, it's a obvious requirement to make this complex of problems an easy task for the developers.

Regards RogerW. 

 Hi, personnaly i create SearchObject DTO, with all properties as Object, so i can check if my object is null, if is null, i don't add the value to the parameters list and to the search line, I create the SearchObject from the Search Form, then i check if a value was typed in a textBox or value was selected etc... you can also parse the queryString.

I dont know if is the best pratice but i use this technic since 2 years now, and i did not had any problem...

Hi,

I thought of the same approach. But only problem is every time when we add or remove a search field in the search panel then we need to modify the class(so my dll changes)

My project has nearly 25 search screens. So I am just worried about only this fact of modifying dll each time when the system goes live.

(I AM TRYING TO USE A DICTIONARY OBJECT INSTEAD OF A CLASS. IS THIS OK?) 

Thanks,

Subbu