jdbcsystemdatabase.java

这是linux下ssl vpn的实现程序

        JDBCPreparedStatement ps = db.getStatement("getShortcutByName.select");
        ps.setString(1, name);
        try {
            ResultSet rs = ps.executeQuery();
            try {
                if (rs.next()) {
                    return buildShortcut(rs);
                } else {
                    return null;
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#deleteShortcut(int)
     */
    public ApplicationShortcut deleteShortcut(int shortcutId) throws Exception {

        ApplicationShortcut sc = getShortcut(shortcutId);
        if (sc == null) {
            throw new Exception("Application shortcut " + shortcutId + " does not exist.");
        }

        JDBCPreparedStatement ps = db.getStatement("deleteShortcuts.delete.favorite");

        // Delete a favorite
        try {
            ps.setString(1, String.valueOf(shortcutId));
            ps.setInt(2, PolicyConstants.APPLICATION_SHORTCUT_RESOURCE_TYPE_ID);
            ps.execute();
            ps.reset();
        } finally {
            ps.releasePreparedStatement();
        }

        // Delete a shortcut
        ps = db.getStatement("deleteShortcuts.delete.shortcut");
        try {
            ps.setInt(1, shortcutId);
            ps.execute();
            ps.reset();
        } finally {
            ps.releasePreparedStatement();
        }

        // Delete all parameters for a shortcut
        ps = db.getStatement("deleteShortcuts.delete.shortcutParameters");
        try {
            ps.setInt(1, shortcutId);
            ps.execute();
            ps.reset();
        } finally {
            ps.releasePreparedStatement();
        }

        return sc;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#verifyIPAddress(java.lang.String)
     */
    public boolean verifyIPAddress(String ipaddress) throws Exception {

        boolean verified = false;

        if (ipaddress.equals("127.0.0.1") || ipaddress.equals("localhost"))
            return true;

        // Check if the state of this ip address is cached
        if (authorizedIPAddresses.contains(ipaddress)) {
            return true;
        } else if (excludedIPAddresses.contains(ipaddress)) {
            return false;
        }

        JDBCPreparedStatement ps = db.getStatement("verifyIPAddress.select.type");
        try {
            ps.setInt(1, 2);
            ResultSet rs = ps.executeQuery();
            try {
                while (rs.next()) {
                    String mask = rs.getString("address");
                    if (ipaddress.startsWith(mask)) {
                        verified = true;
                    }
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }

        try {
            ps.reset();
            ps.setInt(1, 4);
            ResultSet rs = ps.executeQuery();
            try {
                while (rs.next()) {
                    String mask = rs.getString("address");
                    if (ipaddress.startsWith(mask)) {
                        excludedIPAddresses.add(ipaddress);
                        return false;
                    }
                }
            } finally {
                rs.close();
            }
            ps.reset();
        } finally {
            ps.releasePreparedStatement();
        }

        if (!verified) {
            ps = db.getStatement("verifyIPAddress.select.count");
            int count = 0;
            try {
                ResultSet rs = ps.executeQuery();
                try {
                    if (!rs.next()) {
                        throw new Exception("Failed to retrieve IP address information from database");
                    }

                    count = rs.getInt("authorized");
                } finally {
                    rs.close();
                }
            } finally {
                ps.releasePreparedStatement();
            }

            if (count > 0) {

                // We have some authorized which means our ip address MUST be in
                // the
                // list
                // in order to gain access
                ps = db.getStatement("verifyIPAddress.select.addressType");
                ps.setString(1, ipaddress);
                ps.setInt(2, 1);

                try {
                    ResultSet rs = ps.executeQuery();
                    try {
                        if (rs.next()) {
                            verified = true;
                        }
                    } finally {
                        rs.close();
                    }
                } finally {
                    ps.releasePreparedStatement();
                }
            } else {
                // If there are no records in the database of type 1 or 2 then
                // let
                // everything through
                verified = true;
            }
        }

        // Check the excluded; this will OVERRIDE the authorized list

        ps = db.getStatement("verifyIPAddress.select.addressType");
        ps.setString(1, ipaddress);
        ps.setInt(2, 3);

        try {
            ResultSet rs = ps.executeQuery();
            try {
                if (rs.next()) {
                    verified = false;
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }

        if (verified) {
            // Cache IP addresses to stop us doing too many lookups
            authorizedIPAddresses.add(ipaddress);
        } else {
            excludedIPAddresses.add(ipaddress);
        }

        return verified;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#updateTunnel(int,
     *      java.lang.String, java.lang.String, int, boolean, java.lang.String,
     *      java.lang.String, int, com.sslexplorer.boot.HostService, boolean,
     *      int)
     */
    public void updateTunnel(int id, String name, String description, int type, boolean autoStart, String transport,
                             String username, int sourcePort, HostService destination, boolean allowExternalHosts,
                             int parentResourcePermission) throws Exception {

        JDBCPreparedStatement ps2 = db.getStatement("updateTunnel.update");
        try {
            ps2.setString(1, name);
            ps2.setString(2, description);
            ps2.setInt(3, type);
            ps2.setInt(4, autoStart ? 1 : 0);
            ps2.setString(5, transport);
            ps2.setString(6, username == null ? "" : username);
            ps2.setInt(7, sourcePort);
            ps2.setInt(8, destination.getPort());
            ps2.setString(9, destination.getHost());
            ps2.setInt(10, allowExternalHosts ? 1 : 0);
            ps2.setInt(11, parentResourcePermission);
            Calendar now = Calendar.getInstance();
            ps2.setString(12, db.formatTimestamp(now));
            ps2.setInt(13, id);
            ps2.execute();
        } finally {
            ps2.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#addIpRestriction(java.lang.String,
     *      boolean)
     */
    public void addIpRestriction(String ipAddress, boolean authorized) throws Exception {
        int idx = ipAddress.indexOf('*');
        JDBCPreparedStatement ps = db.getStatement("addIpRestriction.insert");
        try {
            if (idx > -1) {
                String mask = ipAddress.substring(0, idx);
                ps.setString(1, mask);
                ps.setInt(2, authorized ? 2 : 4);

            } else {
                ps.setString(1, ipAddress);
                ps.setInt(2, authorized ? 1 : 3);
            }
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
        authorizedIPAddresses.clear();
        excludedIPAddresses.clear();

    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#getIpRestrictions()
     */
    public IpRestriction[] getIpRestrictions() throws Exception {

        JDBCPreparedStatement ps = db.getStatement("getIpRestrictions.select");
        Vector v = new Vector();
        try {
            ResultSet rs = ps.executeQuery();
            try {
                while (rs.next()) {
                    v.add(new IpRestriction(rs.getString("address"), rs.getInt("type"), rs.getInt("restriction_id")));
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.releasePreparedStatement();
        }
        IpRestriction[] array = new IpRestriction[v.size()];
        v.copyInto(array);
        return array;
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#removeIpRestriction(int)
     */
    public void removeIpRestriction(int id) throws Exception {
        JDBCPreparedStatement ps = db.getStatement("removeIpRestriction.delete");
        try {
            ps.setInt(1, id);
            ps.execute();
        } finally {
            ps.releasePreparedStatement();
        }
        authorizedIPAddresses.clear();
        excludedIPAddresses.clear();
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#createTunnel(java.lang.String,
     *      java.lang.String, int, boolean, java.lang.String, java.lang.String,
     *      int, com.sslexplorer.boot.HostService, boolean, int)
     */
    public Tunnel createTunnel(String name, String description, int type, boolean autoStart, String transport, String username,
                               int sourcePort, HostService destination, boolean allowExternalHosts, int parentResourcePermission)
                    throws Exception {

        JDBCPreparedStatement ps2 = db.getStatement("createTunnel.insert");
        try {
            ps2.setString(1, name);
            ps2.setString(2, description);
            ps2.setInt(3, type);
            ps2.setInt(4, autoStart ? 1 : 0);
            ps2.setString(5, transport);
            ps2.setString(6, username == null ? "" : username);
            ps2.setInt(7, sourcePort);
            ps2.setInt(8, destination.getPort());
            ps2.setString(9, destination.getHost());
            ps2.setInt(10, allowExternalHosts ? 1 : 0);
            ps2.setInt(11, parentResourcePermission);
            Calendar now = Calendar.getInstance();
            ps2.setString(12, db.formatTimestamp(now));
            ps2.setString(13, db.formatTimestamp(now));
            ps2.execute();
            return new DefaultTunnel(name, description, db.getLastInsertId(ps2, "createTunnel.lastInsertId"), type, autoStart,
                            transport, username, sourcePort, destination, allowExternalHosts, parentResourcePermission, now, now);

        } finally {
            ps2.releasePreparedStatement();
        }
    }

    /*
     * (non-Javadoc)
     * 
     * @see com.sslexplorer.security.SystemDatabase#removeTunnel(int)
     */
    public Tunnel removeTunnel(int id) throws Exception {
        Tunnel t = getTunnel(id);
        if (t == null) {
            throw new Exception("No tunnel with " + id);
        }
        JDBCPreparedStatement ps = db.getStatement("removeTunnel.deleteFavorite");
        try {
            ps.setInt(1, PolicyConstants.SSL_TUNNEL_RESOURCE_TYPE_ID);
            ps.setString(2, String.valueOf(id));
            ps.execute();
        } finally {