Threat description: SQL injection is a well known threat. Typically either the URL or a screen input form can be used to deliver the unwelcome SQL commands. This is an attack that is easy to execute and does not require significant technical skills to perform. The approach is essentially to use a valid input method where the application is expecting some legitimate SQL commands, and to instead substitute your own malicious SQL commands. These commands can be used to steal data, destroy data or just to disrupt database operations.
Threat impact if not remedied: SQL injection can be range from purely destructive scripts (drop tables, drop the entire database, alter database content) through to the theft of critical data. Given the current climate of mandatory disclosure of compromised data, this can be extremely destructive to a companies reputation and future revenue flow. This is a very serious vulnerability.
Countermeasure approach: There are a number of steps required to prevent SQL injection. Input validation is the most common approach and can prevent a significant amount of compromise. However there are key steps that need to be taken “under the covers” during the development of the application. The legitimate accounts used to interrogate or update the database cannot be and should not be “over-privileged”. There are also a number of other techniques that can be used during development to prevent these attacks or at the very least reduce the severity and impact of them. An example would be to remove the delete privilege from the application account and instead use a deletion flag built into the database to indicate if a record is to be deleted or otherwise excluded from other application processes (e.g. reporting). Limiting the number of rows that can be acted upon at any one time is another remediation technique we have used. All of the preventative measures obviously require both planning and more development cycles than if they are not built in. However the impact of a SQL injection breach is so significant that the comparative cost of prevention is really quite small and should be considered as a mandatory overhead for all Web Application development efforts.
The different steps we advocate for preventative measures against SQL injection are as follows;
Step 1) protect the database environment – be stingy with permissions and privileges
create accounts with minimum permission for the application to use – drive access permissions right down to the field level within a given table within a give database
use multiple users for differing functions so that you don’t have to use an over-privileged account for any database interaction
Step 2) use the code to protect the data – optimize your SQL for secure interactions
compartmentalize your SQL execution – if you execute all your SQL in an object oriented fashion and import only the results into your application you will have better control over your database
use single quotes for all value insertions, do not use variables for content substitution without encasing them in single quotes (see ‘C’ below for a follow-up to this guideline)
use input validation on all fields to restrict the input of any special SQL characters that allow you to escape from the desired function (e.g. single quotes or semi-colons) and begin new SQL statements – limit it to alphanumeric only where appropriate. E.G - This prevents someone from using a close quote and then inserting their own SQL in a data input field
use limit statements in all queries to ensure the minimal number of rows are acted upon for that interaction
for inputs that expect an integer – inspect the input prior to execution to ensure that only integers are entered (you can even establish a range of acceptable integers if appropriate)
in conjunction with restricting privileges and permission you can also design your code to never delete rows but instead deploy a deletion flag on the record to indicate if a record is to be deleted or otherwise excluded from other application processes (e.g. reporting). The added benefit of this is that you can never lose critical data as a result of either a deliberate or even an accidental deletion.
prevent errors from being presented in your production environment. Error messages are necessary during development and have much useful information contained within them. Presenting them in a production environment can allow an attacker even after a failed attack to be presented with key information embedded within an error message that can be used a future exploit.
Tip: Audit everything - all suspicious SQL statements, the IP address of the attacker, method used, the variables used within the transaction, date and time of attack, and the refer page that sourced SQL.
Lior Izik - CEH, GWAS
