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.