· 10 years ago · Feb 03, 2016, 12:36 PM
1package main.java.pl.mn.model;
2
3import com.sun.org.apache.xalan.internal.xsltc.compiler.util.BooleanType;
4
5import java.sql.*;
6
7/**
8 * Klasa odpowiedzialna za komunikację między serwerem a bazą danych SQL
9 * Created by mn on 22.01.16.
10 */
11public class SQLHandler {
12 private Connection c;
13 private Statement stmnt;
14
15 /**
16 * Metoda dodająca użytkownika do bazy danych
17 * @param username nazwa użytkownika
18 * @param password hasło użytkownika
19 */
20 public void insertUser(String username, String password){
21 double salt = Math.random();
22 String query = "INSERT INTO `users`(`username`, `password`, `salt`) " +
23 "VALUES ('"+username+"',PASSWORD(CONCAT(PASSWORD('"+password+"'),"+salt+")),"+salt+")";
24 try {
25 stmnt.executeUpdate(query);
26 } catch (SQLException e) {
27
28 }
29 }
30
31 /**
32 * Metoda przypisująca użytkownika do grupy w tabeli usergroups
33 * @param username nazwa użytkownika
34 * @param groupname nazwa grupy
35 */
36 public void assignToGroup(String username, String groupname){
37 String query = "INSERT INTO `usergroup`(`user_id`,`group_id`) " +
38 "VALUES ((SELECT `id` FROM `users` WHERE `username`='"+username+"')," +
39 "(SELECT `id` FROM `groups` WHERE `group_name`='"+groupname+"'))";
40 try {
41 stmnt.executeUpdate(query);
42 } catch (SQLException e) {
43
44 }
45 }
46
47 /**
48 * Metoda dodajÄ…ca informacje o pliku do bazy danych
49 * @param filename nazwa pliku
50 * @param username nazwa użytkownika dodającego plik
51 */
52 public void insertFileInfo(String filename, String username){
53 String query = "INSERT INTO `files`(`filename`, `owner_id`, `group_id`, `user_read`, `user_write`, `group_read`, `group_write`) " +
54 "VALUES ("+filename+",(SELECT `id` FROM `users` WHERE `username`='"+username+"')," +
55 "(SELECT `group_id` FROM `usergroup` WHERE `user_id`=(SELECT `id` FROM `users` WHERE `username`='"+username+"'))" +
56 ",TRUE,TRUE,TRUE,FALSE)";
57 try {
58 stmnt.executeUpdate(query);
59 } catch (SQLException e) {
60
61 }
62 }
63
64 /**
65 * Metoda sprawdzająca czy hasło jest poprawne
66 * @param username nazwa użytkownika
67 * @param password sprawdzane hasło
68 * @return true, jeżeli hasło jest poprawne; false w przeciwnym wypadku
69 */
70 public boolean verifyPassword(String username, String password){
71 String query = "SELECT `password`=PASSWORD(CONCAT(PASSWORD('"+password+"'),`salt`)) FROM `users` WHERE `username`='"+username+"'";
72 try {
73 ResultSet rs = stmnt.executeQuery(query);
74 if(rs.next()){
75 return true;
76 }
77 } catch (SQLException e) {
78 e.printStackTrace();
79 }
80 return false;
81 }
82
83 /**
84 * Metoda sprawdza sprawdza czy użytkownik ma prawo do odczytu pliku
85 * @param username Nazwa użytkownika proszącego o dostęp
86 * @param filename Nazwa pliku na serwerze
87 * @return true, jeżeli ma prawa do jego odczytu, false w przeciwnym wypadku
88 */
89 public boolean verifyReadPermission(String username, String filename){
90 String query = "SELECT `group_read` OR (`user_read` AND `owner_id`=(SELECT `id` FROM `users` WHERE `username`='"+username+"')) " +
91 "FROM `files` AS `f` WHERE `f`.`filename`='"+filename+"' " +
92 "AND `f`.`group_id` IN (SELECT `group_id` FROM `usergroup` WHERE `user_id`=(SELECT `id` FROM `users` WHERE `username`='"+username+"'))";
93 try {
94 ResultSet rs = stmnt.executeQuery(query);
95 while(rs.next()){
96 Boolean group = rs.getBoolean("group_read");
97 Boolean user = rs.getBoolean("user_read");
98 if (user || group){
99 return true;
100 }
101 return true;
102 }
103
104 } catch (SQLException e) {
105 e.printStackTrace();
106 }
107 return false;
108 }
109
110 public SQLHandler(){
111 try {
112 c= DriverManager.getConnection("jdbc:mysql://mysql.agh.edu.pl/mnitek", "mnitek", "ThPcohoc");
113 stmnt = c.createStatement();
114 initDataBase();
115 } catch (SQLException e) {
116 e.printStackTrace();
117 }
118 }
119
120 private void initDataBase(){
121 String usersQuery = "CREATE TABLE IF NOT EXISTS `users` ("+
122 "`id` INT(1) NOT NULL AUTO_INCREMENT,"+
123 "`username` VARCHAR(10) NOT NULL,"+
124 "`password` VARCHAR(45) NOT NULL,"+
125 "`salt` VARCHAR(20) NOT NULL,"+
126 " PRIMARY KEY (`id`),"+
127 " UNIQUE KEY `username` (`username`)"+
128 ") ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=3";
129
130 String groupsQuery = "CREATE TABLE IF NOT EXISTS `groups` ("+
131 "`id` INT(1) NOT NULL AUTO_INCREMENT,"+
132 "`group_name` VARCHAR(10) NOT NULL,"+
133 "PRIMARY KEY (`id`),"+
134 "UNIQUE KEY `group_name` (`group_name`)"+
135 ")ENGINE=MyISAM DEFAULT CHARSET = latin2 AUTO_INCREMENT=2";
136
137 String usergroupQuery = "CREATE TABLE IF NOT EXISTS `usergroup` ("+
138 "`user_id` INT(11) NOT NULL,"+
139 "`group_id` INT(11) NOT NULL"+
140 ") ENGINE=MyISAM DEFAULT CHARSET=latin2;";
141 String filesQuery = "CREATE TABLE IF NOT EXISTS `files` ("+
142 "`id` INT(11) NOT NULL AUTO_INCREMENT,"+
143 "`filename` VARCHAR(50) NOT NULL,"+
144 "`owner_id` INT(11) NOT NULL,"+
145 "`group_id` INT(11) NOT NULL,"+
146 "`user_read` tinyint(1) NOT NULL DEFAULT '1',"+
147 "`user_write` tinyint(1) NOT NULL DEFAULT '1',"+
148 "`group_read` tinyint(1) NOT NULL DEFAULT '1',"+
149 "`group_write` tinyint(1) NOT NULL DEFAULT '1',"+
150 "PRIMARY KEY (`id`)," +
151 "UNIQUE KEY `filename` (`filename`)" +
152 ")ENGINE=MyISAM DEFAULT CHARSET = latin2 AUTO_INCREMENT=2";
153
154 String adminGroupQuery = "INSERT IGNORE INTO `groups` (`id`, `group_name`) VALUES (1, 'admin')";
155 try {
156 stmnt.executeUpdate(usersQuery);
157 stmnt.executeUpdate(groupsQuery);
158 stmnt.executeUpdate(usergroupQuery);
159 stmnt.executeUpdate(filesQuery);
160 stmnt.executeUpdate(adminGroupQuery);
161 } catch (SQLException e) {
162 e.printStackTrace();
163 }
164 }
165}