SQL Injection for Top Ten Web Security Risks

Injection is often caused by the lack of security checks on input by applications. attackers send some data containing commands to the interpreter, which converts the received data into commands for execution. Common injections include SQL injection, OS Shell, LDAP, Xpath, Hibernate, and so on. SQL injection is particularly common. This type of attack often causes a lot of consequences. Generally, the information of the entire database can be read or tampered with. through SQL injection, attackers can even obtain more privileges, including administrator permissions. Let's talk about how the SQL injection vulnerability is generated, or how a program developer can prevent SQL injection. SQL injection is usually generated when you write a dynamic database query that contains user input. However, it is very easy to prevent SQL injection. Programmers can prevent SQL injection only by no longer writing dynamic queries, or by preventing users from entering malicious SQL statements that disrupt the query logic. Of course, as a tester, it is easy to say and difficult to do. I only use java, so I will use Java code as an example: 1. <font face = "" color = "#000000"> String query = "SELECT account_balance FROM user_data WHERE user_name =" 2. + request. getParameter ("customerName"); 3. 4. try {5. statement statement = 6. connection. createStatement (...); 7. resultSet results = 8. statement.exe cuteQuery (query); 9 .} </font> in the above code, we can see that the customerName variable is not verified. The value of customerName can be directly attached to the query statement and transferred to the database for execution, attackers can inject arbitrary SQL statements. Let's talk about how to avoid SQL injection vulnerabilities. I planned to test this section before introducing it, but it seems that it is easier to understand it first.Defense Method 1: parameterized QueryParameterized query is the first thing developers need to learn when performing database queries. parameterized query forces all developers to define all the SQL code first, and then pass each parameter one by one, this encoding style allows the database to identify code and data. Parameterized query ensures that attackers cannot change the query content. In the example corrected below, if the attacker inputs UsrID as "'or '1' = '1 ", A parameterized query is used to find a user whose name is either 'or '1' = '1. For different programming languages, there are some different suggestions: Java -- use PreparedStatement () with Bound variables; others :... Sorry, this is not an example: 1. <font face = "" color = "#000000"> String custname = request. getParameter ("customerName"); 2. string query = "SELECT account_balance FROM user_data WHERE user_name =? "; 3. 4. preparedStatement pstmt = connection. prepareStatement (query); 5. pstmt. setString1, custname (); 6. resultSet results = pstmt.exe cuteQuery (); </font> defense method 2: The Stored Procedure and parameterized query functions the same, the only difference is that stored procedures are pre-defined and stored in the database for application calls. Java stored procedure example: 1. String custname = request. getParameter ("customerName"); 2. try {3. CallableStatement cs = connection. prepareCall ("call sp_getAccountBalance (?)} "); 4. cs. setString (1, custname); 5. result results = cs.exe cuteQuery (); 6 .} catch (SQLException se) {7. // error handling8.} defense method 3: escape all user input. We know that each DBMS has a character escape mechanism to inform DBMS that the input is data rather than code, if we escape all user input, the DBMS will not confuse data and code, and there will be no SQL injection. Of course, if you want to use this method, you need to escape the used database, or you can use existing escaping routines such as owasp esapi. ESAPI is currently based on the escape mechanism of MySQL and Oracle, which is also very convenient to use. An example of using an Oracle ESAPI is as follows: 1. ESAPI. encoder (). encodeForSQL (new OracleCodec (), queryparam); then, assume that you have a dynamic query code to access the Oracle database: 1. string query = "SELECT user_id FROM user_data WHERE user_name = '" + req. getParameter ("userID") + "'and user_password ='" + req. getParameter ("pwd") + "'"; 2. try {3. statement statement = connection. createStatement (...); 4. resultSet results = statement.exe cuteQuery (query); 5 .} then, you must rewrite the first line of your dynamic query as follows: 1. codec ORACLE_CODEC = new OracleCodec (); 2. string query = "SELECT user_id FROM user_data WHERE user_name = '" + 3. ESAPI. encoder (). encodeForSQL (ORACLE_CODEC, req. getParameter ("userID") + "'and user_password ='" + 4. ESAPI. encoder (). encodeForSQL (ORACLE_CODEC, req. getParameter ("pwd") + "'"; minimal permission method: to avoid damage to the database caused by injection attacks, we can Do not grant the permissions of the DBA or administrator to your application account. The permission is based on the permissions that the user needs, rather than the permissions that the user does not need. When a user only needs the read permission, we only give him the read permission. When the user only needs part of the data in a table, we would rather create another view for him to access. If your policy is to use stored procedures, you can only allow the application's account to execute these queries without giving them the permission to directly access the database table. Such a minimum permission method can ensure the security of our database to a large extent. Input verification whitelist method: input verification can detect whether the input is correct and valid before data is transmitted to an SQL query. Using a whitelist instead of a blacklist can ensure data legitimacy to a greater extent. The above is about the generation of SQL Injection Vulnerabilities and how developers guard against them. How can we test the existence of SQL injection vulnerabilities for our testers? First, we can divide SQL injection attacks into three types: Inband: data is extracted through the SQL code injection channel, which is the most direct attack, information obtained through SQL injection is directly reflected on the web page of the application. Out-of-band: data is obtained by means different from SQL code injection (such as by email: this attack means that there is no real data transmission, but attackers can send specific requests and reorganize the returned results to obtain some information. Regardless of the SQL injection, attackers need to construct an SQL query with the correct syntax. If the application returns an error message for an incorrect query, it is easier to reconstruct the logic of the initial query statement to facilitate injection. If the application hides the error information, then attackers must reverse engineer the query logic, that is, the so-called "SQL blind injection" black box test and example: the first step in this test is to understand when our applications need to access the database. The typical time to access the database is: authentication form: enter the user name and password to check whether the search engine has the permission: Submit a string to obtain the corresponding record from the database e-commerce site: Get the price of a certain type of goods and other information as a tester, we need to make a form for all input field values that may be used for the query field, including the hidden fields of the POST request, then intercept the query statement and generate error information. The first test usually uses a single quotation mark (') or semicolon (;). The former is a string terminator in SQL. If the application does not filter, an error message is generated; the latter is the terminator of an SQL statement in SQL. If it is not filtered, an error message is generated. In Microsoft SQL Server, the returned error message is generally as follows: 1. microsoft ole db Provider for ODBC Drivers error '80040e14' 2. [Microsoft] [odbc SQL Server Driver] [SQL Server] Unclosed quotation mark before the character string ''. 3. /target. asp AND line 113 can also be used for testing "--" AND some keywords such as "AND" in SQL, A common test is to input a string in an input box that requires numbers. The following error message is returned: 1. microsoft ole db Provider for ODBC Drivers error '80040e07 '2. [Microsoft] [odbc SQL Serv Er Driver] [SQL Server] Syntax error converting the varchar value 'tester' to a column of data type int.3./target. asp, line 113 Error response information similar to the above can let us know a lot of database information, usually does not return so much information, will return information such as "500 Server Error, then we need "Blind SQL injection. Note: we need to test all input fields that may have the SQL injection vulnerability, and change the value of only one field in each test case to find the input fields that actually have the vulnerability. Next, let's take a look at the standard SQL injection test. I have mentioned this in the above post. Here we will repeat the following SQL query details: 1. SELECT * FROM Users WHERE Username = '$ username' AND Password =' $ password' if we enter the following username AND password on the page: 1. $ username = 1' or '1' = '12. $ password = 1 'or '1' = '1 then the entire query statement is: 1. SELECT * FROM Users WHERE Username = '1' OR '1' = '1' AND Password = '1' OR '1' = '1' assuming that the parameter value is passed through the GET Method to the server, and the domain name is www.example.com, then our access request is: 1. [url] http://www.example.com/index.php ? Username = 1' % 20or % 20'1' % 20 = % 20' 1 & password = 1' % 20or % 20'1' % 20 = % 20' 1 [/url] After a simple analysis of the preceding SQL statement, we know that the statement is always true, therefore, some data will be returned. In this case, the user name and password are not verified, and in some systems, the first row of the User table record is the administrator, the consequences are even more serious. An example of another query is as follows: 1. SELECT * FROM Users WHERE (Username = '$ username') AND (Password = MD5 (' $ password') in this example, there are two problems, one is the use of parentheses, and the other is the use of MD5 hash functions. For the first problem, we can easily find the missing right brackets to solve. For the second problem, we can try to invalidate the second condition. We add a annotator at the end of the query statement to indicate that all comments are followed. The common comment start character is/* (which is -- in Oracle), that is, we use the following username and password: 1. $ username = 1 'or '1' = '1')/* 2. $ password = foo, the entire SQL statement is changed to: 1. SELECT * FROM Users WHERE (Username = '1' or '1' = '1')/* ') AND (Password = MD5 (' $ password '))) our URL request is changed to: 1. [url] http://www.example.com/index.php ? Username = 1' % 20or % 20 '1' % 20 = % 20'1' [/url]) /* & password = foo Union query SQL injection test another test is to use Union. Union can be used to connect to the query to obtain information from other tables. Suppose we have the following query: 1. SELECT Name, Phone, Address FROM Users WHERE Id = $ id then we set the id value to: 1. $ id = 1 union all select creditCardNumber, 1, 1 FROM CreditCarTable then the overall query will be: 1. SELECT Name, Phone, Address FROM Users WHERE Id = 1 union all select creditCardNumber, FROM CreditCarTable obviously this will give you information for ALL credit card Users. SQL blind injection test we mentioned blind SQL injection above, that is, blind SQL injection, which means that we do not get any information about an operation, generally, this is because the programmer has compiled a specific error return page to hide the database structure information. Using reasoning, we can sometimes restore the values of specific fields. This method usually uses a group of Boolean queries on the server to deduce the meaning of the results based on the returned results. Continue the above www.example.com, there is a parameter named id, then we enter the following url request: 1. [url] http://www.example.com/index.php ? Id = 1' [/url] Obviously, due to a syntax error, we will get a pre-defined error page. Assume that the query statement on the server is SELECT field1, field2, field3 FROMUsers WHERE Id = '$ id'. If we want to obtain the value of the username field, we can use some functions to read the username value by character. Here we use the following functions: 1. SUBSTRING (text, start, length), ASCII (char), LENGTH (text) We define id as: 1. $ Id = 1' and ascii (SUBSTRING (username, 1, 1) = 97 AND '1' = '1 then the final SQL query statement is: 1. SELECT field1, field2, field3 FROM Users WHERE Id = '1' and ascii (SUBSTRING (username, 1, 1) = 97 AND '1' = '1, if the ASCII code of the first character of the user name in the database is 97, then we can get a true value, then we will continue to look for the next character of the user name; if not, then we will incrementally guess the username whose first character's ASCII code is 98, so that we can judge the valid username again and again. So when can we end the inference? we assume that the value of id is: 1. $ Id = 1' and length (username) = n and '1' = '1 where N is the number of characters we have analyzed so far, the overall SQL query is: 1. SELECT field1, field2, field3 FROM Users WHERE Id = '1' and length (username) = n and '1' = '1' if the return value of this query is true, then we have completed the reasoning and we have obtained the expected value. If it is false, it means we have to continue the analysis. This kind of SQL blind injection requires us to input a lot of SQL attempts, and some tools can help us implement it. I have only used sqlmap. I will introduce it later. Stored Procedure injection in the content written above, mentioned that using stored procedures can prevent SQL injection, but also note that if stored procedures are not suitable for use, the use of dynamic queries in stored procedures also results in some SQL injection vulnerabilities. The following SQL stored procedure is used as an example: 1. create procedure user_login @ username varchar (20), @ passwd varchar (20) As2. Declare @ sqlstring varchar (250) 3. set @ sqlstring = '4. select 1 from users5. Where username = '+ @ username +' and passwd = '+ @ passwd6. exec (@ sqlstring) 7. the Go user input is as follows: 1. anyusername or 1 = 1' 2. anypassword if we do not verify the input, the above statement will return a record in the database. Let's look at the following: 1. create procedure get_report @ columnamelist varchar (7900) As2. Declare @ sqlstring varchar (8000) 3. set @ sqlstring = '4. select '+ @ columnamelist +' from ReportTable '5. exec (@ sqlstring) 6. go if the user input is: 1. 1 from users; update users set password = 'Password'; After select *, all user passwords are changed and report information is obtained.