1 18 package freecs.auth.sqlConnectionPool; 19 20 import freecs.Server; 21 import freecs.core.CanceledRequestException; 22 import freecs.core.User; 23 import freecs.interfaces.IUserStates; 24 import freecs.util.HashUtils; 25 26 import java.sql.*; 27 import java.util.Enumeration ; 28 import java.util.Iterator ; 29 import java.util.List ; 30 31 public class PoolElement { 32 public static final short INVALID = -1; 33 public static final short IDLE = 0; 34 public static final short ACTIVE = 1; 35 36 volatile private PreparedStatement select = null, 37 update = null, 38 updateLastlogin = null, 39 insert = null; 40 volatile private String selStrg = null, 41 insStrg = null, 42 updStrg = null, 43 updLastloginStrg = null; 44 private DbProperties dbp; 45 private volatile boolean isValid=true; 46 47 ConnectionPool pool; 48 Connection con = null; 49 int id; 50 volatile int sCnt=0; 51 long validUntil; 52 volatile boolean isActive = false, hasBeenUsed=false, cleanedUp=false; 53 ResultSet rs; 54 55 PoolElement (ConnectionPool pool, Connection con, DbProperties dbp, int id) throws Exception { 56 if (con == null) 57 throw new Exception ("no connection supplied"); 58 this.pool = pool; 59 this.dbp=dbp; 60 this.id = id; 61 this.con = con; 62 con.setAutoCommit (false); 63 validUntil = System.currentTimeMillis() + dbp.conTTL; 64 Server.log ("SqlAuthenticator", "Created new Connetion " + this.toString(), Server.MSG_AUTH, Server.LVL_MAJOR); 65 if (Server.TRACE_CREATE_AND_FINALIZE) 66 Server.log (this, "++++++++++++++++++++++++++++++++++++++++CREATE", Server.MSG_STATE, Server.LVL_VERY_VERBOSE); 67 } 68 69 private boolean isValid() { 70 if (!isValid) 71 return false; 72 if (con == null 73 || cleanedUp) 74 return false; 75 if (!hasBeenUsed) 76 return true; 77 if (sCnt > dbp.maxStmtPerCon) { 78 Server.log(this, "invalid because max-statements/connection has been reached " + dbp.maxStmtPerCon, Server.MSG_AUTH, Server.LVL_VERBOSE); 79 isValid=false; 80 return false; 81 } 82 if (validUntil <= System.currentTimeMillis()) { 83 Server.log(this, "invalid because connection ttl has been reached " + dbp.conTTL, Server.MSG_AUTH, Server.LVL_VERBOSE); 84 isValid=false; 85 return false; 86 } 87 return true; 88 } 89 90 93 public synchronized void cleanup () { 94 if (cleanedUp) 95 return; 96 try { 97 if (select != null) { 98 select.close(); 99 select = null; 100 } 101 } catch (SQLException se) { 102 Server.debug(this, "cleanup: select.close()", se, Server.MSG_ERROR, Server.LVL_MAJOR); 103 } 104 try { 105 if (insert != null) { 106 insert.close(); 107 insert = null; 108 } 109 } catch (SQLException se) { 110 Server.debug(this, "cleanup: insert.close()", se, Server.MSG_ERROR, Server.LVL_MAJOR); 111 } 112 try { 113 if (update != null) { 114 update.close(); 115 update = null; 116 } 117 } catch (SQLException se) { 118 Server.debug(this, "cleanup: update.close()", se, Server.MSG_ERROR, Server.LVL_MAJOR); 119 } 120 try { 121 if (con!=null) { 122 con.close(); 123 con = null; 124 } 125 } catch (SQLException se) { 126 Server.debug(this, "cleanup: connection.close()", se, Server.MSG_ERROR, Server.LVL_MAJOR); 127 } 128 this.pool = null; 129 this.isActive = false; 130 this.cleanedUp = true; 131 Server.log ("SqlAuthenticator", "Closed Connetion " + this.toString(), Server.MSG_AUTH, Server.LVL_MAJOR); 132 } 133 134 138 public void checkWarnings(Statement s, String prefix) { 139 try { 140 SQLWarning sqlW = s.getWarnings(); 141 while (sqlW != null) { 142 StringBuffer sb = new StringBuffer (this.toString()); 143 sb.append (" getResultSet: Encountered SQLWarning: "); 144 sb.append (prefix); 145 sb.append (": "); 146 sb.append (sqlW.getErrorCode()); 147 sb.append (": "); 148 sb.append (sqlW.getCause()); 149 Server.log (Thread.currentThread(), sb.toString (), Server.MSG_ERROR, Server.LVL_MAJOR); 150 sqlW = sqlW.getNextWarning(); 151 } 152 } catch (SQLException se) { 153 this.isValid=false; 154 Server.debug(this, "checkWarnings caused exception", se, Server.MSG_ERROR, Server.LVL_MAJOR); 155 } 156 } 157 158 public String toString() { 159 StringBuffer sb = new StringBuffer ("[PoolElement: "); 160 sb.append (id); 161 sb.append ("/"); 162 sb.append (sCnt); 163 sb.append ("/"); 164 sb.append (dbp.url).append ("(").append (dbp.table).append(")"); 165 sb.append ("]"); 166 return sb.toString(); 167 } 168 169 173 public synchronized short grab() { 174 if (this.isActive) 175 return ACTIVE; 176 if (!isValid()) 177 return INVALID; 178 this.isActive=true; 179 this.hasBeenUsed=true; 180 return IDLE; 181 } 182 183 187 public void release() { 188 try { 189 if (select!=null) { 190 select.clearParameters(); 191 select.clearWarnings(); 192 } 193 if (update!=null) { 194 update.clearParameters(); 195 update.clearWarnings(); 196 } 197 if (insert!=null) { 198 insert.clearParameters(); 199 insert.clearWarnings(); 200 } 201 } catch (Exception se) { 202 Server.debug (this, "catched exception while releasing PoolElement", se, Server.MSG_AUTH, Server.LVL_MAJOR); 203 } 204 this.isActive=false; 205 } 206 207 214 private PreparedStatement getSelect() throws Exception { 215 try { 216 if (select!=null) 217 return select; 218 if (con==null) 219 throw new Exception ("No connection to retrieve a PreparedStatement from"); 220 StringBuffer sb = new StringBuffer ("SELECT "); 221 sb.append (dbp.columns[0]); 222 for (int i = 1; i<dbp.columns.length; i++) { 223 sb.append (", "); 224 sb.append (dbp.columns[i]); 225 } 226 sb.append (", "); 227 sb.append (dbp.fc_password); 228 sb.append (" FROM "); 229 sb.append (dbp.table); 230 sb.append (" WHERE "); 231 sb.append (dbp.fc_username); 232 sb.append (" = ?"); 233 selStrg = sb.toString(); 234 select = con.prepareStatement(selStrg, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); 235 if (dbp.queryTimeout > 0) 236 select.setQueryTimeout(dbp.queryTimeout); 237 return select; 238 } catch (Exception e) { 239 isValid=false; 240 release(); 241 throw e; 242 } 243 } 244 245 private PreparedStatement getUpdate() throws Exception { 246 try { 247 if (update != null) 248 return update; 249 if (con==null) 250 throw new Exception ("No connection to retrieve a PreparedStatement from"); 251 StringBuffer sb = new StringBuffer ("UPDATE "); 252 sb.append (dbp.table); 253 sb.append (" SET "); 254 sb.append (dbp.updCols[0]); 255 sb.append (" = ?"); 256 for (int i = 1; i<dbp.updCols.length; i++) { 257 sb.append (", "); 258 sb.append (dbp.updCols[i]); 259 sb.append (" = ?"); 260 } 261 sb.append (" WHERE "); 262 if (dbp.idField != null) { 263 sb.append (dbp.idField); 264 sb.append (" = ?"); 265 } else { 266 sb.append (dbp.fc_username); 267 sb.append (" = ?"); 268 } 269 updStrg = sb.toString(); 270 update = con.prepareStatement(updStrg); 271 if (dbp.queryTimeout > 0) 272 update.setQueryTimeout(dbp.queryTimeout); 273 return update; 274 } catch (Exception e) { 275 isValid=false; 276 release(); 277 throw e; 278 } 279 } 280 281 282 290 public User loginUser (String username, String password, String cookie) throws Exception { 291 try { 292 checkThread(); 293 PreparedStatement ps = getSelect(); 294 ps.setString(1, username.toLowerCase().trim()); 295 ResultSet rs = ps.executeQuery(); 296 sCnt++; 297 Server.log(Thread.currentThread(), this.toString() + "LOGIN user uname=" + username.toLowerCase() + "/pwd=" + password + "/cookie=" + cookie + "\r\n" + selStrg, Server.MSG_AUTH, Server.LVL_VERY_VERBOSE); 298 dbp.cacheMetaData(rs); 299 if (!rs.next()) { 300 Server.log(Thread.currentThread(), this.toString()+ "LOGIN no user mathing username and password " + username + "/" + password, Server.MSG_AUTH, Server.LVL_MINOR); 301 rs.close(); 302 return new User(username, cookie); } else if (!rs.isLast()) { 305 Server.log(Thread.currentThread(), this.toString() + "LOGIN multible records returned for user " + username, Server.MSG_AUTH, Server.LVL_MAJOR); 306 rs.close(); 307 return null; 309 } 310 checkThread(); 311 String dbpwd = rs.getString(dbp.columns.length+1); 312 if (dbpwd==null || !dbpwd.equals(password)) 313 return null; 314 315 User u = new User (username, cookie); 316 u.isUnregistered = false; 317 318 readColumns(u, rs); 319 320 checkWarnings(ps, "loginUser (getData)"); 321 322 checkThread(); 323 if (!dbp.readOnly) { 325 doLoginUpdates(u, rs); 326 } 327 checkWarnings(ps, "loginUser (update Data)"); 328 rs.close(); 329 Server.log (Thread.currentThread(), this.toString() + "LOGIN returning " + u, Server.MSG_AUTH, Server.LVL_MAJOR); 330 u.isUnregistered = false; 331 return u; 332 } catch (Exception e) { 333 Server.debug (this, selStrg, e, Server.MSG_AUTH, Server.LVL_MAJOR); 334 isValid=false; 335 release(); 336 throw e; 337 } 338 } 339 340 public User loginUser (User u, String password) throws Exception { 341 try { 342 checkThread(); 343 PreparedStatement ps = getSelect(); 344 ps.setString(1, u.getName().toLowerCase().trim()); 345 Server.log(Thread.currentThread(), this.toString() + "LOGIN user uname=" + u.getName().toLowerCase() + "\r\n" + selStrg, Server.MSG_AUTH, Server.LVL_VERY_VERBOSE); 346 ResultSet rs = ps.executeQuery(); 347 sCnt++; 348 dbp.cacheMetaData(rs); 349 if (!rs.next()) { 350 Server.log(Thread.currentThread(), this.toString()+ "LOGIN no user mathing username " + u.getName(), Server.MSG_AUTH, Server.LVL_MINOR); 351 rs.close(); 352 return u; } else if (!rs.isLast()) { 354 Server.log(Thread.currentThread(), this.toString() + "LOGIN multible records returned for user " + u.getName(), Server.MSG_AUTH, Server.LVL_MAJOR); 355 rs.close(); 356 return u; } 358 359 if (u.isUnregistered == true) { 363 String dbpwd = rs.getString(dbp.columns.length+1); 364 if (dbpwd==null || !dbpwd.equals(password)) { 365 return null; 366 } 367 u.isUnregistered = false; 368 } 369 checkThread(); 370 371 readColumns(u, rs); 373 374 checkWarnings(ps, "loginUser (getData)"); 375 checkThread(); 376 if (!dbp.readOnly) { 378 doLoginUpdates(u, rs); 379 } 380 checkWarnings(ps, "loginUser (update Data)"); 381 rs.close(); 382 Server.log (Thread.currentThread(), this.toString() + "LOGIN returning " + u, Server.MSG_AUTH, Server.LVL_MAJOR); 383 return u; 384 } catch (Exception e) { 385 Server.log(this, selStrg, Server.MSG_AUTH, Server.LVL_MAJOR); 386 isValid=false; 387 release(); 388 throw e; 389 } 390 } 391 392 393 394 private void readColumns(User u, ResultSet rs) throws SQLException { 395 for (int i = 0; i<dbp.columns.length; i++) { 396 String cname = dbp.names[i]; 397 int idx = i+1; 398 if ("userright".equals(cname)) { 399 String val = rs.getString(idx); 400 if (val == null || val.length()==0 || "null".equalsIgnoreCase(val)) { 401 u.setPermission(IUserStates.ROLE_USER); 402 } else if ("true".equalsIgnoreCase (val) 403 || "yes".equalsIgnoreCase(val) 404 || "vip".equalsIgnoreCase(val)) { 405 u.setPermission(IUserStates.ROLE_VIP); 406 } else if ("admin".equalsIgnoreCase(val)) { 407 u.setPermission(IUserStates.ROLE_GOD); 408 } else if ("moderator".equalsIgnoreCase(val)) { 409 u.setPermission(IUserStates.ROLE_VIP | IUserStates.IS_MODERATOR); 410 } else if ("guest".equalsIgnoreCase(val)) { 411 u.setPermission(IUserStates.IS_GUEST); 412 } else if ("asshole".equalsIgnoreCase(val)) { 413 u.setPermission(IUserStates.ROLE_ASSHOLE); 414 } else { 415 try { 416 u.setPermission(Integer.parseInt(val)); 417 } catch (NumberFormatException nfe) { 418 Server.log(Thread.currentThread(), this.toString() + "LOGIN userrights-column contains unknown value, corrected to ROLE_USER\r\n(must be null/true,yes,vip or VIP/admin/moderator/guest/user/assohle) ", Server.MSG_AUTH, Server.LVL_MAJOR); 419 u.setPermission(IUserStates.ROLE_USER); 420 } 421 } 422 } else if ("id".equals(cname)) { 423 u.setID(rs.getString(idx)); 424 } else if ("color".equals(cname)) { 425 u.setColCode(rs.getString(idx)); 426 } else if ("chattime".equals(cname)) { 427 u.setProperty("chattime", new Long (rs.getLong(idx))); 428 } else if ("lastlogin".equals(cname)) { 429 switch (dbp.types[i]) { 430 case Types.BIGINT: 431 case Types.INTEGER: 432 case Types.NUMERIC: 433 case Types.SMALLINT: 434 u.setProperty("lastlogin", new Timestamp (rs.getLong(idx))); 435 break; 436 case Types.DATE: 437 case Types.TIMESTAMP: 438 Timestamp ts = rs.getTimestamp(idx); 439 u.setProperty("lastlogin", ts); 440 break; 441 default: 442 String s = rs.getString(idx); 443 if (rs.wasNull()) { 444 u.setProperty("lastlogin", new Timestamp (System.currentTimeMillis())); 445 break; 446 } 447 try { 448 long l = Long.parseLong (s); 449 u.setProperty("lastlogin", new Timestamp(l)); 450 } catch (NumberFormatException nfe) { 451 try { 452 u.setProperty("lastlogin", Timestamp.valueOf(s)); 453 } catch (IllegalArgumentException iae) { 454 Server.log (this, "LOGIN Unable to retrieve lastlogin-value! " + s, Server.MSG_AUTH, Server.LVL_MAJOR); 455 } 456 } 457 } 458 } else if ("friends".equals(cname)) { 459 List users = pool.authenticator.parseUserList(rs.getString(idx)); 460 for (Iterator it = users.iterator(); it.hasNext(); ) { 461 u.addFriend((String ) it.next()); 462 } 463 } else if ("ignorelist".equals(cname)) { 464 List ignores = pool.authenticator.parseUserList(rs.getString(idx)); 465 for (Iterator it = ignores.iterator(); it.hasNext(); ) { 466 u.ignoreUser((String ) it.next()); 467 } 468 } else if ("notifyfriends".equals(cname)) { 469 switch (dbp.types[i]) { 470 case Types.BIGINT: 471 case Types.INTEGER: 472 case Types.NUMERIC: 473 case Types.SMALLINT: 474 u.setFriendsNotification(rs.getShort(idx)); 475 break; 476 default: 477 u.setFriendsNotification(pool.authenticator.parseBoolean(rs.getString(idx)) ? Server.srv.FN_DEFAULT_MODE_TRUE : Server.srv.FN_DEFAULT_MODE_FALSE); 478 } 479 } else if ("customtitle".equals(cname)) { 481 u.setCustomTitle(rs.getString(idx)); 482 } else if ("blocked".equals(cname)) { 483 u.blocked = pool.authenticator.parseBoolean(rs.getString(idx)); 484 } else if ("activated".equals(cname)) { 485 u.activated = pool.authenticator.parseBoolean(rs.getString(idx)); 486 } else { 487 String strg = getEncodedString (rs, idx); 488 u.setProperty(cname, strg); 489 } 490 } 491 } 492 493 private void doLoginUpdates(User nu, ResultSet rs) throws Exception { 494 boolean updated = false, error = false; 495 long ts = System.currentTimeMillis(); 496 int idx = dbp.nameV.indexOf("lastlogin"); 497 if (idx > -1) { 498 try { 499 switch (dbp.types[idx]) { 500 case Types.INTEGER: 501 case Types.SMALLINT: 502 rs.updateInt(idx+1, (int) (ts/1000)); 503 break; 504 case Types.BIGINT: 505 case Types.NUMERIC: 506 case Types.DECIMAL: 507 rs.updateLong(idx+1, ts/1000); 508 break; 509 case Types.DATE: 510 case Types.TIMESTAMP: 511 rs.updateTimestamp(idx+1, new Timestamp(ts)); 512 break; 513 default: 514 rs.updateString(idx+1, String.valueOf(ts/1000)); 515 } 516 updated=true; 517 } catch (SQLException se) { 518 Server.debug (Thread.currentThread(), this.toString() + "LOGIN unable to update lastlogin", se, Server.MSG_AUTH, Server.LVL_MAJOR); 519 error=true; 520 } 521 } 522 idx = dbp.nameV.indexOf("cookie"); 524 if (idx > -1) try { 525 rs.updateString(idx+1, HashUtils.encodeMD5(nu.getCookie())); 526 } catch (SQLException se) { 527 Server.debug (Thread.currentThread(), this.toString() + "LOGIN unable to update cookie", se, Server.MSG_AUTH, Server.LVL_MAJOR); 528 } 529 try { 530 if (updated) { 531 rs.updateRow(); 532 con.commit(); 533 } else if (error) { 534 rs.cancelRowUpdates(); 535 } 536 } catch (SQLException se) { 537 Server.debug (Thread.currentThread(), this.toString() + "LOGIN exception during updateRow/cancelRowUpdates", se, Server.MSG_AUTH, Server.LVL_MAJOR); 538 } 539 } 540 541 542 543 public void logoutUser (User u) throws Exception { 544 try { 545 if (dbp.readOnly || dbp.updCols == null || dbp.updCols.length < 1) 546 return; 547 PreparedStatement ps = getUpdate(); 548 for (int i = 0; i < dbp.updCols.length; i++) { 549 String cname = dbp.updNames[i]; 550 if ("chattime".equalsIgnoreCase(cname)) { 551 ps.setLong(i+1, u.getChattime()); 552 } else if ("userrights".equalsIgnoreCase(cname)) { 553 ps.setInt(i+1, u.getPermissionMap()); 554 } else if ("color".equalsIgnoreCase(cname)) { 555 ps.setString(i+1, u.getColCode()); 556 } else if ("friends".equalsIgnoreCase(cname)) { 557 StringBuffer sb = new StringBuffer (); 558 for (Enumeration e = u.friends(); e.hasMoreElements(); ) { 559 String s = (String ) e.nextElement(); 560 sb.append (s); 561 if (e.hasMoreElements()) 562 sb.append (", "); 563 } 564 ps.setString(i+1, sb.toString()); 565 } else if ("notifyfriends".equalsIgnoreCase(cname)) { 566 int idx = dbp.nameV.indexOf("notifyfriends"); 567 switch (dbp.types[idx]) { 568 case Types.BIGINT: 569 case Types.BIT: 570 case Types.DECIMAL: 571 case Types.INTEGER: 572 case Types.SMALLINT: 573 ps.setInt(i+1, u.notifyFriends()); 574 break; 575 case Types.BOOLEAN: 576 ps.setBoolean(i+1, u.notifyFriends()==User.FN_ALL ? true : false); 577 break; 578 default: 579 ps.setString(i+1, u.notifyFriends()==User.FN_ALL ? "true" : "false"); 580 } 581 } else if ("extratitle".equalsIgnoreCase(cname)) { 582 ps.setString(i+1, u.getCustomTitle()); 583 } else if ("cookie".equalsIgnoreCase(cname)) { 584 ps.setString(i+1, "not_logged_in"); 586 } else if ("blocked".equalsIgnoreCase(cname)) { 587 int idx = dbp.nameV.indexOf("blocked"); 588 switch (dbp.types[idx]) { 589 case Types.BIGINT: 590 case Types.BIT: 591 case Types.DECIMAL: 592 case Types.INTEGER: 593 case Types.SMALLINT: 594 ps.setInt(i+1, u.blocked ? 1 : 0); 595 break; 596 case Types.BOOLEAN: 597 ps.setBoolean(i+1, u.blocked); 598 break; 599 default: 600 ps.setString(i+1, u.blocked ? "1" : "0"); 601 } 602 } else if ("activated".equalsIgnoreCase(cname)) { 603 int idx = dbp.nameV.indexOf("activated"); 604 switch (dbp.types[idx]) { 605 case Types.BIGINT: 606 case Types.BIT: 607 case Types.DECIMAL: 608 case Types.INTEGER: 609 case Types.SMALLINT: 610 ps.setInt(i+1, u.activated ? 1 : 0); 611 break; 612 case Types.BOOLEAN: 613 ps.setBoolean(i+1, u.activated); 614 break; 615 default: 616 ps.setString(i+1, u.activated ? "1" : "0"); 617 } 618 } 619 } 620 if (dbp.idField != null) { 621 if (u.getID()==null) { 622 Server.log(u, "Unable to store logout-data for " + u.getName() + " because of missing id-value", Server.MSG_AUTH, Server.LVL_MAJOR); 623 return; 624 } 625 ps.setString(dbp.updCols.length+1, u.getID()); 626 } else { 627 ps.setString(dbp.updCols.length+1, u.getName().toLowerCase()); 628 } 629 int rows = ps.executeUpdate(); 630 sCnt++; 631 if (rows==1) { 632 con.commit(); 633 } else if (rows < 1) { 634 Server.log(Thread.currentThread(), this.toString() + "LOGOUT unable to update userdata! No record for: " + dbp.idField != null ? dbp.idField + " = " + u.getID() : "username = " + u.getName().toLowerCase(), Server.MSG_AUTH, Server.LVL_MAJOR); 635 return; 636 } else if (rows > 1) { 637 Server.log(Thread.currentThread(), this.toString() + "LOGOUT unable to update userdata! More than one value would be updated: (" + dbp.idField != null ? dbp.idField + " = " + u.getID() : "username = " + u.getName().toLowerCase() + ")", Server.MSG_AUTH, Server.LVL_MAJOR); 638 try { 639 con.rollback(); 640 Server.log (Thread.currentThread(), this.toString() + "LOGOUT rollback successfully", Server.MSG_AUTH, Server.LVL_VERBOSE); 641 } catch (SQLException se) { 642 Server.log (Thread.currentThread(), this.toString() + "LOGOUT rollback failed!!!", Server.MSG_AUTH, Server.LVL_MAJOR); 643 } 644 } 645 checkWarnings(ps, "logoutUser"); 646 } catch (Exception e) { 647 isValid=false; 648 release(); 649 throw e; 650 } 651 } 652 653 659 private static String getEncodedString (ResultSet rs, int idx) { 660 if (rs == null) 661 return null; 662 try { 663 String result = rs.getString (idx); 664 if (result==null) 665 return null; 666 result = result.replaceAll ("[<]", "<"); 667 result = result.replaceAll ("[>]", ">"); 668 return result; 669 } catch (Exception e) { 670 Server.debug ("static PoolElement", "getEncodedString: error geting encoded string", e, Server.MSG_ERROR, Server.LVL_MAJOR); 671 } 672 return null; 673 } 674 675 private void checkThread() throws CanceledRequestException { 676 if (Thread.currentThread().isInterrupted()) 677 throw new CanceledRequestException ("ConnectionBuffer has been invalidated"); 678 } 679 680 public void finalize() { 681 if (Server.TRACE_CREATE_AND_FINALIZE) 682 Server.log(this, "----------------------------------------FINALIZED", Server.MSG_STATE, Server.LVL_VERY_VERBOSE); 683 } 684 } | Popular Tags |