CODEWITHSUNDEEP
javasqlsql-injectionspring-jdbcveracode
Radek Chromík
Radek Chromík

Reputation: 31

Why does Veracode report CWE-89?

I have this SQL query written in JDBC which contains dynamic table and fields names:

    private String checkDimension(String tenantId, String prefix, Long schemaMetaId, Long transactionalMetaId,
                                  String dimension, String dimensionValue) {
        DataSource tenantDataSource = tenantDataSourceProvider.getTenantDataSource(checkEntityName(tenantId));
        try (Connection connection = tenantDataSource.getConnection()) {

            String sql = String.format("select * from \"%s_%s\" input_file join \"DIMENSION_VALUES_%s\" dv on " +
                            "(dv.\"DIMENSION\" = ? and dv.\"DIMENSION_VALUE\" = input_file.\"%s\") limit 1",
                    checkEntityName(prefix), checkEntityName(schemaMetaId.toString()),
                    checkEntityName(transactionalMetaId.toString()), checkEntityName(dimensionValue));
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setString(1, dimension);
                try (ResultSet resultSet = statement.executeQuery()) {
                    if (resultSet.next()) {
                        return "OK";
                    } else {
                        return "Values do not match";
                    }
                }
            }
        } catch (SQLException exception) {
            throw new IllegalStateException(exception);
        }
    }

For mitigation flaws I am using function checkEntityName which should silent them (make them false positive during Veracode scan)

    @SQLQueryCleanser
    static String checkEntityName(String entityName) {
        if (!entityName.matches("^[-a-zA-Z0-9_]*$")) {
            throw new SecurityException(String.format("Invalid name for entity: %s", entityName));
        }
        return entityName;
    }

But Veracode is still reporting this issue:

Flaw Id: 1433

Description: This database query contains a SQL injection flaw. The call to java.sql.Statement.executeQuery() constructs a dynamic SQL query using a variable derived from untrusted input. An attacker could exploit this flaw to execute arbitrary SQL queries against the database. The first argument to executeQuery() contains tainted data from the variable format(). The tainted data originated from an earlier call to java.sql.Statement.executeQuery.

Remediation: Avoid dynamically constructing SQL queries. Instead, use parameterized prepared statements to prevent the database from interpreting the contents of bind variables as part of the query. Always validate untrusted input to ensure that it conforms to the expected format, using centralized data validation routines when possible.

Can you please give me advice how to solve this? Thank you :)

Upvotes: 3

Views: 1945

Answers (1)

SmlCodes
SmlCodes

Reputation: 25

If it still gives an exception even using PrepareStatement try to sanitize the native SQL query string by using the below code.

private static String sanitizeQuery(String query) {
    return query
        .chars()
        .mapToObj(i -> (char) i)
        .map(String::valueOf)
        .collect(Collectors.joining());
}

Upvotes: 0

Related Questions