Skip to content

# Using triggers to block malicious code: an example

MySQL Security SQL Triggers January 1, 2009

Web applications face constant exploitation attempts. Those with a user base must keep their users’ private data, well… private.

While the MySQL security model allows restricting users access to databases, tables and even columns, it has no built in feature for restricting the rows access within the given table.

One cannot allow a user to only update rows 0 through 99, but restrict that user from updating rows 100 to 199. Such restrictions are usually managed in the application level, by adding a necessary “… AND filtering_column = some_value…”

Many web application have the notion of an ‘admin’ account, or several such accounts, which provide greater control over the application. The ‘admin’ account is one account to which many attacks are targeted. One such attack is an attempt to modify the admin’s password, such that the attacker can later log in with and access restricted data.

Assume the following table:

>     CREATE TABLE my_users (
>       ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
>       username VARCHAR(32) CHARSET ascii NOT NULL,
>       password VARCHAR(32) CHARSET ascii NOT NULL COLLATE ascii_bin,
>       UNIQUE KEY(username)
>     );

Let us also assume we are somewhat careful, so that the passwords are not plaintext, but rather encoded with MD5.

>     INSERT INTO my_users (username, password) VALUES
>       ('admin', MD5('qwerty')) ; -- Safe password as can be found!
>     INSERT INTO my_users (username, password) VALUES
>       ('alice', MD5('123456')) ; -- Safer yet!

>     SELECT * FROM my_users;
>     +----+----------+----------------------------------+
>     | ID | username | password                         |
>     +----+----------+----------------------------------+
>     |  1 | admin    | d8578edf8458ce06fbc5bb76a58c5ca4 |
>     |  2 | alice    | e10adc3949ba59abbe56e057f20f883e |
>     +----+----------+----------------------------------+
>     2 rows in set (0.00 sec)

An attacker will try to set the password for the admin account using security holes in the web application. The web application may execute the following query:

>     UPDATE my_users SET password=MD5('att@cker!') WHERE username='admin';

The issued query is valid, and should generally be allowed. However, we may decide to block changes to the specific ‘admin’ row, in the following manner:

>     DELIMITER $$
>     DROP TRIGGER IF EXISTS my_users_bu $$
>     CREATE TRIGGER my_users_bu BEFORE UPDATE ON my_users
>     FOR EACH ROW
>     BEGIN
>       IF (NEW.username='admin') THEN
>         SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`;
>       END IF;
>     END $$
>     DELIMITER ;

Let’s try running again the query:

>     UPDATE my_users SET password=MD5('att@cker!') WHERE username='admin';

>     ERROR 1146 (42S02): Table 'world.Cannot modify admin data!' doesn't exist

The query fails, since the BEFORE UPDATE trigger fails.
We can tweak the trigger to only allow specific users to modify the row:

>     DELIMITER $$
>     DROP TRIGGER IF EXISTS my_users_bu $$
>     CREATE TRIGGER my_users_bu BEFORE UPDATE ON my_users
>     FOR EACH ROW
>     BEGIN
>       IF (NEW.username='admin' AND USER() != 'root@localhost') THEN
>         SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`;
>       END IF;
>     END $$
>     DELIMITER ;

This way it is possible for the root user to modify the password at will. We can further tweak the trigger to INSERT INTO some log table. The information we may wish to register is USER(), the CURRENT_TIMESTAMP(), old password and new password, and perhaps the CONNECTION_ID(). More data means more means to locate the security breach, and monitoring the log table allows for immediate response for such an attempt.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

8 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Roland Boumna

Hi! Nice post. Two things though … #1 There is an error in your code. The USER() function is probably *not* what you want in this case – you need the CURRENT_USER() function. The USER() function returns the credentials provided by the client, and this may not be identical to the credentials that were used to authenticate the user. Crazy perhaps, but that is how it is…Of course the risk of USER is not that big if you assume the attacker did not gain a direct connection to your server, but CURRENT_USER will actually also shield you better from attacks… Read more »

Author
shlomi

Hi Roland! 1. No, I’m afraid I was right. Indeed, my first attempt was with CURRENT_USER(). Guess what? It always gave out the trigger’s *definer*! I was surprised as well. Changing to USER() did in fact return the trigger’s invoker. This is actually a real solution implemented on production due to a hacking problem which constantly hacked the admin account. And the logs did actually help in finding out the source of error. 2. The lack of error raising is a given fact in MySQL’s stored programs. Until this is resolved, we must settle with some ugly patch. I’m pretty… Read more »

Guest
Roland Boumna

Hi! #1 “my first attempt was with CURRENT_USER(). Guess what? It always gave out the trigger’s *definer*! ” Ok – I stand corrected – you are right, the CURRENT_USER() function inside a trigger refers to the definer… (http://dev.mysql.com/doc/refman/5.1/en/account-activity-auditing.html) I must say – I am a bit shocked that it is not really possible to access the authenticated user in this particular case. It seems to me the USER() is next to worthless, and you must take special precautions if you want to use it to find out who is really connected. At the very least, you should check if the… Read more »

Author
shlomi

Hi Roland,

#2.
I agree – the VIEW is a solution which does not resolve to a hack. It would be interesting to see how well it performs in comparison with triggers.

Regards,
Shlomi

Guest
Matthew

Surely the other issue with using a VIEW in this case would be that a potential attacker is less likely to be considerate and attempt to modify through the VIEW (even if this is shown in the application, rather than direct access to the table), since if they’ve got access to be able to issue such a query, they almost certainly have sufficient access to show tables. By using a trigger, the attempt will still be caught however the attacker tries to change the table in question. Unless, of course, you can enforce a VIEW as being the only way… Read more »

Author
shlomi

Matthew,

The MySQL security model aloows for table-level and even column-level restriction. That is, it is possible to “hide” some tables from a user, allowing her to access relevant views.
This usually means a complicated privilege system: if you have many tables and many users, it may become difficult to maintain the privileges.

code.openark.org » Blog Archive » Triggers Use Case Compilation, Part II

[…] Using triggers to block malicious code: an example, I have shown how a trigger may block changes to sensitive data. A trigger is aware of the invoker, […]

Guest
Phil

It’d be really nice to see fine grained access controls in MySQL – so that you could set permissions for individual records in a table.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.