We tracked the following data from "orderBy" Parameter:
GET /spring-4-4.0.1.RELEASE/find_all_sorted_custom?orderBy=name
...which was accessed within the following code:
com.contrastsecurity.framework.spring.dao.UserDaoImpl#findAllUnsafe(), line 98
...and ended up in this database query:
SELECT * FROM users as x order by x.name asc
What's the risk?
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
The most effective method of stopping SQL injection attacks is to only use an
<a target="_new" href="http://en.wikipedia.org/wiki/Object-relational_mapping">Object-Relational Mapping</a> (ORM)
framework like Hibernate that safely handles database interaction. If you must execute queries manually, use java/sql/CallableStatement (for stored procedures) and 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 instead 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:
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 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.
Vulnerability ID: 3N0Q-Q9PK-4HII-VIZ8
Application Name: Pluto
Application Code: PLANET
Vulnerability Link: http://localhost:19080/Contrast/static/ng/index.html#/7c6cfec5-a187-4d5e-984a-d11d96d2ef63/applications/0f9338c6-352f-418b-9d89-696406640d91/vulns/3N0Q-Q9PK-4HII-VIZ8
What Happened?
We tracked the following data from "orderBy" Parameter:
GET /spring-4-4.0.1.RELEASE/find_all_sorted_custom?orderBy=name
...which was accessed within the following code:
com.contrastsecurity.framework.spring.dao.UserDaoImpl#findAllUnsafe(), line 98
...and ended up in this database query:
SELECT * FROM users as x order by x.name asc
What's the risk?
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
The most effective method of stopping SQL injection attacks is to only use an <a target="_new" href="http://en.wikipedia.org/wiki/Object-relational_mapping">Object-Relational Mapping</a> (ORM) framework like Hibernate that safely handles database interaction. If you must execute queries manually, use java/sql/CallableStatement (for stored procedures) and 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 instead 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:
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 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.
First Event
Last Event
HTTP Request
GET http://localhost:32834/spring-4-4.0.1.RELEASE/find_all_sorted_custom?orderBy=name HTTP/1.0 Contrast-Mq-Name: queue-063-SpringOrderByValidatorIT.it_makes_sure_unsafe_orderby_still_finds_vulns-contrastsecurity-docker.jfrog.io/contrast/spring-4:4.0.1.RELEASE-rev8 Host: localhost:32834 User-Agent: okhttp/3.9.1 Connection: Keep-Alive Accept-Encoding: gzip
References
https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html