From afb799c45e5fe231e77b056bd94a6431bbd5a1ca Mon Sep 17 00:00:00 2001 From: Nino Breuer Date: Mon, 17 Nov 2014 17:52:12 +0100 Subject: • changed regular statements to prepared statements to prevent sql injections --- dozentenmodulserver/src/main/java/sql/SQL.java | 1250 +++++++++++++++++++----- 1 file changed, 991 insertions(+), 259 deletions(-) (limited to 'dozentenmodulserver/src/main/java/sql') diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java index 6f04d156..cb5b84a1 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -28,6 +28,9 @@ public class SQL { private static Logger log = Logger.getLogger(BinaryListener.class); + + + public Connection getConnection() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); @@ -35,7 +38,7 @@ public class SQL { | ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); - } + } try { Connection con = DriverManager.getConnection("jdbc:mysql://" + Configuration.config.getSql_connection() + "?user=" @@ -51,43 +54,60 @@ public class SQL { } return null; } + + + public int writeFTPUser(String user, String pass) { - Statement stm; - try { - Connection con = getConnection(); - stm = con.createStatement(); - - int ret = stm - .executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('" - + user - + "',SHA1('" - + pass - + "'),'10001','12345','" - + Configuration.config.getAbsolute_path() - + "temp/');"); - con.commit(); - con.close(); - log.info("Created FTPUser " + user + " : " + pass - + "."); - return ret; - } catch (SQLException e) { - // TODO Auto-generated catch block - log.info("Failed to writeFTPUser."); - e.printStackTrace(); - } - return -1; - } + Statement stm; + try { + Connection con = getConnection(); + stm = con.createStatement(); + + int ret = stm + .executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('" + + user + + "',SHA1('" + + pass + + "'),'10001','12345','" + + Configuration.config.getAbsolute_path() + + "temp/');"); + con.commit(); + con.close(); + log.info("Created FTPUser " + user + " : " + pass + + "."); + return ret; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info("Failed to writeFTPUser."); + e.printStackTrace(); + } + return -1; + } + + + + + public int DeleteUser(String user) { Statement stm; try { Connection con = getConnection(); stm = con.createStatement(); + /* int ret = stm .executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '" + user + "';"); + */ + + String sql = "DELETE FROM `bwLehrpool`.`FtpUsers` where User like ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, user); + + int ret = prest.executeUpdate(); + con.commit(); con.close(); log.info("FTPUser " + user + " deleted."); @@ -100,6 +120,12 @@ public class SQL { return -1; } + + + + + + //no prepared statement to do here public ResultSet getImage() { try { Connection con = getConnection(); @@ -117,17 +143,34 @@ public class SQL { return null; } + + + public String getPathOfImage(String image_id, String version) { try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet rs = stm + //Statement stm = con.createStatement(); + /*ResultSet rs = stm .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='" + image_id + "' and imageVersion='" + version + "';"); + */ + + String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID= " + + "?" + + " and imageVersion= " + + "?" + + ";"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, image_id); + prest.setString(2, version); + + ResultSet rs = prest.executeQuery(); + con.commit(); rs.first(); String path = rs.getString("image_path"); con.close(); @@ -142,24 +185,60 @@ public class SQL { return null; } + + + + + public String setInstitution(String university) { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); + /* ResultSet ret = stm .executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'" + university + "';"); - if (ret.next() == false) { + */ + + String sql = "SELECT * FROM bwLehrpool.m_institution where name like ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, university); + ResultSet ret = prest.executeQuery(); + con.commit(); + + if (ret.next() == false) + { String id = UUID.randomUUID().toString(); + + /* stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('" + id + "','" + university + "');"); + */ + sql = "INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`) VALUES" + "(?,?)"; + + prest = con.prepareStatement(sql); + prest.setString(1, id); + prest.setString(2, university); + prest.executeUpdate(); + con.commit(); + + /* ResultSet rs = stm .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" + university + "';"); + */ + + sql = "SELECT institutionID FROM bwLehrpool.m_institution WHERE name like ?;"; + + prest = con.prepareStatement(sql); + prest.setString(1, university); + + ResultSet rs = prest.executeQuery(); + con.commit(); rs.next(); String institution = rs.getString("institutionID"); con.close(); @@ -179,23 +258,47 @@ public class SQL { return "-1"; } + + + + + + + public String setPerson(String login, String lastname, String firstname, String mail, Date lastlogin, String Institution) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - try { + try + { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); + /* ResultSet ret = stm .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" + lastname + "' and Vorname like '" + firstname + "';"); - - if (ret.next() == false) { + */ + + String sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + + "?" + + " and Vorname like " + + "?" + + ";"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, lastname); + prest.setString(2, firstname); + + ResultSet ret = prest.executeQuery(); + con.commit(); + if (ret.next() == false) + { // String id = UUID.randomUUID().toString(); + /* stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('" + login + "','" @@ -211,18 +314,63 @@ public class SQL { + "','" + Institution + "');"); - con.commit(); - ResultSet rs = stm + */ + + sql = "INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES(" + + "?" + + "," + + "?" + + "," + + "?" + + "," + + "?" + + "," + + "?" + + ",'" + + formatter.format(new Date()) + + "'," + + "?" + + ");"; + + prest = con.prepareStatement(sql); + prest.setString(1, login); + prest.setString(2, login); + prest.setString(3, lastname); + prest.setString(4, firstname); + prest.setString(5, mail); + prest.setString(6, Institution); + + ResultSet rs = prest.executeQuery(); + con.commit(); + + //con.commit(); + /* + ResultSet rs = stm .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" + lastname + "' and Vorname like '" + firstname + "';"); + */ + sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + + "?" + + " and Vorname like " + + "?" + + ";"; + + prest = con.prepareStatement(sql); + prest.setString(1, lastname); + prest.setString(2, firstname); + + rs = prest.executeQuery(); + con.commit(); rs.next(); String userid = rs.getString("userID"); con.close(); return userid; - } else { + } + else + { ret.first(); String userid = ret.getString("userID"); con.close(); @@ -237,6 +385,13 @@ public class SQL { return "-1"; } + + + + + + + public boolean setImageData(String pk_person, boolean license, boolean internet, long cpu, long ram, String imagename, String desc, String imagePath, long filesize, String shareMode, @@ -255,14 +410,15 @@ public class SQL { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); // create a UUID String uid = UUID.randomUUID().toString(); + + /* - log.info(" - Query is: " - + "INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" + uid // GUID_imageID + "',1,'" // imageVersion + imagename // image_name @@ -296,41 +452,59 @@ public class SQL { + "','" + shareMode // shareMode + "');"); */ - - stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" - + uid // GUID_imageID - + "',1,'" // imageVersion - + imagename // image_name - // Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang - // sein, sonst merkert die DB - + "','" + desc // image_description - + "','" + imagePath // image_path - + "','" + formatter.format(new Date()) // image_lastCall + + String sql = "INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES(" + + "?" // GUID_imageID + + ",1," // imageVersion + + "?" // image_name + + "," + "?" // image_description + + "," + "?" // image_path + + ",'" + formatter.format(new Date()) // image_lastCall + "','" + formatter.format(new Date()) // image_create_time + "','" + formatter.format(new Date()) // image_update_time - + "','" + pk_person // image_owner - + "','" + pk_person // image_change_by - + "','" + formatter.format(new Date()) // rec_create_time + + "'," + "?" // image_owner + + "," + "?" // image_change_by + + ",'" + formatter.format(new Date()) // rec_create_time + "','" + formatter.format(new Date()) // rec_change_time - + "','" + pk_person // rec_owner - + "','" + pk_person // rec_change_by - + "','" + pk_os // content_operatingSystem - + "',1" // status_isCompressed + + "'," + "?" // rec_owner + + "," + "?" // rec_change_by + + "," + "?" // content_operatingSystem + + ",1" // status_isCompressed + ",1" // status_isSecure + ",1" // status_isOptimzed + ",1" // status_isValid + ",1" // status_isReady + ",0" // status_isDeleted - + ",0,'" // status_isLastOfficialVersion - + license_bol // cond_hasLicenseRestriction - + "','" + internet_bol // cond_hasInternetRestriction - + "','" + ram // cond_minRAM - + "','" + cpu // cond_minCPUs - + "','0'" // image_isTemplate - + ",'" + filesize // image_filesize - + "','" + shareMode // shareMode - + "');"); + + ",0," // status_isLastOfficialVersion + + "?" // cond_hasLicenseRestriction + + "," + "?" // cond_hasInternetRestriction + + "," + "?" // cond_minRAM + + "," + "?" // cond_minCPUs + + ",0" // image_isTemplate + + "," + "?" // image_filesize + + "," + "?" // shareMode + + ");"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, uid); + prest.setString(2, imagename); + prest.setString(3, desc); + prest.setString(4, imagePath); + prest.setString(5, pk_person); + prest.setString(6, pk_person); + prest.setString(7, pk_person); + prest.setString(8, pk_person); + prest.setString(9, pk_os); + prest.setInt(10, license_bol); + prest.setInt(11, internet_bol); + prest.setLong(12, ram); + prest.setLong(13, cpu); + prest.setLong(14, filesize); + prest.setString(15, shareMode); + + prest.executeUpdate(); con.commit(); + //con.commit(); con.close(); } catch (SQLException e) { // TODO Auto-generated catch block @@ -341,23 +515,37 @@ public class SQL { } + + + + + + + public List getImageListPermissionWrite(String userID) { Connection con = getConnection(); - Statement stm; + //Statement stm; List list = new ArrayList(); try { - stm = con.createStatement(); + //stm = con.createStatement(); - ResultSet res = stm + /*ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '" + userID + "'AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); + */ + String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " + + "?" + + " AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"; - + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + ResultSet res = prest.executeQuery(); + con.commit(); while (res.next()) { list.add(new Image(res.getString("GUID_imageID"), res .getString("imageVersion"), @@ -381,23 +569,39 @@ public class SQL { return list; } + + + + public List getImageListPermissionRead(String userID) { Connection con = getConnection(); - Statement stm; + //Statement stm; List list = new ArrayList(); try { - stm = con.createStatement(); + //stm = con.createStatement(); // ResultSet + /* ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '" + userID + "'AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); - - while (res.next()) { + */ + + String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " + + "?" + + " AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + + ResultSet res = prest.executeQuery(); + con.commit(); + while (res.next()) + { list.add(new Image(res.getString("GUID_imageID"), res .getString("imageVersion"), res.getString("image_name"), res @@ -420,6 +624,12 @@ public class SQL { return list; } + + + + + + //no prepared statement to do here public List getImageList(String userID) { Connection con = getConnection(); @@ -431,10 +641,6 @@ public class SQL { stm = con.createStatement(); // ResultSet - /* - ResultSet res = stm - .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, vl.image_owner as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID"); - */ ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID;"); @@ -461,15 +667,21 @@ public class SQL { return list; } + + + + + + public List getImageListPermissionLink(String userID) { Connection con = getConnection(); - Statement stm; + //Statement stm; List list = new ArrayList(); try { - stm = con.createStatement(); + //stm = con.createStatement(); // ResultSet WITHOUT templates //ResultSet res = stm @@ -478,11 +690,22 @@ public class SQL { // + "' AND u.userID = pmi.userID AND (pmi.link_allowed=1 OR vl.image_isTemplate=1) AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); // ResultSet WITH templates + /* ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '" + userID + "'AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); + */ + + String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " + + "?" + + " AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + ResultSet res = prest.executeQuery(); + con.commit(); while (res.next()) { list.add(new Image( res.getString("GUID_imageID"), @@ -506,22 +729,39 @@ public class SQL { } + + + + public List getImageListPermissionAdmin(String userID){ Connection con = getConnection(); - Statement stm; + //Statement stm; List list = new ArrayList(); try { - stm = con.createStatement(); + //stm = con.createStatement(); // ResultSet + /* ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '" + userID + "'AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"); + */ - while (res.next()) { + + String sql = "SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate, vl.image_description FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = " + + "?" + + " AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + + ResultSet res = prest.executeQuery(); + con.commit(); + while (res.next()) + { list.add(new Image( res.getString("GUID_imageID"), res.getString("imageVersion"), @@ -547,6 +787,10 @@ public class SQL { + + + + //no prepared statement to do here public List getImageListAllTemplates(){ Connection con = getConnection(); Statement stm; @@ -560,7 +804,9 @@ public class SQL { ResultSet res = stm .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.image_description, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE vl.image_isTemplate=1 AND vl.content_operatingSystem=os.operatingSystemID AND vl.image_owner=u.userID;"); - while (res.next()) { + + while (res.next()) + { list.add(new Image( res.getString("GUID_imageID"), res.getString("imageVersion"), @@ -586,19 +832,33 @@ public class SQL { + + public List getLectureListPermissionRead(String userID) { List list = new ArrayList(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet res = stm + //Statement stm = con.createStatement(); + /*ResultSet res = stm .executeQuery("SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " + "WHERE i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.rec_read=true AND u.userID=pml.userID AND u.userID='" + userID + "';"); - while (res.next()) { + */ + + String sql = "SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " + + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " + + "WHERE i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.rec_read=true AND u.userID=pml.userID AND u.userID = ? ;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + + ResultSet res = prest.executeQuery(); + con.commit(); + while (res.next()) + { list.add(new Lecture(res.getString("lectureID"), res .getString("name"), res.getString("isActive"), res @@ -617,19 +877,38 @@ public class SQL { return list; }// end getLectureListPermissionRead + + + + + public List getLectureListPermissionWrite(String userID) { List list = new ArrayList(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet res = stm + //Statement stm = con.createStatement(); + /*ResultSet res = stm .executeQuery("SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " + "WHERE pml.userID='" + userID + "' AND u.userID=pml.userID AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID;"); - while (res.next()) { + */ + + String sql = "SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " + + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " + + "WHERE pml.userID=" + + "?" + + " AND u.userID=pml.userID AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + + ResultSet res = prest.executeQuery(); + con.commit(); + while (res.next()) + { list.add(new Lecture(res.getString("lectureID"), res .getString("name"), res.getString("isActive"), res @@ -648,18 +927,37 @@ public class SQL { return list; }// end getLectureListPermissionWrite - public List getLectureListPermissionAdmin(String userID) { + + + + + + public List getLectureListPermissionAdmin(String userID) + { List list = new ArrayList(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet res = stm + //Statement stm = con.createStatement(); + + /*ResultSet res = stm .executeQuery("SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " + "WHERE i.GUID_imageID=l.imageID AND pml.rec_admin=true AND l.lectureID=pml.lectureID AND u.userID=pml.userID AND u.userID='" + userID + "';"); - while (res.next()) { + */ + + String sql = "SELECT DISTINCT l.lectureID, l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user " + + "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u " + + "WHERE i.GUID_imageID=l.imageID AND pml.rec_admin=true AND l.lectureID=pml.lectureID AND u.userID=pml.userID AND u.userID= ? ;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + + ResultSet res = prest.executeQuery(); + con.commit(); + while (res.next()) + { list.add(new Lecture(res.getString("lectureID"), res .getString("name"), res.getString("isActive"), res @@ -678,14 +976,24 @@ public class SQL { return list; }// end getLectureListPermissionAdmin + + + + + + //No prepared statement to do here public List getLectureList() { List list = new ArrayList(); - try { + try + { Connection con = getConnection(); Statement stm = con.createStatement(); ResultSet res = stm .executeQuery("SELECT l.lectureID, l.name, l.isActive,l.startTime,l.endTime,l.lastUsed,l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.m_user u where i.GUID_imageID=l.imageID and l.admin_owner=u.userID;"); - while (res.next()) { + + + while (res.next()) + { list.add(new Lecture(res.getString("lectureID"), res .getString("name"), res.getString("isActive"), res @@ -704,6 +1012,10 @@ public class SQL { return null; } + + + + //No prepared statement to do here public List getAllOS() { List list = new ArrayList<>(); try { @@ -727,18 +1039,38 @@ public class SQL { } + + + + public Map getPersonData(String Vorname, String Nachname) { Map map = new HashMap(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); + + /* ResultSet rs = stm .executeQuery("SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like '" + Nachname + "' and u.Vorname like '" + Vorname + "' and u.institution=i.institutionID;"); - while (rs.next()) { + */ + String sql = "SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like " + + "?" + + " and u.Vorname like " + + "?" + + " and u.institution=i.institutionID;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, Vorname); + prest.setString(2, Nachname); + + ResultSet rs = prest.executeQuery(); + con.commit(); + while (rs.next()) + { map.put("mail", rs.getString("mail")); map.put("Nachname", rs.getString("Nachname")); @@ -756,21 +1088,31 @@ public class SQL { return null; } + + + + + public int setLectureData(String pk_person, String pk_image, int imageversion, String name, String desc, String shortdesc, - String start, String end, boolean isactive) { + String start, String end, boolean isactive) + { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); int active_bol = 0; - if (isactive == true) { + if (isactive == true) + { active_bol = 1; } - try { + + try + { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); String uid = UUID.randomUUID().toString(); + /* stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES('" + uid + "','" @@ -799,7 +1141,54 @@ public class SQL { + pk_person + "','" + pk_person + "');"); + + */ + String sql = "INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES(" + + "?" + + "," + + "?" + + "," + + "?" + + "," + + "?" + + "," + + "?" + + ",'" + + formatter.format(new Date()) + + "'," + + "?" + + "," + + "?" + + "," + + "?" + + "," + + "?" + + ",'" + + formatter.format(new Date()) + + "','" + + formatter.format(new Date()) + + "'," + + "?" + + "," + + "?" + ");"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, uid); + prest.setString(2, name); + prest.setInt(3, active_bol); + prest.setString(4, start); + prest.setString(5, end); + prest.setString(6, shortdesc); + prest.setString(7, desc); + prest.setString(8, pk_image); + prest.setInt(9, imageversion); + prest.setString(10, pk_person); + prest.setString(11, pk_person); + + prest.executeUpdate(); con.commit(); + + //con.commit(); con.close(); } catch (SQLException e) { // TODO Auto-generated catch block @@ -810,14 +1199,27 @@ public class SQL { } + + + public Map getImageIDandVersion(String name) { Map map = new HashMap(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet image = stm + + //Statement stm = con.createStatement(); + /*ResultSet image = stm .executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '" + name + "';"); + */ + + String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like ?"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, name); + + ResultSet image = prest.executeQuery(); + con.commit(); while (image.next()) { map.put("GUID", image.getString("GUID_imageID")); map.put("version", @@ -834,15 +1236,32 @@ public class SQL { return null; } + + + + public Map getImageData(String id, String version) { Map map = new HashMap(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet data = stm - .executeQuery("SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = '" + //Statement stm = con.createStatement(); + + + /* + ResultSet data = stm.executeQuery("SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = '" + id + "' and imageVersion = '" + version + "' ;"); - while (data.next()) { + */ + + String sql = "SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = ? and imageVersion = ? ;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, id); + prest.setString(2, version); + + ResultSet data = prest.executeQuery(); + con.commit(); + while (data.next()) + { map.put("name", data.getString("image_name")); map.put("internet", data.getString("cond_hasInternetRestriction")); @@ -871,23 +1290,31 @@ public class SQL { } return null; } + + + public int UpdateImageData(String name, String newName, String desc, String image_path, boolean license, boolean internet, long cpu, long ram, String id, String version, boolean isTemplate, - long filesize, String shareMode, String ospk) { + long filesize, String shareMode, String ospk) +{ try { Connection con = getConnection(); DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); int newVersion = Integer.parseInt(version) + 1; int internet_bol = 0; int license_bol = 0; - if (internet == true) { + + if (internet == true) + { internet_bol = 1; } - if (license == true) { + + if (license == true) + { license_bol = 1; } /* @@ -916,6 +1343,7 @@ public class SQL { + version + ";"); */ + /* stm.executeUpdate("UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " + newVersion + ",image_name = '" @@ -949,8 +1377,63 @@ public class SQL { + id + "' AND imageVersion = " + version + ";"); + + */ + + String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " + + "?" + + ",image_name = " + + "?" + + ",image_description = " + + "?" + + " ,image_path = " + + "?" + + " ,image_update_time = '" + + formatter.format(new Date()) + + "' ,rec_change_time = '" + + formatter.format(new Date()) + + "' ,cond_hasLicenseRestriction = " + + "?" + + " ,cond_hasInternetRestriction = " + + "?" + + " ,cond_minRAM = " + + "?" + + " ,cond_minCPUs = " + + "?" + + " ,image_isTemplate = " + // + isTemplate + + "0 " + + ",content_operatingSystem = " + + "?" + + ",image_filesize = " + + "?" + + ",image_syncMode = " + + "?" + + " WHERE GUID_imageID = " + + "?" + + " AND imageVersion = " + + "?" + ";"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setInt(1, newVersion); + prest.setString(2, newName); + prest.setString(3, desc); + prest.setString(4, image_path); + prest.setInt(5, license_bol); + prest.setInt(6, internet_bol); + prest.setLong(7, ram); + prest.setLong(8, cpu); + prest.setString(9, ospk); + prest.setLong(10, filesize); + prest.setString(11, shareMode); + prest.setString(12, id); + prest.setString(13, version); + + prest.executeUpdate(); + + con.commit(); //needed, though executeUpdate()! - con.commit(); + //con.commit(); con.close(); return 0; } catch (SQLException e) { @@ -961,17 +1444,30 @@ public class SQL { return -1; } - public boolean deleteImage(String id, String version) { + + + + public boolean deleteImage(String id, String version) + { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); //delete the image record - stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '"+ id + "' " + "AND imageVersion = '" + version + "';"); + //stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '"+ id + "' " + "AND imageVersion = '" + version + "';"); + String sql = "DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = ? AND imageVersion = ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, id); + prest.setString(2, version); + prest.executeUpdate(); //delete all permission records for this image - stm.executeUpdate("DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = '"+id+"';"); + //stm.executeUpdate("DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = '"+id+"';"); + sql = "DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = ?"; + prest = con.prepareStatement(sql); + prest.setString(1, id); + prest.executeUpdate(); con.commit(); @@ -987,20 +1483,27 @@ public class SQL { return false; } + + + + public int updateLectureData(String pk_image, String imageversion, String name, String newName, String desc, String shortdesc, - String start, String end, boolean isactive, String id) { + String start, String end, boolean isactive, String id) + { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); int active_bol = 0; - if (isactive == true) { + if (isactive == true) + { active_bol = 1; } try { Connection con = getConnection(); - Statement stm = con.createStatement(); - stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '" + //Statement stm = con.createStatement(); + + /*stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '" + newName + "',`isActive` = '" + active_bol @@ -1017,6 +1520,37 @@ public class SQL { + "',`admin_changeTime` = '" + formatter.format(new Date()) + "'WHERE `lectureID` = '" + id + "';"); + */ + + String sql = "UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = " + + "?" + + ",isActive = " + + "?" + + ",startTime = " + + "?" + + ",endTime = " + + "?" + + ",description = " + + "?" + + ",imageID = " + + "?" + + ",imageVersion = " + + "?" + + ",admin_changeTime = '" + + formatter.format(new Date()) + + "'WHERE `lectureID` = ?;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, newName); + prest.setInt(2, active_bol); + prest.setString(3, start); + prest.setString(4, end); + prest.setString(5, desc); + prest.setString(6, pk_image); + prest.setString(7, imageversion); + prest.setString(8, id); + + prest.executeUpdate(); con.commit(); con.close(); log.info("Succeeded to updateLectureData."); @@ -1030,16 +1564,23 @@ public class SQL { return 0; } + public boolean connectedToLecture(String id, String version) { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); - ResultSet rs = stm.executeQuery("SELECT lectureID FROM " - + "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id - + "' AND imageVersion = '" + version + "';"); + //ResultSet rs = stm.executeQuery("SELECT lectureID FROM "+ "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id+ "' AND imageVersion = '" + version + "';"); + + String sql = "SELECT lectureID FROM "+ "bwLehrpool.m_VLData_lecture WHERE imageID = ? AND imageVersion = ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, id); + prest.setString(2, version); + + ResultSet rs = prest.executeQuery(); + con.commit(); boolean ret = rs.first(); con.close(); return ret; @@ -1053,18 +1594,26 @@ public class SQL { return false; } + + + public boolean deleteLecture(String id) { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); //delete the lecture record - stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '" + id + "';"); + //stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '" + id + "';"); + String sql = "DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, id); + + prest.executeUpdate(); //delete all permission records for this lecture - stm.executeUpdate("DELETE FROM bwLehrpool.pm_VLData_lecture WHERE lectureID = '" + id + "';"); + //stm.executeUpdate("DELETE FROM bwLehrpool.pm_VLData_lecture WHERE lectureID = '" + id + "';"); con.commit(); con.close(); @@ -1078,18 +1627,36 @@ public class SQL { return false; } + + public String getFile(String imageid, String imageversion) { try { Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs = stm + /*ResultSet rs = stm .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '" + imageid + "' AND imageVersion = '" + imageversion + "';"); + * + */ + String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = " + + "?" + + " AND imageVersion = " + + "?" + + ";"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, imageid); + prest.setString(2, imageversion); + + ResultSet rs = prest.executeQuery(); + con.commit(); + + rs.next(); String path = rs.getString("image_path"); con.close(); @@ -1104,16 +1671,25 @@ public class SQL { return null; } + + + public Map getDeleteXMLData(String id) { Map map = new HashMap(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); - ResultSet rs = stm - .executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='" - + id + "';"); + //ResultSet rs = stm.executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='"+ id + "';"); + String sql = "SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID = ?;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, id); + + ResultSet rs = prest.executeQuery(); + con.commit(); rs.next(); + map.put("date", rs.getString("admin_createTime").replace(" ", "") .replace("-", "").replace(":", "")); map.put("name", rs.getString("name")); @@ -1129,6 +1705,9 @@ public class SQL { return null; } + + + public int UpdateImagePath(String name) { try { @@ -1142,12 +1721,29 @@ public class SQL { + image_path + "' WHERE `image_path` = '" + "temp/" + name + "';"); */ + /* stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" + image_path + "' WHERE `image_path` = '" + "temp/" + name + "';"); + */ + + String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET image_path = " + + "?" + + " WHERE image_path = '" + + "temp/" + + "'?" + + ";"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, image_path); + prest.setString(2, name); + + prest.executeUpdate(); + + con.commit(); con.close(); log.info("Succesfully updated image path"); @@ -1160,6 +1756,8 @@ public class SQL { return -1; } + + public List getAllUniversities() { List list = new ArrayList<>(); try { @@ -1182,17 +1780,23 @@ public class SQL { } + + + public String getOSpk(String os, String architecture) { try { Connection con = getConnection(); Statement stm = con.createStatement(); //log.info("Getting PrimaryKey for OS: " + os + " architecture: " + architecture); - ResultSet rs = stm - .executeQuery("SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like '" - + os - + "' and architecture like '" - + architecture - + "';"); + //ResultSet rs = stm.executeQuery("SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like '"+ os+ "' and architecture like '"+ architecture+ "';"); + + String sql = "SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like ? and architecture like ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, os); + prest.setString(2, architecture); + + ResultSet rs = prest.executeQuery(); + con.commit(); rs.first(); String pkos = rs.getString("operatingSystemID"); //log.info(" - Key is " + pkos); @@ -1208,6 +1812,9 @@ public class SQL { return null; } + + + public String getOS(String pk) { try { @@ -1215,9 +1822,14 @@ public class SQL { Statement stm = con.createStatement(); // log.info(new Date() + // " - OS: "+os+" architecture: "+architecture); - ResultSet rs = stm - .executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '" - + pk + "';"); + //ResultSet rs = stm.executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '"+ pk + "';"); + String sql = "SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = ?;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, pk); + + ResultSet rs = prest.executeQuery(); + con.commit(); rs.first(); String os = rs.getString("name") + " " + rs.getString("architecture"); @@ -1233,15 +1845,21 @@ public class SQL { return null; } + + public Map getLectureData(String id) { Map map = new HashMap(); try { Connection con = getConnection(); - Statement stm = con.createStatement(); - ResultSet data = stm - //.executeQuery("select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= '" - .executeQuery("select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, i.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= '" - + id + "' ;"); + //Statement stm = con.createStatement(); + //ResultSet data = stm.executeQuery("select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= '"+ id + "' ;"); + + String sql = "select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= ?" ; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, id); + + ResultSet data = prest.executeQuery(); + con.commit(); while (data.next()) { map.put("name", data.getString("name")); map.put("isActive", data.getString("isActive")); @@ -1264,16 +1882,25 @@ public class SQL { return null; } + + + public boolean checkUser(String username) { Statement stm; boolean ret = false; try { Connection con = getConnection(); - stm = con.createStatement(); - ResultSet rs = stm - .executeQuery("Select * from m_user where loginName like '" - + username + "'"); - if (rs.next()) { + //stm = con.createStatement(); + //ResultSet rs = stm.executeQuery("Select * from m_user where loginName like '"+ username + "'"); + + String sql = "Select * from m_user where loginName like ?"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, username); + + ResultSet rs = prest.executeQuery(); + con.commit(); + if (rs.next()) + { ret = true; log.info("Checking user: " + rs.getString("loginName") + " exists."); @@ -1292,15 +1919,26 @@ public class SQL { return ret; } + + + public int getRoleID(String role) { try { Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs = stm + /*ResultSet rs = stm .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '" + role + "';"); + rs.first();*/ + + String sql = "SELECT roleID FROM bwLehrpool.m_role where name like ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, role); + + ResultSet rs = prest.executeQuery(); + con.commit(); rs.first(); return rs.getInt("roleID"); @@ -1313,15 +1951,23 @@ public class SQL { return -1; } + + + public String getLectureID(String name) { String id = null; try { Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet lecture = stm - .executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '" - + name + "';"); - while (lecture.next()) { + //ResultSet lecture = stm.executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '"+ name + "';"); + String sql = "SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like ?;"; + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, name); + + ResultSet lecture = prest.executeQuery(); + con.commit(); + while (lecture.next()) + { id = lecture.getString("lectureID"); } @@ -1335,8 +1981,11 @@ public class SQL { return id; } + + // get all users from satellite -- temp, tb replaced by allUsers bwLehrpool // does -NOT- return the user himself + //TODO prepared statement public List getAllOtherSatelliteUsers(List userID) { Connection con = getConnection(); Statement stm = null; @@ -1350,23 +1999,21 @@ public class SQL { // TODO Auto-generated catch block e.printStackTrace(); } - try { - String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('" - + userID.get(0) + ""; - - for (int i = 1; i < userID.size(); i++) { - query = query + "','" + userID.get(i); - } - query = query + "') ORDER BY Nachname ASC;"; + String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('" + + userID.get(0) + ""; - // res = - // stm.executeQuery("SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID !='"+ - // userID + "' ORDER BY Nachname ASC;"); + for (int i = 1; i < userID.size(); i++) { + query = query + "','" + userID.get(i); + } + query = query + "') ORDER BY Nachname ASC;"; + + try { res = stm.executeQuery(query); - } catch (SQLException e) { + } catch (SQLException e1) { // TODO Auto-generated catch block - e.printStackTrace(); + e1.printStackTrace(); } + try { while (res.next()) { // fill the list with users - permissions are all false because @@ -1384,6 +2031,12 @@ public class SQL { }// end getAllOtherSatelliteUsers() + + + + + + // get the permissions of an image for all other registered users -EXCEPT- // the logged on user public List getPermissionForUserAndImage(String userID, @@ -1401,18 +2054,34 @@ public class SQL { e.printStackTrace(); } try { - res = stm + + /*res = stm .executeQuery("SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!='" + userID + "' AND pmi.GUID_imageID='" + imageID - + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;"); - System.out + + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");*/ + + + String sql = "SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!= " + + "?" + + " AND pmi.GUID_imageID= " + + "?" + + " AND pmi.userID=u.userID ORDER BY u.Nachname ASC;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + prest.setString(2, imageID); + + res = prest.executeQuery(); + con.commit(); + + /*System.out .println("SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!='" + userID + "' AND pmi.GUID_imageID='" + imageID - + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;"); + + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");*/ } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); @@ -1463,29 +2132,39 @@ public class SQL { + + // get the permissions of a lecture for all other registered users -EXCEPT- the logged on user public List getPermissionForUserAndLecture(String userID, String lectureID) { Connection con = getConnection(); - Statement stm = null; + //Statement stm = null; ResultSet res = null; List list = new ArrayList(); + + try { - stm = con.createStatement(); - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - try { - res = stm + /*res = stm .executeQuery("SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname, u.Vorname, u.mail, pml.rec_read, pml.rec_write, pml.rec_admin " + "FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;"); - System.out + */ + String sql = "SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname, u.Vorname, u.mail, pml.rec_read, pml.rec_write, pml.rec_admin " + + "FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!= ? AND pml.lectureID= ? AND pml.userID=u.userID ORDER BY u.Nachname ASC;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + prest.setString(2, lectureID); + + res = prest.executeQuery(); + con.commit(); + + /*System.out .println("SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname, u.Vorname, u.mail, pml.rec_read, pml.rec_write, pml.rec_admin " + "FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;"); + */ } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); @@ -1493,7 +2172,8 @@ public class SQL { try { boolean image_read, image_write, link_allowed, image_admin, lecture_read, lecture_write, lecture_admin; - while (res.next()) { + while (res.next()) + { // reset old values and set the correct new values image_read = false; image_write = false; @@ -1539,51 +2219,17 @@ public class SQL { - - - - - - public boolean updateImagePermissions() { - boolean success = true; - - Connection con = getConnection(); - Statement stm = null; - try { - stm = con.createStatement(); - stm.executeQuery(""); - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - - return success; - }// end setImagePermissions() - - public boolean deleteImagePermissions() { - boolean success = true; - - Connection con = getConnection(); - Statement stm = null; - try { - stm = con.createStatement(); - stm.executeQuery(""); - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - return success; - }// end setImagePermissions() public int setImageRights(String pk_person, String pk_image, int role, int read, int write, int linkallowed, int admin) { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); String uid = UUID.randomUUID().toString(); + /* stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_image`(`GUID`,`GUID_imageID`,`roleID`,`userID`,`image_read`,`image_write`,`image_admin`,`link_allowed`)VALUES('" + uid + "','" @@ -1600,8 +2246,26 @@ public class SQL { + admin + "','" + linkallowed + "');"); + + */ + String sql = "INSERT INTO bwLehrpool.pm_VLData_image(GUID,GUID_imageID,roleID,userID,image_read,image_write,image_admin,link_allowed)VALUES" + "(?,?,?,?,?,?,?,?)"; + + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, uid); + prest.setString(2, pk_image); + prest.setInt(3, role); + prest.setString(4, pk_person); + prest.setInt(5, read); + prest.setInt(6, write); + prest.setInt(7, admin); + prest.setInt(8, linkallowed); + + prest.executeUpdate(); + con.commit(); con.close(); + } catch (SQLException e) { // TODO Auto-generated catch block log.info("Failed to setImageRights."); @@ -1610,37 +2274,51 @@ public class SQL { return 0; }// end setImageRights - public boolean writeAdditionalImageRights(String imageID, String userID, - boolean isRead, boolean isWrite, boolean isLinkAllowed, - boolean isAdmin) { + public boolean writeAdditionalImageRights(String imageID, String userID,boolean isRead, boolean isWrite, boolean isLinkAllowed,boolean isAdmin) + { boolean success = true; String uid = UUID.randomUUID().toString(); + Connection con = getConnection(); - Statement stm = null; try { - stm = con.createStatement(); /* log.info("INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, " + "userID, " + "image_read, " + "image_write, " + "Image_admin, " - + "link_allowed)" + "VALUES(" + "'" + uid + "'," + "'" + + "link_allowed)" + + + "VALUES(" + "'" + uid + "'," + "'" + imageID + "'," + "1," + "'" + userID + "'," + isRead + "," + isWrite + "," + isAdmin + "," + isLinkAllowed + "" + ");"); */ - stm.executeUpdate("INSERT INTO pm_VLData_image (" + "GUID, " + + String sql = "INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, " + "userID, " + "image_read, " + "image_write, " + "Image_admin, " - + "link_allowed)" + "VALUES(" + "'" + uid + "'," + "'" - + imageID + "'," + "1," + "'" + userID + "'," + isRead - + "," + isWrite + "," + isAdmin + "," + isLinkAllowed + "" - + ");"); + + "link_allowed)" + "VALUES" + "(?,?,?,?,?,?,?,?)"; + + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, uid); + prest.setString(2, imageID); + prest.setInt(3, 1); + prest.setString(4, userID); + prest.setBoolean(5, isRead); + prest.setBoolean(6, isWrite); + prest.setBoolean(7, isAdmin); + prest.setBoolean(8, isLinkAllowed); + + prest.executeUpdate(); + con.commit(); con.close(); + log.info("Written additional image rights."); + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); @@ -1648,16 +2326,32 @@ public class SQL { } return success; }// end writeAdditionalImageRights + + public int setLectureRights(String pk_person, String pk_lecture, int role, int read, int write, int admin) { try { Connection con = getConnection(); - Statement stm = con.createStatement(); + //Statement stm = con.createStatement(); String uid = UUID.randomUUID().toString(); + String sql = "INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES" + "(?,?,?,?,?,?)"; + + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, uid); + prest.setString(2, pk_lecture); + prest.setString(3, pk_person); + prest.setInt(4, read); + prest.setInt(5, write); + prest.setInt(6, admin); + + prest.executeUpdate(); + + /* stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES('" + uid + "','" @@ -1666,6 +2360,7 @@ public class SQL { + pk_person + "','" + read + "','" + write + "','" + admin + "');"); + */ con.commit(); log.info("Written lecture rights for " + pk_lecture); con.close(); @@ -1677,22 +2372,42 @@ public class SQL { return 0; } // end setLectureRights + public boolean writeAdditionalLectureRights(String lectureID, String userID, boolean isRead, boolean isWrite, boolean isAdmin) { boolean success = true; String uid = UUID.randomUUID().toString(); Connection con = getConnection(); - Statement stm = null; + //Statement stm = null; try { - stm = con.createStatement(); + //stm = con.createStatement(); + /* stm.executeUpdate("INSERT INTO pm_VLData_lecture (" + "GUID, " + "lectureID, " + "userID, " + "rec_read, " + "rec_write, " - + "rec_admin )" + "VALUES('" + uid + "'," + "'" + lectureID + + "rec_admin )" + + "VALUES('" + uid + "'," + "'" + lectureID + "'," + "'" + userID + "'," + isRead + "," + isWrite + "," + isAdmin + ");"); + */ + String sql = "INSERT INTO pm_VLData_lecture (GUID, lectureID, userID, rec_read, rec_write, rec_admin) VALUES" + + ("(?,?,?,?,?,?)"); + + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, uid); + prest.setString(2, lectureID); + prest.setString(3, userID); + prest.setBoolean(4, isRead); + prest.setBoolean(5, isWrite); + prest.setBoolean(6, isAdmin); + + //log.info("userdata: "+uid+" "+lectureID + " " + userID + " " + isRead + isWrite + isAdmin); + + prest.executeUpdate(); + con.commit(); con.close(); log.info("Written additional lecture rights for '" @@ -1705,6 +2420,8 @@ public class SQL { return success; }// end writeAdditionalLectureRights + + public void deleteAllAdditionalImagePermissions(String imageID, String userID) { Connection con = getConnection(); @@ -1712,11 +2429,17 @@ public class SQL { try { - stm = con.createStatement(); - int ret = stm - .executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '" - + imageID + "' AND userID != '" + userID + "';"); - + //stm = con.createStatement(); + //int ret = stm.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '"+ imageID + "' AND userID != '" + userID + "';"); + + String sql = "DELETE FROM pm_VLData_image WHERE GUID_imageID = ? AND userID != ?;"; + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, imageID); + prest.setString(2, userID); + + prest.executeUpdate(); + } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); @@ -1724,10 +2447,8 @@ public class SQL { try { - stm = con.createStatement(); - int ret = stm - .executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '" - + imageID + "' AND userID != '" + userID + "';"); + //stm = con.createStatement(); + //int ret = stm.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '"+ imageID + "' AND userID != '" + userID + "';"); con.commit(); @@ -1741,24 +2462,27 @@ public class SQL { public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) { Connection con = getConnection(); - Statement stm = null; + //Statement stm = null; try { - stm = con.createStatement(); - int ret = stm - .executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '" - + lectureID + "' AND userID != '" + userID + "';"); + //stm = con.createStatement(); + //int ret = stm.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"+ lectureID + "' AND userID != '" + userID + "';"); + String sql = "DELETE FROM pm_VLData_lecture WHERE lectureID = ? AND userID != ?;"; + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, lectureID); + prest.setString(2, userID); + + prest.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } try { - stm = con.createStatement(); - int ret = stm - .executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '" - + lectureID + "' AND userID != '" + userID + "';"); + //stm = con.createStatement(); + //int ret = stm.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"+ lectureID + "' AND userID != '" + userID + "';"); con.commit(); @@ -1768,6 +2492,8 @@ public class SQL { } }// end deleteAllAdditionalImagePermissions + + public List getAdditionalImageContacts(String imageID) { @@ -1775,15 +2501,21 @@ public class SQL { Connection con = getConnection(); Statement stm; - try { - stm = con.createStatement(); - - ResultSet rs = stm.executeQuery("select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = '"+imageID+"' and image_write = '1' and img.userID = u.userID;"); + try + { + //stm = con.createStatement(); + //ResultSet rs = stm.executeQuery("select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = '"+imageID+"' and image_write = '1' and img.userID = u.userID;"); + String sql = "select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = ? and image_write = '1' and img.userID = u.userID;"; + PreparedStatement prest = con.prepareStatement(sql); + + prest.setString(1, imageID); + + ResultSet rs = prest.executeQuery(); + con.commit(); + while (rs.next()) { - //also possible with mail, not used due to size issues - //list.add(rs.getString("Nachname") + "," + rs.getString("Vorname") + " " + rs.getString("mail")); list.add(rs.getString("Nachname") + "," + rs.getString("Vorname")+" "); } -- cgit v1.2.3-55-g7522