Tag: SQL Injection

  • SQLi Series: SQL Timing Attacks

    In the previous post we built a web page and connected it to a SQL server in order to test and learn about SQL injection. In the previous application the website returned data to the web page making it easy to gather information from the database as the info was printed out.

    What happens if the web application does not print the data from the SQL query. Well, there are still ways to gather data. SQLi attacks where the results are not displayed are referred to as Blind SQL Injection. Of course, this makes the attack more difficult, but these are by far the most common SQLi vulnerabilities, and attackers don’t stop just because they take extra effort.

    One such way is using timing. MySQL servers have a Sleep function which causes the server to sleep for the specified number of seconds. You can use this in conjunction with comparatives allowing the dumping of the database.

    A Refresher

    We’re using basically the same application as last time, except that this time the application only returns success or failure depending on whether the username and password entered are correct or not. As a side note, a success fail message can be used much the same way, but this blog will discuss timing.

    This is the response when the username and password entered were correct:

    A success when the username and password are correct

    And this is the response when the username and password did not match:

    A failure when the password is incorrect

    Now we can login as we did last time by closing the SQL quote and commenting out the rest of the query, but we’ve already gone over that in the first blog in this series. So let’s explore dumping information from the database instead.

    Success when the password is incorrect using a SQLi attack

    Useful SQL Functions & Clauses

    In order to pull information from the database we will use a number of MySQL commands and features.

    SLEEP() Function

    The SLEEP function will cause the server to wait for the specified number of seconds before returning the information.

    Example from the command line:

    The SLEEP function

    As we can see the query takes five seconds to complete.

    SUBSTRING() Function

    We will need a way to test one character at a time, so we need a way to get one character from the returned info so we can compare it. For this we use SUBSTRING():

    SUBSTRING(String, Position, Length)
    The SUBSTRING function

    IF() Statement

    This is how we branch in MySQL.

    IF(Condition, Value if true, Value if false)
    An IF statement

    For the Value if true and Value if false we can do more than just add return values. For instance, we can put the SLEEP function right in the IF function.

    Using the SLEEP function in an IF statement

    We can see that, when the condition was true, the server waited for five seconds.

    COUNT() Function

    There will be times when we need to know how many of a thing we have. For instance, we might need to know how many columns are in a table or how many rows.

    Now, in the database I’m using for testing, I know that there are three columns in the users table.

    Here is an example using COUNT showing that.

    The COUNT function

    DATABASE() Function

    We can get the current database in use by calling the DATABASE() function:

    The DATABASE function

    Querying Database Schemas

    If, for some reason, you need to pull the databases manually, maybe because one isn’t set or you want to see what else is out there, you can use this query:

    SELECT table_schema FROM information_schema.tables WHERE table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' ) GROUP BY table_schema;
    Querying the database schema

    We should note that default databases are removed by the NOT IN() phrase.

    Getting Tables

    We can query the information_schema database to get tables in a database:

    SELECT table_name from INFORMATION_SCHEMA.tables WHERE table_schema='DATABASE';
    Getting tables using the information_schema

    Getting Columns

    We can also query the information_schema database to get the column names in a table:

    SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='DATABASE' and TABLE_NAME='TABLE_NAME';
    Getting columns using the information_schema

    Comparative Queries with the LIKE & BINARY Functions

    = does not always mean equal. With the equal sign we can see how a capital A and a lowercase a are equal. Which is not true in a case sensitive language.

    An IF statement showing that the equal sign doesn't always mean "equals"

    To get around this we can use LIKE BINARY to compare. Here we find that a capital A and a lowercase a are not the same:

    Using LIKE BINARY to find strings that are exactly equal

    CAST() Function

    Sometimes when comparing things, it helps to cast items to a known type.

    Here is an example of casting 9 to a character:

    The CAST function

    LENGTH() Function

    When trying to figure out what a string is, it helps to get the length of the string:

    The LENGTH function

    LIMIT & OFFSET Clauses

    Given that we are using Blind SQL, we can really only test one thing at a time. This is where limiting the amount of returned data comes in handy with the LIMIT clause.

    The LIMIT clause

    We can step down the list using the OFFSET clause. Note that we increase the offset to one less than the count as that will be the last item.

    Using the OFFSET clause

    Bringing It All Together

    Now that we have all the tools we need, let’s put them together and pull info from the database.

    Basically, we will check character by character. First thing we would want to find is the database name. We should probably first figure out how long the database name is.

    Since we are using conditionals, it might be easier to use the username part of the query, that way we don’t need to have the right password.

    Closing off the SQL code after the username so that we don't need to know the password to perform our attacks

    First we see if the length is 1. It’s not, as the response comes back in less than five seconds.

    Discovering that the length is not 1

    Next we try 2, 3, and 4. We find out that 4 is correct, as the application takes longer than five seconds to respond.

    The Length is 4

    Now we need to figure out the letters in much the same way. Now we use the SUBSTRING function to test one letter at a time.

    Testing Letters for the Database Name

    To make things easier, I used Burp Intruder to send the letters automatically, instead of manually.

    We find that the letter S takes five seconds to respond. Now we know the first letter is S.

    Discovering that the first letter is "S" because there is a delay

    Next step is to test the second character.

    Testing the second character

    And we find that the second letter is Q.

    Finding that the second letter is "Q" using a timing attack

    Now, since I created the application, I know the database name is SQLi so let’s move on to getting table names.

    First we use some weird wizardry to discover the number of tables in the database, combining several of the functions we saw above:

    Getting the number of tables in the database

    Here we are getting the count of tables in the SQLi database. We find that there is only one table.

    Now let’s get the table name.

    Let’s start with getting the length of the table name.

    Getting the Length of the Table Name

    We find that the length of the name is five. With the length we can start grabbing the chars.

    Here’s the query we will use.

    Getting the table name one character at a time

    Basically, we are asking for all the table names for tables in the SQLi database. We grab the first one and then use substring to test one character at a time.

    Using Burp Intruder we find the first character is u. Repeating we find that the table name is users.

    Using Burp Intruder to find the character "u" with a timing delay

    Note:

    When retrieving names with this method, knowing the length is not truly required. When trying to compare to additional characters – say position six in the table name – it will always return false, meaning that the delay will never occur. If all the possible results stay under the delay, we know that we have the entire string. I like the idea of using the length to make sure I don’t miss something, but it’s not absolutely necessary.

    Now that we have the table name, it’s time to start getting data from the table itself. First, we need to know how many columns there are in the table.

    Finding There are 3 Columns

    When using the COUNT function to learn the number of columns, we find that there are three, as that’s when the sever takes more than five seconds to respond. With the number of columns in hand, let’s get those column names.

    This is similar to getting the table name but just querying different information.

    Here we get the length of the first column name:

    Finding Length of First Column Name

    And next the column name itself.

    Getting the Column Name

    Since we are getting information in the same way, this is very repetitive, so I’m going to assume you get the idea and go through this quickly.

    As an example, I’ll show how to get the second column’s information, which just means adding an OFFSET to the limit:

    Getting Length of Second Column

    Here we get the first letter of the second column:

    Testing Name of Second Column

    The second column is password, so, as expected, we find that the first letter is p.

    With all the table information, now we just need to start grabbing the data from the table. We can start by seeing how much data is in a table.

    Since this is a small test database, there isn’t a lot of data, so we can count the number of items and compare it to numbers we retrieve easily. On larger sets you may have to be more careful or smarter in gathering info. But this is a basic writeup giving the ideas, and I’ll leave that as an exercise to the readers.

    With this database, we find that there are only three records in the table:

    Getting Amount of Data in Table

    Now let’s get the first username from the table:

    Getting the Username

    And finally, we get their password:

    Getting the Password

    In Conclusion

    Timing attacks, as with all Blind SQLi, take a good deal of time and patience, but the rewards can be discovering credentials to login to the database or sensitive customer information like PII (Personally Identifiable Information) and financial data, like credit card numbers.

    As with all injection attacks, the remediation is to always validate user input. Raxis recommends keeping a list of whitelisted characters and deleting other characters before they process against the database. Cleansing data to be certain that user input is always treated as text (and not run as a command) is also key to this process.

    Understanding how to perform attacks like these are critical for web and mobile application penetration testers, just as understanding the idea of how they work is key for application developers so that they can build safeguards into their apps to protect their data and their users.

  • SQLi Series: An Introduction to SQL Injection

    Web applications often need to store data to better serve their customers. An example would be storing customer login information or comments submitted by users on the webpage. There are many ways of storing data for customers, but a popular way is to store the information in a SQL database.

    A common and basic use of web applications and SQL databases is to handle user login information and functionality. In many web applications a user submits a username and password into a form. The web application takes the submitted data and searches the database to determine if it’s a valid credential set. If it is, then the web application will log the user in.

    SQL Injection

    If the web application does not properly handle user input, an attacker might be able to create malicious input that changes the SQL query that performs that login task behind the scenes. Such SQL injection attacks take a lot of manual effort to discover and exploit, but they are a critical part of the web application penetration tests that we perform at Raxis.

    This blog explains how an attacker could find and exploit a SQL injection, or SQLi, vulnerability.

    Creating an Exploitable Login Webpage

    Let’s build a simple web application that asks for a username and password and returns the user’s ID. In order to help show the SQL injection attack, the application will also show the query used and the input from the login page. The SQL code the application uses (with user input parameters filled in) can be seen at the bottom of each screenshot.

    First let’s take a look at the results for the admin logging in normally.

    Successful login showing SQL query

    We can see that the user entered the admin username and password. We also see that our sample PHP web application uses a query where the username and password must match for success. If both match then, for our testing purposes, it returns the user ID which is printed out at the bottom.

    Here the admin’s account has the ID of 1.

    In the next example, the admin username is entered correctly, but the wrong password is supplied.

    Unsuccessful login showing the SQL query

    Again we can see the information that was input into the application and the query. However, since the password does not match the one in the database, we don’t get the user ID.

    Exploiting the Webpage to Login

    In the example queries above we saw that the username and password input was passed into the query without modification.

    So what happens if we close the single quote around the username?

    Attempted SQL injection with a single quote

    We see that, even though the correct password was entered, the single quote at the end of the username prevents the query from returning a correct result. In this case we get the same error message as if the username and password were incorrect, but other web applications might crash in different ways indicating there was a backend issue.

    In MySQL the # symbol denotes a comment, which makes the database ignore everything after it. Let’s try adding a # after that single quote we just added:

    Successful SQL injection bypassing the password

    Now we see that, even though we submitted the wrong password, the application considers the SQL query successful and returns the correct user ID. The question is What is happening here?

    Let’s take a closer look at the query and the input from the user.

    The input in the username field has injected two special SQL characters: the ’ (single quote) and the #.

    This changes the query itself because the user input is directly inserted into the query.

    Highlighting that the MySQL # comment symbol allowed the SQL injection to work

    The single quote closes the username entry so that the text that comes after is read as part of the SQL query. This means that the # is interpreted as the comment symbol, meaning that the rest of the query is simply ignored.

    Basically, that means that the query is now just running as 

    SELECT id, username, password FROM users WHERE username=’admin’

    Since the admin user exists, we get the successful result of the user id of 1, even with the wrong password.

    This also means that we can login as any user, provided that we know their username. Just change the admin username to the desired username.

    Using the same injection string to login as another user

    Exploiting the Webpage to Get Data

    Now logging in as any user is fun and all, but what else can we do? Well, we can come up with SQL queries that dump information from the database.

    Let’s use the UNION operator to inject another SQL query that goes along with the query created by the application. We should note that, since the sample web application only shows one column at a time, we need to switch what we are asking for first so that the application will show us more information.

    Here is what happens when we add a UNION SELECT for all the user IDs from the users table to the SQL injection we are entering in the username field:

    SQL injection adding UNION SELECT to get a list of all user IDs.

    After asking for the IDs, we enter a new SQL injection and ask for the usernames:

    SQL injection adding UNION SELECT to get a list of all usernames.

    And finally, we perform a SQL injection requesting the passwords:

    SQL injection adding UNION SELECT to get a list of all passwords.

    A Quick Note on Password Hashes

    This is a good time to note why using password hashes (instead of saving passwords in plaintext) is a good idea. If password hashes were in use, an attacker would have to crack the passwords in order for them to be useful in a SQL injection attack.

    Automated Exploitation with SQLMap

    While we can do attacks such as these manually, sometimes, after identifying a vulnerability, it is easier to use tools to exploit it for you. In real life scenarios, SQLi attacks don’t return the SQL statement, and it takes some trial and error to discover how the application is reacting to our input.

    My go-to tool for SQL injection attacks once I find a sign of them is always SQLMap. Here is a screenshot of SQLMap dumping the users table from the SQLi database the web application above uses.

    The SQLMap tool performing the same exploit we did manually

    SQLMap makes it a lot easier to dump information from a database when an application is susceptible to SQLi attacks. While here it dumped the same information we just did, it is capable of finding every table and column and dumping everything.

    More Than SQLi

    We should also note there are other ways of getting information from a database, including timing attacks in which a database waits to respond if something is true while responding quickly if it’s not. Timing attacks allow us to guess what input is valid and what is not valid. Maybe we will take a look at a timing attack example in another post.

    In Conclusion

    Now that we have a basic understanding of what SQL injection is and the types of exploits that can be done with it, my next posts in this series will go into specific attacks and how to perform them.

    As always, remember that these tutorials are guides for penetration testers and people looking to understand their penetration test results better. Attempting these attacks on any sites that don’t belong to you or where you don’t have legal documentation granting you access to perform ethical penetration testing is illegal and punishable under law.