SQL Overview and its application in network security

Security | Network 1. Network applications and SQL injection

1.1 Overview

Some network databases do not filter potentially harmful characters from customer-supplied data, and SQL injections are techniques that exploit harmful characters to attack. Although very easy to guard against, there are still an alarming number of storage systems on the Internet that are vulnerable to this attack. The purpose of this article is to instruct professional security organizations to understand this technology and to tell them the correct way to prevent SQL injection and to deal with various common problems caused by illegal input.

1.2 Background

Before reading this article, you should have some basic understanding of how the database works and how SQL is used to access the database. I recommend that you read Extropia.com's article "Introduction to Databases for Webdevelopers".

(URL: http://www.extropia.com/tutorials/sql/toc.html)

1.3 Character encoding

In most web browsers, punctuation and many other symbols need to encode URLs before they are used for a network request to be properly compiled (interpret). In the examples and screenshots of this article I used fixed ASCII characters to ensure maximum readability. However, in practical applications, you need to use% 25来 in HTTP requests instead of the percent sign (%), using%2b instead of the plus (+), and so on.

1. Network applications and SQL injection

1.1 Overview

Some network databases do not filter potentially harmful characters from customer-supplied data, and SQL injections are techniques that exploit harmful characters to attack. Although very easy to guard against, there are still an alarming number of storage systems on the Internet that are vulnerable to this attack. The purpose of this article is to instruct professional security organizations to understand this technology and to tell them the correct way to prevent SQL injection and to deal with various common problems caused by illegal input.

1.2 Background

Before reading this article, you should have some basic understanding of how the database works and how SQL is used to access the database. I recommend that you read Extropia.com's article "Introduction to Databases for Webdevelopers".

(URL: http://www.extropia.com/tutorials/sql/toc.html)

1.3 Character encoding

In most web browsers, punctuation and many other symbols need to encode URLs before they are used for a network request to be properly compiled (interpret). In the examples and screenshots of this article I used fixed ASCII characters to ensure maximum readability. However, in practical applications, you need to use% 25来 in HTTP requests instead of the percent sign (%), with%2b instead of plus (+) etc. ...

1. Network applications and SQL injection

1.1 Overview

Some network databases do not filter potentially harmful characters from customer-supplied data, and SQL injections are techniques that exploit harmful characters to attack. Although very easy to guard against, there are still an alarming number of storage systems on the Internet that are vulnerable to this attack. The purpose of this article is to instruct professional security organizations to understand this technology and to tell them the correct way to prevent SQL injection and to deal with various common problems caused by illegal input.

1.2 Background

Before reading this article, you should have some basic understanding of how the database works and how SQL is used to access the database. I recommend that you read Extropia.com's article "Introduction to Databases for Webdevelopers".

(URL: http://www.extropia.com/tutorials/sql/toc.html)

1.3 Character encoding

In most web browsers, punctuation and many other symbols need to encode URLs before they are used for a network request to be properly compiled (interpret). In the examples and screenshots of this article I used fixed ASCII characters to ensure maximum readability. However, in practical applications, you need to use% 25来 in HTTP requests instead of the percent sign (%), with%2b instead of plus (+) etc. ...

3.2.5 Like statement query

Another big catastrophe is the trap of falling into a like clause. (Seeing the "like keyword" or percent signs cited in a error message are indications to this situation.) Most web search programs use the LIKE clause to query the database, such as the following:

SqlString = "Select FirstName, LastName, Title from Employees WHERE LastName like '%" & strlastnamesearch & "%"

The% of this is the wildcard, in this case the WHERE clause returns true, as long as there is a string containing strlastnamesearch in the LastName. To prevent SQL Server from returning the expected records, The SQL statement you construct must contain a string that is not in the LastName. The Web search program searches for a string from the user's input. There is usually a ' and a% ' before the input string, so we construct the string, You need to match them in the WHERE clause. If you submit null as a search string, then the like parameter becomes "percent%", which is a full match, and returns all the records.

3.2.6 "Dead End"

Most of the time SQL injection is accompanied by a lot of failed practices, and if you find that you can't insert the relevant statements anyway, and no matter what you do, then you have to decide if you've fallen into a dead end, Most of the time you're probably in a multiple nested where and select clause, or some more complex multiple nesting, even using ";-" is useless, so be careful and avoid staying in this place.

Mismatch of number of 3.2.7 columns

As the illustration shows, we can get a lot of useful information from several mistakes and adjust our request statements, which means that we are not far away from success. When you guess the name of a column, as the figure shows, we submit the statement with the following error "All queries in the Union statement must have the same number of expressions in the target list", which means you need to find out or detect how many columns are in a legitimate request.

Here I explain that the Union statement is used to add two different query result sets to a result set, the only requirement for union is two query information (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 legitimate SELECT statement and the Union SELECT statement we inject must have the same columns in the WHERE clause. For the above statement, if I were to add a union statement, I would have 3 columns both before and after. And the data types of their columns must be matched to each other. If the value of FirstName is a string type, the corresponding value in the statement you injected should also be of the string type. Some databases, such as Oracle, are very strict about type checking. Other databases are relatively good, allowing you to enter any data type and it automatically converts the data type you typed into the correct one. For example, in a SQL database, you can enter a numeric type of data (such as int) in a varchar type where the numeric type is automatically converted to a string type. However, if you enter the text type at the smallint column, it is considered illegal because the text type cannot be converted to an int type. It is permissible to convert data of numeric type to string type, and vice versa, so the data of numeric type is used by default.

To know how many columns are in the target statement that we're injecting, you'll have to tentatively add the corresponding value to the Union SELECT clause until it doesn't say "all queries in the Union statement must have the same number of expressions in the target list". As shown in the picture, if you are encountering a data type mismatch error, then you have to change the column data type. If the return message is just a failure of a converted data type, it means you have guessed the number of columns, except that the data type of the individual columns is incorrect. So the next thing to do is to determine which column's data type is incorrectly causing the error. Then you can change him.

If all goes well, congratulations, you will get a page that is similar to the one in the above format, and you can construct your own statement wherever the dynamic page appears.

  
3.2.8.WHERE keywords

The error is "invalid column name ' EmployeeID '", which 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 we inject a statement that is a UNION all SELECT Otherfield from othertable WHERE 1=1 then we get the following submission:

SELECT FirstName, LastName, Title from Employees WHERE city = ' nosuchcity ' UNION all SELECT Otherfield from othertable whe RE 1=1 and Country = ' USA '

This will cause an error: [MICROSOFT][ODBC SQL Server DRIVER][SQL server] Invalid column name ' Country '.

Actually, the problem is that after you injected the statement, the system did not find a column name named ' Country ' in the table from the database. We can simply comment it out (if we are SQL Server) by using the ";--" annotation symbol. Or just keep guessing the other column names, and then construct a legitimate request as we mentioned in the previous section.

Enumeration of table names

We've started to learn how to use injection to attack, but we also have to decide which table to  from, in other words, the key table names we want to get the useful information we want. How do I get the table name? In SQL Server, you can easily get all the table and column names from the database. But in Oracle and access, you don't have to be so easy to get, it depends on the Web program's access to the database. The key is whether you can get table names and column names that are included in the tables that are automatically generated when the system is built. As in SQL Server, they are ' sysobjects ' and ' syscolumns ' respectively, (at the end of this article we will give the other database system from the table and the corresponding column name) we use the following sentences to list all the column and table names of the database in these tables, (modified by the circumstances):

SELECT name from sysobjects WHERE xtype = ' U '

This will return all the user-defined tables in the database, and if we see the table we are interested in or want to see, then we'll open it up here, take orders as an example construct a statement: select name from syscolumns WHERE id = (SELECT ID From sysobjects where name = ' Orders ') gets the result as shown.

3.2.10. Single record

The statement we constructed above returns a great deal of information, if you only want to display a data record. You can completely construct your injection statements to get the only information you want. All we have to do is add keywords to the WHERE clause to avoid the keyword being selected for some rows. Let me give you an example: ' UNION all SELECT name, Fieldtwo, fieldthree from tableone where ' = '

We can then get the first value of Fieldone,fieldtwo and fieldthree, assuming we are "Alpha", "Beta" and "Delta" respectively. Notice, more interestingly, we're going to get the value of line 2nd, how do we construct the following statement? In this way: ' 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 is a clause "not in VALUES", which does not return the information we have obtained, that is, not alpha, beta, or delta. Since none, the database will be silly to tell us the value of the second row. Let's assume that we get the value "Alphaalpha", "Betabeta" and "Deltadelta" in the second row.

We're going to get the value of the third row, and the construction statement is as follows: ' UNION all SELECT fieldone, Fieldtwo, fieldthree from Tableone WHERE fieldone not in (' Alpha ', ' Alphaal Pha ') and Fieldtwo not in (' Beta ', ' Betabeta ') and Fieldthree not in (' Delta ', ' Deltadelta ') and ' = '

This avoids getting the first and second values we've got, and we'll try to get all the values in the database. This may seem like a real hassle, but it's the most effective here, isn't it?
 
3.3 Insert

3.3.1 Insert Base

Keyword inserts are used to add information to a database, typically using inserts including user registration, forums, adding items to a shopping cart, and so on. Check the vulnerability of INSERT use and check where. You may not want to use inserts, and how to avoid being exploited is an important consideration. Insert injection attempts often cause the database to return results in rows that result in the overflow of separate references and the meaning of the SQL key may change. Depending on the administrator's attention and information on the operation of the database, this is to draw attention to the differences that have just been mentioned, insert injection and select injection. We do a variety of registration in a user, which provides a form for you to enter your name, address, phone, etc. After you submit this form, you must be able to see the information you submitted in order to get further insert weakness. It doesn't matter where it is. You may be sending your spam mail when you log in to give you the right to the name stored in the database. , who knows, looking for a way to at least see the information you enter.

3.3.2

An inserted request looks like this: INSERT INTO tablename values (' Vaule one ', ' Value two ', ' Value Three ') you want to be able to use a clause in parameter VALUES to see other data. We can use this approach, SQL code like this: SqlString = "INSERT into tablename VALUES ('" & Strvalueone & ", '" & Strvaluetwo & " ', ' & Strvaluethree & '] "We fill out the form like this: Name: ' + (SELECT top 1 FieldName from tablename) + ' Email: blah@blah.c The OM phone:333-333-3333 makes the declaration of SQL like this: INSERT into tablename VALUES (' + (SELECT top 1 FieldName from TableName) + ', ' blah@blah.com ', ' 333-333-3333 ') when you go to the Personal Settings page to view your usage information, you will see the first field this is usually username r if you make it out of your subselect to use top 1, you will get an error message saying that your subselect has returned too many records, you can view all the rows in the table, and you can get separate records using the same method of not in ().

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 in a Web application that uses MSSQL in the background, you can use these stored procedures to accomplish some extraordinary results. I will discuss a few special processes. Depending on the Web page program users who use the database, only a few can work, not all users can take advantage of it. First thing you should know. Stored procedure injection cannot determine whether your injection succeeded through the return value of the stored procedure. Depending on what you want to accomplish, you may not need to get the data. You can find other meanings of the data returned to you. Stored procedure injection is easier than normal query injection, and the vulnerability of stored procedure injection looks like this.

Simplequoted.asp?city=seattle '; EXEC Master.dbo.xp_cmdshell ' cmd.exe dir c: '

Attention

Notice how a valid argument are supplied at the beginning and followed by a quote and the final argument to the stored Edure has no closing quote. This would satisfy the syntax requirements inherent in most quoted vulnerabilities. You will also have to deal with parentheses, additional where statements, etc. But after that, you won't need to worry about matching the type of columns and data. The output of this potential weakness is the same as the program cannot return an error message. I like stored procedures best.

5.3.4.2. xp_cmdshell

xp_cmdshell {' command_string '} [, No_output]

Master.dbo.xp_cmdshell is the holy grail of stored procedures, it brings a problem that can invoke the command line of the database user and his run permissions, this is not available unless this Web program uses the database user is SA. Run level is 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 a SQL statement. Sp_makewebtask can query and build a Web page that contains output. Note that you can use a local output just as you would with a UNC path name. This means that the output file can be placed on any one connected to the Internet and has a writable SMB share (SMB requests do not require any authentication). If you have a firewall that restricts the server to the Internet, try putting the output file in the directory of the Web page (you know or guess the directory of the page). It is also noteworthy that reference queries may include the execution of other stored procedures. Making "EXEC xp_cmdshell ' dir c: '" This query will give the output of "Dir C:" In the Web page. When you make a nested reference, remember the individual references and the double quotes.

4.1 Data processing

All client data can be purged by maliciously committed characters or strings. These may be done in all applications, not just using SQL queries. Stripping quotes or putting backslashes in front the them is nowhere near. The best way to filter your data is to not use the regular expression so that it only includes the type of character you want. For example, the REGXP below will only return letters and numbers, filtering as much as possible of special characters such as s/[^0-9a-za-z]//g. Use numbers when possible, and use only numbers and letters after that. If you need to include a variety of logos or punctuation. Be sure to completely convert them to HTML tags, like "e;" or ">". For example, a user submits an email address that only allows numbers and letters to be used, and "@", "_", "." and "-". Only these characters can be converted into HTML tags.

4.2. Writing Secure Web Programs

There are also very few special SQL injection rules here. Prepend and append a quote to all user input.

Although the data makes the numbers. Second, limit the user's access to the database in the Web page application. Do not give this user access to all the stored procedure rights if this user only needs to access some of the predefined.

This section includes all of the system tables that are useful in SQL injection, and you can search on Google to define the columns of each table.

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