CODEWITHSUNDEEP
javaspringjdbctemplate
Kiti_123
Kiti_123

Reputation: 15

Error In JDBC Template Querying

I am trying to write this query in order to authenticate the username and password of my API but I am getting error column not found. Both username and passwords are strings and I am using MYSQL database. I think there is a error with quotations as username and password are strings. How can I rectify the below code or is there a better way to write the same. P.S - I am using spring MVC and this is my first project.

@Override
    public TypeInfo getRole(final TypeInfo typeinfo) {

            String sql =
            "select Role from registartion where UserName=\"" + typeinfo.getUserName() + "and Password=\"" + typeinfo.getPassword() + "/"";
            return jdbcTemplate.query(sql, new ResultSetExtractor<TypeInfo>() {

                @Override
                public TypeInfo extractData(ResultSet rs)
                        throws SQLException, DataAccessException {
                    if (rs.next()) {
                        System.out.println("VALID USER");
                        TypeInfo typeinfo1 = new TypeInfo();
                        typeinfo1.setUserName(typeinfo.getUserName());
                        typeinfo1.setPassword(typeinfo.getPassword());
                        typeinfo1.setRole(rs.getString("Role"));
                        return typeinfo1;
                    }
                    System.out.println("Not A valid user");
                    return null;
                }

            });
        }

I am getting a error that "select Role from registartion where UserName=******" column name ******* not found.

Upvotes: 1

Views: 3401

Answers (5)

Jens
Jens

Reputation: 69440

You have to use single qoutes around the column values:

"select Role from registartion where UserName='" + typeinfo.getUserName() + "' and Password='" + typeinfo.getPassword() + "'";

You should better use PreparedStatement. It is easier to read and safer (prevents sql injection).

Upvotes: 0

waiting_for_peace
waiting_for_peace

Reputation: 61

  1. Change the double quotes (including the escape characters) to single quotes.

  2. Close the single quote enclosing the user name (typeinfo.getUserName()). You need to keep a space between the closing single quote and the subsequent string.

If it still does not work then check the table names and column names. Maybe it is 'registration' and not 'registartion'? Or may be it is 'user_name' and not 'username'? Tips for beginners: Copy paste the sql string into any database browser, replace the variables with actual values and execute. Check for any errors. It is easier to fix errors this way.

And lastly, use parameterized sql queries to avoid sql injection. In my opinion parameterized queries reduces syntax errors too.

Upvotes: 0

Chamly Idunil
Chamly Idunil

Reputation: 1872

Try this. 

String sql = "select Role from registartion where UserName='" + typeinfo.getUserName() + "' and Password='" + typeinfo.getPassword() + "'";

Upvotes: 0

jmcg
jmcg

Reputation: 1567

That's not the way you should write your query.

JdbcTemplate uses an Object[] array as parameters, to avoid SQL injection.

code it somewhere in the lines of this:

    String user = "yourUser";
    String password = "yourPassword";
    final String sql = "SELECT * from FOO where username = ? and password = ?";
    Object[] sqlParameters = new Object[]{user, password};

   List<YourEntityClass> list = getJdbcTemplate.query(sql, new BeanPropertyRowMapper<Your Entity Class>(YourEntityClass.class), sqlParameters);

BeanPropertyRowMapper actually maps the values for you. just make sure your entity class has the same property names as the ones on your database

more info here: jdbcTemplate examples

Upvotes: 1

Mureinik
Mureinik

Reputation: 311018

The proper solution would be to use a PreparedStatement, in order to avoid having to mess with quoting and enhance security.

If you really must construct the statement by string concatination, you should note that string literals in SQL are denoted by single quotes ('), not double quotes ("):

String sql = 
"select Role from registartion where UserName='" + typeinfo.getUserName() + "' and Password='" + typeinfo.getPassword() + '";

Upvotes: 0

Related Questions