CODEWITHSUNDEEP
javadatabaseselectjdbcwhere-clause
question_1
question_1

Reputation: 112

using select statement in java database

I' m trying to get a specific data using the select where statement.I have a database that works fine. What i ve done till now is this

while (rs.next()){

            String username2=rs.getString("username");
            if(puname.equals(username2)) {
                System.out.println(puname);
                String queryPass= "select password from userdata where username="+username2;
                rs2 = st.executeQuery(queryPass);
                String passdb=rs2.getString("password");
                System.out.println("pass"+passdb);

end the error that i get is Unknown column 'john' in 'where clause'

Upvotes: 0

Views: 509

Answers (3)

StuPointerException
StuPointerException

Reputation: 7267

As pointed out in the other 2 answers, you're constructing a malformed SQL statement. This is one reason (but probably the least significant reason since it's fail-fast) that you shouldn't generate SQL this way.

By using a prepared statement:

//statement is a global\cached statement
if(statement == null) {
    String sql = "select password from userdata where username=?";
    statement = connection.prepareStatement(sql);
}
statement.setString(1, "John");
ResultSet rs = statement.executeQuery(selectSQL);

The database only has to work out the execution plan for the statement once (which is a costly exercise) and you are immune from SQL injection.

In your example, if I passed the value: 'stu' or 1=1 I would be able to execute the statement: 

select password from userdata where username='Stu' or 1=1

returning all the passwords in your database.

Upvotes: 1

kosa
kosa

Reputation: 66667

One issue I see is:

String queryPass= "select password from userdata where username="+username2;

should be (When String parameter, put it in single quote):

String queryPass= "select password from userdata where username= '"+username2+"'";

Note: It is advised to use PreparedStatments instead of plain SQL statements to avoid SQL Injection.

Upvotes: 1

rgettman
rgettman

Reputation: 178333

This is your resulting SQL string:

select password from userdata where username=John

It's not quoted, so the database thinks it's a column name.

You can use a PreparedStatement, and place a ? character where the value should be. Then you can supply the value with setString before executing the query. Using this technique (bind variables) prevents SQL injection, whereas simply quoting your value directly into the string does not.

Upvotes: 2

Related Questions