Exercise Instructions

SQL Injection


In this interactive tutorial you will understand how SQL injection attacks are used to compromise the security of a web application, and how to write code more securely to protect against this type of attack.


1. Exercise Background

The vulnerable application pane loads the TradePORTAL application, an online trading platform. Registered users of the system can login to buy and sell stocks, bonds and currencies. Alice is a trader and registered (legitimate) user of the application.



Action

Click "Next" to continue.




Next

2. SQL Query Logs

The live log pane displays real-time SQL database queries that are generated and logged by the TradePORTAL application.



Action

Click "Next" to continue.




Next

3. Logging In

Alice tries to login to the application with the following credentials:

Username: alice@bank.com

Password: alice123

Note: Keep an eye on the live log pane when attempting to login.



Action

Use Alice's credentials to login.




4. Bad Input

So the password alice123 doesn't seem to work for Alice's account. Before contacting the administrator to reset her password, Alice tries entering the same password followed by a single quote ' character. Something like:

Username: alice@bank.com

Password: alice123'



Action

Use Alice's credentials to login. Note: this time add the single ' to the end of Alice's password.




5. Strange Error

Something broke. Adding the single quote to the password caused the TradePORTAL application to crash with a HTTP 500 Internal Server Error

Looking at the live log pane, this seems to have been due to the SQL syntax error

Read the error log output carefully. Do you think the single quote ' in Alice's password caused this error?



Action

As Alice click "Back to Login" button to continue with the exercise.




6. Authentication Logic

To understand why the error occurred, let's first analyze TradePORTAL's authentication method, specifically the code responsible for checking Alice's authentication credentials.



Action

Click below to continue with the code walkthrough.




7. Building the SQL Query

To further understand how Alice's input is used to build the SQL statement used for authentication, let's first simplify the authentication source code for the TradePORTAL application.



Action

Click below to simplify our authentication code.




8. Understanding Injection

Step 1: Enter the password alice123' and watch the code window.
Notice how the single quote ' you appended is interpreted by the SQL server as a string delimiter.
However, when the query is processed, the last quote does not have a closing/matching ' character, which causes a SQL syntax error , resulting in the HTTP 500 Internal Server Error

Step 2: Now try logging with a password followed by two single quotes. e.g. alice123''
Interestingly, the application does not error in this case.



Action

1. Enter the password alice123' and watch the code window.

2. Enter the password alice123'' to continue with the exercise




9. Bypassing Authentication

At this point we know that injecting characters interpreted by the database server is known as SQL Injection

However, its not just ' characters that can be injected, entire strings can be injected. What if this could be used to alter the purpose of the SQL statement entirely?

Try entering the following credentials:

Username: alice@bank.com

Password: ' or 1=1)#

Note in MySQL the # character is used for code comments. Keep an eye on the code window, everything to the right of the # character is commented out, including the extra ' and ) character.

NB: The provided SQL payload is the one you must use



Action

Inject SQL by using the supplied username and password string and see if you can bypass TradePORTAL’s authentication




10. SQL Injection Explained

Authentication was bypassed, but why? Recall the following inputs were submitted:

Username: alice@bank.com

Password: ' or 1=1)#

The above resulted in the following SQL statement:
SELECT *
WHERE (email = 'alice@bank.com'
AND password = '' OR 1=1)#

Because the statement is both syntactically valid and OR 1=1 always returns true, the authentication mechanism was bypassed. Let's now analyse the vulnerability from a code perspective.



Action

Click below to continue with the code walkthrough.




11. Remediation

Prepared statements (aka parameterized queries) are the best mechanism for preventing SQL injection attacks.

Prepared statements are used to abstract SQL statement syntax from input parameters. Statement templates are first defined at the application layer, and the parameters are then passed to them.

In Java this can be achieved using a PreparedStatement class for sending SQL statements to the backend database.

Aside from a better security posture against SQL injection attacks, prepared statements offer improved code quality from a legibility and maintainability perspective due to separation of the SQL logic from its inputs.



Action

Click below to apply fix.




12. Conclusion

In this module we learned how and why basic SQL Injection attacks work.

We also learned that when a SQL Injection attack is successful it can be used to seriously compromise an application.

We also learned that using Prepared Statements in your code are a good defense against this type of attack.



Action

Click "Next" to complete the exercise.




Next
BROWSER LOCKED
Live Log
Code
Well done !

Sales Form