Web applications face constant exploitation attempts. Those with a user base must keep their users’ private data, well… private.<\/p>\n
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.<\/p>\n
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…”<\/p>\n
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.<\/p>\n
Assume the following table:<\/p>\n
\nCREATE TABLE <\/strong>my_users (\r\n ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY<\/strong>,\r\n username VARCHAR<\/strong>(32) CHARSET <\/strong>ascii NOT NULL<\/strong>,\r\n password VARCHAR<\/strong>(32) CHARSET <\/strong>ascii NOT NULL COLLATE<\/strong> ascii_bin,\r\n UNIQUE KEY<\/strong>(username)\r\n);<\/pre>\n<\/blockquote>\nLet us also assume we are somewhat careful, so that the passwords are not plaintext, but rather encoded with MD5.<\/p>\n
\nINSERT INTO<\/strong> my_users (username, password) VALUES<\/strong>\r\n ('admin', MD5('qwerty')) ; -- Safe password as can be found!<\/span>\r\nINSERT INTO<\/strong> my_users (username, password) VALUES<\/strong>\r\n ('alice', MD5('123456')) ; -- Safer yet!<\/span>\r\n\r\nSELECT <\/strong>* FROM <\/strong>my_users;\r\n+----+----------+----------------------------------+\r\n| ID | username | password |\r\n+----+----------+----------------------------------+\r\n| 1 | admin | d8578edf8458ce06fbc5bb76a58c5ca4 |\r\n| 2 | alice | e10adc3949ba59abbe56e057f20f883e |\r\n+----+----------+----------------------------------+\r\n2 rows in set (0.00 sec)<\/pre>\n<\/blockquote>\nAn 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:<\/p>\n
\nUPDATE <\/strong>my_users SET <\/strong>password=MD5('att@cker!') WHERE <\/strong>username='admin';<\/pre>\n<\/blockquote>\nThe 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:<\/p>\n
\nDELIMITER $$\r\nDROP TRIGGER IF EXISTS my_users_bu $$\r\nCREATE TRIGGER my_users_bu BEFORE UPDATE ON my_users\r\nFOR EACH ROW\r\nBEGIN\r\n IF (NEW.username='admin') THEN\r\n SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`;\r\n END IF;\r\nEND $$\r\nDELIMITER ;<\/code><\/pre>\n<\/blockquote>\nLet’s try running again the query:<\/p>\n
\nUPDATE <\/strong>my_users SET <\/strong>password=MD5('att@cker!') WHERE <\/strong>username='admin';\r\n\r\nERROR 1146 (42S02): Table 'world.Cannot modify admin data!' doesn't exist<\/span><\/pre>\n<\/blockquote>\nThe query fails, since the BEFORE UPDATE<\/strong> trigger fails.
\nWe can tweak the trigger to only allow specific users to modify the row:<\/p>\n\nDELIMITER $$\r\nDROP TRIGGER IF EXISTS my_users_bu $$\r\nCREATE TRIGGER my_users_bu BEFORE UPDATE ON my_users\r\nFOR EACH ROW\r\nBEGIN\r\n IF (NEW.username='admin' AND USER() != 'root@localhost') THEN\r\n SELECT 0 INTO @admin_error FROM `Cannot modify admin data!`;\r\n END IF;\r\nEND $$\r\nDELIMITER ;<\/code><\/pre>\n<\/blockquote>\nThis 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[16,21,30],"class_list":["post-145","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-security","tag-sql","tag-triggers"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-2l","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/145","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=145"}],"version-history":[{"count":15,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":465,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/145\/revisions\/465"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}