Click here to Skip to main content
Click here to Skip to main content

SQL Injection Knowhow

, 6 Jun 2011
Rate this:
Please Sign up or sign in to vote.
This article explains SQL injection attacks, mitigation strategies, and factors to consider while testing.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Arpit Dubey
Systems Engineer
United States United States
Awarded software professional with excellent acadmic record
Works as Systems Engineer in leading MNC and loves to write the tech blogs.

Comments and Discussions

 
QuestionQuestions Question Questions PinprofessionalPaulo Kunzel8-Oct-13 7:17 
GeneralMy vote of 5 PinprofessionalSrinivas Kalabarigi19-Jul-13 15:56 
GeneralMy vote of 5 PinmemberSridhar Patnayak12-Jun-13 2:17 
GeneralMy vote of 5 PinmemberMazen el Senih31-Jan-13 6:52 
QuestionNeed more Information on SQL injection PinmemberHARISHCHOWDHARY22-Aug-11 20:04 
GeneralMy vote of 4 PinmemberHARISHCHOWDHARY22-Aug-11 20:00 
GeneralMy vote of 5 Pinmemberjawed.ace15-Jul-11 2:17 
QuestionGreat Job! PinmemberYenchi21-Jun-11 23:02 
GeneralMy vote of 5 PinmemberYenchi21-Jun-11 23:01 
GeneralMy vote of 5 PinmemberAlanWalls12-Jun-11 0:45 
GeneralQuery regarding lateral injection PinmemberAshuMukharjee6-Jun-11 22:16 
GeneralRe: Query regarding lateral injection PinmemberArpit Dubey6-Jun-11 22:49 
Yes. When one uses SQL command parameters to bind the SQL statement it would not consider the comparision value for the specified variable as is and will use the binded parameter instead.
GeneralMy vote of 5 PinmemberAshuMukharjee6-Jun-11 22:14 
GeneralHmmm... PinmemberMarcus Kramer6-Jun-11 3:49 
GeneralRe: Hmmm... PinmemberArpit Dubey6-Jun-11 8:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 6 Jun 2011
Article Copyright 2011 by Arpit Dubey
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid