What are SQL injection attacks on SQL Server? How can we prevent them?

Question

What are SQL injection attacks on SQL Server? How can we prevent that ?

Answer 1

SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

http://en.wikipedia.org/wiki/SQL_injection

How To: Protect From SQL Injection in ASP.NET

https://msdn.microsoft.com/en-us/library/ff648339.aspx

https://msdn.microsoft.com/en-us/magazine/cc163917.aspx

Below are some tips:

• NEVER EVER trust user inputs.

• Always use parameterized stored procedure.

• Always encode/decode user inputs.

• Don't write inline queries.

• Always Validate user inputs for special characters(<,>) in client side and server side

https://msdn.microsoft.com/en-us/library/hh882339%28v=vs.110%29.aspx

http://www.asp.net/whitepapers/request-validation

Answer 2

In simple language, SQLi or SQL injection attacks are attacks which make the application treat data as commands. For example, say you have an authentication system which uses the following SQL statement:

SELECT * 
FROM userlist 
WHERE name ='" + username + "';

Now, say the attacker enters the following username:

username = ' OR '1'='1

This will create a query which will look like this:

SELECT * 
FROM userlist 
WHERE username=' ' OR '1'='1';

Thereby bypassing the authentication as the result of the query will always return a valid response due to the OR condition always evaluating as true. This is a basic SQLi attack. You can combine multiple queries such as:

DROP TABLE userlist;

and append it to the original query in the following way:

SELECT * 
FROM userlist 
WHERE username=' ' OR '1'='1';DROP TABLE userlist;

which will delete the table named 'userlist' from the database.

Hope that helped.