diff options
| author | Nino Breuer | 2014-11-17 17:52:12 +0100 |
|---|---|---|
| committer | Nino Breuer | 2014-11-17 17:52:12 +0100 |
| commit | afb799c45e5fe231e77b056bd94a6431bbd5a1ca (patch) | |
| tree | 8fe681e9a2b02eba38a5b4565a520e2e37d42d2b | |
| parent | - (diff) | |
| download | tutor-module-afb799c45e5fe231e77b056bd94a6431bbd5a1ca.tar.gz tutor-module-afb799c45e5fe231e77b056bd94a6431bbd5a1ca.tar.xz tutor-module-afb799c45e5fe231e77b056bd94a6431bbd5a1ca.zip | |
• changed regular statements to prepared statements to prevent sql injections
| -rw-r--r-- | dozentenmodulserver/src/main/java/sql/SQL.java | 1250 |
1 files changed, 991 insertions, 259 deletions
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<Image> getImageListPermissionWrite(String userID) {
Connection con = getConnection();
- Statement stm;
+ //Statement stm;
List<Image> list = new ArrayList<Image>();
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<Image> getImageListPermissionRead(String userID) {
Connection con = getConnection();
- Statement stm;
+ //Statement stm;
List<Image> list = new ArrayList<Image>();
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<Image> 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<Image> getImageListPermissionLink(String userID) {
Connection con = getConnection();
- Statement stm;
+ //Statement stm;
List<Image> list = new ArrayList<Image>();
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<Image> getImageListPermissionAdmin(String userID){
Connection con = getConnection();
- Statement stm;
+ //Statement stm;
List<Image> list = new ArrayList<Image>();
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<Image> 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<Lecture> getLectureListPermissionRead(String userID) {
List<Lecture> list = new ArrayList<Lecture>();
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<Lecture> getLectureListPermissionWrite(String userID) {
List<Lecture> list = new ArrayList<Lecture>();
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<Lecture> getLectureListPermissionAdmin(String userID) {
+
+
+
+
+
+ public List<Lecture> getLectureListPermissionAdmin(String userID)
+ {
List<Lecture> list = new ArrayList<Lecture>();
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<Lecture> getLectureList() {
List<Lecture> list = new ArrayList<Lecture>();
- 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<String> getAllOS() {
List<String> list = new ArrayList<>();
try {
@@ -727,18 +1039,38 @@ public class SQL { }
+
+
+
+
public Map<String, String> getPersonData(String Vorname, String Nachname) {
Map<String, String> map = new HashMap<String, String>();
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<String, String> getImageIDandVersion(String name) {
Map<String, String> map = new HashMap<String, String>();
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<String, String> getImageData(String id, String version) {
Map<String, String> map = new HashMap<String, String>();
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<String, String> getDeleteXMLData(String id) {
Map<String, String> map = new HashMap<String, String>();
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<String> getAllUniversities() {
List<String> 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<String, String> getLectureData(String id) {
Map<String, String> map = new HashMap<String, String>();
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<Person> getAllOtherSatelliteUsers(List<String> 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) + "";
+ 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;";
-
- // 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<Person> 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<Person> getPermissionForUserAndLecture(String userID,
String lectureID) {
Connection con = getConnection();
- Statement stm = null;
+ //Statement stm = null;
ResultSet res = null;
List<Person> list = new ArrayList<Person>();
+
+
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<String> 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")+" ");
}
|