What You Should Know About SQL Injection

What You Should Know About SQL Injection

Put on your black hat. Now let's learn something really interesting about SQL injection. Please remember, you all use these things to be seen well, OK?

SQL injection attacks are a particularly interesting adventure due to the following:

1. As a framework that can automatically regulate input, writing vulnerable code becomes increasingly difficult-but we still write poor code.

2. because you use the stored procedure or ORM framework, you may not be very clear (although you realize that SQL injection may penetrate them, right) the code we write under these protection measures is still vulnerable.

3. Using a well-designed automated tool to crawl the web to search for vulnerable sites, this type of site can be easily detected remotely. However, we are still releasing them ).

SQL injection attacks are retained to the top ten hidden risks in the OWASP (Open Web Application Security Project) list for a very appropriate reason-it is particularly common, it is very easy to use and has a severe impact. A very small injection risk often exposes all the data in the entire system-and I will show you how to use a large number of different technologies to do so on your own.

I wrote the "the OWASP Top 10 for. NET developers" a few years ago and showed how to prevent SQL injection attacks. Therefore, I will not focus on these attacks, which are exploited by vulnerabilities. Enough of the boring defense tools, let's attack something else.

If we can break the search content, your data will be ours.

Let's make a quick summary of the possible causes of SQL injection attacks. In short, this is the input query and decryption of data. Let me visualize it to you: for example, you have a URL containing a string parameter similar to "id = 1, the following method is used to construct an SQL query.

The whole URL may look like this:

This is a basic thing, and it will become interesting when you can control the information in the link and change the value passed to the query. Well, changing 1 to 2 will give you another thing you want, but what if you do?

Http://widgetshop.com/widget? Id = 1 or 1 = 1

It may be stored in the database server as follows:

SELECT * FROM Widget WHERE ID = 1 OR 1=1

This tells us that the data is not purified-in the above example, the ID should be an integer, but the value of "1 OR 1 = 1" is also accepted. More importantly, because the data is simply added to the query, it can change the statement function. This query can select all records instead of a single record, because the "1 = 1" statement is always established.

Alternatively, we can change "or 1 = 1" to "and 1 = 2" to force the page not to return any records, because it never works, so no results are returned. In these two optional schemes, we can easily determine whether the program is threatened by injection attacks.

This is the essence of SQL injection attacks-cleverly manipulating query execution through untrusted data-that occurs when developers do this.

Query = "SELECT * FROM Widget where id =" + Request. QueryString ["ID"]; // Execute the query...

Of course, what they do is to parameterize untrusted data, but I will not describe it too much in this article (if you want to know the preventive measures, switch back to part one of my OWASP series ), we will talk more about how to launch attacks.

Well, the background section explains how to demonstrate the existence of SQL Injection risks, but what can you do with it? Let's start exploring some common injection modes.

Split: Merge query-based injection

Let's take an example to show that we want to return a bunch of record pages. Here is a bunch of URLs with small items with "TypeId" 1. Like this:

Http://widgetshop.com/Widgets? TypeId = 1

The results on the page will look like this:

We will expect this query to become something like this when it enters the database:

SELECT Name FROM Widget WHERE TypeId = 1

However, if we can apply what I described above, that is, we may be able to add SQL statements to the data in the query string, we may do something like this:

Http://widgetshop.com/Widgets? TypeId = 1 union all select name from sysobjects where xtype = 'U'

 

Then it generates the following SQL query:

SELECT Name FROM Widget WHERE TypeId = 1 union all select name from sysobjects where xtype='u'

Now we have remembered that the system object table lists all objects in the database. In this example, we use xtype "u" to filter the table, that is, the user table.

When an injection risk exists, the following output is displayed:

This is called merging query-based injection attacks. Just as we simply added an item as the original result, it is directly included in the HTML output-simple! Since we already know that there is a data table named "User", we can do this:

Http://widgetshop.com/Widgets? TypeId = 1 union all select password from [user]

If the "user" in the data table is not enclosed in brackets, the SQL Server becomes difficult to control considering that the word "user" has other meanings in the database. In any case, this is what it returns:

Of course, the union all statement works only when the first SELECT statement and the second statement have the same field. This is easy to find. You only need to try "union all select 'A'". If the query fails, try "union all select 'A', 'B, and so on. Basically, you are constantly guessing the number of columns until you construct a query.

We can continue to study this aspect and find various types of data, but let's learn the next attack method. Sometimes a merge query-based injection does not work, and it depends on the input format, data added in the query, and even how the results are displayed. To bypass it, we need to be more creative.

Leak the program itself: Injection Based on Error Information

Http://widgetshop.com/widget? Id = 1 or x = 1

Wait a moment. This is not a legal SQL statement. The "x = 1" won't be processed. At least it won't be processed when there is no column named x. So it will not throw an exception? Strictly speaking, in fact you will see exceptions like this:

This is an ASP. NET error, and other frameworks have similar styles. But it is important that these error messages expose the internal implementation method. In other words, this tells us that there is no field called "x" in the database. Why is this important? Basically, this is because once you establish an application that exposes an SQL exception, you can do the following:

http://widgetshop.com/widget/?id=convert(int,(select top 1 name from sysobjects where id=(select top 1 id from (select top 1 id from sysobjects where xtype=’u’ order by id) sq order by id DESC)))

There are many things to be understood. I will come back to explain them in detail later. More importantly, you can get the following result in your browser:

Now we can see that there is a form in the database called "Widget ". You will often see that the injection attack relies on internal database errors and is called "Injection Based on Error information ". Let's deconstruct this query in the URL:

convert(int, (    select top 1 name from sysobjects where id=(      select top 1 id from (        select top 1 id from sysobjects where xtype='u' order by id      ) sq order by id DESC    )  ))

From the deepest point of view, we first obtain the first record ID from the sysobjects table in the order of IDs. Where we get the last ID (that is why it is sorted in descending order) and pass it to the first select statement. The statement then converts the form name to an integer. This conversion will fail in most cases (you should not name the data table with "1", "2", or other integers !), This exception exposes the form name in the UI.

Why are three select statements? This means that we can go to the deepest layer and change "top1" to "top2". The following result is displayed:

Now we know that this database has a data table named "User ". Using this method, we can find the field names of each form (you only need to apply the same idea to the syscolumns table ). We can further expand this idea.

In the previous article, I have found a form named User and a column named Password. Now all I need to do is to select the table (of course, you can use nested select statements to enumerate all records one by one ), and convert the string to an integer to construct an exception (you can always add an English character after the data to see if it is an integer, then an exception occurs when you try to convert the entire string to an integer ). If you want to further understand how simple this can be, I recorded a video that I taught my 3-year-old son to use Havij for automatic injection last year, where I used this technology.

But there is a problem here-the only thing that succeeds is probably because the app is a little naughty and shows internal error information to the public. In fact, the app almost directly tells us the name of the form and list and returns data when we make an appropriate query. What if the app doesn't do this? I mean, what if the app is set properly without leaking internal error messages?

 

This is where we use "blind" SQL injection, which is really interesting.

Blindly try to inject

In the previous example (in fact, in many successful injection attack cases), the attack relies on the attacked app to explicitly include internal details or merge forms, either return the data or send the error message back to the browser. The internal implementation method of leakage has always been a one-click problem, because as you have seen before, insecure error handling like this can not only lead to application architecture leakage, it makes it easy for you to obtain data from it.

An appropriately set app should be able to return a similar error message when an unhandled exception is obtained:

This is the default error page for the new ASP. NET app to handle custom errors, but similar styles also appear in other technology stacks. Now this page is exactly the same as the previous page that shows internal SQL exceptions, except that an error message is used instead of the exception displayed directly. If we cannot implement a Combined Query-based attack at the same time, will the SQL Injection risk completely disappear? Not necessarily ......

Blind SQL injection attacks rely on us to get self-evident information, in other words, we can draw conclusions by observing the form name that the app does not directly tell us or the list data that is directly displayed in the browser. Of course, the question is: how can we let the app reveal our previous information in an observed format without explicitly telling us?

We will appreciate two attempts: Boolean-based and time-based.

Ask (APP), and then you will be answered: Boolean-based injection

This is only true when you ask the app the correct question. Previously, we were able to clearly ask such questions, for example, "what form do you have ?" Or "What data columns do you have in each form ?", Then the database will clearly tell us. Now we need to slightly change the method of first-line inquiry, such:

http://widgetshop.com/widget/?id=1 and 1=2 Clearly this equivalency test can never be true – one will never be equal to two. How an app at risk of injection responds to this request is the cornerstone of blind SQLi and it can happen in one of two different ways.

Obviously, this equality test will never be true-1 is never equal to 2. How an app handles such a query determines its SQL Injection risk. There may be two ways.

First, if no record is returned, it may throw only one exception. Developers usually assume that there is a record related to the query string, because it is often the link generated by the app itself and the data is obtained on another page. But when there is no data to return, things will be different. Alternatively, the app may throw an exception and will not display records at the same time, because the equality is always wrong. In any case, the app will implicitly tell us that no records in the database are returned.

Now let's try this:

1 and(  select top 1 substring(name, 1, 1) from sysobjects where id=(    select top 1 id from (      select top 1 id from sysobjects where xtype='u' order by id    ) sq order by id desc )) = 'a'

Remember to use this entire statement block to replace the query string "? Id = 1 ", which is actually a small change made in the previous query, trying to get the form name. In fact, the main difference is that we are not trying to construct an exception by converting a string to an integer, instead, an equal test is used to check whether there is a form with the first letter "a" (assuming it is not case sensitive ). If this query matches "?", Id = 1 "gives us the same information, so it is equivalent to confirming to us that the equality test is true. In sysobjects, there is indeed a Form starting with". If it gives us one of the two scenarios we mentioned before, we know that the form does not start with "a" because no information is returned.

Now we only get the first letter of the Form in sysobjects. When you want to get the second letter as the substring statement, you need to change it to the following:

select top 1 substring(name, 2, 1) from sysobjects where id=(

You can see that it starts from 2 instead of 1. of course, this is very laborious: After enumerating all forms in sysobjects, you enumerate all possible words in the alphabet until you finally get the result, then you need to repeat the process with every character in the form name. However, there is a shortcut like this:

1 and(  select top 1 ascii(lower(substring(name, 1, 1))) from sysobjects where id=(    select top 1 id from (      select top 1 id from sysobjects where xtype='u' order by id    ) sq order by id desc  )) > 109

Here is a subtle but important difference. It does not check single character matching, but searches for the position of Characters in the ASCII table. In fact, it first converts the form name to a lower-case letter, so that we only need to process 26 characters (of course, assume that there are only letters in the name), and then it gets the ASCII value of that letter. In the previous example, it then checks whether the form contains a letter starting with a letter after "m" (ASCII value 109, then the same potential successfully describes the previous application (either a record is returned or not ). The main difference is that there are no 26 attempts to guess letters (and 26 HTTP requests in a row ), it will now exhaust all possibilities in five attempts-You just need to keep halving the possible ASCII value range until there is only one possibility to be left.

For example, if the ASCII value of a character is greater than 109, it must be between "n" and "z", so you divide (roughly) the interval to half, then try the one larger than 115. If it is wrong, the correct character must be between "n" and "s", so you will cut the range by half and try the one greater than 112. It was correct at that time, so now there are only three characters left, so you can reduce the interval to 1 in at most two attempts. One sentence is a total of up to 26 guesses (an average of 13). Now it only takes five guesses, if you simply cut the answer range by half each time.

By constructing an appropriate inquiry app, we will still tell you what it told you through a clear error message, but it is a little shy now and you need to coax it to get the answer. This is often called "Boolean-value-based" SQL injection, which plays a role in the previous demonstration of "merge-Based Query" and "error-based" solutions. But this is not foolproof. Let's look at another way. This time we will have some patience.

Patient waiting for leakage: time-based blind Injection

The success of all real-time solutions is based on the assumption that the app will leak information through HTML output. In the previous example, the Combined Query and error message-based attempt clearly tells us the Object Name and leaked internal data by giving us data in the browser. In a blind Boolean-based example, we are implicitly told that the same piece of information has different results by means of HTML and true-false equality tests. What should I do if this information cannot be leaked through HTML, either explicitly or implicitly?

Let's imagine another attack media is this URl:

Http://widgetshop.com/Widgets? OrderBy = Name

In this example, it is normal to assume that the query will be translated into something like this:

SELECT * FROM Widget ORDER BY Name

Obviously, we cannot directly add something to the order by statement (although there are other angles where you can mount an attack based on boolean values), so we need to try another way. A common SQL injection technique is to terminate a statement and then append a statement, for example:

http://widgetshop.com/Widgets/?OrderBy=Name;SELECT DB_NAME()

This is a harmless statement (although the database name may be useful), a more harmful way may be something similar to "drop table Widget. Of course, the account called by the web app to connect to the database requires such permissions. The problem is that once you start to connect the connection, its potential will be realized.

Back to the blind SQL injection attack, what we need to do now is to find a test based on Boolean value discussed before using the attached statement. To achieve this, we need to use the waitfor delay statement to produce latency. Try this to see the size:

This is only a small change with the previous example. Previously, we changed the returned record bibliography by manipulating the WHERE statement, now, we only use a new statement to check whether a form in sysobjects starts with a letter larger than "m". If a form exists, the query will wait for 5 seconds. We still need to narrow down the form name range and try every character in the form name, while we still need to query other forms in sysobjects (of course we also need to look at syscolumns and extract the data ), however, it takes a little time to complete all this. 5 seconds may be a little longer than needed or it may not be long enough. It all comes down to how consistent the application response time is, this is because it is ultimately designed to operate on an observed behavior-how long it takes to get the result from the start of the query.

This attack-and the previous ones-can of course be completely automated, because there is no more than simple enumeration and conditional logic. Of course, it may take some time, but it is a relative concept: If a normal query takes 1 second, and only half of the five attempts need to be completed, you should expect to get a character every 17.5 seconds. For example, if a database has 10 characters, it takes about 3 minutes to get a form. Maybe a database has 20 forms, we think that you will get the name of every form in the system in about an hour. This is the case where you use a single thread.

It's not over ......

This is a topic that has a bunch of different points of view, not only because there are too many databases, app frameworks, and servers, not to mention the entire defense system, such as the network application firewall. One tricky example is that if you need to request time-based attacks and the database does not support the latency function, such as an Access database (yes, games are actually used on websites !) One way here is to use the heavy queries solution. The query will slow the response because of its nature.

Another piece of SQL injection attacks is worth mentioning that there are two key factors for attack success: the first is the app's input specification, this determines what characters the app will eventually receive and send them to the database. We usually see a very fragmented approach, such as angle brackets and quotation marks being stripped, but everything else is allowed. In this case, attackers need to be creative and consider how to construct appropriate queries to avoid "roadblocks. This is the second point-the attacker's SQL strength is crucial. This does not mean that you use the select from capability of TSQL, those excellent SQL injections have a lot of tips to bypass input detection and select data from the system so that they can be retrieved through the web page. For example, you can use tips like this to search for a column type:

http://widgetshop.com/Widget/?id=1 union select sum(instock) from widget

In this example, error-based injection attacks will be returned to the UI when the error message is returned (of course, if no error is reported, it is an integer) will tell you what type the "InStock" column is

Or, once you get bored with the damn vulnerable site, try this:

http://widgetshop.com/Widget/?id=1;shutdown

However, injection attacks can go further by obtaining information from HTTP. For example, there is a carrier that can give attackers machine scripts or try another digress-why not try to get the damn thing directly through HTML? Create a local SQL Server and remotely connect to SQL Server Management Studio through port 1433! Wait, you will need the web app to link the database to create a user account, right? Yes, and most people need it. In fact, you only need to use Google to find them) (Of course, in this case, SQL injection attacks are unnecessary because the database can be publicly obtained at this time)

Finally, if you have any questions about the prevalence of SQL injection attacks and vulnerabilities and their impact in today's software industry, we have a news article last week about one of the largest hacking solutions so far, it is said to have produced 0.3 billion losses

The indictment also implies that hackers, in most cases, did not deploy complicated solutions to access the enterprise network. This report also demonstrates the ethics of SQL Injection Vulnerabilities in most cases-a threat that has been thoroughly proven and understood for more than a decade.

SQL injection attacks may not be understood as some people believe.