using select statement in java database

Question

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'

read this: xkcd.com/327 you'll be convinced to use a PreparedStatement. — jlordo
– jlordo, Commented Aug 2, 2013 at 20:47

rgettman · Accepted Answer · 2013-08-02 20:46:06Z

2

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.

answered Aug 2, 2013 at 20:46

rgettman

179k30 gold badges282 silver badges365 bronze badges

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

Comments

StuPointerException · Accepted Answer · 2013-08-02 20:59:11Z

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.

kosa · Accepted Answer · 2013-08-02 20:53:03Z

1

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.

edited Aug 2, 2013 at 20:53

answered Aug 2, 2013 at 20:45

kosa

66.7k15 gold badges134 silver badges170 bronze badges

Collectives™ on Stack Overflow

using select statement in java database

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related