A type of exploit whereby hackers are able to execute SQL statements via an Internet browser.
SQL injection is the name for a general class of attacks that can allow nefarious users to retrieve data, alter server settings, or even take over your server if you're not careful. SQL injection is not a SQL Server problem, but a problem with improperly written applications.
How To Avoid SQL Injection
So now that we know what SQL Injection is, how can we avoid it? Let's look at the mistakes Ima and his developer made:
The Developer Did Not Validate User Input
Your first line of defense against SQL Injection is always input validation. Any time an application accepts input from a user and uses it in a query against a database, it absolutely has to be validated. This validation can be done server-side, client-side, or even both. In our example, we know that certain special characters in input strings can open us up to SQL injection: double-dash (--), single-quote(') and semi-colon (;) are the primary trouble-makers. We can check for these and other special characters on the client or server side and eliminate them before we dynamically build queries based on user input.
A good rule of thumb is if you don't allow certain characters in the input string, eliminate them completely (or throw an exception) during the validation phase. For instance, all our usernames consist of only alphabetic characters, so we could limit input in that form field to letters 'A' - 'Z' and 'a' - 'z'. Anything the user inputs, including drop-down box form inputs, should be validated before you try to query with it.
The Developer Built A Query Dynamically
Our next line of defense is to avoid building queries dynamically when possible. Always look to parameterized queries (sp_executesql in T-SQL) first.
But if you do need to build a query dynamically, validate your input well and be certain to replace single quotes (') with escaped single quotes ('') in string constants. This can be done with the T-SQL Replace() function, or the .NET String.Replace() method can be used for this purpose.
For this particular application, however, it makes much more sense for Ima and the Developer to use a Parameterized Query to validate our user. Parameterized queries pass the input values to the server separately from the actual SQL Query. The server then provides a level of safety by using sp_executesql with parameters instead of dynamically building the query string.
Build queries dynamically only when you have to. And when you do have to, be sure to validate all input to the generated query string before running it against the server. Also when building dynamic SQL Queries, you generally should not need to dynamically insert table names, column names, etc. (unless you're building some sort of SQL DDL/Admin tools). If you find that you are generating a lot of dynamic queries with dynamic table and column names, it might be a good idea to take a closer look at your database implementation.
Ideally, Ima will build a stored procedure that accepts the username and password as parameters. This procedure will validate the user and return a success or failure code and any other pertinent information. The stored procedure model will add a layer of abstraction that will keep the hacker a little further away from our base tables.
The DBA Gave Away The Farm
You might notice in the example attacks that the hacker was able to run such commands as DELETE FROM Test_Logins and DROP TABLE Test_Logins. This is because Ima made the ASPNET user the Northwind dbo. Also, Ima gave the ASPNET user SysAdmin rights. Our DBA needs to scale back the rights for the ASPNET user to something more reasonable. Most likely this user will have read-only rights to most information; and Ima's ASPNET user should never, ever have rights to run DDL commands such as CREATE TABLE, DROP TABLE, etc. Ima also needs to lock down the master database to ensure the ASPNET user doesn't grab all available information about the server configuration.
The Developer Is Sending Too Much Information Across The Wire
In our sample, our Developer is sending much more information across the wire (between the IIS and SQL Server) than is necessary. All we need to know is if our username and password matched. So all we really need to return from SQL Server is a single number, not a complete table row! We could change the SELECT statement to return the EmployeeID of the logged in user with SELECT EmployeeID FROM .... The less information we have to pass along, the better our security.
The DBA Is Storing Plain Text In Critical Columns
Closely related to the previous problem, Ima is storing passwords in plain text format in the Test_Logins table. This is a big no-no. Sensitive information such as passwords should be stored in encrypted or hashed format. That way, even if our hacker happens to get past all the other lines of defense and is able to view the data in our tables, it will be unusable to him. One method of protecting passwords is to hash the passwords using a hash algorithm (like MD5) before storing them in your table. Then when you go to compare passwords at login time, hash the password in your ASP.NET code or your Windows Forms code before sending it to the SQL Server. Any time we can prevent sending sensitive information across the wire in plain text format, we should take advantage of it.
SQL injection is a security vulnerability that occurs in the database layer of an application. Its source is the incorrect escaping of variables embedded in SQL statements. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.