Java executeQuery returning null

Question

I'm trying to write a method that checks my database if a user has the permission to execute a certain method.

The response that I'm getting after executing the query it's null.

I have the following method for doing this :

    public boolean checkRole(String encToken,String methodName)throws Exception, RuntimeException{
     CryptoHelper crypto = new CryptoHelper();
     SecretKeySpec key= new SecretKeySpec(keyString.getBytes("UTF-8"),"AES");
    try{
    String token = crypto.decrypt(encToken.toString(), key);
      String [] parts = token.split( ":" );
      String user = parts[0];

      String query ="SELECT EXISTS(SELECT * FROM data INNER JOIN Permissions "
            + "ON data.Role = Permissions.Permission "
            + "where Username = '" + user + "' AND Function ='" + methodName + "')";
      res = stm.executeQuery(query);

      if(res.equals(true)){           
            System.out.println("Welcome " + user );
            return true;
          }          
  }
       catch(Exception e){
        System.out.println(e);
       }
    return false;

  }

I've also checked if I'm passing the correct user and methodName and that seemed to be fine.

The database structure :

USE DB;
DROP TABLE IF EXISTS `data`;
CREATE TABLE IF NOT EXISTS `data` (
  `id_u` int(11) NOT NULL AUTO_INCREMENT,
  `Username` varchar(30) NOT NULL,
  `Password` varchar(64) NOT NULL,
  `Salt` varchar(64) NOT NULL,
  `Role` int(30)  NOT NULL,
);

--
-- Dumping data for table `data`
--

INSERT INTO `data` (`id_u`,`Username`, `Password`, `Salt`,`Role`) VALUES
(1,'Mike', 'TzBql1WR9wZjN0LoKr2OBk2majc=', '4NWwJULan8U=','1'), --- password : ThisTheFirstPassword
(2,'Cecilia', 'TzBql1WR9wZjN0LoKr2OBk2majc=', '4NWwJULan8U=','1'),
(3,'Erika', 'iubXIju+Hd+EOgIuivTx3RbRDoU=', '2MWwJULan8U=','2'),
(4,'Alice', 'mWrE8czs6KkOeP1WiMyn0NEnKGw=', '4NWSJULgn8U=','2'),
(5,'Bob', 'YNvbZBcchzXYRyRJBx5WkPmwxfo=', '4NWwJILan9U=','3'),
(6,'David', 'OPhte5nto3U+rJucbb3GUTGCSiI=', '4NWwFULan8X=','3');
COMMIT;

--
-- Table structure for table `Permissions`
--

DROP TABLE IF EXISTS `Permissions`;
CREATE TABLE IF NOT EXISTS `Permissions` (
  `id_p` int(11) NOT NULL AUTO_INCREMENT,
  `Function` varchar(30) NOT NULL,
  `Permission` int(30) NOT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `Permissions`
--

INSERT INTO `Permissions` (`id_p`, `Function`, `Permission`) VALUES
(1, 'print', 1),
(2, 'print', 2),
(3, 'print', 3),
(4, 'queue', 1),
(5, 'queue', 2),
(6, 'queue', 3),
(7, 'topQueue', 1),
(8, 'topQueue', 2),
(9, 'restart', 1),
(10, 'restart', 2);
(11, 'restart', 4);
(12, 'start', 1);
(13, 'start', 4);
(14, 'stop', 1);
(15, 'stop', 4);
(16, 'status', 1);
(17, 'status', 4);
(18, 'readConfig', 1);
(19, 'readConfig', 4);
(20, 'setConfig', 1);
(21, 'setConfig', 4);

Also when I'm executing the query in Mysql it does work as intended.

Where is your PreparedStatement and why don't you prepare it with setString(int, String) but instead pass a concatenated String as query? — deHaar
– deHaar, Commented Nov 28, 2019 at 11:34
executeQuery never returns null (and if a driver would do that, it would violate a basic requirement of the JDBC API). Why do you think that res.equals(true) would be an appropriate thing to do? A ResultSet will never be equal to true, and any decent IDE would warn you about that. — Mark Rotteveel
– Mark Rotteveel, Commented Nov 28, 2019 at 13:14
@MarkRotteveel I will replace res.equals(true). My logic was that I'm trying to check if the query execution will return true that's why I thought I could use that. — Andrei Călugăr
– Andrei Călugăr, Commented Nov 28, 2019 at 13:40
@deHaar I'll try to use PreparedStatement instead of concatenated String. — Andrei Călugăr
– Andrei Călugăr, Commented Nov 28, 2019 at 13:43
A query returns a result set, in your case the result set contains a row that has the value true. That doesn't mean the result set itself is true. — Mark Rotteveel
– Mark Rotteveel, Commented Nov 28, 2019 at 13:45

Joop Eggen · Accepted Answer · 2019-11-28 13:32:15Z

2

SELECT EXISTS delivers exactly one record. So one would need:

  String query ="SELECT EXISTS(SELECT * FROM data INNER JOIN Permissions "
        + "ON data.Role = Permissions.Permission "
        + "WHERE Username = ? AND Function = ?)";
  try (PreparedStatement stm = con.prepareStatement(query)) {
      stm.setString(1, user);
      stm.setString(2, methodName);
      try (ResultSet res = stm.executeQuery()) {
          if (res.next()) {           
              boolean exists = res.getBoolean(1);
              if (exists) {
                  System.out.println("Welcome " + user);
              }
              return exists;
          }
          return false;
      }
  }

Your solution would be possible using a simpler SQL

  String query ="SELECT Username FROM data INNER JOIN Permissions "
        + "ON data.Role = Permissions.Permission "
        + "WHERE Username = ? AND Function = ? "
        + "LIMIT 1"; // One record only
  try (PreparedStatement stm = con.prepareStatement(query)) {
      stm.setString(1, user);
      stm.setString(2, methodName);
      try (ResultSet res = stm.executeQuery()) {
          if (res.next()) {           
              System.out.println("Welcome " + user);
              return true;
          }
          return false;
      }
  }

Note: in first version I forgot to remove the parameter of executeQuery.

edited Nov 28, 2019 at 13:32

answered Nov 28, 2019 at 13:27

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

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

1 Comment

Andrei Călugăr Over a year ago

Thank you, @Joop Eggen seems like that was the problem. I tried the first solution that you provided. I understand what I was doing wrong and it even seems better to do it like this.

Arvind Kumar Avinash · Accepted Answer · 2019-11-28 13:04:27Z

1

Replace

if(res.equals(true))

with

if(res.next())

Please check https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html for more details.

Also, you should use PreparedStatement instead of Statement for parametrized queries. More details at https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

edited Nov 28, 2019 at 13:04

answered Nov 28, 2019 at 12:00

Arvind Kumar Avinash

81k10 gold badges98 silver badges144 bronze badges

Collectives™ on Stack Overflow

Java executeQuery returning null

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related