Security: SQL Injection

SQL Injection flaws are introduced when dynamic database queries utilize user supplied input in some form.

Most of the current web applications follow an MVC architecture where the Model typically represents a relational database. The flaws within this design are easily exploitable and could cause data loss, privacy and potential financial loss.


SQL injection is a code injection technique that exploits a security vulnerability in a website's software. Code Injection is the general name for a lot of types of attacks which depend on inserting code, which is interprated by the application. 


Simple Example:

Let's say we have the below query to retrieve the users from accounts table:
select name, salary from accounts where userid='inputdata' and accessid='123';

Now if the inputdata is sent as is without an validation, a malicious user could send the value as
:
someuser' OR 1=1 --

This would generate the below query:
select name, salary from accounts where userid='someuser' OR 1=1 -- and accessid='123';

This effectively subverts all our checks and the query would end up returning the names and salaries of all the users in the accounts table. Definitely not ideal !

• For Select statements, the injection is usually after the where clause but could be used at ORDER by or names of tables and columns.

• For INSERT statement, since the parameters values and types are unknown, we can keep adding fields to the VALUES clause until it works:
• INSERT INTO users (username, password, ID, privs) VALUES ('ddd','pwd', 1111, 1)
• Example:
• junk')--
• junk', 1)--
• junk', 1, 1)--
• junk', 1, 1, 1)--

• An integer is implicitly casted to a string and 2000 to a date so we can use these at each position.
• It's possible that a field in the Select clause that is vulnerable to SQL injection is also used in subsequent UPDATE statements and this could cause problems downstream. So any SQL Injection verification should keep that in mind.
• UPDATE accounts SET password='newpwd' WHERE user = 'me' and password= 'pwd'
• As described for select statements, update and DELETE statements are equally vulnerable to SQL Injection.

Quick steps to verify if an application is vulnerable:

• Verify if the app is interacting with a backend DB by submitting "%" in a search parameter to see if additional results are being returned.
• Try with single and two single quotes in user supplied data to see how the application responds. Single quote normally results in a a SQL error and you can study the error message.  two single quotes are treated by the DB as a literal single quote so the error might disappear and indicate that the DB processed this as valid input. This might signal SQL Injection vulnerability.
• While testing for SQl Injection vulnerability(especially without using an intercepting proxy), we may need to encode the params to pass them to the DB layer.
• If you fail to encode problem characters correctly, you may inval-idate the entire request or submit data you did not intend to.
• User input values that are used in table or column names or ORDER by clauses don't need a single quote.
• If SQL injection vulnerability occurs in a SELECT statement, you can often employ the UNION operator to perform a second query, and combine the results with those of the first. 
• But the two result sets must have the same structure. i.e. they must contain the same number of columns, which have the same or compatible data types, appearing in the same order.
• A persistent attacker can first figure out the number of columns by using "SELECT NULL" and then try different data types.
• Database metadata can be used to further gather information about the tables and columns:
• information_schema table can be used for MS-SQL, MySQL etc
• all_tab_columns can be used for Oracle.
• It is possible to bypass filters at the application level by using ASCII codes for characters ( if single quote is being blocked). If comment is blocked then we can construct in a way to avoid breaking the syntax.
• It is also possible to bypass blacklist based validations by using null charater etc.
•  You can check if t
• 
  
• 
  

Fingerprinting the Database: The below shows how the string services could be constructed on the common types of database:

• Oracle: ‘serv’?‘ices’  ( send '||'London as a String parameter and the result is same)
• MS-SQL: ‘serv’+‘ices’ ( send '+'London as a String parameter and the result is same)
• MySQL: ‘serv’ ‘ices’ (note the space)

Second-Order SQL Injection<TODO>

Many applications handle data safely when it is first inserted into the database. Once data is stored in the database, it may later be processed in unsafe ways, either by the application itself or by other back-end processes. 

Advanced Exploitation<TODO>

attacker could turn off an MS-SQL database with the shutdown command:

' shutdown --

Beyond SQL Injection: Escalating the Database Attack

MS-SQL

Perhaps the most notorious piece of database functionality that an attacker can misuse is the xp_cmdshell stored procedure, which is built into MS-SQL by default. This stored procedure allows users with DBA permissions to execute operating system commands in the same way as the cmd.exe command prompt. For example:master..xp_cmdshell 'ipconfig > foo.txt'

The opportunity for an attacker to misuse this functionality is huge. He can perform arbitrary commands, pipe the results to local files, and read them back. He can open out-of-band network connections back to himself and create a backdoor command and communications channel. Because MS-SQL runs by default as Localsystem, the attacker typically can fully compromise the underlying operating system, performing arbitrary actions. MS-SQL contains a wealth of other extended stored procedures, such as xp_regread and xp_regwrite, that can be used to perform powerful actions within the registry of the Windows operating system.

<TODO>

Preventing SQL Injection

Despite the complex and dangerous attacks that can be mounted by SQL injection, it is in general one of the easier vulnerabilities to prevent. 

But merely, escaping single quotation marks by doubling them will not prevent SQL Injection. It fails in the below two situations:

• If numeric user-supplied data is being embedded into SQL queries, this is not usually encapsulated within single quotation marks. Hence, an attacker can break out of the data context and begin entering arbitrary SQL without the need to supply a single quotation mark.
• In second-order SQL injection attacks, data that has been safely escaped when initially inserted into the database is subsequently read from the database and then passed back to it again. Quotation marks that were doubled initially return to their original form when the data is reused.

Escaping Dynamic Queries

• One can use escaping routines for specific database:
• ESAPI.encoder().encodeForSQL( new OracleCodec(), queryparam );
• encodeForSQL
• Encode input for use in a SQL query, according to the selected codec (appropriate codecs include the MySQLCodec and OracleCodec). This method is not recommended. The use of the PreparedStatement interface is the preferred approach. However, if for some reason this is impossible, then this method is provided as a weaker alternative. The best approach is to make sure any single-quotes are double-quoted. Another possible approach is to use the {escape} syntax described in the JDBC specification in section 1.5.6. However, this syntax does not work with all drivers, and requires modification of all queries.

While stored procedure can have security and performance benefits, it doesn't prevent SQL Injection in all cases. Consider:

• A SP invoked in an unsafe way using user-supplied input.
• exec sp_createUser 'johm', 'pwd'
• This statement may be just as vulnerable as a simple INSERT statement. For example, an attacker may supply the following password:
• abc'; exec some_malicious --
• which causes the application to perform the batch query

How about parameterized Queries?

In the case of Parameterized Queries, the query structure has already been defined, the relevant API handles any type of placeholder data in a safe manner, so it is always interpreted as data rather than part of the statement's structure.

A few things to keep in mind though:

• Be consistent and use PQs all throughout the application.
• Don't use any parameter concatenation to construct a PQ. 
• (Rare but possible). Some applications accept parameters for table and column names. Ensure white list validation is done, along with length and whitespace restrictions and also allowing only alphanumeric characters.
• Parameter placeholders cannot be used for any other parts of the query, such as the ASC or DESC keywords that appear within an ORDER BY clause, or any other SQL keyword, since these form part of the query structure. 

Defense in Depth

• lowest possible level of privileges when accessing the database. An application can even use multiple accounts i.e. one for read only and one for read write. This mitigates risks that a sql injection flaws.
• Unnecessary database functions should be removed/disabled. A skilled and determined attacker may be able to recreate some required functions but this is not usually straightforward,
• All vendor-issued security patches should be applied in a timely way (They should be tested first).

TIPS

• Use SET DEFINE OFF or SET SCAN OFF to ensure that automatic character replacement is turned off. 
• If this character replacement is turned on, the & character will be treated like a SQLPlus variable prefix that could allow an attacker to retrieve private data.
• But It will also disable variables (  & is used for variables as well). Best option is to:
• You need SET DEFINE ON to make variables work
• And SET ESCAPE ON to escape uses of &.

Questions/Answers

1.You are trying to exploit a SQL injection flaw by performing a UNION attack to retrieve data. You do not know how many columns the original query returns. How can you find this out?

2.You have located a SQL injection vulnerability in a string parameter. You believe the database is either MS-SQL or Oracle, but you can't retrieve any data or an error message to confirm which database is running. How can you find this out?

3.You have submitted a single quotation mark at numerous locations throughout the application. From the resulting error messages you have diagnosed several potential SQL injection flaws. Which one of the following would be the safest location to test whether more crafted input has an effect on the application's processing?

Registering a new user

Updating your personal details

Unsubscribing from the service

4.You have found a SQL injection vulnerability in a login function, and you try to use the input ‘ or 1=1— to bypass the login. Your attack fails, and the resulting error message indicates that the -- characters are being stripped by the application's input filters. How could you circumvent this problem?

5.You have found a SQL injection vulnerability but have been unable to carry out any useful attacks, because the application rejects any input containing whitespace. How can you work around this restriction?

6.The application is doubling up all single quotation marks within user input before these are incorporated into SQL queries. You have found a SQL injection vulnerability in a numeric field, but you need to use a string value in one of your attack payloads. How can you place a string in your query without using any quotation marks?

7.In some rare situations, applications construct dynamic SQL queries from user-supplied input in a way that cannot be made safe using parameterized queries. When does this occur?

8.You have escalated privileges within an application such that you now have full administrative access. You discover a SQL injection vulnerability within a user administration function. How can you leverage this vulnerability to further advance your attack?

9.You are attacking an application that holds no sensitive data and contains no authentication or access control mechanisms. In this situation, how should you rank the significance of the following vulnerabilities?

SQL injection

XPath injection

OS command injection

You are probing an application function that enables you to search personnel details. You suspect that the function is accessing either a database or an Active Directory back end. How could you try to determine which of these is the case?

======================================================================================

1.You can determine the number of columns in two easy ways. First, you can SELECT the type-neutral value NULL from each column, increasing the number of columns until the application returns data, indicating that the correct number of columns were specified, for example:

' UNION SELECT NULL--

' UNION SELECT NULL, NULL--

' UNION SELECT NULL, NULL, NULL--

Note that on Oracle you will need to add FROM DUAL after the final NULL in each case.

Second, you can inject ORDER BY clauses and increment the specified column until an error occurs, indicating that an invalid column was requested:

' ORDER BY 1--

' ORDER BY 2--

' ORDER BY 3-- 

2.An easy way to confirm the database type is to use database-specific string concatenation syntax to construct some benign input within the query you control. For example, if the original value of the parameter is London you can submit the following items in turn:

'||'London

'+'London

If the first results in the same behavior as the original, the database is probably Oracle. If the second results in the same behavior, the database is probably MS-SQL. 

3.While it may seem counterintuitive, the user registration function is probably the safest. Registration functions normally use INSERT statements, which are unlikely to affect other records if you modify them. A function to update personal records is probably using conditional UPDATE statements. If you inject a payload like ' or 1=1-- you may cause all records in the table to be modified. Similarly, the function to unsubscribe is probably using conditional DELETE statements, and could impact on other users if you are not careful.

That said, it is impossible to be completely certain in advance which statements are being carried out by any kind of functionality, and you should advise the application owner of the risks before you perform the test. 

4.An easy way to achieve the same effect without using comment characters is with the input ' or 'a'='a. 

5.You can SQL comment characters to separate keywords and other items in your injected payloads, for example:

'/**/UNION/**/SELECT/**/username,password/**/FROM/**/users-- 

6.You can use the CHAR command to return a string value from a numeric ASCII character code. For example, on Oracle the string FOO can be represented as:

CHAR(70)||CHAR(79)||CHAR(79) 

7.This situation arises where user-supplied input is being placed into other elements of a query, such as table and column names, rather than the query’s parameters. A parameterized query cannot be precompiled with placeholders for these items, so a different solution needs to be used, probably based on very stringent input validation. 

8.Because you already have administrative access, it is likely that you can retrieve any data you desire using the application itself, meaning that a SQL injection attack to retrieve the application’s own data may be redundant. However, you can still leverage the attack to access any data relating to other applications that is held within the same database, or to escalate privileges within the database or the underlying operating system, to compromise the database server and extend your attack into the wider internal network. 

9.XPath injection can only be used to retrieve data from the targeted XML file. Hence, if the application contains no sensitive data this is likely to be a low impact issue. Similarly, SQL injection flaws may not enable you to extract any sensitive data from the database. However, they can sometimes be leveraged to escalate privileges within the database and develop your attack in other ways. Depending on the situation, SQL injection may be a more significant vulnerability. OS command injection, on the other hand, is almost always a high impact vulnerability, because it usually enables you to directly compromise the underlying server and use it as the launch point for further attacks against internal systems. 

10.If the function is accessing a database, then submitting the SQL wildcard % as the search query is likely to return a large number of records. Similarly, if the function is accessing an Active Directory, then submitting the wildcard * is likely to return a large number of records. Neither wildcard should have the same effect on the other system.