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.

Good post.

Here is my article on this: http://www.aspnetpro.com/newsletterarticle/2006/12/asp200612jk_l/asp200612jk_l.asp

Best,

Joydip

Author: ASP.NET Data Presentation Controls Essentials (Packt Publishing)

http://www.amazon.com/ASP-NET-Data-Presentation-Controls-Essentials/dp/1847193951

nice post, why you don't write same community article and submit here 

Nice Article,Your article is very useful to novice users who are fully unaware of sql injection attack

The author of the thread Joydip made a good post to demonstrate the novice users what sqlinjection is all about and how a profound damage can be done by deleting the tables in the database. We should take this discussion a little further by citing how to use xp_cmdshell, xp_makewebtask etc. Also, how to enable xp_cmdshell if it is disabled thorugh SqlInjection. Also, if a form contains a display for outputting a single value and displays multiple values in a grid, how to extract multiple pieces of information like the whole database schema, tables, columns, values etc. Also, some Sql Injection tools that really work to find lopholes in your site etc.

it is a nice for list this topic, if you could post a vidoe stuff to explain what you did here, it will be great !

 always use sql helper n oracle helper to be in touch wid any database.

error handling is easy n less eror pron.

bind parameters with command object.

n use properties to avoid sql injection. 

Why not write up your notes into an article for www.codeproject.com? 

also avoid the 'exec' sql command 

exec in and of itself is not necessarily unsafe.  rather it is how exec is used that can open you up to injection attacks.

for example: http://www.dotnetjunkies.com/WebLog/chris.taylor/archive/2004/10/13/28370.aspx