CODEWITHSUNDEEP
javamysqlsqljdbc
Andrei Călugăr
Andrei Călugăr

Reputation: 145

Java executeQuery returning null

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.

enter image description here

Upvotes: 1

Views: 1752

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109547

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.

Upvotes: 2

Arvind Kumar Avinash
Arvind Kumar Avinash

Reputation: 79005

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

Upvotes: 1

Related Questions