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.
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.
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.
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.
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.
author: Denny Cherry
Source: techtarget.com
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.
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.
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.
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.
author: Denny Cherry
Source: techtarget.com
Comments
Post a Comment