SQL injection attacks

SQL injection attacks
"SQL injection" is an attack method that uses unfiltered/unaudited user input ("cache overflow" is different from this method ), this means that the application should not run the SQL code. If the application creates SQL strings with no precaution and runs them, unexpected results may occur. This article is translated by zer0Black from SQL Injection Attacks by Example.
A customer asked us to perform penetration tests on the Intranet of the enterprise that only their employees and customers can use. This is part of the security evaluation. Although we have never used SQL injection to penetrate the network before, we are quite familiar with the concept. Finally, we achieved great success in this task. Now let's review every step of this process and record it as a case. We have recorded the twists and turns that have taken place after many wrong turns, and a more experienced person will have this difference-or even better. But in fact, after we succeed, we understand that we are not completely misled.
Other SQL articles include more details. However, this article not only shows the process of exploits, but also describes how vulnerabilities are discovered.
Currently, we are looking at a complete and customized website in the target internal network. We have never seen this website before and have no right to view its source code: this is a "black box" attack. The result of the 'spying 'shows that this server runs on Microsoft's IIS6 and is in the ASP. NET architecture. This implies that our database is Microsoft's SQL server: we believe that our skills can be applied to any web application, no matter which SQL server it uses.
On the login page, there is a traditional user-Password form, but there is an additional "mail my password to me" link. Later, this place was confirmed to be the key to the entire system collapse.
When you enter an email address, the system queries the email address in the user database and mails some content to the address. However, my email address cannot be found, so it will not send me anything.
For any SQL form, the first test is to input a data with single quotation marks to see if they have filtered the SQL string. After the single quotes are submitted as the mail address, we get Error 500 (server error), which means that the "harmful" input is actually directly used for SQL statements. That's it!
I guess the SQL code may be like this:

SELECT fieldlist    FROM table   WHERE field = '$EMAIL';  

$ EMAIL is the address submitted by the user from the form, and this query contains quotation marks on the string end $ EMAIL. We do not know the exact names of fields or tables, but we understand their nature, which helps us make correct guesses.
When we type steve@unixwiz.net '-note that the ending quotation marks-below is the composition of this SQL field:

SELECT fieldlist    FROM table   WHERE field = 'steve@unixwiz.net'';

When this SQL statement starts to be executed, the SQL parser will find extra quotation marks and interrupt execution, and give a syntax error prompt. How to clearly express this error to the user based on the internal error recovery procedures of the application, but generally there is no prompt "the email address does not exist ". The response to this error is the door to death. It tells other users that the input is not correctly handled, which leaves an opportunity for application cracking.
This data is displayed in the WHERE clause. Let's change the input in a way that complies with the SQL specification to see what will happen. Type anything 'OR 'X' = 'x, and the result is as follows:

SELECT fieldlist FROM table WHERE field = 'anything' OR 'x'='x';  

Because the application will not think about the Input-just construct the string-we use single quotes to convert the single composition of the WHERE clause into a double composition, and the 'X' = 'X' clause is always standing, no matter what the first clause is. (There is a better way to ensure "always true", which we will be exposed to later ).


However, unlike the "real" query that only returns a single data at a time, the preceding structure must return all data of this Member database. To know what the application will do in this case, the only way is to try again. We get this:
Your logon information has been mailed to random.person@example.com.
We guess this address is the first record found. This guy will really receive the password he forgot in this mailbox, and he will be surprised and alert him.
We now know that we can modify the query statement based on our own needs. Although we do not know enough about the invisible parts, we have noticed three different responses after multiple attempts:
"Your login information has been mailed to the mailbox" "we cannot identify your email address" Server Error

The first two responses are valid SQL statements, and the last response is invalid SQL statements: this difference is useful when querying the statement structure.

Mode Field ing

The first step is to guess the field name: We reasonably speculate that the query contains "email address" and "password ", there may also be fields such as "US Mail address", "userid", or "phone number. We especially want to execute the show table statement, but we do not know the TABLE name. There is no obvious way to get the TABLE name.

We proceed to the next step. In each test, we add some special construction statements with the known parts. We already know that the execution result of this SQL statement is an email address comparison, so let's guess the field name of the email:

SELECT fieldlist FROM table WHERE field = 'x' AND email IS NULL; --';

The purpose is to assume the field name (email) of the query statement to test whether the SQL statement is valid. I don't care what the matching email address is (we use a pseudo name 'X'), '--' indicates the start point of the SQL comment. It is a very effective way to remove the quotation marks provided at the end of the application. We don't have to care what we block.

If we get a server error, it means that the SQL statement is inappropriate and the syntax error is thrown: it is more likely that the field name is incorrect. If we get any effective response, we can guess that the field name is correct. This is the process in which we get the "email unknown" or "password was sent" response.

We can also use the AND connector to replace OR: This is meaningful. In the SQL mode ing stage, we do not need to worry about guessing a specific email address, we also don't want to use the app to randomly send "this is your password" emails to users-this is not very good and may cause suspicion. Using AND to connect to the email address will become invalid. we can ensure that the query statement always returns 0 rows AND will never generate a password reminder email.

The above part is indeed submitted to the response of "unknown email address". Now we know that the email address is indeed stored in the email field name. If it does not take effect, we can try field names such as email_address or mail. This process requires a lot of speculation.

Next, let's guess other obvious names: password, user ID, name, and so on. Only one field can be guessed at a time. As long as the response is not "server failure", it means we have guessed it.

SELECT fieldlist FROM table WHERE email = 'x' AND userid IS NULL; --';

In this process, we found several correct field names:

Email
Passwd
Login_id
Full_name

There is certainly more (there is a clue in the form field name), and no more is found after a while of mining. However, we still don't know the table names of these field names. Where did they find them?

 

Search for database table names

The application's built-in query command has already created a table name, but we don't know what it is: There are several ways to find the table name. One of them relies on subselect (word query ).

An independent Query

SELECT COUNT(*) FROM tabname

Returns the number of records in the table. If the table name is invalid, the query fails. We can create our own string to test the table name:

SELECT email, passwd, login_id, full_name    FROM table   WHERE email = 'x' AND 1=(SELECT COUNT(*) FROM tabname); --'; 

We don't care how many records there are, but whether the table name is correct. After repeated guesses, we finally found that members is the valid table name in the database. But is it used in this query? So we need another test, using table. field: the actual query part only works in this table, instead of executing as long as the table exists.

SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x'                      AND members.email IS NULL; --';  

When "Email unknown" is returned, it means that our SQL injection is successful, and we have guessed the table name correctly. This is very important for subsequent work, but we will try other methods for the moment.

Find User Account

We have a local concept for the structure of the members table, but we only know a user name: any user may get an email with "Here is your password. In retrospect, we never get the information itself, but only the address it sends. We have to get a few more user names to get more data.

First, we start from the company website to find a few people: "About us" or "Contact" Page usually provides a list of company members. Generally, email addresses are included. Even if they do not provide this list, we can use our tools to find them based on some clues.

The LIKE clause can be used for user query, allowing us to locally match the user name or email address in the database. If the message "We sent your password" is displayed and the email is sent, it proves that it takes effect.

Warning: the email address is obtained, but the email is sent to the recipient, which may cause suspicion and be used with caution.

We can query the email name or full name (or other speculative information). Each time we put the % wildcard, we will perform the following query:

SELECT email, passwd, login_id, full_name    FROM members   WHERE email = 'x' OR full_name LIKE '%Bob%';  

Remember that although there may be more than one "Bob", we can only see one message: we recommend refining the LIKE clause.

Brute force password cracking

We can be certain that we can perform brute force password cracking on the login page, but many systems have implemented monitoring or even defense against it. Some methods may include operation logs, account locking, or other ways that can impede our actions. However, because of unfiltered input, we can bypass more protection measures.

The constructed string contains the mailbox name and password to test the password. In our example, we used the victim bob@example.com and tried multiple sets of passwords.

SELECT email, passwd, login_id, full_name FROM members WHERE email = '<a                          href="mailto:bob@example.com">bob@example.com</a>' AND passwd                          = 'hello123';  

This is a very good SQL statement. We won't get a server error prompt. As long as we get the message "your password has been mailed to you", we will prove that we have obtained the password. At this time, the victim may be alert, but who cares about him? We have a password.

This process can be automatically completed using the perl script. However, during the script writing process, we found another method to crack the system. The database is not read-only.

So far, we have not done anything outside the database query. Although SELECT is read-only, it does not mean that SQL can only do this. SQL uses semicolons to indicate the end. If the input is not properly filtered, there is nothing to prevent us from constructing query-independent commands after the string.

The most drastic example is:

This is the most popular medicine:

SELECT email, passwd, login_id, full_name    FROM members   WHERE email = 'x'; DROP TABLE members; --';  -- Boom! 

In the first part, we have prepared a forged email address -- 'X' -- we don't care about what the query result returns: All we want is our own SQL command. This attack deleted the entire members table, which is not fun.

This indicates that we can not only split SQL commands, but also modify databases. This is allowed.

Add new user

We have learned about the local structure of the members table. adding a new record to the table is a feasible method: If this succeeds, we can simply log on to the system with the newly inserted identity.

Don't be surprised. This SQL statement is a little long. We show it as a branch for ease of understanding, but it is still a statement:

SELECT email, passwd, login_id, full_name    FROM members   WHERE email = 'x';          INSERT INTO members ('email','passwd','login_id','full_name')           VALUES ('steve@unixwiz.net','hello','steve','Steve Friedl');--'; 

Even if we get the correct field name and table name, we still need to know a few things before the attack is successful:

In web forms, we may not have enough space to type so much text (although it can be done using scripts, it is not easy ). The web application may not have the INSERT permission for the members table. There must be other fields in the and members tables, which may require initial values. Otherwise, the INSERT will fail. Even if we insert a new record, the application may not run properly, because the field names that we cannot provide will automatically insert NULL. A correct "member" may not only need a record in the members table, but also the information of other tables (for example, access permission). Therefore, it may not be enough to add only one table.

In this case, we encountered a problem #4 or #5, and we could not determine which one it was. Because a server error message was returned when we logged in with the constructed username. Although this implies that our unconstructed fields are required, we cannot properly handle them.

A feasible method is to guess other fields, but this is a labor-consuming process: although we can guess other "obvious" fields, it is too difficult to get the entire application's organizational structure.

We finally tried other methods.

Mail me the password

We realized that although we could not add new records to the members database, we could modify existing records, which proved to be feasible.

I learned from the previous step that the bob@example.com account in this system, we use SQL injection to change this record in the database to our own email address:

SELECT email, passwd, login_id, full_name FROM members WHERE email = 'x';                              UPDATE members SET email = <a href="mailto:'steve@unixwiz.net">'steve@unixwiz.net</a>'                              WHERE email = <a href="mailto:'bob@example.com">'bob@example.com</a>';  

After the operation, we naturally got the prompt "we didn't know your email address", but this was expected, after all, we used a fake email address. The UPDATE operation does not notify the application, so it is quietly executed.

 

After that, we used the "I lost my password" function and used the email address we just updated. One minute later, we received this email:

From: <a href="mailto:system@example.com">system@example.com</a>                                  To: <a href="mailto:steve@unixwiz.net">steve@unixwiz.net</a>                                  Subject: Intranet login This email is in response to your request for your                                  Intranet log in information. Your User ID is: bob Your password is: hello  

Now, we need to follow the standard login process to enter the system. This is a high-level employee with high-level permissions, which is better than our INSERT users.

We found that this enterprise's internal site contains a lot of content, and even contains a full list of users. We can reasonably launch many intranets with the same enterprise Windows network account, they may use the same password everywhere. We can easily obtain any Intranet password, and find an open pptp vpn port on the Enterprise Firewall, which makes the login test easier.

We have selected several other accounts and failed the test. We cannot know whether the password is "Incorrect" or "whether the internal enterprise account is different from the Windows Account name ". However, we feel that automated tools will make the job easier.

Other methods

In this specific penetration, we were given sufficient permissions. We didn't need more, but there were other methods. Let's try out the method we have come up with but is not common enough.

We realized that not all methods have nothing to do with the database. Let's try it.

Call xp_cmdshell

Microsoft SQLServer supports the Stored Procedure xp_cmdshell to execute arbitrary operating system commands. If this function is available to web users, penetration of webserver is unavoidable.

So far, what we have done has been limited to web applications and databases, but if we can execute any operating system commands, then powerful servers cannot help penetration. Xp_mongoshell is usually used by a very small number of administrator accounts, but it may also be authorized to lower-level users.

Draw database structure

After this login, it provides a variety of functional applications, and there is no need to perform further mining, but it may not be enough in other environments with more restrictions.

The ability to systematically draw visible database structures, including tables and their field structures may not be directly helpful. However, this provides a path for website penetration.

Collect more information about the data structure from other aspects of the website (for example, the "message board" Page? "Help forum", etc ?). However, this is highly dependent on the application environment, and you have to make accurate guesses.

Mitigate hazards

We believe that web application developers generally do not consider "harmful input", but security personnel should consider (including bad guys), so there are three methods available.

Input Filtering

Filtering input is very important to ensure that the input does not contain dangerous code, whether it is an SQL Server or HTM itself. The first thing that comes to mind is to remove "malicious characters", such as quotation marks, semicolons, or escape characters, but this is not a good way. Although it is easy to find some dangerous characters, it is hard to find them all.

The web language itself is full of special characters and strange markup (including replacement characters that express the same character), so it is unlikely to succeed in trying to identify all "malicious characters.

In other words, rather than "Removing known malicious data", it is better to remove "all data other than good data": this difference is very important. In our example, the email address can only contain the following characters:

 

abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ 0123456789 @.-_+

It is no good to allow incorrect Character Input. you should reject them early-there may be some error messages-not only blocking SQL injection, you can also capture some input errors rather than saving them to the database.

A special email address will cause the verification program to be in trouble, because each person has different definitions of "valid. The email address is denied because it contains a character you did not consider.

The real authority is RFC 2822 (more than RFC822), which defines the content that is allowed. This kind of more academic specification is expected to accept & and * (more) as valid email addresses, however, other people, including authors, are willing to use a reasonable subset to include more email addresses.

Those who adopt more restrictive methods should be fully aware of the consequences of not including these addresses, especially those with better restrictions (pre-compilation/execution, stored procedures) to avoid security issues caused by these "strange" characters.

Realizing "filter input" does not mean simply "Removing quotation marks", because even a "regular" character can cause trouble. In the following example, an integer ID value is used for comparison with the user's input (Digital PIN ):

SELECT fieldlist FROM table WHERE id = 23 OR 1=1; -- Boom! Always matches!

In practice, this method has many restrictions, because there are too few fields that can completely exclude all dangerous characters. The above method is valuable for "date", "email address", or "integer", but we will inevitably use other methods to mitigate dangers in the real environment.

Input encoding/escape

Now you can filter the phone number and email address, but you cannot process the "name" field in the same way. Otherwise, the name like Bill o'reilly may be excluded: for this field, the quotation marks are valid.

Some people think of adding a single quotation mark when filtering out single quotation marks, so that there is no problem-but this is a problem!

Pre-process each string to replace single quotes:

SELECT fieldlist    FROM customers   WHERE name = 'Bill O''Reilly';  -- works OK  

This method is prone to problems because most databases support the transcoding mechanism. For example, MySQL allows you to input 'to replace single quotes. Therefore, if you input'; drop table users;-and use two quotation marks to "Protect" the database, we will get:

SELECT fieldlist    FROM customers   WHERE name = '\''; DROP TABLE users; --';  -- Boom!  

''' Is a complete SQL statement (containing only one quotation mark). Generally, malicious SQL code is followed by it. It is not just a backslash Symbol: Like Unicode encoding, Other encoding or parsing rules will inadvertently open holes for programmers. Perfect filtering is very difficult, which is why many database excuses provide functions for you to use. After the same content is processed for "string quoting" and "string parsing", it will be better and safer.

For example, MySQL functions mysql_real_escape_string () and perl DBD's $ dbh-> quote ($ value) methods are required.

Parameter binding (pre-compiled Statement)

Although escaping is a useful mechanism, we are still in the loop of "user input is treated as an SQL statement. A better method is: Pre-compilation. In essence, all database programming interfaces support pre-compilation. Technically, the SQL statement is created with a question mark for each parameter placeholder-and then compiled in the internal table.

The pre-compiled query is executed according to the parameter list:

 

Examples in Perl

 

$sth = $dbh->prepare("SELECT email, userid FROM members WHERE email = ?;");    $sth->execute($email);  

Thanks to Stefan Wagner for helping me write a java implementation:

Uneasy full version

Statement s = connection.createStatement();  ResultSet rs = s.executeQuery("SELECT email FROM member WHERE name = "                               + formField); // *boom*

Security Edition

PreparedStatement ps = connection.prepareStatement(      "SELECT email FROM member WHERE name = ?");  ps.setString(1, formField);  ResultSet rs = ps.executeQuery(); 

$ Email is obtained from the user form and is passed as a parameter at #1 (where the first question mark is located). This SQL statement can be parsed under any circumstances. Quotation marks, semicolons, backslashes, and SQL command marks-no special effect is produced for any character, because they are "only data. This will not cause damage to other things. Therefore, this application largely defends against SQL injection attacks.

If the Pre-compiled query statement is executed multiple times (only compiled once), the performance will be improved. However, this is insignificant compared with a large number of security improvements. This may be the most important step for us to ensure web Application Security.

Restrict database permissions and isolate users

In this case, we observe that only two interactive actions are not in the context of the login user: "login" and "Send password to me ". The web application should impose restrictions on database connection permissions: members tables can only be read and other tables cannot be operated.

The role is that even a "successful" SQL injection attack can only achieve very limited success. Oh, we won't be able to make an authorized UPDATE request. We have to resort to other methods.

Once the web application determines that the authentication passed in the login form is valid, it will switch the session to a user with more permissions.

For any web application, no sa permission is required.

Database Access uses stored procedures

If the database supports stored procedures, use stored procedures to execute database access behaviors, so that SQL is not required (assuming that the stored procedure is properly programmed ).

Encapsulate the query, update, delete, and other action rules into a separate process to test and archive the basic and executed business rules (for example, if the customer exceeds the credit card quota, the order may be rejected during the "Add new record" process ).

This may only produce a small number of benefits for simple queries. However, once the operations become complex (or used in more places), a separate definition is provided for the operations, functions will become more stable and easy to maintain.

Note: dynamic creation of a query stored procedure can be achieved: this does not prevent SQL Injection-it only binds pre-compilation/execution together, or bind the SQL statement with the variables that provide protection.

Isolate Web Servers

After all the above measures are implemented, some omissions may still occur, resulting in server penetration. The designer should assume that the bad guy has obtained the highest privileges of the system to design the network facilities, and then minimize the impact of its attacks on other things.

For example, place this machine inside the DMZ network ", this means that even if you have obtained full control of the web server, you cannot automatically obtain full access to everything else. Of course, this does not prevent all intrusions, but it can make the intrusion very difficult.

Configuration Error Report

Some framework error reports contain the development bug information, which should not be disclosed to users. Imagine how easy it would be to attack a complete query if it was actually found and pointed out a syntax error.

This information is useful for developers, but it should not be made public-if possible-it should be restricted to internal user access.

Note: not all databases are configured in the same way, and not all databases support the same SQL syntax ("S" stands for "structured" rather than "standard "). For example, MySQL in most versions does not support subqueries, and generally does not allow multiple statements in a single row (multiple statements): When you penetrate the network, in fact, these are factors that complicate the problem.

Again, although we chose the "forgot password" link to try the attack, it is not because this function is not secure. Instead of focusing on "forgot password", it is one of several easy-to-attack points.

This tutorial does not cover the content of SQL injection, nor is it even a tutorial: it is just a record of penetration testing that we spent hours doing. We have read other discussions about SQL injection, but they only give the results rather than the process.

However, results reports require technical background to be understood, and penetration details are also valuable. Without source code, penetration testing is also valuable.