Coding Instructions To Prevent SQL-Injection

SQL-Injection is a very sensitive and very common technology to hack an application. I am describing a few techniques to make the SQL-Injection difficult.

The main point of injection is a dynamic query. Whenever a dynamic query is executed on the database there is a chance of SQL-Injection. If we follow some rule then we can minimize the hack attack.

Microsoft suggests using parameterized queries to avoid the SQL-Injection hack. For detailed information kindly visit: How To: Protect From SQL Injection in ASP.NET. We can create a parameterized query in both applications and databases. I am describing how to create and execute a parameterized query.

Generally we use this terminology to create and execute a query.



In the preceding query if vulnerable data is passed using a query string “name” then the SQL-Injection can happen. Suppose a hacker passes “sumit’ or 1=1--” as the value of the query string “name” then the actual query that will be executed on the database will be:



This query will return all the records. In the same manner any query can be executed using this page or parameter. Like when a hacker passes “sumit’; drop table TestInjection;--” the actual query that will be executed will be:



To prevent these queries from execution we can create the same queries in this way.



If queries are created in the preceding form then the @name parameter is treated as a literal value and not as executable code. In other words if any injection values are passed using a parameter then whatever is passed in the parameter will be entirely used as a single value, not as two separate queries. And as a result no result will be returned from the database.

Sometimes we need to create and execute dynamic queries in a procedure also. Normally we create dynamic queries in this way.



This way of creating and executing a query is again vulnerable. For testing just create the procedure as shown above and execute in the following manner.



We can create parameterized queries in a database too. See the following code:



I would like to describe the method of creating and executing a query. I will describe only the preceding query. For detailed information please refer to the following online documentation: sp_executesql (Transact-SQL) and Using sp_executesql.

For creating a dynamic query in SQL server first declare a nvarchar variable and assign a query to this variable. In the query text write SQL parameters instead of the actual parameter passed to the procedure from the application as I used @namenew.

Now again declare a new nvarchar variable for parameter definition. This variable will describe the parameter details that are used in the actual query. As I declared @p.

Now use the sp_executesql procedure to execute this query. This procedure will take three arguments to execute the query. First the query to be executed, second the parameter defining the variable and third the value for the parameters used in the query.

If any query is written and executed in this way then it is safe from SQL-Injection.