SQL Injection from "password" Parameter on "attack" page

[sourabhkatti]

Trace UUID: Q6IU-HB9L-Q48T-Y5O9

https://app.contrastsecurity.com/Contrast/static/ng/index.html#/e264d365-25e4-409e-a129-ec4c684c9d50/vulns/Q6IU-HB9L-Q48T-Y5O9/overview

Description

We tracked the following data from "password" Parameter:

POST /webgoat/attack?Screen=1537271095&menu=1100&stage=1

password=1%27+OR+us&employee_id=101&action=Login

...which was accessed within the following code:

org.owasp.webgoat.plugin.sqlinjection.LoginSqlInjection#login(), line 132

...and ended up in this database query:

SELECT * FROM employee WHERE userid = 101 and password = '1' OR us'

Risk

With a maliciously crafted input, an end user could change the structure of the SQL query and perform a SQL Injection attack. SQL injection is possible when developers hand-build SQL statements containing user-supplied data without validation or encoding. The goal of such attacks is to force the database to retrieve and output data to which the user would not otherwise have access. For example, an attacker could use SQL Injection on a vulnerable application in order to query the database for customer credit card numbers and other data, even if it wasn't part of the query the developer created. SQL injection also allows privilege escalation, account hijacking, and in some cases, it may be possible for an attacker to gain shell access to the database server.

Recommendation to fix this finding

The most effective method of stopping SQL injection attacks is to only use an http://en.wikipedia.org/wiki/Object-relational_mapping$$LINK_DELIM$$Object-Relational Mapping (ORM)
like http://www.hibernate.org$$LINK_DELIM$$Hibernate
that safely handles database interaction. If you must execute queries manually, use
http://docs.oracle.com/javase/6/docs/api/java/sql/CallableStatement.html$$LINK_DELIM$$CallableStatement

(for stored procedures) and
http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html$$LINK_DELIM$$PreparedStatement

(for normal queries). Both of these APIs utilize bind variables. Both techniques completely stop the injection of code if used properly.
You must still avoid concatenating user supplied input to queries and use the binding pattern to keep user input from being
misinterpreted as SQL code.

Here's an example of an unsafe query:

String user = request.getParameter("user");
String pass = request.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = '" + user + "' and user_password = '" + pass +"'";
try {
  Statement statement = connection.createStatement( );
  ResultSet results = statement.executeQuery( query ); // Unsafe!
}

Here's an example of the same query, made safe with PreparedStatement:

String user = request.getParameter("user");
String pass = request.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = ? and user_password = ?";
try {
  PreparedStatement pstmt = connection.prepareStatement( query );
  pstmt.setString( 1, user );
  pstmt.setString( 2, pass );
  pstmt.execute(); // Safe!
}

There are some scenarios, like dynamic search, that make it difficult to use parameterized queries because the order and quantity
of variables is not predetermined. If you are unable to avoid building such a SQL call on the fly, then validation and escaping all
user data is necessary. Deciding which characters to escape depends on the database in use and the context into which the untrusted
data is being placed.

This is difficult to do by hand, but luckily the https://www.owasp.org/index.php/ESAPI$$LINK_DELIM$$ESAPI library offers such functionality. Here's an example of safely encoding a dynamically built statement for an Oracle database using untrusted data:

	Codec ORACLE_CODEC = new OracleCodec();
String user = req.getParameter("user");
String pass = req.getParameter("pass");
String query = "SELECT user_id FROM user_data WHERE user_name = '" + 
     ESAPI.encoder().encodeForSQL( ORACLE_CODEC, **user**) + "' and user_password = '" +
     ESAPI.encoder().encodeForSQL( ORACLE_CODEC, **pass**) + "'";

It's also helpful to ensure that the application is granted only the minimum database privileges necessary to perform its function. This may help reduce the impact of a successful SQL injection attack. At a minimum, access to powerful database APIs that interact with the operating or file systems should be revoked.