Injection in SQL SERVER

SQL injection is a technique where hacker can inject SQL commands into an SQL statement, via web page.Injected SQL commands can compromise the security of a website.

SQL Injection Based on 0=0 is Always True

Let's say that the original purpose of the code was to create an SQL statement to select a user with a given Emp_id.

Emp_id: 112 or 0=0

SELECT * FROM Emp_Details WHERE Emp_id = 112 or 0=0

The SQL above is valid. It will return all rows from the table, as WHERE 0=0 is always true.

 

SQL Injection Based on ""="" is Always True

Here is a public construction, used to verify user login on a web page:

User Name:

Password:

The Final Statement will be

UserName = request.form("UserName");
Pass = request.form ("UserPass");

sql=" SELECT * FROM Emp_Details WHERE UserName ='" + UserName + "' AND PassWord ='" + Pass + "'"

Hackers might get access to user names and passwords in a database by simply inserting “or ""=" into the user name or password text box.

The code at the server will create a valid SQL statement like this:

SELECT * FROM Emp_Details WHERE UserName ="" or ""="" AND PassWord ="" or ""=""

The result SQL is valid. It will return all rows from the table Users,since  WHERE ""="" is always true.

SQL Injection Based on Batched SQL Statements 

Most databases support batched SQL statement, separated by semicolon.

e.g.

SELECT * FROM Emp_Details ; DROP TABLE EmpLoginDetails

The SQL above will return all rows in the Emp_Details table, and then delete EmpLoginDetails table.

If we had the following server code:

UserName = request.form("UserId");
txtSQL = " SELECT * FROM Emp_Details WHERE UserName = " + UserName;

And the following input:

User Name: 105; DROP TABLE EmpLoginDetails

The code at the server would create a valid SQL statement like this:

SELECT * FROM Emp_Details WHERE UserName = 105; DROP TABLE EmpLoginDetails

Parameters for Protection

The only way to protect a web page from SQL injection, is to use SQL parameters. SQL parameters are values that are added to an SQL query at execution time, in a controlled manner. The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.

UserName = request.form("UserId");
sql = " SELECT * FROM Emp_Details WHERE  UserName = @a";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@a", UserName);
command.ExecuteReader();