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.