In-band Sql Injection
In-band SQL Injection is the most common and easy-to-exploit of SQL Injection attacks. In-band SQL Injection occurs when an attacker is able to use the same communication channel to both launch the attack and gather results.
Detection
This webapp takes the value of name parameter, query database for information about the name, and then displays the information on the page based on the value of name parameter. Let’s try to change the value of name parameter.
After changing the value to user, we got nothing on the page which means user did not exist in the database.
Since the webapp was using the value of name to query the database, We can predict the query being executed on the backend as something like this:-
SELECT id,name,age from [table_name] where name='[input]'.
Break
Let’s attempt to break the query by supplying root'. This will result in something like this SELECT id,name,age from [table_name] where name='root'' being executed on the backend, which will break the query.
As you can see, nothing like table was displayed on the page because our input broke the query.
Fix
Let see if we can fix it by supplying root';-- -, which will make the query to be something like SELECT id,name,age from [table_name] where name='root';-- -'. This will not break the query because whatever comes after -- is ignored by sql engine, therefore the rest -' will not be executed as part of the query.
As you can see we were able to fix the query.
IDentification
Number Of Columns
We can identify the number of columns using either order by or union select. This is important as it will allow us to use union to retrieve data from the database, we cannot use union without knowing the actual number of columns.
We will use order by to identify the number of columns. We will startwith number 1 i.e order by 1 and then increment the number i.e order by 2.,order by 3… until we find the number of columns.
our input will be something like root' order by 1;-- -, which will be executed as SELECT id,name,age from [table_name] where name='root' order by 1;-- -'.
As you can see, we were able to get user’s information with 1. which means one or more than one columns exist. Let’s keep incrementing the number until we find the right one.
Here, using 6, nothing was displayed on the page . This means we exceeded the number of columns exist in the table. So the immediate number before 6 is our number of columns, which is 5.
Columns that Hold Strings
We need to identify the columns that are of string datatype as most of the data that we need to retrieve are string i.e username,password,.... Finding column that holds string data will help us to retrieve most of the data we need.
Since null datatype is supported by any column, we will make use of it to identify our target columns i.e string. Let’s supply this root' union select null,null,null,null,null;-- -,
Now let’s replace the first null with a string 'yes' and see whether or not will be displayed on the page. Our input will be root' union select 'yes',null,null,null,null;-- -
Well, we got it displayed on the page, this means the column supports string,so we can use it to retrieve data from the database.
We can keep identifying the remaining columns by replacing the remaining null with string one by one.
Database and DBMS
We need to know the dbms in use i.e mysql,postgresql... since different dbms use different syntax, knowing the dbms will allow us to focus on a specific syntax. For example, mysql uses @@version , postgresql uses version(), oracle uses v$version .
Let’s check for mysql using database(), user(), @@version. If those commands are executed successfully, then our dbms is mysql.
Our commands executed successfully.
We also got the name of our database exercises, it will help us in exploitation.
Table
We need to know the table from which we want to retrieve the data, Since this is mysql, we can take advantage of information_schema.tables to get the available tables in our target database exercises. By using root' union select group_concat(table_name),null,null,null,null from information_schema.tables where table_schema='exercises';-- -, we will get the available tables in exercises database.
As you can see, we got users table.
Columns
We also need to know the columns of the table from which we want to retrieve data. We can use information_schema.columns to get the available columns in the table. By using root' union select group_concat(column_name),null,null,null,null from information_schema.tables where table_name='users';-- -, we will get the available columns in table users.
We got five columns id,name,age,groupid,passwd.
Exploitation
We now got enough information i.e dbms, database,columns, table, ... to proceed to retrieving data from the table. We will retrieve all data from name and passwd columns as they look more interesting to us. Our input will be root' union select group_concat(name, ':=>', passwd),null,null,null,null from users;-- -.
Mitigations
- Use of Prepared Statements (with Parameterized Queries).
- Enforcing Least Privilege
- Escaping All User Supplied Input