Using triggers to block malicious code: an example

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 (
  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:

  IF (NEW.username='admin') THEN
    SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`;
END $$

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:

  IF (NEW.username='admin' AND USER() != 'root@localhost') THEN
    SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`;
END $$

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.

tags: , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "Using triggers to block malicious code: an example"

  1. Roland Boumna wrote:


    Nice post.

    Two things though ...

    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 on the inside.

    The trigger solution is nice, but unfortunately, you (and me, and legions before is) have to bite it and accept the absence of a proper mechanism to raise a user-defined error. It may be paranoid, but what if somebody creates the table called `Cannot modify admin data!`?

    Anyway, my point is that you could have solved it with a VIEW too, and depending on taste this could be a more clean solution. For example:

    VIEW v_my_users AS
    SELECT * FROM my_users
    WHERE username != 'admin'
    OR CURRENT_USER() = 'root@localhost'

    this will not result in an error when the UPDATE is executed, so you must use the ROW_COUNT() function to check if the UPDATE succeeded or failed.

    kind regards,


  2. shlomi wrote:

    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 much sure some character should never be allowed in table names, even though they may actually be supported: take ".", for example. It's such an elementary delimiter (table_schema.table_name.column_name) that it would be a very bad practice, in my opinion, to include it in table names using back ticks.

    So what I'm saying is paranoia must have its limits, though mathematically speaking it may be substantiated.

    With regard to using a view such as the one you've recommended: my personal inclination is not to use updatable views (and I agree it's a matter of taste). In this particular case I find the following issues:
    - There must be an index-merge for this view to work well (because of the OR). I've yet to rely on the success of inde-merge. Insofar, I have found it to be sporadic, not much reliable.
    - The particular implementation I've encountered has used Hibernate (the popular java persistence framework). I'm pretty much certain it would be very difficult, if not impossible (though I never tried), to let Hibernate talk with this view when doing some operations, but use the actual table when proper SELECT ... JOIN queries are required.
    - The trigger solution avoids changing the application code. I'm not generally in favor of that, I usually prefer to keep program logic in the program, but in this case the module which handled the users registration was 3rd party, and rewriting it was more troublesome.


  3. Roland Boumna wrote:


    "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... (

    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 data returned by USER() matches a real account. in the mysql.user table.

    All good points. My main point is that enforcing a constraint like this without resorting to a hack is possible. Apparently it would not have been the ideal choice in your particular case.



  4. shlomi wrote:

    Hi Roland,

    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.


  5. Matthew wrote:

    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 to access a particular table - I've not come across the idea, but my general use of views is for reading data.

  6. shlomi wrote:


    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.

  7. » Blog Archive » Triggers Use Case Compilation, Part II wrote:

    [...] 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, [...]

  8. Phil wrote:

    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.

Leave Your Comment

Powered by Wordpress and MySQL. Theme by