Protect From SQL Injection

1.
Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures.
If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions.
A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions.
Never let the web site log in as sa!

2.
For web applications: never expose error messages from SQL Server to the end user.

3.
Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().

4.
If the SQL parameters strings do not include any user input, there is no opening for SQL injection.
Example:
CREATE PROCEDURE search_orders @custid   nchar(5) = NULL,
                               @shipname nvarchar(40) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT OrderID, OrderDate, CustomerID, ShipName ' +
              ' FROM dbo.Orders WHERE 1 = 1 '
IF @custid IS NOT NULL
   SELECT @sql = @sql + ' AND CustomerID LIKE @custid '
IF @shipname IS NOT NULL
   SELECT @sql = @sql + ' AND ShipName LIKE @shipname '
EXEC sp_executesql @sql, N'@custid nchar(5), @shipname nvarchar(40)',
                   @custid, @shipname

5.
When using dynamic SQL- enclose all object names in quotename().
http://copypastenet.blogspot.com/2011/08/quotename.html

6.
Stored procedures with static SQL only are more safe than Stored procedures with dynamic SQL.

7.
like '%\' + IsNull(@QCTName, '') + '%' ESCAPE '\'