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
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
