CRASH COURSE FOR FINDING SQL INJECTION IN WEBAPPS:PART 3

12 min readJan 13, 2021

BYPASSING THE FILTERS

In some situations, an application that is vulnerable to SQL injection may implement various input filters that prevent you from exploiting the flaw without restrictions. For example, the application may remove or sanitize certain characters or may block common SQL keywords. Filters of this kind are often vulnerable to bypasses, so you should try numerous tricks in this situation.

The single quotation mark is not required if you are injecting into a numeric data field or column name. If you need to introduce a string into your attack payload, you can do this without needing quotes. You can use
various string functions to dynamically construct a string using the ASCII
codes for individual characters. For example, the following two queries
for Oracle and MS-SQL, respectively, are the equivalent of select ename,
sal from emp where ename=’marcus’ :
SELECT ename, sal FROM emp where ename=CHR(109)||CHR(97)||
CHR(114)||CHR(99)||CHR(117)||CHR(115)
SELECT ename, sal FROM emp WHERE ename=CHAR(109)+CHAR(97)
+CHAR(114)+CHAR(99)+CHAR(117)+CHAR(115)
If the comment symbol is blocked, you can often craft your injected data
such that it does not break the syntax of the surrounding query, even
without using this. For example, instead of injecting:
`or 1=1- -
you can inject:
‘ or ‘a’=’a
When attempting to inject batched queries into an MS-SQL database,
you do not need to use the semicolon separator. Provided that you fi x
the syntax of all queries in the batch, the query parser will interpret them
correctly, whether or not you include a semicolon.
Some input validation routines employ a simple blacklist and either block or remove any supplied data that appears on this list. In this instance, you should try the standard attacks, looking for common defects in validation and canonicalization mechanisms. For example, if the SELECT
keyword is being blocked or removed, you can try the following bypasses:
SeLeCt
%00SELECT
SELSELECTECT
%53%45%4c%45%43%54
%2553%2545%254c%2545%2543%2554

Examining the database in SQL injection attacks

Querying the database type and version:

Different databases provide different ways of querying their version. You often need to try out different queries to find one that works, allowing you to determine both the type and version of the database software.

Here the cheatsheet

Microsoft and MySQL:SELECT @@version

Oracle: SELECT * FROM v$version

PostgreSQL: SELECT version()

Again we will use union queries to retrieve data, I will show you how we can determine the type of database using above cheatsheet

we will be going to deal with an oracle database

On Oracle databases, every SELECT statement must specify a table to select FROM. If your UNION SELECT attack does not query from a table, you will still need to include the FROM keyword followed by a valid table name.

There is a built-in table on Oracle called DUAL which you can use for this purpose. For example: UNION SELECT 'abc' FROM DUAL

Here our web application

Now we will be attacking filter functionality with sql injection

Now since we will be using union based attack first approach is as you know to determine the number of columns using null technique.

Now here there is a catch we can’t use query just only like 'union select null,null-- because in Oracle every select query must be from a table so we will use built-in table called dual so our query becomes

'union select null,null from dual-- (Now I have not shown step by ste adding null characters to know no of columns because I already have briefly explained it in PART 2 of the series).

Here is the result of our query

since we got no error it means that there are two columns in the table.lets check which column support string data lets check our first column

'union select 'a',null from dual--

no error proves first column supports string data lets check our second column 'union select null,'a' from dual--

Again no error implies second column also supports string data now we can use any one of the column to get the version number of ORACLE database.

Now version string of ORCALE database stored in the built-in table called v$version so we will extract version string it from this table.It’s column name is also built-in its default name across all oracle databases is BANNER.so we can use query like these

'union select BANNER,null from v$version--

'union select null,BANNER from v$version--

We can see that we were able to extract version of the ORACLE DATABASE.

Now during a Bug bounty hunt if you find a sqli then after you determined the number of columns (techniques for detecting number of columns is irrespective of any database it is same for all but with slight modification for oracle as you saw we need to use a dual table with select statement) try to extract version string like just above if database returns the version number then database is ORACLE otherwise it will give you error which means database is not oracle so you can continue to look for other databases.By the way just even when trying to find columns using null methods you will come to know the database is ORACLE as you need to use dual table when enumerating no of columns if database is ORACLE.

Now let me show you what approaches we need to take if database is not ORACLE

Now here our web application

Now for MySQL and MSSQL the version extraction technique is same so this definetely reduces our load as we don’t need to test them separately

Now lets first determine the number of columns in table.

'union select null,null--+

It returns no error it means table has two columns now lets check which column support string data 'union select 'a',null--+

So first column supports string data lets check for the second 'union select null,'a'--+

Second column also supports string data so now we can extract database version string using built in variable @@version

we can use either of the two queries

'union select @@version,null--+

'union select null,@@version--+

So we are succesful in determining the version number of the database.

So if you find out that during a bug bounty hunt that database is not oracle then try the above techniques for MYSQL and MSQL if yuo are successful in finding version number of database then the database is either MYSQL or MSQL.

Listing the contents of the database

Most database types (with the notable exception of Oracle) have a set of views called the information schema which provide information about the database.

You can query information_schema.tables to list the tables in the database:

SELECT * FROM information_schema.tables

This returns output like the following:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ===================================================== MyDatabase dbo Products BASE TABLE MyDatabase dbo Users BASE TABLE MyDatabase dbo Feedback BASE TABLE

This output indicates that there are three tables, called Products, Users, and Feedback.

You can then query information_schema.columns to list the columns in individual tables:

SELECT * FROM information_schema.columns WHERE table_name = 'Users'

This returns output like the following:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE ================================================================= MyDatabase dbo Users UserId int MyDatabase dbo Users Username varchar MyDatabase dbo Users Password varchar

This output shows the columns in the specified table and the data type of each column.

Now till now all of our excursions have been whitebox tests but now we will se e how we will approach a graybox scenario in which we all we know that database is not ORACLE.

We will attack webapp to get administrator credentials we don’t know the database name or names of tables inside it.

Here our webapp

Now we will put sqli payloads in filter functionality lets first determine the number of columns

'union select null,null--

Now lets check which column supports string data

'union select 'a',null--

first column supports string data lets check the second

'union select null,'a'--

second column also supports string data okay now we have two possibilites either the database is of type ,MySQl/MSSQL or postgresql.

We will first try for MySQL because of its popularity

We don’t know the table names also not column names too but we can use built-in variables to get the table and column name

As we have seen in case of ORACLE there is a built-in table called DUAL there is also a built-in table in MySQL called information_schema

we can get table name using variable table_name from information_schema tables

To do this query will be

'union select table_name,null from information_schema.tables--

We got hug amount of tables,the information_schema table stores all tables in the database but to gain administrative privileges we only need to know that table table which contains credentials.

Now here we need to be a detective looking at large amount of data to figure out his next investigation he don’t need all,a one particular find can move us ahead.

Now lets think like a developer every developer irrespective of how much he may be frustrated during development table containing credentials either must contain names like ‘creds’,’users’,’user_creds’ etc.

so lets search for user keyword in the haystack of data.

We have total 16 results okay this narrowed down very well lets see each

not looks promising

looks promising

not looks promising

both don’t look promising at all

not looks promising

looks promising

not looks promising at all

not looks promising

not looks promsing

Now only two out of 16 looks promising pg_users and user_wulktan

lets firs check pg_users

'union select column_name,null from information_schema.columns where table_name='pg-users'--

This is what we got in reply

Now lets check the second one that is user_wulktan

'union select column_name,null from information_schema.columns where table_name='user_wulktan'--

this is what we got

Looking at both the results of two different tables lets first check user_wulktan

so to extract query will be

'union select username_fdyygt,password_Iytzir from user_wulktan--

the reply is

we got administrator password lets login to administrator account

On Oracle, you can obtain the same information with slightly different queries.

You can list tables by querying all_tables:

SELECT * FROM all_tables

And you can list columns by querying all_tab_columns:

SELECT * FROM all_tab_columns WHERE table_name = 'USERS'

Now I’m attaching all the enviroment varibales (built-in variables)list in various databases.

MySQL :: MySQL 8.0 Reference Manual :: 4.9 Environment Variables

This section lists environment variables that are used directly or indirectly by MySQL. Most of these can also be found…

dev.mysql.com

Oracle Environment Variables

This appendix describes variables that can be specified in the ORAENV file or the POSIX shell. Oracle parameters, such…

docs.oracle.com

Environment Variables

This documentation is for an unsupported version of PostgreSQL. You may want to view the same page for the current…

www.postgresql.org

Configure environment variables for SQL Server on Linux - SQL Server

Applies to: SQL Server (all supported versions) - Linux You can use several different environment variables to…

docs.microsoft.com

Lets look at our web app

lets check its filtering functionality try to perform a union sqli.Since We are attacking an Oracle database we need a table name to query from.Lets use inbuilt DUAL table.

union select 'a','b' from dual--