In my last article I discussed about the performance issue with SQL Cursors. In this article I would like to discuss about the Database Security.
What is SQL Injection?
As name suggests, it is special kind of Sql attack on your database. And your Application is the interface for this Attack. Here attacker enters some invalid string sequence in the user input Form or web Page and submit it. These strings may perform some destruction activity in your database.
While developing Web Site we always think about security, user Privilege, Authentication, Authorization, SSL and all… But despite Sql Injection fatality it is not given due attention this is what my perception.
I directly start with an Example.
Example:
This is what my simple Login Method does. Here I am not validating the user inputs. This is a hypothetical example of user login. Here I am using normal SQL to check whether User is Exist or Not.
try
{
using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
cn.Open();
SqlCommand comLogin = new SqlCommand();
comLogin.Connection = cn;
comLogin.CommandType = CommandType.Text;
comLogin.CommandText = "Select * from Users where UserName= '" + txtUserName.Text + "' and " +
"Password = '" + Password.Text + "'";
SqlDataReader dr = comLogin.ExecuteReader();
if (dr.Read())
{
// Succsess
Response.Redirect("LoginSuccess.aspx");
}
else
{
// Failure
Response.Redirect("LoginFail.aspx");
}
}
}
catch(Exception ex)
{
throw ex;
}
In above example I am directly taking the user input from controls.
Now suppose I do enter User Name as Nitin and Password as ‘; Drop Table Users -- ‘ and press Login button. You just guess what damage will be caused by this.
If your Sql profiler is on you will find some thing like below is executed.
Select * from Users where UserName= 'nitin' and Password = ''; drop table Users --''
Above command actually executes two SQL queries.
- Select * from Users where UserName= 'nitin' and Password = '';
- drop table Users --''
End result, I do not get any record but Users table is dropped. Instead of dropping the Table, you can write some other command which can update existing records or even insert new record.
Or you can just trace pass this login validation by entering password as ' or 1=1 ---'
It makes end sql statement like this.
Select * from Users where UserName= 'nitin' and Password = '' or 1=1 ---''
It always returns records because 1 = 1 is always true.
By now we understood this attack can be performed by skilled attackers like you and me. J
There are many more ways or variety of Inputs an attacker can perform and cause following damages.
- Delete, update, and insert the data in tables.
- Change existing important data like Email Id for particular user and can get other information.
- Get the information about the existing tables and Columns of database.
- Drop the entire table.
- Shut down the Sql Server itself by executing command for this.
- Insert some commands or executable statement in table which may later on cause some damage.
- Get the information about the Machine which is hosting the Sql Server by executing some system procedures.
And many more ………..
All depends on Attackers expertise with SQL
What can we do to avoid these attacks and make the system more robust?
- Replacing Single Quote with Double Quote
I think everybody has used this line of code.
serName.Text.Replace("'","''")
Generally here we are replacing any single quote with Double one. Actually to save Single Quote we have to replace single Quote with Double quotes, that is what we did here. But knowingly or unknowingly we avoided many Sql Injections too.
- Validate the input for its type and length.
We may have user input where we expect the numeric value. Just replacing the single Quote with double will not work in this case. Suppose your SQL query is expecting a numeric value and with out validation something like “1; Drop table Users” is passed to your Query. Check this SQL
Select * from Users where UserID = 1; Drop table Users
In above example user write a numeric value “1” which application is expected and then put a semicolon “;” which ends first SQL query and then wrote “Drop table Users”. This will do all the damage. To avoid this we should always validate the Data at first place. We should validate our data for type, Length, escape sequences, etc.. Never build your query directly from User Input, just like what I did in my example. ;)
- Use Parameterized Dynamic SQL or Parameterized Stored Procedures.
Using parameter collection has two advantages.
- Whole Input will be treated as Literal value not an executable code. So even you enter password ‘; Drop Table Users -- ‘. It will do no harm at all like it did in case of direct SQL.
- You can validate the input for its type and length like below.
comLogin.Parameters.Add("@UserName", SqlDbType.VarChar, 5);
- Do not show end user actual database error.
Error message can have information about your database schema, which can be used by attacker for Sql Injection. It is good practice to show the User a general user friendly error message and log the actual Error in Database.
- Always store the confidential data in Encrypted format
It is a good practice to store the confidential data in encrypted format even you are taking all the precaution to avoid any SQL Injection.
- Do not use Admin user account
If not necessary do not use Admin user or user with Owner rights for connection the database.Better use an account with less privileged, which can perform only the necessary SQL Operations.
References:
