From b70876b7a3b852d0f0b9aade30d6a9405c054ac5 Mon Sep 17 00:00:00 2001 From: Demian Date: Tue, 9 Jun 2015 13:10:50 -0300 Subject: Moved all the SQL queries to the configuration file --- .gitignore | 4 ++ debug.xml | 70 +++++++++++++++++++++++++++++++ src/org/traccar/database/DataManager.java | 50 +++++++--------------- 3 files changed, 89 insertions(+), 35 deletions(-) create mode 100644 .gitignore diff --git a/.gitignore b/.gitignore new file mode 100644 index 000000000..3fe98582b --- /dev/null +++ b/.gitignore @@ -0,0 +1,4 @@ +/target +.classpath +.project +.settings/ diff --git a/debug.xml b/debug.xml index 6ca0c224a..bd747c97f 100644 --- a/debug.xml +++ b/debug.xml @@ -117,9 +117,78 @@ id INT PRIMARY KEY AUTO_INCREMENT); + + INSERT INTO server (registration, latitude, longitude, zoom) + VALUES (:registration, :latitude, :longitude, :zoom); + + + + SELECT * FROM server; + + + + UPDATE server SET registration = :registration WHERE id = :id; + + + + SELECT * + FROM user + WHERE email = :email AND password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR); + + + + SELECT * FROM user; + + + + INSERT INTO user (name, email, password, admin) + VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin); + + + + 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; + + + + DELETE FROM user WHERE id = :id; + + + + "SELECT userId, deviceId FROM user_device;" + + SELECT * FROM device; + + + SELECT * FROM device WHERE id IN (SELECT deviceId FROM user_device WHERE userId = :userId); + + + + INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId); + + + + UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id; + + + + DELETE FROM device WHERE id = :id; + + + + INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId); + + + + SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to; + INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other) @@ -130,6 +199,7 @@ UPDATE device SET positionId = :id WHERE id = :deviceId; + 5000 diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index db863e8f4..7c2505f77 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -167,9 +167,7 @@ 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(); @@ -213,46 +211,37 @@ 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) .executeQuerySingle(new User()); } public Collection 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(); } 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 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 +251,38 @@ public class DataManager { } public Collection 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 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 +300,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(); } -- cgit v1.2.3 From c4f3d8958caf55f99e383553980d8c2eea968243 Mon Sep 17 00:00:00 2001 From: Demian Date: Tue, 9 Jun 2015 16:29:27 -0300 Subject: Added compatibility for MySQL on all queries. Moved the sha256 logic to java to make it compatible across all databases --- debug.xml | 23 +++++++++++++---------- src/org/traccar/database/DataManager.java | 18 +++++++++++++++--- src/org/traccar/database/QueryBuilder.java | 22 ++++++++++++++++++++++ src/org/traccar/model/User.java | 24 +++++++++++++++++++++--- 4 files changed, 71 insertions(+), 16 deletions(-) diff --git a/debug.xml b/debug.xml index bd747c97f..d427b2cec 100644 --- a/debug.xml +++ b/debug.xml @@ -37,12 +37,12 @@ sa true - + CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(1024) NOT NULL, - email VARCHAR(1024) NOT NULL UNIQUE, + email VARCHAR(256) NOT NULL UNIQUE, password VARCHAR(1024) NOT NULL, salt VARCHAR(1024) DEFAULT '' NOT NULL, readonly BOOLEAN DEFAULT false NOT NULL, @@ -58,7 +58,7 @@ CREATE TABLE device ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(1024) NOT NULL, - uniqueId VARCHAR(1024) NOT NULL UNIQUE, + uniqueId VARCHAR(256) NOT NULL UNIQUE, status VARCHAR(1024), lastUpdate TIMESTAMP, positionId INT, @@ -67,8 +67,8 @@ CREATE TABLE user_device ( userId INT NOT NULL, deviceId INT NOT NULL, - read BOOLEAN DEFAULT true NOT NULL, - write BOOLEAN DEFAULT true NOT NULL, + `read` BOOLEAN DEFAULT true NOT NULL, + `write` BOOLEAN DEFAULT true NOT NULL, FOREIGN KEY (userId) REFERENCES user(id) ON DELETE CASCADE, FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE); @@ -101,7 +101,7 @@ FOREIGN KEY (deviceId) REFERENCES device(id)); ALTER TABLE device ADD - FOREIGN KEY (positionId) REFERENCES position(id); + FOREIGN KEY (positionId) REFERENCES `position`(id); ALTER TABLE device ADD FOREIGN KEY (dataId) REFERENCES data(id); @@ -133,7 +133,7 @@ SELECT * FROM user - WHERE email = :email AND password = CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR); + WHERE email = :email AND password = :password; @@ -142,18 +142,21 @@ INSERT INTO user (name, email, password, admin) - VALUES (:name, :email, CAST(HASH('SHA256', STRINGTOUTF8(:password), 1000) AS VARCHAR), :admin); + VALUES (:name, :email, :password, :admin); 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)) + admin = :admin WHERE id = :id; + + UPDATE user SET password = :password WHERE id = :id; + + DELETE FROM user WHERE id = :id; diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index 7c2505f77..7ded88d15 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,8 +32,10 @@ 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.Log; @@ -42,6 +46,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; @@ -173,7 +179,7 @@ public class DataManager { mockData(admin.getId()); } - + private void mockData(long userId) { if (Boolean.valueOf(Context.getProps().getProperty("database.mock"))) { try { @@ -213,7 +219,7 @@ public class DataManager { public User login(String email, String password) throws SQLException { return QueryBuilder.create(dataSource, properties.getProperty("database.loginUser")) .setString("email", email) - .setString("password", password) + .setBytes("password", User.sha256(password)) .executeQuerySingle(new User()); } @@ -232,6 +238,12 @@ public class DataManager { 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 { 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/model/User.java b/src/org/traccar/model/User.java index 2df5f276d..70a4498f5 100644 --- a/src/org/traccar/model/User.java +++ b/src/org/traccar/model/User.java @@ -15,6 +15,10 @@ */ package org.traccar.model; +import java.io.UnsupportedEncodingException; +import java.security.MessageDigest; +import java.security.NoSuchAlgorithmException; + public class User implements Factory { @Override @@ -34,9 +38,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 = sha256(password); } private boolean readonly; @@ -58,4 +62,18 @@ public class User implements Factory { private int zoom; + + public static byte[] sha256(String text) { + try { + MessageDigest md = MessageDigest.getInstance("SHA-256"); + + md.update(text.getBytes("UTF-8")); + byte[] digest = md.digest(); + return digest; + } catch (NoSuchAlgorithmException e) { + throw new RuntimeException(e); + } catch (UnsupportedEncodingException e) { + throw new RuntimeException(e); + } + } } -- cgit v1.2.3 From 62048ed5ec8054c9e0205f3308d0954926746866 Mon Sep 17 00:00:00 2001 From: Demian Date: Tue, 9 Jun 2015 19:45:22 -0300 Subject: Replaced tabs for spaces --- debug.xml | 26 +++++++++++++------------- src/org/traccar/database/DataManager.java | 6 +++--- src/org/traccar/model/User.java | 22 +++++++++++----------- 3 files changed, 27 insertions(+), 27 deletions(-) diff --git a/debug.xml b/debug.xml index d427b2cec..b058cd184 100644 --- a/debug.xml +++ b/debug.xml @@ -37,7 +37,7 @@ sa true - + CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, @@ -123,33 +123,33 @@ - SELECT * FROM server; + SELECT * FROM server; - UPDATE server SET registration = :registration WHERE id = :id; + UPDATE server SET registration = :registration WHERE id = :id; - SELECT * - FROM user - WHERE email = :email AND password = :password; + SELECT * + FROM user + WHERE email = :email AND password = :password; - SELECT * FROM user; + SELECT * FROM user; - INSERT INTO user (name, email, password, admin) + INSERT INTO user (name, email, password, admin) VALUES (:name, :email, :password, :admin); UPDATE user SET - name = :name, - email = :email, - admin = :admin + name = :name, + email = :email, + admin = :admin WHERE id = :id; @@ -190,8 +190,8 @@ - SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to; - + SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to; + INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other) diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index 7ded88d15..fdab359f4 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -240,9 +240,9 @@ public class DataManager { .executeUpdate(); if(user.getPassword() != null) { - QueryBuilder.create(dataSource, properties.getProperty("database.updateUserPassword")) - .setObject(user) - .executeUpdate(); + QueryBuilder.create(dataSource, properties.getProperty("database.updateUserPassword")) + .setObject(user) + .executeUpdate(); } } diff --git a/src/org/traccar/model/User.java b/src/org/traccar/model/User.java index 70a4498f5..9186e66c5 100644 --- a/src/org/traccar/model/User.java +++ b/src/org/traccar/model/User.java @@ -64,16 +64,16 @@ public class User implements Factory { public static byte[] sha256(String text) { - try { - MessageDigest md = MessageDigest.getInstance("SHA-256"); + try { + MessageDigest md = MessageDigest.getInstance("SHA-256"); - md.update(text.getBytes("UTF-8")); - byte[] digest = md.digest(); - return digest; - } catch (NoSuchAlgorithmException e) { - throw new RuntimeException(e); - } catch (UnsupportedEncodingException e) { - throw new RuntimeException(e); - } - } + md.update(text.getBytes("UTF-8")); + byte[] digest = md.digest(); + return digest; + } catch (NoSuchAlgorithmException e) { + throw new RuntimeException(e); + } catch (UnsupportedEncodingException e) { + throw new RuntimeException(e); + } + } } -- cgit v1.2.3 From 96a2d024658746f50628727f7255c4e19ecdb1b4 Mon Sep 17 00:00:00 2001 From: Demian Date: Tue, 9 Jun 2015 19:51:50 -0300 Subject: Moved hashing function to a separate helper class --- src/org/traccar/database/DataManager.java | 3 ++- src/org/traccar/helper/Hashing.java | 20 ++++++++++++++++++++ src/org/traccar/model/User.java | 21 ++------------------- 3 files changed, 24 insertions(+), 20 deletions(-) create mode 100644 src/org/traccar/helper/Hashing.java diff --git a/src/org/traccar/database/DataManager.java b/src/org/traccar/database/DataManager.java index fdab359f4..9633ada58 100644 --- a/src/org/traccar/database/DataManager.java +++ b/src/org/traccar/database/DataManager.java @@ -38,6 +38,7 @@ 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; @@ -219,7 +220,7 @@ public class DataManager { public User login(String email, String password) throws SQLException { return QueryBuilder.create(dataSource, properties.getProperty("database.loginUser")) .setString("email", email) - .setBytes("password", User.sha256(password)) + .setBytes("password", Hashing.sha256(password)) .executeQuerySingle(new User()); } 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 9186e66c5..410bc4d74 100644 --- a/src/org/traccar/model/User.java +++ b/src/org/traccar/model/User.java @@ -15,9 +15,7 @@ */ package org.traccar.model; -import java.io.UnsupportedEncodingException; -import java.security.MessageDigest; -import java.security.NoSuchAlgorithmException; +import org.traccar.helper.Hashing; public class User implements Factory { @@ -40,7 +38,7 @@ public class User implements Factory { private byte[] password; public byte[] getPassword() { return password; } - public void setPassword(String password) { this.password = sha256(password); } + public void setPassword(String password) { this.password = Hashing.sha256(password); } private boolean readonly; @@ -61,19 +59,4 @@ public class User implements Factory { private double longitude; private int zoom; - - - public static byte[] sha256(String text) { - try { - MessageDigest md = MessageDigest.getInstance("SHA-256"); - - md.update(text.getBytes("UTF-8")); - byte[] digest = md.digest(); - return digest; - } catch (NoSuchAlgorithmException e) { - throw new RuntimeException(e); - } catch (UnsupportedEncodingException e) { - throw new RuntimeException(e); - } - } } -- cgit v1.2.3