In an earlier post titled Security Development Lifecycle: Introduction I begun introducing what the Security Development Lifecycle (SDL) represents and as I continue this series I will focus on the SDL model that Microsoft has so graciously provided to the community.
In part 2 of this series I want to focus of SQL Injection and for those of you just getting started it is important to understand what a SQL Injection attack is. Here is what Microsoft has stated:
- SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parametrized data can be manipulated by a skilled and determined attacker.
- The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.
- The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark “–”. Subsequent text is ignored at execution time.
The two root causes for SQL Injection attacks are the use of dynamic SQL and ineffective or even all together lacking input validation. The best advice here is:
- Use parameterized queries or stored procedures
- Validate all input. Always assume that the data coming in is corrupt and possibly malicious.
Breaking The Bank
We are all too familiar with the login process and much like your favorite social network or even your banking website the typical login process includes the collection of a username and password. Assume for a moment that your favorite website does not address SQL Injection and therefore uses dynamic SQL. Consider the following:
SELECT OrderID, CustomerID, ShipAddress, ShipCity, ShipPostalCode FROM Orders WHERE (CustomerID = '" + customerid + "'";)
Do you spot the problems? The issue is the customer id is passed as literal string values and they may contain malicious input which can allow the end user to perform actions that you never thought that they could. The user may be able to access data they should not, drop tables, or even worse drop the entire database for example. Let us assume for a moment that the end user enters the id of VINET’. Now paying attention to the single quote the SQL is now constructed as:
SELECT OrderID, CustomerID, ShipAddress, ShipCity, ShipPostalCode FROM Orders WHERE (CustomerID = 'VINET'')
When this statement executes, the SQL parser will locate the extra quote mark and throws an error. How this error is captured and the user alerted is a topic in itself but let us assume that the raw error is being returned to the user. This error is a dead giveaway that user input is not being sanitized and that the application is wide open to SQL injection attacks. With this information the user can begin to probe the database. Never provide to much details to the user via error messages.
Since we are talking about merely constructing essentially a string that will represent a query that is in turn to be executed against a given database if we construct the following you begin to understand what is occurring. So assume the user enters VINET;drop table Orders– , what do you think would happen? If you said the statement is valid and would not only execute the query but also drop the Orders table then you are correct. This is not the fault of the database nor should one think it is. In fact the SQL syntax is 100% valid.
SELECT OrderID, CustomerID, ShipAddress, ShipCity, ShipPostalCode FROM Orders WHERE CustomerID = 'VINET';DROP TABLE Orders--'
So the lesson here is simple, never trust a user’s data input to perform database actions.
Sanitize the user input at all times to insure that they do not inject malicious values. You may begin to consider building a blacklist to prevent unacceptable characters, such as quotes or semicolons or escapes, but I strongly urge you not to take this approach. Though it may be simple to point out some dangerous characters, it’s harder to capture all of them. Rather establish a whitelist and define what is acceptable and anything else is rejected.
SQL injection attacks are nothing new and not difficult at all to address. All it really takes is attention to to detail and a few extra minutes of time where in the long run your efforts will protect your database, otherwise you may find yourself in a precarious position. If you find that you have inherited a project and you run across this vulnerability then by all means bring it to the attention of the team.