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