Prepared Statement with SQL Query issue

Question

The code displayed below is used to search through job listings, the prepared statement was added in to prevent SQL injections which it is vulnerable too. The query statement search worked before adding the prepared statement, however, now returns nothing. Can anyone see what I'm doing wrong? Thanks. The previous code looked like this:

try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/SQL_DB", "root", "root"); Statement stmt = con.createStatement()) { rs = stmt.executeQuery(queryStatement);

The code now looks like this:

public List<jobs> searchjobs(@RequestParam String query) {
    log.debug("REST request to search jobs for query {}", query);


    String queryStatement = "SELECT * FROM jobs WHERE description like '%" + query + "%'";
    log.info("Final SQL query {}", queryStatement);

    ResultSet rs = null;

    try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/SQL_DB", "root", "root");
        PreparedStatement stmt = con.prepareStatement(queryStatement)) {
        stmt.setString(1, query);
        rs = stmt.executeQuery(queryStatement);
        return extractjobs(rs);
    } catch (SQLException ex) {
        log.error(ex.getMessage(), ex);
        return new ArrayList();
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException ex) {
            log.error(ex.getMessage(), ex);
        }
    }
}

please post ALL the previous code, not only the connection string, most probably there is a problem when you stmt.setString(1, query) since the query has no parameter — Edoardo
– Edoardo, Commented Mar 17, 2020 at 15:52

Your Common Sense · Accepted Answer · 2020-03-17 15:36:21Z

1

You missed the essence of a prepared statement. When using it, you are substituting all variables with placeholders like this

String queryStatement = "SELECT * FROM jobs WHERE description like ?";

and then you can bind the query variable like you did. Just add those percent characters to it first

stmt.setString(1, "%" + query + "%");

edited Mar 17, 2020 at 15:36

answered Mar 17, 2020 at 15:26

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RobertS Over a year ago

Sorry, not really sure what you mean. Could you please show me in the context of the question?

RobertS Over a year ago

Still returning nothing unfortunately

Your Common Sense Over a year ago

OK. Change the other line accordingly. as shown in my answer. Can you do that?

Bill Karwin · Accepted Answer · 2020-03-17 15:34:11Z

You might have read the common advice "use prepared statements to protect against SQL injection." But this advice is only half of the truth.

The full truth is that you should use query parameters to separate untrusted content from your query. It just happens that using query parameters requires you to use prepared statements, so you can separate the prepare step from the execute step, and bind parameters to your prepared query in between these two steps.

This helps protect against SQL injection because the prepare is when your query is parsed. If you keep untrusted content separate from the query until after it's parsed, then the untrusted content cannot introduce SQL injection.

Your query has no parameter placeholders. You're concatenating your untrusted content into the statement just like before, which means it still has an SQL injection vulnerability.

String queryStatement = "SELECT * FROM jobs WHERE description like '%" + query + "%'";

Should be:

String queryStatement = "SELECT * FROM jobs WHERE description like ?";

Since your query has no parameter placeholders, it should be an error when you attempt to bind to it:

    stmt.setString(1, query);

Does this throw an exception? You seem to log it, so check your log.

Collectives™ on Stack Overflow

Prepared Statement with SQL Query issue

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related