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.
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.
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 "
by changing the values of the environment variables
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
NLS_Date_Format whenever it encounters “d” concatenated to the string. What the attacker does is change the values of the environment
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
- 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.
- Execute privileges should be granted only to users who are authorised to perform DDL and DML operations.
- 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
AUTHID CURRENT_USER clause to further avoid SQL injection.
- Appropriately choose the privileges or rights such as
AUTHID CURRENT_USER and
- 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.
- Developers should use SQL statement text which are compile-time-fixed.
- 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.
- All the input values should be validated before putting them under code to perform database transactions.
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
- The SQL error messages should be concise and should not expose any metadata information such as column table, view names, etc.
- Implement static and dynamic testing methodologies for code walkthroughs, inspection, peer reviews etc.
- 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.)
- Perform static code analysis.
- Test each input parameter individually and while performing this, leave all the other parameters unchanged with valid data as their arguments.