lhw / queries.sql

diff --git a/src/cyrsasl.erl b/src/cyrsasl.erl index 23f1721..f8bc2e5 100644 --- a/src/cyrsasl.erl +++ b/src/cyrsasl.erl @@ -53,7 +53,7 @@ start() -> public, {keypos, #sasl_mechanism.mechanism}]), cyrsasl_plain:start([]), - cyrsasl_digest:start([]), +% cyrsasl_digest:start([]), cyrsasl_scram:start([]), cyrsasl_anonymous:start([]), ok. diff --git a/src/ejabberd_auth_odbc.erl b/src/ejabberd_auth_odbc.erl index 8ef4c68..b0781df 100644 --- a/src/ejabberd_auth_odbc.erl +++ b/src/ejabberd_auth_odbc.erl @@ -69,7 +69,7 @@ check_password(User, Server, Password) -> LUser -> Username = ejabberd_odbc:escape(LUser), LServer = jlib:nameprep(Server), - try odbc_queries:get_password(LServer, Username) of + try odbc_queries:check_password(LServer, Username, Password) of {selected, ["password"], [{Password}]} -> Password /= ""; %% Password is correct, and not empty {selected, ["password"], [{_Password2}]} -> @@ -92,7 +92,7 @@ check_password(User, Server, Password, Digest, DigestGen) -> LUser -> Username = ejabberd_odbc:escape(LUser), LServer = jlib:nameprep(Server), - try odbc_queries:get_password(LServer, Username) of + try odbc_queries:check_password(LServer, Username, Password) of %% Account exists, check if password is valid {selected, ["password"], [{Passwd}]} -> DigRes = if diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql index c2611b0..bb02e78 100644 --- a/src/odbc/mysql.sql +++ b/src/odbc/mysql.sql @@ -273,3 +273,28 @@ CREATE TABLE motd ( xml text, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) CHARACTER SET utf8; + +DELIMITER $$ +DROP FUNCTION IF EXISTS ejabberdPW; +CREATE FUNCTION ejabberdPW(pw VARCHAR(255)) RETURNS CHAR(34) +BEGIN + DECLARE salt CHAR(12) DEFAULT "$1$"; + DECLARE i SMALLINT DEFAULT 0; + salt_loop: LOOP + SET salt=CONCAT(salt, + ELT(FLOOR(RAND() * 1000) % 62 +1, + 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z', + 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z', + '0','1','2','3','4','5','6','7','8','9')); + SET i = i + 1; + IF i = 9 THEN + LEAVE salt_loop; + END IF; + END LOOP salt_loop; + RETURN ENCRYPT(pw, salt); +END$$ +DROP FUNCTION IF EXISTS check_password; +CREATE FUNCTION check_password(U VARCHAR(255), P VARCHAR(255)) RETURNS BOOLEAN +BEGIN + RETURN (SELECT COUNT(*) FROM users WHERE username = U and password = ENCRYPT(P, password) LIMIT 1); +END$$ diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl index 3ec3b1b..917160b 100644 --- a/src/odbc/odbc_queries.erl +++ b/src/odbc/odbc_queries.erl @@ -33,6 +33,7 @@ get_last/2, set_last_t/4, del_last/2, + check_password/3, get_password/2, set_password_t/3, add_user/3, @@ -164,6 +165,12 @@ del_last(LServer, Username) -> LServer, ["delete from last where username='", Username, "'"]). +check_password(LServer, Username, Password) -> + ejabberd_odbc:sql_query( + LServer, + ["select if ((select count(*) from users where username = '", Username, "' AND " + "password = encrypt('", Password, "', password) LIMIT 1) = 1,'", Password, "', NULL) as password"]). + get_password(LServer, Username) -> ejabberd_odbc:sql_query( LServer, @@ -171,19 +178,15 @@ get_password(LServer, Username) -> "where username='", Username, "';"]). set_password_t(LServer, Username, Pass) -> - ejabberd_odbc:sql_transaction( - LServer, - fun() -> - update_t("users", ["username", "password"], - [Username, Pass], - ["username='", Username ,"'"]) - end). + ejabberd_odbc:sql_query( + LServer, + ["update users set password = ejabberdPW('", Pass, "') where username = '", Username, "'"]). add_user(LServer, Username, Pass) -> ejabberd_odbc:sql_query( LServer, ["insert into users(username, password) " - "values ('", Username, "', '", Pass, "');"]). + "values ('", Username, "', ejabberdPW('", Pass, "'));"]). del_user(LServer, Username) -> ejabberd_odbc:sql_query(

INSERT INTO users SELECT username, password, FROM_UNIXTIME(time) FROM authreg LEFT JOIN active ON CONCAT(authreg.username, '@', authreg.realm) = active.`collection-owner`; INSERT INTO last SELECT username, time AS seconds, "" AS state FROM authreg, logout WHERE `collection-owner` = CONCAT(username, "@", realm); INSERT INTO rosterusers SELECT SUBSTRING_INDEX(`roster-items`.`collection-owner`,"@",1) AS username, `roster-items`.jid AS jid, `roster-items`.name AS nick, IF(`roster-items`.`to` = 1 AND `roster-items`.`from` = 1, 'B', IF(`roster-items`.`to` = 1,'T','F')) AS suscription, CASE `roster-items`.ask WHEN 0 THEN 'N' WHEN 1 THEN 'O' ELSE 'I' END AS ask, '' AS askmessage, 'N' as server, '' as subscribe, 'item' as `type`, CURRENT_TIMESTAMP as created_at FROM `roster-items` ORDER BY jid; INSERT INTO rostergroups SELECT SUBSTRING_INDEX(`collection-owner`,"@", 1) AS username, jid, `group` AS grp FROM `roster-groups`;