aboutsummaryrefslogtreecommitdiff
path: root/src/org
diff options
context:
space:
mode:
authorAnton Tananaev <anton.tananaev@gmail.com>2015-06-10 15:45:55 +1200
committerAnton Tananaev <anton.tananaev@gmail.com>2015-06-10 15:45:55 +1200
commitee087f587a96700d91e407d434e94d1616c18368 (patch)
treefc9642ed3159dd16ebd1c5448233b251b091921c /src/org
parentee4c07115d3bc3e58b92310f54bee7bd616ec669 (diff)
parent96a2d024658746f50628727f7255c4e19ecdb1b4 (diff)
downloadtrackermap-server-ee087f587a96700d91e407d434e94d1616c18368.tar.gz
trackermap-server-ee087f587a96700d91e407d434e94d1616c18368.tar.bz2
trackermap-server-ee087f587a96700d91e407d434e94d1616c18368.zip
Merge pull request #1240 from demianalonso/master
Move SQL queries to the config file
Diffstat (limited to 'src/org')
-rw-r--r--src/org/traccar/database/DataManager.java69
-rw-r--r--src/org/traccar/database/QueryBuilder.java22
-rw-r--r--src/org/traccar/helper/Hashing.java20
-rw-r--r--src/org/traccar/model/User.java9
4 files changed, 78 insertions, 42 deletions
diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java
index db863e8f4..9633ada58 100644
--- a/src/org/traccar/database/DataManager.java
+++ b/src/org/traccar/database/DataManager.java
@@ -15,10 +15,12 @@
*/
package org.traccar.database;
-import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.io.File;
+import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLClassLoader;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
@@ -30,10 +32,13 @@ import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
+
import javax.naming.InitialContext;
import javax.sql.DataSource;
+
import org.traccar.Context;
import org.traccar.helper.DriverDelegate;
+import org.traccar.helper.Hashing;
import org.traccar.helper.Log;
import org.traccar.http.JsonConverter;
import org.traccar.model.Device;
@@ -42,6 +47,8 @@ import org.traccar.model.Position;
import org.traccar.model.Server;
import org.traccar.model.User;
+import com.mchange.v2.c3p0.ComboPooledDataSource;
+
public class DataManager {
private static final long DEFAULT_REFRESH_DELAY = 300;
@@ -167,15 +174,13 @@ public class DataManager {
Server server = new Server();
server.setRegistration(true);
- QueryBuilder.create(dataSource,
- "INSERT INTO server (registration, latitude, longitude, zoom)" +
- "VALUES (:registration, :latitude, :longitude, :zoom);")
+ QueryBuilder.create(dataSource, properties.getProperty("database.insertServer"))
.setObject(server)
.executeUpdate();
mockData(admin.getId());
}
-
+
private void mockData(long userId) {
if (Boolean.valueOf(Context.getProps().getProperty("database.mock"))) {
try {
@@ -213,46 +218,43 @@ public class DataManager {
}
public User login(String email, String password) throws SQLException {
- return QueryBuilder.create(dataSource,
- "SELECT * FROM user WHERE email = :email AND " +
- "password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR);")
+ return QueryBuilder.create(dataSource, properties.getProperty("database.loginUser"))
.setString("email", email)
- .setString("password", password)
+ .setBytes("password", Hashing.sha256(password))
.executeQuerySingle(new User());
}
public Collection<User> getUsers() throws SQLException {
- return QueryBuilder.create(dataSource,
- "SELECT * FROM user;")
+ return QueryBuilder.create(dataSource, properties.getProperty("database.selectUsersAll"))
.executeQuery(new User());
}
public void addUser(User user) throws SQLException {
- user.setId(QueryBuilder.create(dataSource,
- "INSERT INTO user (name, email, password, admin) " +
- "VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin);")
+ user.setId(QueryBuilder.create(dataSource, properties.getProperty("database.insertUser"))
.setObject(user)
.executeUpdate());
}
public void updateUser(User user) throws SQLException {
- QueryBuilder.create(dataSource,
- "UPDATE user SET name = :name, email = :email, admin = :admin," +
- "password = CASEWHEN((SELECT password FROM user WHERE id = :id) = :password, :password, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR)) WHERE id = :id;")
+ QueryBuilder.create(dataSource, properties.getProperty("database.updateUser"))
.setObject(user)
.executeUpdate();
+
+ if(user.getPassword() != null) {
+ QueryBuilder.create(dataSource, properties.getProperty("database.updateUserPassword"))
+ .setObject(user)
+ .executeUpdate();
+ }
}
public void removeUser(User user) throws SQLException {
- QueryBuilder.create(dataSource,
- "DELETE FROM user WHERE id = :id;")
+ QueryBuilder.create(dataSource, properties.getProperty("database.deleteUser"))
.setObject(user)
.executeUpdate();
}
public Collection<Permission> getPermissions() throws SQLException {
- return QueryBuilder.create(dataSource,
- "SELECT userId, deviceId FROM user_device;")
+ return QueryBuilder.create(dataSource, properties.getProperty("database.getPermissions"))
.executeQuery(new Permission());
}
@@ -262,45 +264,38 @@ public class DataManager {
}
public Collection<Device> getDevices(long userId) throws SQLException {
- return QueryBuilder.create(dataSource,
- "SELECT * FROM device WHERE id IN (" +
- "SELECT deviceId FROM user_device WHERE userId = :userId);")
+ return QueryBuilder.create(dataSource, properties.getProperty("database.selectDevices"))
.setLong("userId", userId)
.executeQuery(new Device());
}
public void addDevice(Device device) throws SQLException {
- device.setId(QueryBuilder.create(dataSource,
- "INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);")
+ device.setId(QueryBuilder.create(dataSource, properties.getProperty("database.insertDevice"))
.setObject(device)
.executeUpdate());
}
public void updateDevice(Device device) throws SQLException {
- QueryBuilder.create(dataSource,
- "UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;")
+ QueryBuilder.create(dataSource, properties.getProperty("database.updateDevice"))
.setObject(device)
.executeUpdate();
}
public void removeDevice(Device device) throws SQLException {
- QueryBuilder.create(dataSource,
- "DELETE FROM device WHERE id = :id;")
+ QueryBuilder.create(dataSource, properties.getProperty("database.removeDevice"))
.setObject(device)
.executeUpdate();
}
public void linkDevice(long userId, long deviceId) throws SQLException {
- QueryBuilder.create(dataSource,
- "INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);")
+ QueryBuilder.create(dataSource, properties.getProperty("database.linkDevice"))
.setLong("userId", userId)
.setLong("deviceId", deviceId)
.executeUpdate();
}
public Collection<Position> getPositions(long userId, long deviceId, Date from, Date to) throws SQLException {
- return QueryBuilder.create(dataSource,
- "SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;")
+ return QueryBuilder.create(dataSource, properties.getProperty("database.selectPositions"))
.setLong("deviceId", deviceId)
.setDate("from", from)
.setDate("to", to)
@@ -318,14 +313,12 @@ public class DataManager {
}
public Server getServer() throws SQLException {
- return QueryBuilder.create(dataSource,
- "SELECT * FROM server;")
+ return QueryBuilder.create(dataSource, properties.getProperty("database.selectServer"))
.executeQuerySingle(new Server());
}
public void updateServer(Server server) throws SQLException {
- QueryBuilder.create(dataSource,
- "UPDATE server SET registration = :registration WHERE id = :id;")
+ QueryBuilder.create(dataSource, properties.getProperty("database.updateServer"))
.setObject(server)
.executeUpdate();
}
diff --git a/src/org/traccar/database/QueryBuilder.java b/src/org/traccar/database/QueryBuilder.java
index ff26221de..05ec3e35c 100644
--- a/src/org/traccar/database/QueryBuilder.java
+++ b/src/org/traccar/database/QueryBuilder.java
@@ -15,6 +15,7 @@
*/
package org.traccar.database;
+import java.lang.reflect.Array;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
@@ -31,7 +32,9 @@ import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
+
import javax.sql.DataSource;
+
import org.traccar.model.Factory;
public class QueryBuilder {
@@ -211,6 +214,23 @@ public class QueryBuilder {
return this;
}
+ public QueryBuilder setBytes(String name, byte[] value) throws SQLException {
+ for (int i : indexes(name)) {
+ try {
+ if (value == null) {
+ statement.setNull(i, Types.VARCHAR);
+ } else {
+ statement.setBytes(i, value);
+ }
+ } catch (SQLException error) {
+ statement.close();
+ connection.close();
+ throw error;
+ }
+ }
+ return this;
+ }
+
public QueryBuilder setObject(Object object) throws SQLException {
Method[] methods = object.getClass().getMethods();
@@ -231,6 +251,8 @@ public class QueryBuilder {
setString(name, (String) method.invoke(object));
} else if (method.getReturnType().equals(Date.class)) {
setDate(name, (Date) method.invoke(object));
+ } else if (method.getReturnType().equals(byte[].class)) {
+ setBytes(name, (byte[]) method.invoke(object));
}
} catch (IllegalAccessException error) {
} catch (InvocationTargetException error) {
diff --git a/src/org/traccar/helper/Hashing.java b/src/org/traccar/helper/Hashing.java
new file mode 100644
index 000000000..7e7579ede
--- /dev/null
+++ b/src/org/traccar/helper/Hashing.java
@@ -0,0 +1,20 @@
+package org.traccar.helper;
+
+import java.nio.charset.StandardCharsets;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
+
+public class Hashing {
+
+ public static byte[] sha256(String text) {
+ try {
+ MessageDigest md = MessageDigest.getInstance("SHA-256");
+ md.update(text.getBytes(StandardCharsets.UTF_8));
+
+ return md.digest();
+ } catch (NoSuchAlgorithmException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+}
diff --git a/src/org/traccar/model/User.java b/src/org/traccar/model/User.java
index 2df5f276d..410bc4d74 100644
--- a/src/org/traccar/model/User.java
+++ b/src/org/traccar/model/User.java
@@ -15,6 +15,8 @@
*/
package org.traccar.model;
+import org.traccar.helper.Hashing;
+
public class User implements Factory {
@Override
@@ -34,9 +36,9 @@ public class User implements Factory {
public String getEmail() { return email; }
public void setEmail(String email) { this.email = email; }
- private String password;
- public String getPassword() { return password; }
- public void setPassword(String password) { this.password = password; }
+ private byte[] password;
+ public byte[] getPassword() { return password; }
+ public void setPassword(String password) { this.password = Hashing.sha256(password); }
private boolean readonly;
@@ -57,5 +59,4 @@ public class User implements Factory {
private double longitude;
private int zoom;
-
}