Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
Data Access 5
.NET 2
VS2010 2
dot net 2

Archive · View All
May 2010 5
April 2010 3
May 2011 1
December 2010 1

Deepak Palkar's Blog

Tips and Tricks to prevent SQL Injection in .Net Code

May 23 2010 3:49PM by Deepak Palkar   

There are many detailed articles available on web on SQL Injection topic and hence I’ll just try to keep it short and simple in this post.

SQL Injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. This can allow an attacker to steal the data as well as modify and delete it. Conventional security measures like use of SSL and IPSec, do not protect the application from SQL injection attacks.

The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". Subsequent text is ignored at execution time.

For example, if we have following code in our application
string sql = "select * from Sales.SalesOrderHeader OH inner join " +
                 " Person.Address A on OH.ShipToAddressID = A.AddressID " +
                 " where City = '" + txtCity.Text + "'";

Here we are just expecting a name of the city unaware that an attacker may enter following,

Paris'; drop table Sales.SalesOrderHeader--

In this case the following query will be executed.

select * from Sales.SalesOrderHeader OH 
inner join Person.Address A 
on OH.ShipToAddressID = A.AddressID
where City = 'Paris';drop table Sales.SalesOrderHeader--'

As you can see, as per the attacker’s input, the semicolon starts a new statement to execute and conveniently ignores the single quote which is concatenated in the code. SQL Server will run this code and drop the table.

Prevention Techniques
Countermeasures include using a list of acceptable characters to constrain input, using parameterized SQL for data access, and using a least privileged account that has restricted permissions in the database. Using stored procedures with parameterized SQL is the recommended approach because SQL parameters are type safe. Type-safe SQL parameters can also be used with dynamic SQL. In situations where parameterized SQL cannot be used, consider using character escaping techniques.

To counter SQL injection attacks, you need to:

  • Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.
  • Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
  • Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.
  • Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user.


This MSDN white-paper explains these measures further in detail.

Happy Coding!!

Tags: Data Access, BRH, dot net, sql injection, #DATAACCESS, #DOTNET,

Deepak Palkar
182 · 1% · 269


Your Comment

Sign Up or Login to post a comment.

"Tips and Tricks to prevent SQL Injection in .Net Code" rated 5 out of 5 by 2 readers
Tips and Tricks to prevent SQL Injection in .Net Code , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]