Revision of queries.sql

lhw revised this gist 1 month ago. Go to revision

2 files changed, 159 insertions

modification.patch(file created)

		@@ -0,0 +1,117 @@
1	+	diff --git a/src/cyrsasl.erl b/src/cyrsasl.erl
2	+	index 23f1721..f8bc2e5 100644
3	+	--- a/src/cyrsasl.erl
4	+	+++ b/src/cyrsasl.erl
5	+	@@ -53,7 +53,7 @@ start() ->
6	+	public,
7	+	{keypos, #sasl_mechanism.mechanism}]),
8	+	cyrsasl_plain:start([]),
9	+	- cyrsasl_digest:start([]),
10	+	+% cyrsasl_digest:start([]),
11	+	cyrsasl_scram:start([]),
12	+	cyrsasl_anonymous:start([]),
13	+	ok.
14	+	diff --git a/src/ejabberd_auth_odbc.erl b/src/ejabberd_auth_odbc.erl
15	+	index 8ef4c68..b0781df 100644
16	+	--- a/src/ejabberd_auth_odbc.erl
17	+	+++ b/src/ejabberd_auth_odbc.erl
18	+	@@ -69,7 +69,7 @@ check_password(User, Server, Password) ->
19	+	LUser ->
20	+	Username = ejabberd_odbc:escape(LUser),
21	+	LServer = jlib:nameprep(Server),
22	+	- try odbc_queries:get_password(LServer, Username) of
23	+	+ try odbc_queries:check_password(LServer, Username, Password) of
24	+	{selected, ["password"], [{Password}]} ->
25	+	Password /= ""; %% Password is correct, and not empty
26	+	{selected, ["password"], [{_Password2}]} ->
27	+	@@ -92,7 +92,7 @@ check_password(User, Server, Password, Digest, DigestGen) ->
28	+	LUser ->
29	+	Username = ejabberd_odbc:escape(LUser),
30	+	LServer = jlib:nameprep(Server),
31	+	- try odbc_queries:get_password(LServer, Username) of
32	+	+ try odbc_queries:check_password(LServer, Username, Password) of
33	+	%% Account exists, check if password is valid
34	+	{selected, ["password"], [{Passwd}]} ->
35	+	DigRes = if
36	+	diff --git a/src/odbc/mysql.sql b/src/odbc/mysql.sql
37	+	index c2611b0..bb02e78 100644
38	+	--- a/src/odbc/mysql.sql
39	+	+++ b/src/odbc/mysql.sql
40	+	@@ -273,3 +273,28 @@ CREATE TABLE motd (
41	+	xml text,
42	+	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
43	+	) CHARACTER SET utf8;
44	+	+
45	+	+DELIMITER $$
46	+	+DROP FUNCTION IF EXISTS ejabberdPW;
47	+	+CREATE FUNCTION ejabberdPW(pw VARCHAR(255)) RETURNS CHAR(34)
48	+	+BEGIN
49	+	+ DECLARE salt CHAR(12) DEFAULT "$1$";
50	+	+ DECLARE i SMALLINT DEFAULT 0;
51	+	+ salt_loop: LOOP
52	+	+ SET salt=CONCAT(salt,
53	+	+ ELT(FLOOR(RAND() * 1000) % 62 +1,
54	+	+ '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',
55	+	+ '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',
56	+	+ '0','1','2','3','4','5','6','7','8','9'));
57	+	+ SET i = i + 1;
58	+	+ IF i = 9 THEN
59	+	+ LEAVE salt_loop;
60	+	+ END IF;
61	+	+ END LOOP salt_loop;
62	+	+ RETURN ENCRYPT(pw, salt);
63	+	+END$$
64	+	+DROP FUNCTION IF EXISTS check_password;
65	+	+CREATE FUNCTION check_password(U VARCHAR(255), P VARCHAR(255)) RETURNS BOOLEAN
66	+	+BEGIN
67	+	+ RETURN (SELECT COUNT(*) FROM users WHERE username = U and password = ENCRYPT(P, password) LIMIT 1);
68	+	+END$$
69	+	diff --git a/src/odbc/odbc_queries.erl b/src/odbc/odbc_queries.erl
70	+	index 3ec3b1b..917160b 100644
71	+	--- a/src/odbc/odbc_queries.erl
72	+	+++ b/src/odbc/odbc_queries.erl
73	+	@@ -33,6 +33,7 @@
74	+	get_last/2,
75	+	set_last_t/4,
76	+	del_last/2,
77	+	+ check_password/3,
78	+	get_password/2,
79	+	set_password_t/3,
80	+	add_user/3,
81	+	@@ -164,6 +165,12 @@ del_last(LServer, Username) ->
82	+	LServer,
83	+	["delete from last where username='", Username, "'"]).
84	+
85	+	+check_password(LServer, Username, Password) ->
86	+	+ ejabberd_odbc:sql_query(
87	+	+ LServer,
88	+	+ ["select if ((select count(*) from users where username = '", Username, "' AND "
89	+	+ "password = encrypt('", Password, "', password) LIMIT 1) = 1,'", Password, "', NULL) as password"]).
90	+	+
91	+	get_password(LServer, Username) ->
92	+	ejabberd_odbc:sql_query(
93	+	LServer,
94	+	@@ -171,19 +178,15 @@ get_password(LServer, Username) ->
95	+	"where username='", Username, "';"]).
96	+
97	+	set_password_t(LServer, Username, Pass) ->
98	+	- ejabberd_odbc:sql_transaction(
99	+	- LServer,
100	+	- fun() ->
101	+	- update_t("users", ["username", "password"],
102	+	- [Username, Pass],
103	+	- ["username='", Username ,"'"])
104	+	- end).
105	+	+ ejabberd_odbc:sql_query(
106	+	+ LServer,
107	+	+ ["update users set password = ejabberdPW('", Pass, "') where username = '", Username, "'"]).
108	+
109	+	add_user(LServer, Username, Pass) ->
110	+	ejabberd_odbc:sql_query(
111	+	LServer,
112	+	["insert into users(username, password) "
113	+	- "values ('", Username, "', '", Pass, "');"]).
114	+	+ "values ('", Username, "', ejabberdPW('", Pass, "'));"]).
115	+
116	+	del_user(LServer, Username) ->
117	+	ejabberd_odbc:sql_query(

queries.sql(file created)

		@@ -0,0 +1,42 @@
1	+	INSERT INTO
2	+	users
3	+	SELECT
4	+	username,
5	+	password,
6	+	FROM_UNIXTIME(time)
7	+	FROM
8	+	authreg LEFT JOIN active ON CONCAT(authreg.username, '@', authreg.realm) = active.`collection-owner`;
9	+	INSERT INTO
10	+	last
11	+	SELECT
12	+	username,
13	+	time AS seconds,
14	+	"" AS state
15	+	FROM
16	+	authreg, logout
17	+	WHERE
18	+	`collection-owner` = CONCAT(username, "@", realm);
19	+	INSERT INTO
20	+	rosterusers
21	+	SELECT
22	+	SUBSTRING_INDEX(`roster-items`.`collection-owner`,"@",1) AS username,
23	+	`roster-items`.jid AS jid,
24	+	`roster-items`.name AS nick,
25	+	IF(`roster-items`.`to` = 1 AND `roster-items`.`from` = 1, 'B', IF(`roster-items`.`to` = 1,'T','F')) AS suscription,
26	+	CASE `roster-items`.ask WHEN 0 THEN 'N' WHEN 1 THEN 'O' ELSE 'I' END AS ask,
27	+	'' AS askmessage,
28	+	'N' as server,
29	+	'' as subscribe,
30	+	'item' as `type`,
31	+	CURRENT_TIMESTAMP as created_at
32	+	FROM
33	+	`roster-items`
34	+	ORDER BY jid;
35	+	INSERT INTO
36	+	rostergroups
37	+	SELECT
38	+	SUBSTRING_INDEX(`collection-owner`,"@", 1) AS username,
39	+	jid,
40	+	`group` AS grp
41	+	FROM
42	+	`roster-groups`;

Newer Older