adam's notes

Lab - Performing and Detecting SQL Injection

Scenario

In this lab, you will perform a SQLi (SQL injection) attack, then investigate the result.

As a cybersecurity analyst, you are working to discover weaknesses and vulnerabilities that your organization, Structureality Inc., needs to mitigate throughout its internal network. In this lab, you will perform SQL Injection (SQLi) against a client’s public-facing website hosted in their screened subnet to gain access to discover the details of the database structure and extract user information. Finally, you will investigate the website’s logs for evidence and IoCs (Indications of Compromise) related to SQLi activities.

Understand your environment

You will be working from a virtual machine named KALI, hosting Kali Linux, and a virtual machine named LAMP, hosting Ubuntu Server. You will initially use KALI to perform the attack targeting the DVWA website hosted on the LAMP VM, and then you will work from LAMP to perform the investigation.

Perform SQL Injection

SQLi (Structured Query Language injection) is an attack where DBMS commands are injected into a website to manipulate the backend database. This is a variation of injection attacks that takes advantage of a website’s weakness in allowing submitted code and commands to execute. This is often accomplished through the use of metacharacters (i.e., symbols with programmatic power) which are not being properly filtered or escaped (i.e., reverted back to basic symbols without programmatic power).

In this exercise, you will be acting like an attacker. First, you will probe a target website for vulnerabilities, then perform reconnaissance to learn configuration details, then finally perform data extraction from the website’s database.

This exercise uses the DVWA as the target of several database exploitations. However, you must first log into DVWA to access the various challenges. The DVWA has four difficulty levels (Low, Medium, High, and Impossible) and is set to the Low level by default.

DVWA or Damn Vulnerable Web Application is a safe and legal security playground that security professionals can use to improve their skills and learn tools and techniques related to web attacks and exploitations. DVWA is designed to be installed into a private (i.e., non-Internet) lab environment for internal use. Do NOT install DVWA on a production or an Internet-accessible system.

  1. Connect to the KALI and sign in as root using Pa$$w0rd as the password.

  2. Open Firefox by selecting its icon from the Kali Linux toolbar.

  3. Maximize the Firefox window.

  4. In the address field of Firefox, enter dvwa.structureality.com.

    If an error of Unable to connect is displayed, wait 30 seconds, then refresh the page. The LAMP VM may not have fully booted before you attempted to access the website.

  5. The initial page of DVWA is displayed along with the application’s login fields. Type admin and password into the Username and Password fields, respectively, then select Login.

    In a real-world situation, you would attempt to exploit any input field you discover. However, with DVWA, you must first log in to the application itself to access the attack target elements.

  6. The Welcome to Dann Vulnerable Web Application! page should be displayed.

    If you scroll to the bottom of any DVWA page, you will see a footer that indicates several values, including the security level. To change the security level, select DVWA Security from the left-side navigation menu bar, make a selection from the pull-down list, then select Submit. This lab assumes the default security level of Low.

  7. In the left-side navigation menu bar, select SQL Injection.

  8. The Vulnerability: SQL Injection page should be displayed.

  9. Type 1 into the User ID: field, then select Submit.

  10. The results should confirm that the User ID of 1 is the admin. Notice that the URL has changed to include parameters. It should look like the following:

    Type

    dvwa.structureality.com/vulnerabilities/sqli/?id=1&Submit=Submit#

    The URL displayed after a form field submission often reveals details about the variables used in the server-side script. In a real-world situation, you would use this information to predict how the code is crafted and work towards discovering SQL statements that will enable you to perform arbitrary commands against the database or its underlying OS. In this DVWA simulation, you could select the View Source button at the bottom of the page to see the actual code in use on the server. Knowing the server script code is immensely helpful in discovering vulnerabilities to take advantage of.

  11. Type 7 into the User ID: field, then select Submit.

  12. There should be no result — not even an error message stating that the User ID doesn’t exist in the database.

  13. Type in a single quote character (i.e., ’) into the User ID: field, then select Submit.

    This is a common test to determine if a website is filtering metacharacters. Most SQLi attacks use the apostrophe or single quote.

  14. This should return an error message stating “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1”. Because of this error, you now know that the website is not filtering metacharacters and is, therefore, vulnerable to SQLi. You also know that the DBMS is MySQL.

    The end of that error message statement will then show a series of five single quotes. This error message attempts to show you the syntax error in quotes. However, since the error is the occurrence of an odd number of quotes (i.e., you are causing the User ID string assignment to be three quotes instead of a number within quotes), the problem is the presence of three quotes (i.e., ''') which is then itself presented inside of single quotes.

    Discovering information from error messages is known as error-based SQLi. Whether you happen across error messages or inject statements that cause specific error messages to be displayed purposely is an effective means of gathering information to further your efforts in SQLi attacks.

  15. Select the ← (i.e., back button) on the Firefox toolbar to return to the Vulnerability: SQL Injection page.

  16. Test to see if you can exploit the server script through Boolean logic. Type the following into the User ID: field, and then select Submit.

    Type

    1' or '1'='1

    This is a typical initial SQLi method that uses logic to trick the target into revealing more information than its programmers intended. The concept is you are setting up an OR expression between a variable condition (i.e., whether a User ID exists) and a tautology (i.e., a statement of truth (e.g., 1=1)). The results of this statement will always be true regardless of whether the variable condition is true. This results in a lack of context for the remainder of the script, which instead of returning a single entry as intended, the script may return all entries.

    If you make a mistake in typing any of the SQLi statements and receive an error instead of the expected result, then use the back arrow on the Firefox toolbar to return to the Vulnerability: SQL Injection page and try again. Any mistakes you make will be reflected in the DVWA website access log on the LAMP host. You will see all of your submitted or injected values during the analyst exercise.

  17. The result of this submission should be a presentation of all five user accounts on this website.

    What user account name is not present in this SQLi result?

    • admin
    • Hack
    • Morgan
    • Bob
    • Pablo
    • Gordon

  18. A common next step is to determine the column query limit. You must know the column query limit to abuse it. This will require trial and error to determine. Enter the following into the User ID: field, then select Submit.

    Type

    ' ORDER BY 1#

    Most SQL injection statements start with a single quote (i.e., ’). This initial single quote is used to terminate the string (i.e., whatever data is normally typed into the input field) assignment to a variable in the script on the web server. This means whatever follows that initial single quote will be perceived by the web server as code instead of string input. Be sure you are typing in the leading single quote, then a space, then the “ORDER BY…” statement.

    The final octothorp (i.e., #) is used here as the end-of-line comment function for this MySQL target (you discovered the identity of this DBMS from the error message earlier). There are variations of SQL syntax between some DBMSes. For example, some DBMSes use double-dash (i.e., —) for this purpose.

    In many instances, you are limited as to the amount of data (i.e., number of columns) you can retrieve through a SQLi based on what the script is already programmed to do. So, you need to determine the number of columns retrieved from the table. While you may be able to guess this based on the results seen when you provided valid input, it may be the case that more values are being retrieved by the script than what is being displayed on the resulting web page.

  19. This should have no results. So, increment the number and try again.

  20. Enter the following into the User ID: field, then select Submit.

    Type

    ' ORDER BY 2#

    The SQL expressions used in SQLi do not need to be entered in all capital letters, but it is a common practice to do so anyway. This helps to differentiate the SQL expressions from the various object names or other command logic used in the longer and more complex SQLi statements. The SQL expressions commonly used in SQLi include ORDER BY, UNION, SELECT, UPDATE, INSERT, DELETE, and DROP. However, only some of these expressions will be used in this exercise.

  21. This should have no results. So, increment the number and try again.

  22. Enter the following into the User ID: field, then select Submit.

    Type

    ' ORDER BY 3#

  23. This should result in an error message of “Unknown column ‘3’ in ‘order clause’”. This indicates that the table has two (2) columns.

    With knowledge of the column limitation, you can now attempt to pull other data from the DBMS. To craft more advanced SQL injection queries, you need to know the name of the database and the name of the tables. Since the target’s DBMS is a MySQL variant, you can use knowledge of standard MySQL elements to access more data.

    Rather than assume you know everything about MySQL already, you are provided with several facts about default MySQL installations and use of SQL expressions in the following steps.

  24. Select the ← (i.e., back button) to return to the Vulnerability: SQL Injection page.

  25. Confirm the DMBS version by typing the following command into the User ID: field, then select Submit:

    Type

    ' UNION SELECT @@version, NULL#

    Some website configurations may block the display of error messages, especially those that would reveal DBMS configuration details. So knowing how to elicit the DBMS version is often helpful.

    The UNION SQL expression combines the operation of two or more SELECT expression statements. It is often used in SQLi to add an injected set of instructions to whatever the targeted system’s script would have executed in normal conditions.

    “NULL” is used as a placeholder in the second value position of the query.

  26. The results should show the DBMS version as “8.0.31-0ubuntu0.20.04.1” in the “First name:” field and the “Surname:” field should be empty.

  27. Type the following command into the User ID: field, then select Submit:

    Type

    ' UNION SELECT table_schema, table_name FROM information_schema.tables#

    This SQLi expression is attempting to request two columns of data (i.e., table_schema, table_name) from the default DMBS database information container of MySQL (i.e., information_schema.tables). The table_schema value will be the name of the database (displayed on the First name: lines), and the table_name will be the name of a table within the database (displayed on the Surname lines).

  28. The results should be a long list of database and table names. Since you know you are working against a DVWA website, the database of the same name is most likely being used. Look for the “dvwa” name in the “First name:” field and the names of the tables it contains in the “Surname:” field.

    Keep in mind that the server script controls the presentation and organization of the retrieved database, while your injected command only affects what data is retrieved. So, the server-determined data layout and labeling are still being used to present the data you pulled from the table.

    What is the first table name discovered from the DVWA database?

    What is the second table name discovered from the DVWA database?

  29. You now need to discover the column names of the tables. However, the fastest way to do that results in all columns from all tables being dumped at once. Type the following command into the User ID: field, then select Submit:

    Type

    ' UNION SELECT table_name, column_name FROM information_schema.columns#

    This SQLi expression is attempting to request two columns of data (i.e., table_name, column_name) from the default DMBS table information container of MySQL (i.e., information_schema.columns). The table_name value will be the table’s name (displayed on the First name: lines), and the column_name will be the name of a column within the table (displayed on the Surname lines).

  30. The results should be a long list of table and column names. You elect to focus on the users table. Look for the “users” name in the “First name:” field and the contained column names in the “Surname:” field. Unfortunately, the order presentation of the columns is not always consistent, nor are all the column names from the same table necessarily grouped together.

  31. Press CTRL+F on your keyboard to open the find function toolbar of Firefox. The find toolbar should appear at the bottom of the Firefox window.

  32. In the empty search term field of the Firefox find toolbar, enter name: users, and then select to mark the Highlight All checkbox.

    There should be twelve results of column names from the users table. However, you only care about seven of them (see Note). Select the up and down arrows on the Firefox find toolbar to move between the results to view them all.

    There are four results you can ignore, as they are not actual columns of the users table but are related to the operations and communications with the users table. These results will all be capitalized. They are: CURRENT_CONNECTIONS, TOTAL_CONNECTIONS, MAX_SESSION_CONTROLLED_MEMORY, and MAX_SESSION_TOTAL_MEMORY. The users table column names of interest will be listed in lowercase letters only.

    Which of the following are column names from the users table of the dvwa database? (Select eight column names)

    • user_id
    • avatar
    • failed_login
    • comment
    • first_name
    • last_login
    • last_name
    • name
    • password
    • permissions
    • users

  33. Close the Firefox find toolbar by selecting the X at the far-right end of the toolbar. Then, scroll to the top of the page (or press CTRL+HOME on your keyboard).

  34. With knowledge of the available columns in a table, you can now attempt to retrieve the data from those columns. So, you decide to pull user names and passwords from the users table. Type the following command into the User ID: field, then select Submit:

    Type

    ' UNION SELECT user, password FROM users#

  35. The results should be a presentation of all user accounts’ usernames in the “First name:” field and the corresponding password hashes in the “Surname:” field.

    At this point, you could export the enumerated password hashes to a file. Then use a password cracker to attempt to discover the passwords. This would be a similar task to what you have done in Assisted Lab: Exploiting Weak Cryptography.

  36. With eight columns of data, it will take several SQLi statements to retrieve all the data. But you might be able to squeeze several columns into a single result. Type the following command into the User ID: field, then select Submit.

    Type

    ' UNION SELECT CONCAT(user, ' ', avatar), password FROM users#

    Typing this into the small User ID: field and being able to review it can be challenging. You could opt to open Text Editor from the Kali toolbar, type in the command there, then cut-n-paste it into the web page’s User ID: field.

    The space between the retrieved values is represented in this expression by the space surrounded by single quotes. Be sure to type this in carefully.

    The CONCAT() function is used to concatenate or combine elements. In this SQLi expression, it is being used to combine two values from different columns into one result separated by a space. A comma followed by a space must separate each item in the value list.

  37. The results should display both the username and the avatar image’s URL-stem in the “First name:” field and the corresponding account’s password hash in the “Surname:” field.

  38. Type the following into the User ID: field, then select Submit:

    Type

    ' UNION SELECT CONCAT(user, ' ', avatar, ' ', first_name, ' ', last_name, ' ', last_login, ' ', failed_login, ' ', user_id), password FROM users#

  39. The results should display the values from all eight table columns. However, it may be a bit confusing as to what each value is.

  40. Customize the presentation of the results by including a custom sub-layout by typing the following into the User ID: field, then select Submit:

    Type

    ' UNION SELECT CONCAT('\n u: ', user, '\n a: ', avatar, '\n fn: ', first_name, '\n ln: ', last_name, '\n ll: ', last_login, '\n fl: ', failed_login, '\n id: ', user_id, '\n hash: ', password), NULL FROM users#

    The “\n” is the insert new line function.

    A single incorrect symbol (e.g., a period instead of a comma) or a misspelling of a field (e.g., failed_logon instead of failed_login) will cause an error. This command would be easiest to cut and paste from the Text Editor.

  41. The results should be a more pleasant output that is easy to understand and interpret.

    As you can see, SQLi attacks can become quite complex and tedious very easily. And the elegance of the output is dependent upon the injected command. You can retrieve data in a raw dump that is hard to understand or you can format the output to your preferences.

    At this point, you have performed some basic SQLi attacks against the DVWA. In the next exercise, you will investigate the website’s logs for evidence and IoCs of SQLi.

Investigate SQLi

You have received a report that several users claim that they think their accounts’ passwords have been compromised. There is also a report of a data dump on a hacker discussion forum containing several users’ personal information. As a cybersecurity analyst, you are tasked with investigating the issue. You suspect that the website was the target of a SQLi attack. In this exercise, you will investigate the log of the company’s website to see if you can find evidence or IoCs of SQLi.

  1. Connect to the LAMP virtual machine and sign in as lamp using Pa$$w0rd as the password.

  2. Elevate to use root privileges by entering: sudo su and then entering Pa$$w0rd as the password.

  3. Enter cd /var/log/apache2 to change into the apache2 log directory.

  4. Enter ls -l to view the log filenames, sizes, and timestamps.

  5. Enter less access.log to view the website’s access.log. Look over the log for anything interesting.

    When using the less file viewing utility, press the spacebar to view the next page. You can return to a previous page using b or scroll one line up or down utilizing the arrow keys.

    You need to ‘ignore’ the directory path element of “/vulnerabilities/sqli/” as this is the obvious name of the HTML document on the DVWA (Damn Vulnerable Web Application) that is designed to demonstrate SQLi. In a real-world situation, you will not see the term “SQLi” in the logs. SQLi attacks are usually more subtle than that.

    The Apache web server access log has two default log formats. The Common Log Format includes the following seven default fields:

    1. IP address of the client
    2. The identity of the client, but typically presented as only a hyphen (i.e., - )
    3. User ID of requesting user, but will be a hyphen when there is no established user context
    4. Date and time of the request (in square brackets)
    5. The HTTP request type (i.e., GET, POST, etc.) and the resource being requested
    6. The HTTP response status code
    7. The size of the object returned to the client

    The Combined Log Format includes the following two additional fields:

    1. The HTTP referrer (i.e., the address from which the request for the resource originated.)
    2. The User Agent of the client, which identifies information about the browser that the client is using to access the resource.

    It is also possible to customize the fields of the Apache logs.

    In this exercise, Apache is configured to use the Combined Log Format. Note: The User ID is a hyphen in the access.log for this exercise because when using the DVWA as the target, while you must log in as admin to access the vulnerable applications of the demo service, you are not using a user account or active login on most of the demonstration sub-pages.

  6. Starting from the top of the access.log file (i.e., the oldest entry in the log), look down through the entries to find the one with the following as its HTTP request:

    Type

    "GET /vulnerabilities/sqli/?id=1&Submit=Submit# HTTP/1.1"

    This was your first submission to the SQLi page of just the number ‘1’. On its own, this is a record that could be benign or an element of reconnaissance.

  7. Look at the referrer value for this log record.

    What is the HTTP referrer for this log record related to your first submission to the SQLi page of just the number ‘1’?

    • “GET /vulnerabilities/sqli/”
    • “GET /vulnerabilities/sqli/?id=7&Submit=Sumbit# HTTP/1.1”
    • “GET /vulnerabilities/xss_r/ HTTP/1.1”
    • “GET /vulnerabilities/sqli/?id=1&Submit=Sumbit# HTTP/1.1”

    The HTTP referrer indicates the URL of the page which was displayed in the browser of the user, which is the context from which the next URL is requested (i.e., the HTTP request). This record’s HTTP request was submitted to the web server from the ‘home’ page of the SQLi site.

  8. Look further down, maybe only a single record, to find the submission of the number ‘7’. The HTTP request should be:

    Type

    "GET /vulnerabilities/sqli/?id=7&Submit=Submit# HTTP/1.1"

    This was your next submission to the SQLi page of just the number ‘7’. On its own, this is also a record that could be benign or an element of reconnaissance. However, it begins to show a pattern of probing that could be considered SQLi pre-attack reconnaissance.

    Notice that this record’s referrer is the prior page which was the result of submitting the number ‘1’. For each remaining log record you look at, you should see this progression (i.e., the current HTTP request’s log record will have the prior page as the referrer). While it is possible to backtrack to an initial page before each SQLi submission, it is not that common typically, because the attacker needs information from an information query to then form the next SQLi command.

  9. The next log record should include the submission of a single quote. However, the log will not retain metacharacters. Instead, they will be converted to percent-encoded values. It is also possible that the SQLi statements will include pre-encoded percent encodings of metacharacters to avoid filters.

    You may need to consult a reference table to determine the characters being obfuscated by the log. Some recognition of percent-encoding will be necessary in order to interpret website log entries. Here is a partial reference table of commonly used percent-encodings related to SQLi:

    EncodingValue
    %20(space)
    %21!
    %22”
    %23#
    %27’
    %28(
    %29)
    %2b+
    %2c,
    EncodingValue
    %2f/
    %3a:
    %3c<
    %3d=
    %3e>
    %3f?
    %40@
    %5C|

    When dealing with hex values, such as those used in percent-encoding, the case of the hex letter is irrelevant. They can be lowercase or uppercase without issue. Thus, %3c and %3C are the same when they are resolved into the < character.

    What could be found in a website’s access log as a representation of a space in an HTTP request? (Select 2)

    • + (a plus sign)
    • %3c
    • %20
    • %22

    What is the percent-encoding for a single quotation mark?

    • %3e
    • %3a
    • %27
    • %21

  10. The next log record should contain the SQLi code of the following:

    Type

    1' or '1'='1

    This input will be encoded in the access log as:

    Type

    1%27+or+%271%27%3D%271

    The record will have a full HTTP request of:

    Type

    "GET /vulnerabilities/sqli/?id=1%27+or+%271%27%3D%271&Submit=Submit# HTTP/1.1"

    This is the first clear evidence of IoC of a SQLi statement. This is the injection of a logical operation that is intended to confuse the server-side script. In this instance, this injection string was submitted to a page to retrieve user information related to a User ID. As you recall, the result of this injection was a dump of all of the user accounts on the system.

  11. Next in the log, locate a record with the HTTP request of the following:

    Type

    "GET /vulnerabilities/sqli/?id=%27+ORDER+BY+1%23&Submit=Submit# HTTP/1.1"

    This is the first of three queries to determine the column query limit. This is another IoC observable that a SQLi attack is occurring.

  12. Next in the log, locate a record with the HTTP request of the following:

    Type

    "GET /vulnerabilities/sqli/?id=%27+UNION+SELECT+@40%40version%2C+NULL%23&Submit=Submit# HTTP/1.1"

    This is a SQLi statement used to determine the version of the DBMS running behind the web server. This is absolutely evidence of reconnaissance before initiating further SQLi attacks.

    Why is the octothorpe after the ‘NULL’ parameter used in this submitted SQLi statement?

    • end-of-line comment
    • carriage return and line feed
    • to append the command to the existing script statement
    • retrieve certain records from one or more tables.

  13. Next in the log, locate a record with the HTTP request of the following:

    Type

    "GET /vulnerabilities/sqli/?id=%27+UNION+SELECT+table_schema%2C+table_name+FROM+information_schema.tables%23&Submit=Submit# HTTP/1.1"

    This is a SQLi statement used to extract all of the table names from the default DMBS database information container of MySQL (i.e., information_schema.tables). This is how an attacker learns the names of all of the tables hosted by a website’s DBMS.

  14. Next in the log, locate a record with the HTTP request of the following:

    Type

    "GET /vulnerabilities/sqli/?id=%27+UNION+SELECT+table_name%2C+column_name+FROM+information_schema.columns%23&Submit=Submit# HTTP/1.1"

    This is a SQLi statement used to extract all of the column names from all of the tables from the default DMBS database information container of MySQL (i.e., information_schema.tables). This is how an attacker learns the names of all of the columns within each table hosted by a website’s DBMS.

  15. Next in the log, locate a record with the HTTP request of the following:

    Type

    "GET /vulnerabilities/sqli/?id=%27+UNION+SELECT+user%2C+password+FROM+users%23&Submit=Submit# HTTP/1.1"

    This is a SQLi statement used to extract the columns of user and password from the users table. This is how an attacker is able to exfiltrate the password hashes of users. Once the attacker obtains the password hashes, they can initiate password cracking and potentially discover user passwords. In this exercise’s scenario, this could be the means by which the attackers were able to take control of users’ accounts.

  16. Next in the log, locate the three records with an HTTP request which starts off with the following:

    Type

    "GET /vulnerabilities/sqli/?id=%27+UNION+SELECT+CONCAT(

    These are examples of SQLi statements which are extracting some to all of the columns of user information from the users table. This is likely the means by which the attackers were able to exfiltrate user details from the website, which was then posted to a hacker discussion forum.

    The primary IoC for SQLi is the use of SQL expressions. If you see any of the following terms in an HTTP request, then there is a high likelihood that SQLi is taking place. The following is a partial table of common SQL commands used in SQLi statements:

    SQL ExpressionDescription
    ORDER BYSort data in ascending or descending order.
    UNIONCombine the results of two or more SELECT statements.
    SELECTRetrieve certain records from one or more tables.
    UPDATEModify records.
    INSERTCreate a record.
    DELETEDelete a record.
    DROPDelete an entire table, a view of a table, or other objects in the database.

  17. When finished looking over the access.log, type q to exit the less viewer.

With the evidence you have discovered from the access.log, you have clearly discovered IoCs of SQLi.

Graph View