SQL Security Settings Introduction

Security | Raiders

The recent SQL injection attack test intensified, many large websites and forums have been injected successively. These sites typically use a SQL Server database, which is why many people are beginning to suspect SQL Server security. In fact, SQL Server 2000 has passed the U.S. government's C2 level security certification-This is the industry can have the highest level of authentication, so the use of SQL Server is quite safe. Of course, there is still a gap between orcal, DB2 and so on, but the ease and breadth of SQL Server can still be the reason why we continue to use it. So how to make SQL Server settings for people to use the rest assured?

The first step must be to get the latest security patch on SQL Server, and now the patch is SP3. Download Address: http://www.microsoft.com/sql/downloads/2000/sp3.asp. If this step is not done well, then we do not have to go on the necessary.

The second step is to modify the default 1433 port and hide SQL Server. This prevents responses to broadcasts that attempt to enumerate existing SQL Server clients on the network. In addition, you will need to screen out 1433 ports in TCP/IP filtering to hide your SQL Server database as much as possible. Like this one but let the attack create SQL Server account, also can not immediately use Query Analyzer remote login for next attack. If you are constructing a malicious statement from a page such as a asp,php, you also need to look at the return value problem, which is better than the Direct Query Analyzer. So the first thing we have to do is to get someone injected and not let the attacker do the next thing. How to Modify: Enterprise Manager--> your database group--> properties--> General--> Network configuration--> TCP/IP--> properties, where your default port is modified and SQL Server is hidden.

Step three is an important step, and SQL injection is often generated in web code. As a system administrator or a database administrator, you can't always look at every piece of code. Even if you look at the code often, there is no guarantee of our negligence. Then what? We are going to start with the database role, so that the database user's permissions to the lowest point. The default permissions for SQL Server make people really very headache, the permissions are very high, small permissions and nothing can do, sysadmin and db_owner really let people love and hate. An attacker who confirmed the existence of a SQL injection vulnerability in a Web site must have a step in the way of testing the permissions of the SQL Server user of the site. Typically, select Is_srvrolemember (' sysadmin '), or select Is_member (' db_owner '), or user = 0 (to compare characters to numbers, SQL The server prompts for the error message, from which you can know some sensitive information, and so on to test the statement. method also, I dare not say more. One is afraid of the wrong, the other is afraid of the people in the league flat. At the moment, if the site's database users are using SA privileges, plus confirming the absolute path the Web is in, then it announces your site over. DB_Owner permissions are the same, if the absolute path is confirmed, then there is a 50% chance to give your machine on the web-style trojan, such as Haiyang. So here's a little bit of confirmation that we have to create our own permissions so that attackers can't find a place to lower their mouths. Here's a reference to an example in SQL Server online Help:

Create SQL Server database role
1.         Expand the server group, and then expand the server.
2.        Expand the Databases folder, and then expand the database in which you want to create a role.
3.       , right-click Roles, and then click the new Database Role command.
4.        Enter the name of the new role in the Name box.
5.        Click Add to add members to the standard roles list, and then click one or more users to add. (optional)
Only users in the selected database can be added to the role.

object permissions
The category of permissions that you need to call object permissions when processing data or executing procedures:
        SELECT, INSERT, UPDATE, and DELETE statement permissions, which can be applied to the entire table or view. The
        SELECT and UPDATE statement permissions, which can be applied selectively to individual columns in a table or view. The
        SELECT permission, which can be applied to user-defined functions. The
        INSERT and DELETE statement permissions, which affect the entire row, can be applied only to tables or views and not to individual columns. The
        EXECUTE statement permissions, which can affect stored procedures and functions.

Statement permissions
The activity involved in creating an item in a database or database, such as a table or stored procedure, requires another class of permissions called statement permissions. For example, if a user must be able to create a table in a database, the CREATE TABLE statement permission should be granted to that user. Statement permissions, such as CREATE database, apply to the statement itself, not to specific objects defined in the database.
Statement permissions are:
· BACKUP DATABASE
· BACKUP LOG
· CREATE DATABASE
· CREATE DEFAULT
· CREATE FUNCTION
· CREATE PROCEDURE
· CREATE rule
· CREATE TABLE
· CREATE VIEW

Implied permission
Implied permissions control activities that can be performed only by members of a predefined system role or by the owner of a database object. For example, members of the sysadmin fixed server role automatically inherit all permissions that are manipulated or viewed in a SQL Server installation.
Database object owners also have implied permissions to perform all activities on the objects they own. For example, a user who owns a table can view, add, or delete data, change the table definition, or control permissions that allow other users to manipulate the table.

DB_Owner has full permissions in the database.
Db_accessadmin can add or remove user IDs.
Db_securityadmin can manage all permissions, object ownership, roles, and role memberships.
Db_ddladmin can emit all DDL, but cannot issue GRANT, REVOKE, or DENY statements.
Db_backupoperator can emit DBCC, CHECKPOINT, and BACKUP statements.
Db_datareader can select all the data in any user table in the database.
Db_datawriter can change all data in any user table in the database.
Db_denydatareader cannot select any data in any of the user tables in the database.
Db_denydatawriter cannot change any data in any of the user tables in the database.

Here, configure the permissions for the newly created database role, such as which table, view, stored procedure, etc. you want to use. The db_owner and db_securityadmin and Db_backupoperator are then canceled, giving the attacker the opportunity to backup the database and create table, but the attacker has these two privileges, Then your site is still in a very dangerous state. Also note that when creating a database account, you must not choose the server role.

The fourth step is to modify the SQL Server built-in stored procedures. SQL Server is estimated to be installed or otherwise, and it has a number of dangerous stored procedures built into it. Can read the registry information, can write registry information, read disk sharing information and so on ... You see here, in the mind may be thinking, my site has other code, and not like Query Analyzer to check the results output. Give you this permission, can not how, still can't see the information. It would be a big mistake if you think so. Hint, if the attacker has permission to create TABLE, then creates a temporary table, and then inserts the information into the table, then select it, and then compare with the number, let SQL Server error, then the result is all out ... So we have to report a better way to kill, do not miss the attitude of repair.

First, list the dangerous built-in stored procedures:

xp_cmdshell
Xp_regaddmultistring
Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumkeys
Xp_regenumvalues
Xp_regread
Xp_regremovemultistring
Xp_regwrite

ActiveX Automatic scripting:

sp_OACreate
sp_OADestroy
sp_OAMethod
sp_OAGetProperty
sp_OASetProperty
sp_OAGetErrorInfo
sp_OAStop

All of the above are blocked in our list, such as xp_cmdshell shielding method: Sp_dropextendedproc ' xp_cmdshell ', if necessary, and then use Sp_addextendedproc ' xp_cmdshell ', ' Xpsql70.dll ' for recovery. If you don't know which DLL file xp_cmdshell is using, you can use Sp_helpextendedproc xp_cmdshell to see which dynamic join Library the xp_cmdshell is using. In addition, after shielding the xp_cmdshell, the next step is to rename the Xpsql70.dll file to prevent it from being recovered by an attacker who obtains the SA.

We do this, and your SQL Server is basically secure. But the information can still be the same leak. After all, select we can't cancel unless your site is using HTML. The prevention of SQL injection also requires the attention of our programmers, this is the root of the solution. We then proceed to the next analysis of SQL Server security in the Advanced Setup article. If there are any mistakes in this article, please forgive me. Thank you......

Another recommendation, SQL injection test Tools NBSI2, this is by the Alliance of Small and medium-sized bamboo development, the injection of SQL injection representative role, the other is the younger brother's Nbwebshell. These tools can be downloaded to the Federated web site

NB Union-jadesun (underwear) qq:280155
NB Website: www.54nb.com

SQL Injection Defense Method-Programmer article

Author: NB Union-small Bamboo

SQL injection is being exploited more and more to invade the site, some web programmers also began to pay attention to this knowledge, but because of the intrusion of a smattering of methods, resulting in the filter to omit some characters, resulting in security vulnerabilities, or panic, some legitimate user requests are shut out, imagine, When the user wants to enter an I ' m a boy, but give you a scolding, he will be willing to go to your site again?

Next, I introduce the defense method of SQL injection from the aspect of the program, first of all, look at the simplest SQL statements of these three sentences
1.sql= "Select * from Users where userid=" & Request ("ID")
2.sql= "SELECT * from Users where userid= '" & Request ("ID") & ""
3.sql= "Select * from Users where UserName like '%" & Request ("Name") & "%"

In the first sentence, the argument is a numeric type, which is obvious. The second sentence, if the field userid is int type, some people are not clear. In fact, the distinction between the number and character parameters, as long as the SQL statement parameters on either side of the single quote can be, it is obvious that the first sentence is not single quotes, is a numeric type; the second third sentence has single quotes, which are character types.

For numeric variables, incoming arguments are attached directly to the SQL statement, and because the parameters are numeric, it is safe to judge with IsNumeric, and I tried to disconnect the arguments with the same, but the result was a failure.

For character-type variables, the incoming arguments are constants, such as when you pass 1 and 1=1 in, the SQL statement is userid= ' 1 and 1=1 ', and the value in the single quote range is always a constant, and to break that range, the only character is the defined character: single quotes. So, the character variable just filters the ' number and it's completely safe, as for how to filter, it is best to replace a single quotation mark with two single quotes, because inside the SQL statement, the constant, in which the constants are expressed, can be replaced with two single quotes. In this way, both can keep the original appearance of user input, but also can guarantee the security of the program.

Here are two functions, you can copy the past direct call on the line.

'---------------------------------------------------------------
' NB Alliance anti-injection function reqnum/reqstr
'---------------------------------------------------------------
Function Reqnum (StrName)
Reqnum = Request (StrName)
If not IsNumeric (Reqnum) Then
Response.Write "parameter must be numeric!"
Response.End
End If
End Function

Function Reqstr (StrName)
Reqstr = Replace (Request (StrName), "'", "" "
End Function

With the above three-sentence SQL statement, describe the calling method:
1.sql= "Select * from Users where userid=" & Reqnum ("ID")
2.sql= "SELECT * from Users where userid= '" & Reqstr ("ID") & ""
3.sql= "Select * from Users where UserName like '%" & Reqstr ("Name") & "%"

To reiterate: The above method is absolutely safe for SQL Server libraries or access or other databases, but note that SQL Server's stored procedures are an exception, in which case the single quotes are replaced with four single quotes for security.

In addition, NB Union-underwear has written a SQL Server security settings article, when the program is flawed, the article can allow intruders or have the least number of permissions and data, the article has been published in Www.54NB.com, interested can go to see.