SQL Injection Knowhow

Overview

SQL Injection is a technique to exploit SQL statements and trick the client side data from database. Here attackers try to trick the SQL statements that are used in the application (desktop and web based) to get the complete or the intended details from the database for which the attacker does not have proper permissions. To gain this unauthorized access, attackers try to input false or tricky string inputs into the SQL command line statements to view or manipulate restricted data. With SQL Injection attack, the attacker does not necessarily intend to view or manipulate data, but they may have intentions to perform blind actions like creating users, granting/revoking permissions to different users, etc.

How attacks are performed

There are number of SQL injection techniques available and they differ from attacker to attacker; however, the functionality or malfunctioning they exploit is the same. They find out the vulnerability in SQL queries using the web URL or the error messages generated. Often developers use dynamic SQL statements made up of strings that are concatenated or query parameters directly specified along with input keywords.

For example:

Select * from MyLoginAccounts whereloginname='arpit' 
         and loginID='123' and permission='admin'

When there is a SQL statement like above, there are great chances of exploit as the developer is passing the values directly into the SQL statements and which can be hacked and manipulated to give all the login details including the password and the database permission to the hacker if he /she tries to manipulate the above statement to cause a SQL injection attack. So here the attacker tries to manipulate the above SQL statement string as below:

Select * from MyLoginAccounts where loginname='arpit' or 
        '1'='1'—and loginID='123' and permission='admin'

By passing one more parameter such as "or '1'='1'" which is always true, the user tries to capture all the records from the system. Also, to restrict the other condition to be executed from the system, attacker uses '--' to make the keywords following it look like a comment statement.

This way, when an attacker passes a flawed string to the database query, it will return all the records to the attacker regardless of the original query. Thus the attacker can access sensitive information from the database even though he/she is not a legitimate user.

Attack categories

1. First order attack

In this type of attack, the attacker enters a malicious SQL string and tries to run it immediately to get the piece of information needed. In this type of attack, the attacker may add a subquery or a union statement to the existing SQL query to buy information illegally. Here attackers tweak an existing query in such a way that when executed, it executes only the intended part of the query; that is, in this case, the added subquery or union statement.

2. Second order attack

In this type of attack, the attacker tries to get control of persistent storage systems like rows and perform malicious activity on them. Here, the attacker tries to create database objects like tables, restricted views, and even login accounts which can further be used to carry out dangerous attacks like retrieving the database schema, login account details, customer sensitive information, etc.

3. Lateral injection

In this type of attack, the attacker can manipulate implicit functions. For example, manipulating the function "To_Char()" by changing the values of the environment variables NLS_Date_Format or NLS_Numeric_Characters. When the letter “d” is concatenated with the string, the built-in function To_Char is called by SQL by default. Now this To_Char function converts the “d” into the format specified by NLS_Date_Format whenever it encounters “d” concatenated to the string. What the attacker does is change the values of the environment variable NLS_Date_Format to what he intends to, and this in turn helps in carrying out the lateral attack as the malicious value injected by the attacker in NLS_Date_Format is executed instead of the original variable “d”.

How to avoid SQL injection attacks

1. Developers should expose a database only via a PL/SQL API. User privileges should be carefully crafted so that the client has no direct access to tables and views.
2. Execute privileges should be granted only to users who are authorised to perform DDL and DML operations.
3. The program should be executed with invoker's right if there is no need of elevated privileges. Programs that are to be executed with invoker's rights must include the AUTHID CURRENT_USER clause to further avoid SQL injection.
4. Appropriately choose the privileges or rights such as AUTHID CURRENT_USER and AUTHID DEFINER.
5. Limit user inputs, like restrict users to specified web pages using the restricted language for input, not specifying VARCHAR2 parameter when the parameter will be used as a number, and using natural instead of number if you need only positive integers.
6. Developers should use SQL statement text which are compile-time-fixed.
7. If dynamic SQL is not needed, you should prefer static SQL because it reduces SQL injection vulnerability and its successful compilation creates schema object dependencies which helps in improving the overall performance.
8. All the input values should be validated before putting them under code to perform database transactions.

Mitigation strategies

1. Use bind arguments

Use SQL command parameters instead of directly passing the text value to input fields. This would eliminate attacks and will help in improving performance.

2. Avoid dynamic SQL with concatenated input

Try to avoid concatenated input as this attracts attackers and thus attacks.

3. Filter and sanitize input

Create query filters to only pass values which are intended ones, and filter out those which may cause or attract attacks. For example, the DBMS_ASSERT package contains a number of functions that can be used to sanitize user input and help in guarding against SQL injection in applications that use dynamic SQL built with concatenated input values.

4. Reduce the attack surface

Carry out a thorough analysis of the privileges granted to users versus the requirements. If found in excess, revoke those permissions and allow only intended ones.

Testing SQL injection

1. The SQL error messages should be concise and should not expose any metadata information such as column table, view names, etc.
2. Implement static and dynamic testing methodologies for code walkthroughs, inspection, peer reviews etc.
3. Provide random inputs in input fields which are connected to a database and observe the issues and error messages generated for the wrong inputs. (Fuzz testing.)
4. Perform static code analysis.
5. Test each input parameter individually and while performing this, leave all the other parameters unchanged with valid data as their arguments.