Tag Archives: sql injection

Lowering Defenses to Increase Security

Starting at WhiteHat was a career change for me. I wasn’t sure exactly what to expect, but I knew there was a lot of unfamiliar terminology: “MD5 signature”, “base64″, “cross-site request forgery”, “‘Referer’ header”, to name a few.

When I started testing real websites, I was surprised that a lot of what I was doing looked like this:


Everything was definitely not that simple…but a lot of things were. How could I be correcting the work of people who knew so much more about computers than me? I’d talk to customers on the phone, and they already knew how to fix the vulnerabilities. In fact, they were even already aware of them, in some cases! Periodically, WhiteHat publishes statistics about how long it takes vulnerabilities to get fixed in the real world, and how many known vulnerabilities are ever fixed. The most recent report is available here, with an introduction by Jeremiah Grossman here.

SQL injection was first publicly described in 1998, and we’re still seeing it after 17 years. Somehow, the social aspects of the problem are more difficult than the technical aspects. This has been true since the very beginning of modern computing:

Apart from some less-than-ideal inherent characteristics of the Enigma, in practice the system’s greatest weakness was the way that it was used. The basic principle of this sort of enciphering machine is that it should deliver a very long stream of transformations that are difficult for a cryptanalyst to predict. Some of the instructions to operators, however, and their sloppy habits, had the opposite effect. Without these operating shortcomings, Enigma would, almost certainly, not have been broken.

Speaking of the beginning of computing, The Psychology of Computer Programming (1971) has the following passage about John von Neumann:

John von Neumann himself was perhaps the first programmer to recognize his inadequacies with respect to examination of his own work. Those who knew him have said that he was constantly asserting what a lousy programmer he was, and that he incessantly pushed his programs on other people to read for errors and clumsiness. Yet the common image today of von Neumann is of the unparalleled computer genius: flawless in his every action. And indeed, there can be no doubt of von Neumann’s genius. His very ability to realize his human limitations put him head and shoulders above the average programmer today.

Average people can be trained to accept their humanity – their inability to function like a machine – to value it and work with others so as to keep it under the kind of control needed if programming is to be successful.

The passage above is from a section of the book called “Egoless Programming.” It goes on to describe an anecdote in which a programmer named Bill is having a bad day, and calls Marilyn over to look at his code. After she finds 17 bugs in 13 statements, he responds by seeing the humor in the situation and telling everyone about it. In turn, Marilyn thinks that there must be more bugs if she could spot 17, and 3 more were spotted by others. The code was put into production and had no problems for 9 years.

The author of the book, Gerald Weinberg, made another interesting observation:

Now, what cognitive dissonance has to do with our programming conflict should be vividly clear. A programmer who truly sees his program as an extension of his own ego is not going to be trying to find all the errors in that program. On the contrary, he is going to be trying to prove that the program is correct, even if this means the oversight of errors which are monstrous to another eye. All programmers are familiar with the symptoms of this dissonance resolution — in others, of course…And let there be no mistake about it: the human eye has an almost infinite capacity for not seeing what it does not want to see. People who have specialized in debugging other people’s programs can verify this assertion with literally thousands of cases. Programmers, if left to their own devices, will ignore the most glaring errors in their output—errors that anyone else can see in an instant. Thus, if we are going to attack the problem of making good programs, and if we are going to start at the fundamental level of meeting specifications, we are going to have to do something about the perfectly normal human tendency to believe that one’s “own” program is correct in the face of hard physical evidence to the contrary.

What is to be done about the problem of the ego in programming? A typical text on management would say that the manager should exhort all his programmers to redouble their efforts to find their errors. Perhaps he would go around asking them to show him their errors each day. This method, however, would fail by going precisely in the opposite direction to what our knowledge of psychology would dictate, for the average person is going to view such an investigation as a personal trial. Besides, not all programmers have managers — or managers who would know an error even if they saw one outlined in red.

No, the solution to this problem lies not in a direct attack — for attack can only lead to defense, and defense is what we are trying to eliminate. Instead, the problem of the ego must be overcome by a restructuring of the social environment and, through this means, a restructuring of the value system of the programmers in that environment.

By the nature of what we do, WhiteHat does try to find mistakes in other people’s work. It’s not personal, and those mistakes are rarely unique! In the big picture, what brought us computers was the scientific method, that is, the willingness to learn from mistakes.

The Insecurity of Security Through Obscurity

The topic of SQL Injection (SQL) is well known to the security industry by now. From time to time, researchers will come across a vector so unique that it must be shared. In my case I had this gibberish — &#MU4<4+0 — turn into an exploitable vector due to some unique coding done on the developers’ end. So as all stories go, let’s start at the beginning.

When we come across a login form in a web application, there are a few go-to testing strategies. Of course the first thing I did in this case was submit a login request with a username of admin and my password was ‘ OR 1=1–. To no one’s surprise, the application responded with a message about incorrect information. However, the application did respond in an unusual way: the password field had ( QO!.>./* in it. My first thought was, “what is the slim chance they just returned to me the real admin password? It looks like it was randomly generated at least.” So, of course, I submitted another login attempt but this time I used the newly obtained value as the password. This time I got a new value returned to me on the failed login attempt: ) SL”+?+1′. Not surprisingly, they did not properly encode the HTML markup characters in this new response, so if we can figure out what is going on we can certainly get reflective Cross Site Scripting (XSS) on this login page. At this point it’s time to take my research to a smaller scale and attempt to understand what is going on here on a character-by-character level.

The next few login attempts submitted were intended to scope out what was going on. By submitting three requests with a, b, and c, it may be possible to see a bigger picture starting to emerge. The response for each appears to be the next letter in the alphabet — we got b, c, and d back in return. So as a next step, I tried to add on a bit to this knowledge. If we submit aa we should expect to get bb back. Unfortunately things are never just that easy. The application responded with b^ instead. So let’s see what happens on a much larger string composed of the same letter; this time I submitted aaaaaaaaaaaa (that’s 12 ‘a’ characters in a row), and to my surprise got this back b^c^b^b^c^b^. Now we have something to work with. Clearly there seems to be some sort of pattern emerging of how these characters are transforming — it looks like a repeating series. The first 6 characters are the same as the last 6 characters in the response.

So far we have only discussed these characters in their human readable format that you see on your keyboard. In the world of computers, they all have multiple secondary identities. One of the more common translations is their ASCII numerical equivalent. When a computer sees the letter ‘a’ the computer can also convert that character into a number, in this case 97. By giving these characters a number we may have an easier time determining what pattern is going on. These ASCII charts can be found all over the Internet and to give credit to the one I used, www.theasciicode.com.ar helped out big time here.

Since we have determined that there is a pattern repeating for every 6 characters, let’s figure out what this shift actually looks like numerically. We start off with injecting aaaaaa and as expected get back b^c^b^. But what does this look like if we used the ASCII numerical equivalent instead? Now in the computer world we are injecting 97,97,97,97,97,97 and we get back 98,94,99,94,98,94. From this view it looks like each value has its own unique shift being applied to it. Surely everyone loved matrices as much as I did in math class, so lets bust out some of that old matrix subtraction: [97,97,97,97,97,97] – [98,94,99,94,98,94] = [-1,3,-2,3,-1,3]. Now we have a series that we can apply to the ASCII numerical equivalent to what we want to inject in order to get it to reflect how we want.

Finally, its time to start forming a Proof of Concept (PoC) injection to exploit this potential XSS issue. So far we have found out that they have a repeating pattern of 6 character shifts being applied to our input and we have determined the exact shift occurring on each character. If we apply the correct character shifts to an exploitable injection, such as “><img/src=”h”onerror=alert(2)//, we would need to submit it as !A:llj.vpf<%g%mqduqrp@`odur+1,.2. Of course, seeing that alert box pop up is the visual verification that needed that we have reverse engineered what is going on.

Since we have a working PoC for XSS, lets revisit our initial testing for SQL injection. When we apply the same character shifts that we discovered to ‘ OR 1=1–, we find that it needs to be submitted as &#MU4<4+0. One of the space characters in our injection is being shifted by -1 which results in a non-printable character between the ‘U’ and the ‘4’. With the proper URL encoding applied, it would appear as %26%23MU%1F4%3C4%2B0 when being sent to the application. It was a glorious moment when I saw this application authenticate me as the admin user.

Back in the early days of the Internet and well before I even started learning about information security, this type of attack was quite popular. In current times, developers are commonly using parameterized queries properly on login forms so finding this type of issue has become quite rare. Unfortunately this particular app was not created for the US market and probably was developed by very green coders within a newly developing country. This was their attempt at encoding users passwords when we all know they should be hashed. Had this application not returned any content in the password field on failed login attempts, this SQL injection vulnerability would have remained perfectly hidden to any black box testing through this method. This highlights one of the many ways a vulnerability may exist but is obscured to those testing the application.

For those still following along, I have provided my interpretation of what the backend code may look like for this example. By flipping the + and – around I could also use this same code to properly encode my injection so that it reflects the way I wanted it too:

<!DOCTYPE html>
<form name="login" action="#" method="post">
$strinput = $_POST['password'];
$strarray = str_split($strinput);
for ($i = 0; $i < strlen($strinput); $i++) {
    if ($i % 6 == 0) {
        $strarray[$i] = chr(ord($strarray[$i]) - 1);
    if ($i % 6 == 1) {
        $strarray[$i] = chr(ord($strarray[$i]) + 3);
    if ($i % 6 == 2) {
        $strarray[$i] = chr(ord($strarray[$i]) - 2);
    if ($i % 6 == 3) {
        $strarray[$i] = chr(ord($strarray[$i]) + 3);
    if ($i % 6 == 4) {
        $strarray[$i] = chr(ord($strarray[$i]) - 1);
    if ($i % 6 == 5) {
        $strarray[$i] = chr(ord($strarray[$i]) + 3);
$password = implode($strarray);
echo "Login:<input type=\"text\" name=\"username\" value=\"" . htmlspecialchars($_POST['username']) . "\"><br>\n";
echo "Password:<input type=\"password\" name=\"password\" value=\"" . $password . "\"><br>\n";
// --- CODE SNIP ---
$examplesqlquery = "SELECT id FROM users WHERE username='" . addslashes($_POST['username']) . "' AND password='$password'";
// --- CODE SNIP ---
<input type="submit" value="submit">

In Your Oracle: Part Two

In Micheal‘s previous post, ‘In Your Oracle: The Beginning‘, he introduced a blind SQL Injection vulnerability that a client was asking us to dig deeper into. The client wanted us to do this, because while they recognized that the vulnerability was real, actionable, and a threat – especially to their users – they weren’t convinced of its severity. Instead, the client claimed that the vulnerability could only be leveraged to read data already intended to be accessible by the logged-in user. In other words, the SQL query was executing within the context of a low-privileged database user.

A quick aside: I had a different client who recently downplayed the severity of an SQL Injection vulnerability because the result set was being parsed and formatted before being incorporated into the response. Because of this behavior, they didn’t think any data could be accessed other than what was intended to be parsed and formatted into the page. Aside from being able to UNION other data into the result set, or simply to brute force dropping tables, I introduced the client to something Micheal touched on in his post: The true/false/error condition. More on this in a minute.

The Vulnerability

The vulnerability that Micheal and I were digging into did not involve the entire result set being output to the page, so we couldn’t simply UNION other data and get it all dumped back to us. That’s because the application would execute an SQL query – using an ID that was being supplied in the query string – and the first row of data returned by the query would be used to determine the response. Therefore, we could only return a limited amount of data at a time, and that data would have to conform to certain data types – otherwise, the page would error out.

Here’s an example of what the back-end SQL query may have looked like (though, in reality, it was much more complex than this):

SELECT * FROM listingsData WHERE id='504'

And an example of the URL we were injecting on:


And last, but not least, an extraordinarily simplified example of the output from the page:

Listing ID: 504
Listing Entity: Acme, Inc.
Listing Data: <a table of data>

The True/False/Error Condition

When an SQL Injection vulnerability can’t be leveraged to just dump rows upon rows of data, a true/false condition can allow an attacker to fuzz the database, and determine the table and column names, the cell values, and more. Basically, with a reliable true/false condition, an attacker can brute force the entire database in a practical amount of time.

However, due to strange behavior from the application (plus what we suspected was a complex query syntax that we couldn’t discern), we were not able to simply append our own WHERE clause condition, like this:


We were, however, able to perform string concatenation. Injecting id=5'||'0'||'4 would give us the same response as id=504. We also discovered that id=54 would result in the following response:

Listing ID:
Listing Entity:
Listing Data:

Furthermore, we found that a syntax error, such as what id=' would cause, produced the following response:

An error has occurred. Please try again.

In Oracle, there exists a dummy table called ‘dual‘. We were able to use this table, in combination with string concatenation and a sub-query, to establish a boolean condition:


The URL encoding makes it look messy. Here’s the URL-decoded version of our injection:

5'||(SELECT 0 FROM dual WHERE 1=1)||'4

Because the WHERE clause of our sub-query evaluated to TRUE, the SELECT would return a zero, which got concatenated with the 5 and 4, and became 504. This injection resulted in Acme, Inc.’s information being returned in the page, and became our TRUE condition.

Now consider this injection (URL decoded for readability):

5'||(SELECT 0 FROM dual WHERE 1=0)||'4

Because the WHERE clause evaluated to FALSE, the SELECT returned nothing, which got concatenated with 5 and 4, and became 54. This injection resulted in blank listing data in the response, and was considered to be our FALSE condition.

The TRUE condition let us know when the WHERE clause of our sub-query evaluated to TRUE, and the FALSE condition told us the inverse. The error condition (described a few paragraphs above) let us know if there was a syntax error in our query (possibly due to characters being unexpectedly encoded).

Now that we had established a reliable true/false/error condition, we could start having some fun.

The Good Stuff

We were able to use the true/false condition to brute force some pieces of information that we figured the client did not intend logged-in users to obtain, such as the database name (from this point forward, all injections will remain URL-decoded for the sake of readability):


The above injection took the first character of the database and determined if it was a lowercase letter. If true, we’d get Acme, Inc.’s data in the response; if false, we’d get blank listing data.

We could quickly brute force each character by cutting our BETWEEN range in half for each test. For example, if the above injection resulted in a TRUE condition, then we could figure out which lowercase letter the database name started with by using the following process:

Cut the range of characters in half:


If the above resulted in a TRUE condition, then we knew the letter was between ‘a’ and ‘m’, and could then test for it being between ‘a’ and ‘g’. If the above resulted in a FALSE condition, then we’d drill down into the ‘m’ through ‘z’ range. In either case, we kept narrowing the search range until we were able to pinpoint the correct character.

We could then brute force the rest of the database name characters by incrementing the second parameter of the SUBSTR function (2 for the second character, 3 for the third, etc). If we incremented the parameter and got an error condition as a result, we knew we had surpassed the length of the database name.

We could also pre-determine the length of the database name with a similar “test and drill down” technique with this injection:


Once we had brute forced the entire value, we verified our finding with this injection:

5'||(SELECT 0 FROM dual WHERE SYS.DATABASE_NAME='dbmaster01')||'4

We were able to extract information from other tables, too, such as:

5'||(SELECT 0 FROM SYS.USER_USERS WHERE SUBSTR(username, 1, 1) BETWEEN 'a' AND 'z')||'4

Using this method, we were able to extract various pieces of information, such as the database name, database user (which the query was being executed with), database user ID, and default table space. However, Micheal and I were not happy stopping there. With the help of pentestmonkey.net, we discovered some interesting Oracle features that gave us some juicy insights into our client’s network.

The Juicy Stuff

Oracle has a couple of variables that allowed us to extract the server’s internal hostname and IP address: UTL_INADDR.get_host_name and UTL_INADDR.get_host_address, respectively. Using the same brute force technique described above, we were able to successfully extract the hostname/IP and verify our findings with the following injections:

5'||(SELECT 0 FROM dual WHERE UTL_INADDR.get_host_name='srvdb01')||'4
5'||(SELECT 0 FROM dual WHERE UTL_INADDR.get_host_address='')||'4

What we found even more interesting was the fact that UTL_INADDR.get_host_name would accept a parameter – an IP – and would allow us to basically perform DNS queries through the SQL Injection vulnerability:

5'||(SELECT 0 FROM dual WHERE LENGTH(UTL_INADDR.get_host_name(''))>0)||'4

If the above resulted in a TRUE condition, we knew the IP resolved successfully, and we could proceed with brute forcing the hostname. If the result was a FALSE condition, we’d presume the IP to be invalid.

Micheal and I, being the PHP fans that we are, collaborated with each other to automate the entire process. Several hundred lines of code later, we were able to quickly harvest dozens of internal IPs and corresponding hostnames – information that would come in quite handy for a network-level attack, or even for a social engineering approach (“Hi, I’m Matt from IT. I’m having trouble logging in to srvdb01 at IP Is the root password still “qSSQ[W2&(8#-/IQ4b{W;%us”?).

Conclusion & Take-Aways

Never assume that you know the full extent of the threat that a vulnerability represents to your organization. While you can reach a particular level of confidence in your knowledge and understanding of the situation, you never know what new and imaginative avenues of attack or combinations of techniques an attacker may discover or create – like mapping out your internal network through an SQL Injection vulnerability.

Imagination is more important than knowledge. For knowledge is limited to all we now know and understand, while imagination embraces the entire world, and all there ever will be to know and understand.” -Albert Einstein

The 3:00 A.M. Incident Response Phone Call − A Success Story

It’s 3:00 A.M., and you receive the dreaded IR phone call. Your CSO is demanding an immediate response to an attack on your company’s resources. Dreary and lethargic, you stumble out of bed and VPN into your network. You pull up your centralized log management and see that there have been literally thousands of requests to your website in the span of time that typically sees between 50 and 100 requests. You feel your heart rate pick up, your palms get damp….

You’re under attack.

You begin rummaging through your network changelogs for the past twenty-four hours, attempting to see if there have been any major changes to the infrastructure or major software roll-outs across the network. But you find there have been no network changes, and no previously unvetted software updates have been pushed. “Damn,” you mutter to yourself, “if only the problem were that easy to identify….”

Your fingers flash across the keyboard in a rush as your Chief of the Network Operations Center floods your instant messenger with requests for updates.

C-NOC: “I guess since you’re up at this ungodly hour, CSO has you running IR for the breach?”

Me@3: “Yeah, any word from the network side? Hopefully we’re not seeing any data exfiltration from internal, right?”

C-NOC: “No, just a metric ton of smtp requests coming from the log management…. What alert controls did you have in place in case of an attack?”

Me@3: ”Crap, sorry  John, guess I forgot to put the alert mail cap in place…wait a second, I have to go, John. I totally forgot to check one of the most obvious things!”

C-NOC: ”Ha, you forgot to check the WAF? Noob 😛 “

{C-NOC John has disconnected}

You have to love an environment where even the most severe problems result in good-hearted ribbing between colleagues.

You quickly surf to the URL where your WAF typically resides, and find the elegant interface filled with thousands of requests, which appear to be the result of someone running a fuzzer against the account information pages. It seems as if someone is attempting to SQLmap to iterate through all possible injections.

You laugh maniacally to yourself and lean back in your office chair, thoroughly satisfied with your department’s preparations for this very problem.  Just three weeks ago, you completed the transition from raw user interaction with the SQL database to a more secure parameterized transaction. As you pour yourself a bowl of cereal, you begin mentally drafting the incident report to your boss.

It’s going to be a good day.

Recent SQL Injection Hacks – Things You Should Know

SQL Injection, the remote root exploit for Web applications, has been the initial attack vector behind several high profile compromises over the last six months. MySQL.com, Sun.com, HBGary Federal, Comodo’s RA (GlobalTrust.it/InstantSSL.it), eHarmony, Nasdaq, Savannah GNU, PlentyOfFish, Royal Navy Website, BoingBoing, and no doubt countless others we’ll never hear about. Let’s also not forget if a website is serving up drive-by-download malware, as many thousands have already, chances are it’s because a SQL Injection exploit inserted a malicious iFrame. Clearly OWASP made a good call placing “Injection” at the top of the Top Ten.

Here are some quick tips to avoid becoming tomorrows headline and an end of the year statistic:

  1. If Parameterized SQL statements, not Stored Procedures, are used everywhere in the code, the odds of SQL Injection vulnerabilities will drop dramatically. Purge all forms of concatenated database query strings and add a healthy code of input validation. There is no substitute.
  2. Suppression of verbose error messages is still a good idea, but DO NOT do so just to get the vulnerability to “go away” in the application vulnerability scanner report. Fix your code. Don’t be fooled by vendors claim of Blind SQL Injection detection in scanning products. The lack of verbose error messaging remains serious hindrance to automated detection with painful side effects. Direct source code access the has advantage here on comprehensiveness — use to your advantage.
  3. Hack yourself first. That means ALL your websites. Not just the “main” ones. Learn what the bad guys know or eventually will. Attackers are quite capable and smart enough to compromise  secondary websites, use them as launching pads, and then pivot around the network.
  4. Detect any malware on website(s) before Google does. Failure to do so will get you black listed from search results. Give Dasient a look.

Yes there are many other things you can do to prevent SQL Injection, like detecting attacks with WAFs/IDS or database hardening procedures. Only let’s get some of the basics down first shall we?