All Questions ASP.NET CSharp SQL Server | All Questions WCF MVC HR Round | |
| Search This Site | ||
Employee Search Page  |
The HTML for the Employee Serach Page is shown below. As you can see from the HTML, the Employee Serach Page contains TextBox, Button and a GridView control.
Employee Search Page HTML  |
The codebehind page for the EmployeeSearchPage is shown below.
Employee Search Page Code Behind |
The Button1_Click event handler has the required ADO.NET code to get data from the database. This code is highly susceptible to sql injection attack and I will never ever have code like this in production environment. The second line in Button1_Click event handler, dynamically builds the sql query by concatenating the Employee ID that we typed into the TextBox.
So, for example, if we had typed 2 into the Employee ID textbox, we will have a SQL query as shown below.
Select * from Employees where Id=2
If a malicious user, types something like 2; Delete from Employees into the TextBox, then we will have a SQL query as shown below.
Select * from Employees where Id=2; Delete from Employees
When this query is executed, we loose all the data in the Employees table. This is SQL Injection Attack, as the user of the application is able to inject SQL and get it executed against the database. It is very easy to avoid SQL Injection attacks by using either parameterized queries or using stored procedures.
You may be thinking, how will the user of the application know the name of the table. Well, one way is to simply guess or inject a sql syntax error. The injected SQL syntax error causes the page to crash and can possibly reveal the name of the table as shown below. However, proper exception handling and custom error pages can be used to prevent the end user from seeing the yello screen of death. The screen shot below shows the table name Employees.
Page crash revealing Employees table name  |
All Questions ASP.NET CSharp SQL Server | All Questions WCF MVC HR Round | |
| Search This Site | ||
You haven't mentioned the exact ways to avoid the situation. It would be great if you could explain it in detail.
ReplyDeleteUse stored procedure instead of plain sql query in code.
DeleteIn a way, he did, if you have a proper handling of the exception then you won't show an error like, exposing table names. One easy way, is imply wrap around the answer , in this case 2 with single quotes. that way is different this:
ReplyDeleteselect * from Employees where ID = 2;delete from Employees, than select * from Employees where ID = '2;delete from Employees'. The second one will generate an error that you can capture.
yep i also want an explaination that how to avoid it by showing eg through queries.
ReplyDeletepleas explaine how to solve this problem
ReplyDeleteThe solution is that we need to think as if we are tester and try to code according to ppl who may write such a kind of things. The better way is to always use store procs and fucntions to get values from database.
ReplyDeleteMay be we can control that from the text box level, by avoiding the user to enter special characters or curtail the length of the textbox ! Or else u check for the validity of the query too ! Yes agree with all of you. He should have shown an example to avoid this.
ReplyDeleteFor those who asked for a solution, he mentions it "It is very easy to avoid SQL Injection attacks by using either parameterized queries or using stored procedures."
ReplyDeleteAlways, always, always use stored procedures. This not only helps stop a SQL injection attack, it also encapsulates the SQL in the database and not in the web site code. Additionally security can be placed against that SP further prevent unauthorized execution.
You can also implement javascript to validate the input for the form field to prevent another vector of attack.
Very much interesting Materail
ReplyDeletenice explanation.i would happy if provide eg of how to avoid error by ussing procesures.i m expecting u will append soon
ReplyDeleteits a very nice blog
ReplyDeleteits a very nice blog
ReplyDeleteFor a comprehensive approach to security, multiple layers are the best bet, and approaching the best practice:
ReplyDelete1. Asking for the id (primary key) for the input is questionable. Some other input is perferrable.
2. Javascript/Jquery validation of the input is a good way of stopping problems at the client, before reaching the server. In this case, validating the string as a whole integer of a certain range (ie, 1 to 9999).
3. Utilization of stored procedures.
4. Fully specify the sql query (ie, "Select fname, lname from employees where lname like " + lname + "%", instead of "select *..."
5. Put the connection string somewhere else, other than directly in the code. An encrypted config file is a valid choice.
Oh, missed this one:
ReplyDeleteValidate input on the server side, as well. In this case, insuring you have a numeric value in the proper range (ie, 1 to 99999) before executing the sql code (hopefully in a stored procedure) is good practice
This is an employee search page and not a CRUD. so just have a select grant for the user. No delete or update grants. always validate the controls at the client site to avoid unnecessary round trips of post backs.
ReplyDeleteIn addition to previous advices, you can also use user with read-only access for select queries.
ReplyDeletenice
ReplyDelete