Prevent SQL Injection in Asp.Net (C#)

How to prevent SQL Injection in Asp.Net (C#)?

An SQL Injection attack takes place when someone inserts SQL code into a field on a web page that is then passed on to the database. For example if I had a grudge against some company and their site wasn't secure, I could insert an SQL Injection attack on their unsecured website and delete all the data in their database, or possible steal all their credit card numbers.

Preventing an SQL Injection attack is as simple as using proper coding standards when accessing your backend database. Using these methods doesn't add significantly to your development time and in many cases actually reduces it, because it reduces many opportunities for errors, and allows for better error handling.

      Sample Bad Query:
      -----------------------------

      strSQL = “SELECT * FROM CUSTOMERS WHERE EMAILADDRESS = " +
      txtEmailAddress.Text +";


In the above C# code, basically we are taking input directly from the web field containing the user entered email address and passing it straight into our query without any checks. If for example I had typed in ” 1'; DELETE FROM CUSTOMERS; “, it would have selected the customers where the email address equals “1? and then deleted all records from CUSTOMERS.

This vunerability is amazingly common even on today’s modern websitesand most of them don’t even realize it.

The Solution
------------------
Solving this little problem is as simple as changing the way you make your query. Instead of contactenating your strings to build a query, simply use an SQL Command object and parameters. Not only are you gaining the added security and protecting your business, you will actually make your site more efficient because queries using parameters are compiled for future use by SqlServer and therefore have better repeat performance.


Sample Proper Code:
-------------------------------

      cmdTemp.CommandText = “SELECT * FROM CUSTOMERS WHERE EMAILADDRESS =
      @EMAILADDRESS”;
      cmdTemp.Parameters.Add(“@EMAILADDRESS”, SqlDbType.Varchar, 50).Value
      = txtEmailAddress.Text;


Yes you have one extra line of code, but that line of code actually helps you out. For example if this was an INSERT instead of a SELECT then it would automatically prevent me from sending a string that was to long for the field to the SQL Server, allowing me to catch the error on the business logic side. The same would hold true if for example I was trying to pass a string into an int field.

Take this simple step, it’s not only a better way to code, it could potentially save your company millions in lawsuits and hundreds or thousands of people the pain and suffering of having their credit card numbers stolen.

SQL management studio 2005: Creating a temp pivot table

I used the following dynamic SQL to create a temp pivot table in a script to join onto my final result set. I used this method as the names of the columns could change depending on user input.
It involves creating a temp table then adding columns and populating the table using dynamic SQL in  a loop.




DECLARE @work_order int
--create temp function table first
DECLARE @function_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
[Function] varchar(100))

DECLARE @item_category_counter INT
DECLARE @loop_counter INT

INSERT INTO @function_table
SELECT wo_team_label_desc FROM ref_wo_team WHERE dept_id = @dept_id AND
inactive_ind = 0 ORDER BY sort_order

--Pivot function table
CREATE TABLE #PIVOT_FUNCTON_TABLE (primary_key INT IDENTITY(1,1) NOT NULL,
work_order int)

SET  @loop_counter = ISNULL((SELECT COUNT(*) FROM @function_table),0)
-- Set the @loop_counter to the total number of rows in the memory table

SET @item_category_counter = 1

WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN

--Add dynamic column name
DECLARE @ColumnName NVARCHAR(100)
SET @ColumnName = (select [Function] FROM @function_table WHERE primary_key
= @item_category_counter)
DECLARE @query NVARCHAR(4000)
SET  @query = 'ALTER TABLE #PIVOT_FUNCTON_TABLE
ADD [' + @ColumnName + ']  NVARCHAR(100);'
EXECUTE(@query)

SET @item_category_counter = @item_category_counter + 1
END

DECLARE @item_category_counterWO INT
DECLARE @loop_counterWO INT

SET  @loop_counterWO = ISNULL((SELECT COUNT(*) FROM @WOtable),0)
-- Set the @loop_counter to the total number of rows in the memory table
SET @item_category_counterWO = 1

WHILE @loop_counterWO > 0 AND @item_category_counterWO <= @loop_counterWO
BEGIN

SET @work_order = (SELECT work_order_id FROM @WOtable WHERE primary_key =
@item_category_counterWO)

--Populate table
DECLARE @item_category_counter3 INT
DECLARE @loop_counter3 INT

SET  @loop_counter3 = ISNULL((SELECT COUNT(*) FROM @function_table),0)
-- Set the @loop_counter to the total number of rows in the memory table

SET @item_category_counter3 = 1

DECLARE @UpdateQueryHead NVARCHAR(4000)
DECLARE @UpdateQueryBody NVARCHAR(4000)
DECLARE @UpdateQueryFooter NVARCHAR(4000)
SET @UpdateQueryHead = 'INSERT INTO #PIVOT_FUNCTON_TABLE VALUES (' + (CAST
( @work_order  AS NVARCHAR(4000))) + ''
SET @UpdateQueryBody = ','
SET @UpdateQueryFooter = ')'

WHILE @loop_counter3 > 0 AND @item_category_counter3 <= @loop_counter3
BEGIN

--Create @UpdateQueryBody string
DECLARE @Value NVARCHAR(100)
SET @Value = Replace(
(SELECT RU.first_name + ' ' + RU.last_name
FROM wo_team WOT
INNER JOIN ref_wo_team rwt ON  WOT.dept_id = rwt.dept_id AND WOT.wo_team_id
= rwt.wo_team_id
INNER JOIN ref_user RU ON WOT.dept_id = RU.dept_id AND WOT.user_id = RU.
user_id
WHERE WOT.dept_id = @dept_id
AND work_order_id = @work_order
AND WOT.user_id <> ''
AND WOT.user_id <> 'NA'
AND WOT.user_id <> 'N/A'
AND wo_team_label_desc = (select [Function] FROM @function_table WHERE
primary_key = @item_category_counter3)),'''','')

SET @UpdateQueryBody = @UpdateQueryBody + (CASE WHEN @Value IS NULL THEN
'NULL' ELSE  '''' + @Value + '''' END) + ', '
SET @item_category_counter3 = @item_category_counter3 + 1

END

DECLARE @UpdateQuery NVARCHAR(4000)

--Remove trailing comma
SET @UpdateQueryBody = SUBSTRING ( @UpdateQueryBody ,1 , (len(
@UpdateQueryBody)-1))
SET @UpdateQuery = @UpdateQueryHead + @UpdateQueryBody + @UpdateQueryFooter

EXECUTE(@UpdateQuery)

SET @item_category_counterWO = @item_category_counterWO + 1
END