public static Integer insert( Connection conn, Integer networkId, NetworkIntegerSettingEnum settingEnum, Integer settingValue) throws SQLException { conn = start(conn); String sql = "insert into `network_integer_settings` (" + "`network_id`, " + "`setting_id`, " + "`setting_value` " + ") values (?, ?, ?);"; PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ps.setInt(1, networkId); ps.setInt(2, settingEnum.getId()); ps.setInt(3, settingValue); ps.execute(); Integer generatedId = DatabaseUtils.getFirstGeneratedKey(ps.getGeneratedKeys()); end(conn, ps, null); return generatedId; }
private static NetworkIntegerSetting loadPrimitives(ResultSet rs) throws SQLException { NetworkIntegerSetting out = new NetworkIntegerSetting(); out.setId(DatabaseUtils.getInt(rs, "id")); out.setValue(DatabaseUtils.getInt(rs, "setting_value")); out.setSettingEnum(NetworkIntegerSettingEnum.getById(DatabaseUtils.getInt(rs, "setting_id"))); return out; }
public static NetworkIntegerSetting getByNetworkIdAndSettingEnum( Connection conn, Integer networkId, NetworkIntegerSettingEnum setting) throws SQLException { conn = start(conn); String sql = "select * " + "from `network_integer_settings` " + "where `network_id` = ? " + "and `setting_id` = ? " + "limit 1;"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, networkId); ps.setInt(2, setting.getId()); ResultSet rs = ps.executeQuery(); NetworkIntegerSetting out = null; while (rs.next()) out = loadPrimitives(rs); end(conn, ps, rs); return out; }
public static void updateByNetworkIdAndSettingEnum( Connection conn, Integer networkId, NetworkIntegerSettingEnum settingEnum, Integer settingValue) throws SQLException { conn = start(conn); String sql = "update `network_integer_settings` " + "set `setting_value` = ? " + "where `network_id` = ? " + "and `setting_id` = ? " + "limit 1;"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, settingValue); ps.setInt(2, networkId); ps.setInt(3, settingEnum.getId()); ps.execute(); end(conn, ps, null); }
public static Tuple<Integer, Integer> add( Integer networkId, Integer userId, Integer smartGroupRef, String text) throws SQLException { // Currently non-transactional Connection conn = null; // Getting network settings Map<NetworkIntegerSettingEnum, Integer> networkIntegerSettings = NetworkIntegerSettingEnum.getMapByNetworkId(networkId); // Validating minimum length if (text.length() < networkIntegerSettings.get(NetworkIntegerSettingEnum.SHARED_ITEM_MIN_LENGTH)) throw new UIException("Message is too short"); // Validating max length if (text.length() > networkIntegerSettings.get(NetworkIntegerSettingEnum.SHARED_ITEM_MAX_LENGTH)) throw new UIException("Message is too long"); // Validating for timing attacks if (Vars.enableTimelocks) { // Validating for minute attack Integer per5Minutes = networkIntegerSettings.get(NetworkIntegerSettingEnum.SHARED_ITEMS_PER_FIVE_MINUTES); Integer last5Minutes = SharedItemDao.countByNetworkIdAndUserIdAndCreatedAfter( conn, networkId, userId, DateUtils.minutesAgo(5)); if (last5Minutes >= per5Minutes) throw new UIException("Limit: " + per5Minutes + " messages every 5 minutes"); // Validating for hour attack Integer perHour = networkIntegerSettings.get(NetworkIntegerSettingEnum.SHARED_ITEMS_PER_HOUR); Integer lastHour = SharedItemDao.countByNetworkIdAndUserIdAndCreatedAfter( conn, networkId, userId, DateUtils.hoursAgo(1)); if (lastHour >= perHour) throw new UIException("Limit: " + perHour + " messages per hour"); // Validating for day attack Integer perDay = networkIntegerSettings.get(NetworkIntegerSettingEnum.SHARED_ITEMS_PER_DAY); Integer lastDay = SharedItemDao.countByNetworkIdAndUserIdAndCreatedAfter( conn, networkId, userId, DateUtils.daysAgo(1)); if (lastDay >= perDay) throw new UIException("Limit: " + perDay + " messages per day"); } // Retrieving points per shared item Integer pointsPerSharedItem = networkIntegerSettings.get(NetworkIntegerSettingEnum.SHARED_ITEM_POINTS_PER); UserToNetwork fromUserToNetwork = UserToNetworkDao.getByUserIdAndNetworkId(conn, userId, networkId); // Check that the from user has the required number of points if (fromUserToNetwork.getCurrentPoints() < (pointsPerSharedItem * -1)) { // User does not have enough points, return null throw new UIException("Not enough points"); } // Check that the user has the authority to add a message SmartGroup smartGroup = SmartGroupDao.getByNetworkIdAndRef(null, networkId, smartGroupRef); // Only editors can submit messages to official smart groups if (smartGroup.getVisibility().equals(SmartGroupVisibilityEnum.OFFICIAL)) { if (fromUserToNetwork.getRole().isLowerThan(RoleEnum.EDITOR)) throw new UIException("Members can not post in official groups"); // Visitors can not post messages to smart groups } else if (smartGroup.getVisibility().equals(SmartGroupVisibilityEnum.SHARED)) { if (fromUserToNetwork.getRole().isLowerThan(RoleEnum.MEMBER)) throw new UIException("Visitors can not post in groups"); } // Retrieving max ref Integer maxRef = SharedItemDao.getMaxRefByNetworkIdAndSmartGroupRef(conn, networkId, smartGroupRef); /* Creating next ref, starting with 1 and not 0 */ Integer nextRef = 1; if (maxRef != null) nextRef = maxRef + 1; // Insert shared item SharedItemDao.insert(conn, networkId, userId, smartGroupRef, nextRef, text); // Increment one point for author UserToNetworkDao.incrementPointsByUserIdAndNetworkId( conn, userId, networkId, pointsPerSharedItem); // Sending notification emails EmailServices.newSharedItemForActiveUserToSmartGroups(networkId, smartGroupRef, nextRef); Tuple<Integer, Integer> out = new Tuple<Integer, Integer>(nextRef, pointsPerSharedItem); return out; }