The following is an excerpt from Securing SQL Server: Protecting Your Database from Attackers by author Denny Cherry and published by Syngress. This section from chapter nine explores why SQL injection attacks are so successful.
SQL Injection attacks are so successful for a few reasons, the most common of which is that many newer developers simply do not know about the problem. With project timelines being so short, these junior developers do not have the time to research the security implications of using dynamic SQL. These applications then get left in production for months or years, with little to no maintenance.
Leaving Notes for Future Generations
Would not it have been great if when you were first learning about writing application code that talked to the database, if someone had told you how to properly write parameterized code? Even if that did not happen, it can happen for the next guy. Leave some comments in the application source code that you maintain, that explain why you are parameterizing the database code the way that you are so that the next guy can learn from how you have done things.
These developers can then move through their career without anyone giving them the guidance needed to prevent these problems.
Now developers are not solely to blame for SQL Injection attack problems. The IT Management should have policies in place in order to ensure that newer developers that come in do not have the ability to write dynamic inline SQL against the database engine; and these policies should be in forced by code reviews to ensure that things are being done correctly. These policies should include rules like the following.
- All database interaction must be abstracted through stored procedures.
- No stored procedure should have dynamic SQL unless there is no other option.
- Applications should have no access to table or view objects unless required by dynamic SQL, which is allowed under rule #2.
- All database calls should be parameterized instead of being inline dynamic SQL.
- No user input should be trusted and thought of as safe; all user interactions are suspect.
With the introduction of Object Relational Mappings (ORM) such as Link to SQL and nHybernate, the SQL Injection problems are greatly lessened as properly done ORM code will automatically parameterize the SQL queries. However, if the ORM calls stored procedures, and those stored procedures have dynamic SQL within them, the application is still susceptible to SQL Injection attacks.
SQL Injection Happens at All Levels
Unfortunately, not just small companies can have problems with SQL Injection attacks. In 2009, for example, ZD Net reported that some of the international websites selling Kaspersky antivirus, specifically Kaspersky Iran, Taiwan, and South Korea, were all susceptible to SQL Injection attacks. In the same article (https://www.zdnet.com/article/international-kaspersky-sites-susceptible-to-sql-injection-attacks/) ZD Net also reported that websites of F-Secure, Symantec, BitDeffender, and Kaspersky USA all had problems with SQL Injection attacks on their websites.
These are some of the major security companies of the day, and they are showing a total lack of security by letting their websites fall prey to the simple injection attack. Considering just how simple it is to protect a website from an SQL Injection attack, the fact that some of the biggest security companies in the industry were able to have SQL Injection problems on their production customer facing websites is just ridiculous.
Because of how intertwined various websites are with each other, real-estate listing providers and the realtors which get their data from the listing providers, a lot of trust must exist between these companies and the people who use one companies site without knowing that they are using another companies data. This places the company that is showing the real-estate listings to their users in a position of trusting the advertising company to have a safe application. However, this trust can backfire as on a few occasions various partner companies have suffered from SQL Injection attacks, in some cases pushing out malicious software to the users of dozens, hundreds, or thousands of different websites that display the data.
How to figure out you have been attacked
There are two basics ways that SQL Injection attacks are used. The first is to query data from a database and the second is to change data within the database. When the attacker is performing a query only SQL Injection attack, detecting the SQL Injection attack is not the most straightforward thing to do. When an attacker does a good job executing a query only SQL Injection attack against your website there should be little to no evidence of a SQL Injection attack on the SQL Server database instance itself. The only evidence of the SQL Injection attack will be within the web server's logs, assuming that the web server is configured to log all requests to the website.
Securing SQL Server: Protecting Your Database from Attackers
Author: Denny Cherry
Learn more about Securing SQL Server from publisher Syngress
At checkout, use discount code PBTY25 for 25% off this and other Elsevier titles
If logging is enabled then the web server will include within the logs any HTTP requests which are passed to the web servers. These requests will include the SELECT statements which were attempted to be executed against the database engine. Analyzing these logs should be done using some sort of automated process. Packages could be created in SQL Server Integration Services which could process the transaction logs for a smaller scale website which could be used to parse the log files looking for successful requests which include SQL Statements in the query string or POST requests. For larger websites it may be more efficient to process the logs using a NoSQL platform such as Hadoop or Microsoft's HD Insight version of Hadoop so that the workload can be processed by dozens or hundreds of nodes depending on the amount of web server log data which needs to be processed daily. Making the processing of the web servers logs harder is the fact that you need to look for more than just the SELECT keyword. Remember from Example 9.3 where binary values were passed in from the attacker to the web server which were then converted by the SQL Server back to dynamic SQL? This attack vector also needs to be watched for by looking for keywords such as EXEC and CONVERT or CAST. Due to the rapid complexity of searching through the web server's logs scaling out this processing on a distributed system quickly becomes a much more flexible and scalable option.
SQL Injection attacks which change data are easier to identify as they leave a trail behind in the changed data which resides on the SQL Server Instance. Typically if an attacker is making changes to data within the SQL Server database they are going to change large amounts of data at once. One way to initially detect this is to look for transaction logs which are suddenly larger than expected. This can give you a clue that may be a SQL Injection attack has modified large amounts of data within the database platform.
Other options include auditing data which should not be changing to see if that data has changed. If it has an alert can be sent to the database administrator or security team so that a person can visually inspect the data to see if it has been modified by normal processes or if invalid data, such as an HTML iframe tag has been injected into the data within the SQL Server's tables.
Read an excerpt
Download the PDF of chapter nine in full to learn more!
The third way to detect a SQL Injection attack against a SQL Server database where data has been changed is to monitor the web servers logs much like for the read only SQL Injection attack. While different key words may need to be evaluated this will still give you a good idea if something is attempting to attack the SQL Server instance.
There are unfortunately no standard SQL Server Integration Services packages of Hadoop queries which can be documented as each environment is different. These differences include the web server software which is used, the data points which are captured, the key words which need to be included or excluded based on the names of web pages, parameters which are expected, etc.
About the author:
Denny Cherry (MCSA, MCDBA, MCTS, MCITP, MCM) has been working with Microsoft technology for more than 15 years, beginning with Windows 3.51 and SQL Server 6.5. In 2009, he was named as a Microsoft MVP for the Microsoft SQL Server product, and in 2011 Mr. Cherry earned the Microsoft Certified Master certification for SQL Server 2008. He has written dozens of articles for a variety of websites as well as print magazines on a variety of subjects including SQL Server, Clustering, Storage Configuration, and SharePoint.