Prevent SQL Injections in your applications

SQL injection is an area where most of the applications leave venerable openings to the system. This can be prevented with in many ways as described as follows.

If we take an example of the following url

http://localhost:8080/MySite.aspx?id=1

we can divide this url in to main two sections in terms of security.

The first part up until the “id=” sections is considered as the trusted section , or trusted area. Because this part is controlled by the developer. Ie : it has some kind of predefined control in it.

But if you go the query string section, it is considered as the untrusted section. Because, anyone can take control of the payload of that section.

Following are the common sources of untrusted data

·         Query strings

·         Forms postings

·         Cookies

·         Request headers

·         External data integrations

Following are some points where we can improve the security in terms of SQL injections.

1.       Least privilege principle

Normally when we are creating a user ( lets say a SQL Server user where the we are using in the Connection string to connect to the DB ) , we are giving the user default privileges. But is this useful? Normally, we do not need any DBA rights to the application user. Just read and write privileges for the transactions tables and other relevant db object would be far more enough to the application user. So even though the application user credentials have got leaked , we can minimize the impact to the db.

2.       Use inline SQL parameterization

Take a look at the following query

var cmdQuery = “Select * from Students where id=” + studentId;

so the studentId variable may would contain any malicious payloads which can harm your database. The best way to prevent is by using inline SQL parameterization as below.

  var cmdQuery = “Select * from Students where id=@StudentId”;

you can bind the @StudentId param with the incoming variable.

3.       Use SQL Stored procedures instead of inline queries

If you do not have the option of using an ORM to access data. The best way to execute queries in terms of security  is using stored procedures with parameters. This would isolate the db queries from the application logic as well.

4.       Implement  a white list for incoming parameters

We can implement the whitelist using type conversion, regular expressions and using list of known values. Best way to do is get rid of strings as much as possible. But if you must using strigns the best way to use is implement reg ex expression filters to filter strings.

5.       Use ORM

Always try to use an ORM ( Object Relational Mapper ) to connect with your Database. This would eliminate many SQL injection vuanaabilities and improves performance ( this is subjective though ) , and decrees number lines of codes as well.

6.       Securing inline queries inside a Stored Procedure.

As I explained before , using Stored Procedure would eliminate most of the injection vunarabilities. But we need to use Stored procedures in causion. Take a look at the following TSQL.

DECLARE @Query VARCHAR(50)

SET @Query =   ‘ Select * from Students where Name Like ‘’%’ + @SearchName + ‘%’’’

EXEC(@Query)

What we are doing here is just move the injection vulnerability from the application code to the back end code. Anyone use the @SearchName to pass in malicious payload.

Eg : ‘ Or 1=1 – would return full dataset in above example

Best is not to use dynamic inline query like these in the TSQL or any back end. But if you must use inline queries , you can mitigate this particular risk update the code as follows.

DECLARE @Query NVARCHAR(50)

SET @Query =   ‘ Select * from Students where Name Like ‘’%’ ‘+ @SearchName + ‘‘%’’’

EXEC sp_executesql @Query, N’@LocalSearchName VARCHAR(50)’ , @LocalSearchName =@SearchName