SQL query with error in Java [closed]

Question

SQL query with error in Java [closed]

Navigation

#1 by (5 votes)
#2 by (3 votes)

1

I am trying to make a webapp in java, but I am a beginner, in the part of login of the app, I created a routine that receives as parameter an object and performs a search in the database according to the object that contains login = otavio e password = 123.

My bank has a table named users where it has 2 fields, user and password.

public boolean login(Cliente cliente)throws SQLException, Exception{
       String comando = "select senha from usuarios where usuario="+cliente.getLogin();       
       PreparedStatement stmt = con.prepareStatement(comando);      
       ResultSet rs = stmt.executeQuery();
       while(rs.next()){
           if (cliente.getSenha().equals(rs.getString("senha"))){
               return true;
           }
       }
       return false;
    }

But this is giving this error:

Exception in thread "main" java.sql.SQLSyntaxErrorException: A coluna 'OTAVIO' não está presente em nenhuma tabela da lista FROM, ou aparece dentro de uma especificação de junção e está fora do escopo da especificação de junção, ou aparece em uma cláusula HAVING e não está na lista GROUP BY. Se esta for uma instrução CREATE ou ALTER TABLE, então, 'OTAVIO' não é uma coluna da tabela de destino.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
at persistencia.Usuario.login(Usuario.java:32)
at Control.teste.main(teste.java:24)
Caused by: org.apache.derby.client.am.SqlException: A coluna 'OTAVIO' não está presente em nenhuma tabela da lista FROM, ou aparece dentro de uma especificação de junção e está fora do escopo da especificação de junção, ou aparece em uma cláusula HAVING e não está na lista GROUP BY. Se esta for uma instrução CREATE ou ALTER TABLE, então, 'OTAVIO' não é uma coluna da tabela de destino.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
... 3 more

Java Result: 1

sql java servlet

asked by anonymous 14.11.2014 / 01:20

2 answers

Include class as link Error in my C Program

score 5 · Answer 1

Avoid throwing the values entered by the user directly into the query, use prepared statements not to give sql injection a chance. The error happens because there are missing single quotes around the value that is returned by getLogin() .

To fix do as follows.

String comando = "select senha from usuarios where usuario = ?";
PreparedStatement stmt = con.prepareStatement(comando); 
stm.setString(1, cliente.getLogin());
ResultSet rs = stmt.executeQuery();

The questions are exchanged for the values in a sequential way if there was one more parameter it would be number two and so on.

Ex:

String comando = "SELECT * FROM usuarios where usuario = ? AND senha = ? and token = ?";
PreparedStatement stmt = con.prepareStatement(comando); 
stm.setString(1, cliente.getLogin());--------------------^
stm.setString(2, cliente.getSenha());----------------------------------^
stm.setString(3, cliente.getToken());-----------------------------------------------^

score 3 · Answer 2

The error is here

"select senha from usuarios where usuario="+cliente.getLogin();

the "right" is

"select senha from usuarios where usuario= '"+cliente.getLogin()+"'";

See that it has a simple quotation mark surrounded by client.getLogin ()

But never do this concatenation directly in the String sql, this way it is very easy to make a sql Injection and your database will easily be in danger. The correct is you parametrize the arguments, as the colleague 'Lost' explained.

String comando = "select senha from usuarios where usuario = ?";
PreparedStatement stmt = con.prepareStatement(comando); 
stm.setString(1, cliente.getLogin());
ResultSet rs = stmt.executeQuery();