Mysql DB select from table where field statement java

Question

I'm trying to query database using java, I don't understand the problem with this function. it return a garbage values.

I just want to retrieve from a MySQL database the values matching the first_name.

public List<Customer> select(String cusDB) {
    return jdbcTemplate.query(
            "SELECT id, first_name, last_name FROM customers WHERE first_name= cusDB",
            (rs, rowNum) -> new Customer(rs.getLong("id"),
                    rs.getString("first_name"), rs.getString("last_name")));
}

I'd be very surprised if this would return "garbage values", I'd sooner expect an exception about an unknown column cusDB or something like that. — Mark Rotteveel
– Mark Rotteveel, Commented Feb 15, 2017 at 10:03

Youcef LAIDANI · Accepted Answer · 2017-09-10 18:27:15Z

3

You can use two ways the first is to concatinate your query with your first_name that you want to search:

"SELECT id, first_name, last_name FROM customers WHERE first_name= '" + cusDB + "'"

Second use PrepapredStatement like so :

"SELECT id, first_name, last_name FROM customers WHERE first_name= ?"
st.setString(1, cusDB);

But i don't see any sign about PrepapredStatement so you can learn here Prepared Statement doc

Edit

Like @André Schild said in the comment :

you are vulnerable to SQL injections, for example a firstname with '; delete from customers; //will remove all customers from your database. Always(tm) use prepared statements

edited Sep 10, 2017 at 18:27

answered Feb 15, 2017 at 9:11

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

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

2 Comments

André Schild Over a year ago

Don't use the first version, you are vulnerable to SQL injections, for example a firstname with '; delete from customers; //will remove all customers from your database. Always(tm) use prepared statements

Youcef LAIDANI Over a year ago

yes @AndréSchild for that i suggest to use PreparedStement, i think he miss some thing about String concatenation for that i post the first one thank you so much

Mark Rotteveel · Accepted Answer · 2017-02-15 10:02:47Z

You can't just have the name of a Java parameter in the query string. You need to provide the parameter to the query explicitly. To do this, change your code to:

public List<Customer> select(String cusDB) {
    return jdbcTemplate.query(
            "SELECT id, first_name, last_name FROM customers WHERE first_name= ?",
            new Object[] { cusDB },
            (rs, rowNum) -> new Customer(rs.getLong("id"),
                    rs.getString("first_name"), rs.getString("last_name")));
}

That is, you introduce a parameter placeholder (?) in the query string, and add an array of parameter values to the method call (one value for each parameter placeholder in your query). See also the JdbcTemplate documentation and documentation of JdbcTemplate.query(String sql, Object[] args, RowMapper<T> rowMapper).

Collectives™ on Stack Overflow

Mysql DB select from table where field statement java

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related