Java / SQL: MySQLSyntaxErrorException: Unknown column 'username' in 'where clause'

Question

I am stuck with the following code, and I don't see why I get an exception.

The string runs fine when I put it into the SQL field of phpMyAdmin.

And I receive a table as I expect, namely the table cid with 1 value, the playerID.

I also used this code in variations where I say "WHERE playerID = " +playerID and that works....

So am really excited why this does not work when I run it through eclipse.

Yes, I did search here, found a lot of similar MySQLSyntaxErrorException threads but none did lead me to a solution

public int getUserIDfromDBcoolpag(String inputUsername){
    System.out.println("getUserIDfromDBcoolpag called");
    int playerID = 0;
        try {
            SQLConnection.getInstance().init(DATABASE_HOST, DATABASE_PORT, DATABASE_NAME, DATABASE_USER, DATABASE_PASSWORD);
            Connection connection = SQLConnection.getInstance().getConnection();
            try
            {
                String getPlayerID = "SELECT coolpag.player.id as cid FROM coolpag.player WHERE username=" +inputUsername;
                PreparedStatement statement = connection.prepareStatement(getPlayerID);
                try
                {
                    ResultSet res = statement.executeQuery();
                    try
                    {
                        while (res.next())
                        {
                            playerID = res.getInt("cid");
                        }
//                      System.out.println("LOG: output operation finished");
                    }
                    finally
                    {
                        res.close();
                    }
                }
                finally
                {
                    statement.close();
                }
            }
            finally
            {
            }

            boolean isAlive = SQLConnection.getInstance().getConnection().isClosed();
            if(isAlive){
                System.out.println("Connection is not yet closed");
            }
        } catch (NullPointerException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return playerID;
    }

`inputUsername` must be quoted, you should use a paramaterized query for this. — Alex K., Jul 31 '15 at 14:10

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

instead of

String getPlayerID = "SELECT coolpag.player.id as cid FROM coolpag.player WHERE username=" +inputUsername;//inputUsername must be quoted

PreparedStatement statement = connection.prepareStatement(getPlayerID);

use

String getPlayerID = "SELECT coolpag.player.id as cid FROM coolpag.player WHERE username=?";

PreparedStatement statement = connection.prepareStatement(getPlayerID);
statement.setString(inputUsername);//to avoid sql injection

Avoid sql injection

edited May 23 '17 at 11:51

Community

1
1

answered Jul 31 '15 at 14:15

SatyaTNV

4,137
3
15
31

Hi there,thank you! I know that method and use it as well. What I wonder about is why the same (not as good as the setString method) query works in one case (using an int) and not in the other (as shown above). Anyway, thank you a lot! – N30 Jul 31 '15 at 14:20
`inputUsername` is `string` so i'm using `setString()` – SatyaTNV Jul 31 '15 at 14:23
@N30: you might as well ask why `int i=0;` works and `String text=abc;` doesn't – user158037 Jul 31 '15 at 14:29
No no, you got me all wrong. Of course I use setInt(playerID) when using an integer :) – N30 Jul 31 '15 at 14:48

Java / SQL: MySQLSyntaxErrorException: Unknown column 'username' in 'where clause'

1 Answers1