Hello Santosh,
As far as I know, SQL injection can happens in front-end code (ASP.NET/Codebehind) as well backend code (Stored Proc/Functions).
Front-End
In front-end the common reason of SQL injection is not using parameterized quries (
SQLCommand
). What it means that dynamic query string are constructed by concatenating the request values. e.g.
strSQL = "SELECT * FROM user_table WHERE user_code = '" + Request["userCode"] + "' AND user_pass = '" + Request["userPass"] + "'";
In this case a hacker may type in values for say password field such that the resulting query may look like
SELECT * FROM user_table WHERE user_code = 'IUnknown' || user_code LIKE '%'
One way to solve this, but not reommended is to sanitize the input by escaping certain characters (',",| etc.) and generate the dynamic SQL string.
The preferrd way is to use bind variables (parameterized quries using
SqlCommand
). This way query only contains the placeholders and values are passed to the database. This not only helps databse server to reuse the query execution plan but also removes the need of hard parsing.
Now second type of SQL injection happens due to improper use of bind variables. In this case the front-end code is using the bind variables to say invoke a stored proc. Inside this stored procedure a dynamic query is getting constructed again by concatenating the values. So to prevent this agin you have to use parameterized queires inside your stored proc. A typical example for Oracle database will be
select * from emp where deptno = :deptno;
Note that :deptno actually refers to a variable whch might be a paramter passed to the stored proc.
Please also look at site and go through their
Secure Coding Practices guide to know more about other types of vlunarabilities.
There is also a similar guide by
Microsoft.
Regards,