As a general rule, and its something that confuse a heck of a lot of people... Try to do as little "work" as possible in control events. The only thing you need to worrie about is:

PreInit -> to create controls on the fly

Load -> declare page wide objects and initialize stuff that needs to be done on !IsPostBack

All of the control events (assume they're happening all at once and cannot talk to each other).

All of the DataBind events (so if you need to do something in the SelectedIndexChange of a dropdown, that will be done -before- the databind events, UNLESS you call them explicitely, of course, by invoking DataBind())

PreRender -> Do everything that matters here, as this will always happen after all other events, give or take.

A good example is if you want to toggle visibility of a control depending on a value in a dropdown list. Since the dropdown list go change, its selected value could be modified from other events, it could be databound again, etc... you don't want to toggle visibility of your dependent control in Page Load or something, as most people seem to do. You do it in PreRender, since then you're sure nothing will change, DataBind is usually already done, etc. So its the "safe" place.

If you think of stuff in the above order, it looks a lot more like what you're used to, except for the PreInit, which is really only for asp.net controls, so usually you won't need it anyway. You declare your variables at the top (not in an event), initialize them in Load (at the top), handle your events, databind stuff, then render your stuff... Thats pretty much in the order you'd do it in PHP or whatsnot, so if you put your code in that order too in your page, you'll be able to read from top to bottom in the order it happens :)

shados:

Try to do as little "work" as possible in control events.

That's why I concatenated string for datasource selectcommand in page_load event  instead of using parametrized query

Anyway, thank you very much for all your tips!
 

 This script seems vulnerable by the standards stated in this thread:

http://www.asp101.com/samples/db_search_aspx.asp

this is exactly how I code using concatenating strings, and what I shouldn't do.

Or would you say it's OK to use it?

Thats really awful code, from the naming conventions (though I guess thats a matter of preference for private members) to performance (no parameterized query = no cached query plan = performance hit when you start needing to scale), to security (if someone can bypass the quote trick, which isn't even always required for sql injection, you're screwed).

Basically, its an awful sample. The general guideline is: 50% (or more!) of the code samples you see on the net and even in books are very bad, so you always should be highly critical of them. Heck, if you read Framework Design Guidelines: Conventions, Idioms and Patterns, there's a lot of comments in the book about Microsoft's own employees that point out to examples in the .NET Framework that were horrible mistakes that should never be copied/followed :)

EDIT: Besides, again another thing that it breaks: if, in the theoritical event that concatenating string was a great way of doing things, you should be using the StringBuilder class to do it, not just a big serie of + signs, as, if you have a lot of queries, it will make the garbage collector go crazy in the long run.

 Protection against SQL Injection attack needs to be in depth

• Minimum priority SQL account
• Stored procedures (or parameterised dynamic TSQL in exceptional circumstances)
• Whitelist filtering of all user text input. My filter functions at http://www.codeplex.com/CommonData use StringBuilder for efficiency.
  

A couple of points here. First, escaping strings is not fail-safe: with some databases, it can be circumvented by feeding in badly-formed Unicode data in an appropriate encoding.

Second, stored procedures are not a magic bullet and introduce problems of their own, most notably maintainability. (See the blog entry "Stored procedures are bad, m'kay" by Frans Bouma for a fuller discussion.) I've seen stored procedures that themselves perform string concatenation and then feed the result into an eval statement. Guess what that's called? Yes, it's "SQL injection vulnerability."

And to add to that, whitelisting can get awkward fast if you have a multi-language web site. Chinese anyone?

Hi,

From the above discussion

I have A Query

I got this from above

Don't ever use string concatenation (or a StringBuilder) to create SQL commands. An example is this:

string sql = "SELECT * FROM Products WHERE Category=" + cat;

There are a lot of reasons why not to do this:
1. Strings inside the command text needs to be enclosed between ' and '. You can have a problem when the value of cat contains a ' itself. You can avoid this by doubling all single quotes inside the cat string, but it still is not recommended.
2. SQL Injection attacks!!! Don't be tricked by this one, it's easy to avoid. Think of a string cat that contains the following value:

1; DROP TABLE Products; --

-- is the comment operator in T-SQL. So, the resulting command is this:

SELECT * FROM Products WHERE Category=1; DROP TABLE Products; --

The result: the Products table is droppe. Thus, pretty simple to do if the cat value comes from the querystring or from a form input.

How to avoid this:
1. Don't ever ever connect to the database as "sa" or another db owner with full access to the underlying database. Always connect with the least privileges needed to do the job.
2. Don't use string concat, but use parameterized commands instead, like this:

string query = "SELECT * FROM Products WHERE Category=@Category";


SqlCommand cmd = new SqlCommand(query, conn);


cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 50);


cmd.Parameters["@Category"].Value = cat;


//...

This will make sure the anomalities with quotes are solved for you, as well as avoid basic injections and perform checkings for the input length of the strings (+ type checking etc).
3. Even better, use a stored procedure with parameters on the server and call it using SqlCommand. The idea is the same, but the SQL command with params itself is stored on the server. This allows better performance and even better security.

I have query again that How can i avoid that

1; DROP TABLE Products; --

 entring inside my database

ie .

if query is in this way

string sql = "SELECT * FROM Products WHERE Category=" + cat;

and cat=1; DROP TABLE Products; -- 

Then there is sql injection..

Now as specified to avoid we use parameterized query

ie if modified in parameterized the query becomes 

string query = "SELECT * FROM Products WHERE Category=@Category";


SqlCommand cmd = new SqlCommand(query, conn);


cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 50);


cmd.Parameters["@Category"].Value = cat;

Now I want to ask that here also the variable cat

can contain this value 1; DROP TABLE Products; --  from outside

How will this query will protect from sql injection if it is parameterized

string query = "SELECT * FROM Products WHERE Category=@Category";

Your query would try to compare Category with any string value you supply. It would not try top execute the command as string concatenation did.

In another words,

select * from myTable where Category = ' ;DROP myTable'

Hi,Naom

I didnt get wht u said

Naom:

Your query would try to compare Category with any string value you supply. It would not try top execute the command as string concatenation did.

In another words,

select * from myTable where Category = ' ;DROP myTable'

 

 

I am sayin that

suppose my query is

string sql = "SELECT * FROM Products WHERE id=" + cat;

and cat=1; DROP TABLE Products; -- 

Then there is sql injection..

because in database that line will be seen as

I'm trying to explain that using parameters this way you're protected against injection attack, because your query becomes

select * from products where category = 'myCategory ; Drop myTable'

this query simply returns 0 records. It is not the same as

select * from products where category = '' ; DROP myTable as the first example with concatenation will translate into.

 However, using parameters would not save you 100% from malicious scripts in insert or update commands.

Hi,Naom

I got something

that parameterized query helps in Sql injection

I have tested folowwin g code.

I sql I had my table  

CREATE TABLE [dbo].[users](

[id] [int] identity ,

[username] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[password] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

SET ANSI_PADDING OFF

insert into [users]

select 'Test','123'

union all

select 'TestNew','456'

In front end First I wrote Without parameterized

ie

SqlCommand cmd = new SqlCommand();

string username = "'' OR 1=1 --";

SqlCommand cmd = new SqlCommand();

// string username = "'' OR 1=1 --";

Here the reader shows that dont have any  rows that means sql injection is avoided.

Now My question is What would be the query becomes finaly in Sql

which protect from sql injection

I tried to see in addwatch or quickwatch window but i seeing query as

I want to know the query finally which has become. 

The simplest way to see what is actually send to SQL Server is go to Tools and Select Query Profiler and run it.

However, I see what I don't have it with SQL Studio Express 2008. Does someone know if it possible to install or add this?

Thanks in advance. 

 >Does someone know if it possible to install or add this

You need "SQL Server Express (32-bit), Express with Tools, and Express with Advanced Services (32-bit)"

http://www.microsoft.com/downloads/details.aspx?FamilyID=b5d1b8c3-fda5-4508-b0d0-1311d670e336&DisplayLang=en

No, that's the version I installed. See http://msdn.microsoft.com/en-us/library/cc645993.aspx

Looks like I'm out of luck with this unless someone knows another tool or a way to get it.

Also I'm using Windows 64 OS, so I need 64 version, I think.