1 /* */
2 /*
3 Copyright (C) 1999-2004 IC & S dbmail@ic-s.nl
4 Copyright (c) 2004-2012 NFG Net Facilities Group BV support@nfg.nl
5
6 This program is free software; you can redistribute it and/or
7 modify it under the terms of the GNU General Public License
8 as published by the Free Software Foundation; either
9 version 2 of the License, or (at your option) any later
10 version.
11
12 This program is distributed in the hope that it will be useful,
13 but WITHOUT ANY WARRANTY; without even the implied warranty of
14 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 GNU General Public License for more details.
16
17 You should have received a copy of the GNU General Public License
18 along with this program; if not, write to the Free Software
19 Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
20 */
21
22 /**
23 * \file db.c
24 *
25 */
26
27 #include "dbmail.h"
28 #include "dm_mailboxstate.h"
29
30 #define THIS_MODULE "db"
31
32 // Flag order defined in dbmailtypes.h
33 static const char *db_flag_desc[] = {
34 "seen_flag",
35 "answered_flag",
36 "deleted_flag",
37 "flagged_flag",
38 "draft_flag",
39 "recent_flag",
40 NULL
41 };
42 const char *imap_flag_desc[] = {
43 "Seen",
44 "Answered",
45 "Deleted",
46 "Flagged",
47 "Draft",
48 "Recent",
49 NULL
50 };
51 const char *imap_flag_desc_escaped[] = {
52 "\\Seen",
53 "\\Answered",
54 "\\Deleted",
55 "\\Flagged",
56 "\\Draft",
57 "\\Recent",
58 NULL
59 };
60
61 extern ServerConfig_T *server_conf;
62 extern DBParam_T db_params;
63 #define DBPFX db_params.pfx
64
65 /*
66 * builds query values for matching mailbox names case insensitivity
67 * supports utf7
68 * caller must free the return value.
69 */
mailbox_match_new(const char * mailbox)70 struct mailbox_match * mailbox_match_new(const char *mailbox)
71 {
72 struct mailbox_match *res = g_new0(struct mailbox_match,1);
73 char *sensitive, *insensitive;
74 size_t i, j, len;
75 int uscore = 0, verbatim = 0, has_sensitive_part = 0;
76 char p = 0, c = 0;
77
78 len = strlen(mailbox);
79 for (i = 0; i < len; i++)
80 if (mailbox[i] == '_')
81 uscore++;
82
83 sensitive = g_new0(char, len + uscore + 1);
84 j = 0;
85 for (i = 0; i < len; i++) {
86 if (mailbox[i] == '_')
87 sensitive[j++] = '\\';
88 sensitive[j++] = mailbox[i];
89 }
90
91 insensitive = g_strdup(sensitive);
92
93 len = strlen(sensitive);
94 for (i = 0; i < len; i++) {
95 c = sensitive[i];
96 if (i>0)
97 p = sensitive[i-1];
98
99 /**
100 * RFC 3501 [Page 19]
101 *
102 * "&" is used to shift to modified BASE64 and "-" to shift back to
103 * US-ASCII.
104 *
105 * This means, that the change to non-verbatim should occur for the first char
106 * *after* '-' is detected.
107 */
108 /* turn off verbatim, just if the last character was finishing the modified BASE64 */
109 switch (p) {
110 case '-':
111 verbatim = 0;
112 break;
113 }
114 /* check for the current char, just once we checked for the last char */
115 switch (c) {
116 case '&':
117 verbatim = 1;
118 has_sensitive_part = 1;
119 break;
120 }
121
122 /* verbatim means that the case sensitive part must match
123 * and the case insensitive part matches anything,
124 * and vice versa.*/
125 /* Do not replace percentage chars with underscores.
126 * This function is sometimes called for "Folder/%"-like mailbox names.
127 * Replacing percentage signs would reduce the number of matches.
128 */
129 if (verbatim) {
130 if (insensitive[i] != '\\' && insensitive[i] != '%')
131 insensitive[i] = '_';
132 } else {
133 if (sensitive[i] != '\\' && sensitive[i] != '%')
134 sensitive[i] = '_';
135 }
136 }
137
138 if (has_sensitive_part) {
139 res->insensitive = insensitive;
140 res->sensitive = sensitive;
141 } else {
142 res->insensitive = insensitive;
143 g_free(sensitive);
144 }
145
146 return res;
147 }
148
mailbox_match_free(struct mailbox_match * m)149 void mailbox_match_free(struct mailbox_match *m)
150 {
151 if (! m) return;
152 if (m->sensitive) g_free(m->sensitive);
153 if (m->insensitive) g_free(m->insensitive);
154 g_free(m); m=NULL;
155 }
156
157
158
159 /** list of tables used in dbmail */
160 #define DB_NTABLES 20
161 const char *DB_TABLENAMES[DB_NTABLES] = {
162 "acl",
163 "aliases",
164 "envelope",
165 "header",
166 "headername",
167 "headervalue",
168 "keywords",
169 "mailboxes",
170 "messages",
171 "mimeparts",
172 "partlists",
173 "pbsp",
174 "physmessage",
175 "referencesfield",
176 "replycache",
177 "sievescripts",
178 "subscription",
179 "usermap",
180 "users"
181 };
182
183 #define REPLYCACHE_WIDTH 100
184
185 GTree * global_cache = NULL;
186 /////////////////////////////////////////////////////////////////////////////
187
188 /* globals for now... */
189 ConnectionPool_T pool = NULL;
190 URL_T dburi = NULL;
191 int db_connected = 0; // 0 = not called, 1 = new dburi but not pool, 2 = new dburi and pool, but not tested, 3 = tested and ok
192
193 /* This is the first db_* call anybody should make. */
db_connect(void)194 int db_connect(void)
195 {
196 int sweepInterval = 60;
197 Connection_T c;
198 GString *dsn;
199 GString *uri;
200
201 if (strlen(db_params.dburi) != 0) {
202 uri = g_string_new("");
203 g_string_append_printf(uri,"%s", db_params.dburi);
204 //add application-name to the uri, only if application-name parameter was not added.
205 if ( !strstr(uri->str,"application-name") ){
206 //If it already has parameters then add it with "&" otherwise start adding them with "?"
207 if ( strchr(uri->str,'?') ){
208 g_string_append_printf(uri, "&application-name=%s", server_conf ? server_conf->process_name : "dbmail_client");
209 }else{
210 g_string_append_printf(uri, "?application-name=%s", server_conf ? server_conf->process_name : "dbmail_client");
211 }
212 }
213 TRACE(TRACE_DEBUG,"dburi: %s", uri->str);
214 dburi = URL_new(uri->str);
215 g_string_free(uri,TRUE);
216 } else {
217 dsn = g_string_new("");
218 g_string_append_printf(dsn,"%s://",db_params.driver);
219 if (db_params.host)
220 g_string_append_printf(dsn,"%s", db_params.host);
221 if (db_params.port)
222 g_string_append_printf(dsn,":%u", db_params.port);
223 if (db_params.db) {
224 if (SMATCH(db_params.driver,"sqlite")) {
225
226 /* expand ~ in db name to HOME env variable */
227 if ((strlen(db_params.db) > 0 ) && (db_params.db[0] == '~')) {
228 char *homedir;
229 Field_T db;
230 if ((homedir = getenv ("HOME")) == NULL)
231 TRACE(TRACE_EMERG, "can't expand ~ in db name");
232 g_snprintf(db, FIELDSIZE, "%s%s", homedir, &(db_params.db[1]));
233 g_strlcpy(db_params.db, db, FIELDSIZE);
234 }
235
236 g_string_append_printf(dsn,"%s", db_params.db);
237 } else {
238 g_string_append_printf(dsn,"/%s", db_params.db);
239 }
240 }
241 if (db_params.user && strlen((const char *)db_params.user)) {
242 g_string_append_printf(dsn,"?user=%s", db_params.user);
243 if (db_params.pass && strlen((const char *)db_params.pass))
244 g_string_append_printf(dsn,"&password=%s", db_params.pass);
245 if (SMATCH(db_params.driver,"mysql")) {
246 if (db_params.encoding && strlen((const char *)db_params.encoding))
247 g_string_append_printf(dsn,"&charset=%s", db_params.encoding);
248 }
249 }
250
251 if (strlen((const char *)db_params.sock))
252 g_string_append_printf(dsn,"&unix-socket=%s", db_params.sock);
253
254 if (MATCH(db_params.driver,"postgresql")) {
255 g_string_append_printf(dsn, "&application-name=%s", server_conf ? server_conf->process_name : "dbmail_client");
256 }
257
258 dburi = URL_new(dsn->str);
259 g_string_free(dsn,TRUE);
260 }
261 TRACE(TRACE_DATABASE, "db at dburi: [%s]", URL_toString(dburi));
262 db_connected = 1;
263 if (! (pool = ConnectionPool_new(dburi)))
264 TRACE(TRACE_EMERG,"error creating database connection pool");
265 db_connected = 2;
266
267 if (db_params.max_db_connections > 0) {
268 if (db_params.max_db_connections < (unsigned int)ConnectionPool_getInitialConnections(pool))
269 ConnectionPool_setInitialConnections(pool, db_params.max_db_connections);
270 ConnectionPool_setMaxConnections(pool, db_params.max_db_connections);
271 TRACE(TRACE_INFO,"database connection pool created with maximum connections of [%d]", db_params.max_db_connections);
272 }
273
274 ConnectionPool_setReaper(pool, sweepInterval);
275 TRACE(TRACE_DATABASE, "run a database connection reaper thread every [%d] seconds", sweepInterval);
276
277 ConnectionPool_setAbortHandler(pool, TabortHandler);
278 ConnectionPool_start(pool);
279 TRACE(TRACE_DATABASE, "database connection pool started with [%d] connections, max [%d]",
280 ConnectionPool_getInitialConnections(pool), ConnectionPool_getMaxConnections(pool));
281
282 if (! (c = ConnectionPool_getConnection(pool))) {
283 TRACE(TRACE_ALERT, "error getting a database connection from the pool");
284 return -1;
285 }
286 db_connected = 3;
287 db_con_close(c);
288
289 if (! db_params.db_driver) {
290 const char *protocol = URL_getProtocol(dburi);
291 if (MATCH(protocol, "sqlite"))
292 db_params.db_driver = DM_DRIVER_SQLITE;
293 else if (MATCH(protocol, "mysql"))
294 db_params.db_driver = DM_DRIVER_MYSQL;
295 else if (MATCH(protocol, "postgresql"))
296 db_params.db_driver = DM_DRIVER_POSTGRESQL;
297 else if (MATCH(protocol, "oracle"))
298 db_params.db_driver = DM_DRIVER_ORACLE;
299 }
300
301 return db_check_version();
302 }
303
304 /* But sometimes this gets called after help text or an
305 * error but without a matching db_connect before it. */
db_disconnect(void)306 int db_disconnect(void)
307 {
308 if(db_connected >= 3) ConnectionPool_stop(pool);
309 if(db_connected >= 2) ConnectionPool_free(&pool);
310 if(db_connected >= 1) URL_free(&dburi);
311 db_connected = 0;
312 return 0;
313 }
314
db_con_get(void)315 Connection_T db_con_get(void)
316 {
317 int i=0, k=0; Connection_T c = NULL;
318 while (! c) {
319 c = ConnectionPool_getConnection(pool);
320 if (c) break;
321 if((int)(i % 5)==0) {
322 TRACE(TRACE_ALERT, "Thread is having trouble obtaining a database connection. Try [%d]", i);
323 k = ConnectionPool_reapConnections(pool);
324 TRACE(TRACE_INFO, "Database reaper closed [%d] stale connections", k);
325 }
326 sleep(1);
327 i++;
328 }
329
330 Connection_setQueryTimeout(c, (int)db_params.query_timeout);
331 TRACE(TRACE_DATABASE,"[%p] connection from pool", c);
332 return c;
333 }
334
dm_db_ping(void)335 gboolean dm_db_ping(void)
336 {
337 Connection_T c; gboolean t = FALSE;
338 int try = 0;
339 while (try++ < 2) {
340 c = db_con_get();
341 t = Connection_ping(c);
342 db_con_close(c);
343 if (t)
344 break;
345 db_disconnect();
346 TRACE(TRACE_WARNING, "database has gone away. trying to reconnect...");
347 sleep(3);
348 if (db_connect() == DM_EQUERY)
349 break;
350 }
351
352 if (!t) TRACE(TRACE_ERR,"database has gone away");
353
354 return t;
355 }
356
db_con_close(Connection_T c)357 void db_con_close(Connection_T c)
358 {
359 TRACE(TRACE_DATABASE,"[%p] connection to pool", c);
360 Connection_close(c);
361 return;
362 }
363
db_con_clear(Connection_T c)364 void db_con_clear(Connection_T c)
365 {
366 TRACE(TRACE_DATABASE,"[%p] connection cleared", c);
367 Connection_clear(c);
368 Connection_setQueryTimeout(c, (int)db_params.query_timeout);
369 return;
370 }
371
log_query_time(char * query,struct timeval before,struct timeval after)372 void log_query_time(char *query, struct timeval before, struct timeval after)
373 {
374 unsigned int elapsed = (unsigned int)diff_time(before, after);
375 TRACE(TRACE_DATABASE, "last query took [%d] seconds", elapsed);
376 if (elapsed > db_params.query_time_warning)
377 TRACE(TRACE_WARNING, "slow query [%s] took [%d] seconds", query, elapsed);
378 else if (elapsed > db_params.query_time_notice)
379 TRACE(TRACE_NOTICE, "slow query [%s] took [%d] seconds", query, elapsed);
380 else if (elapsed > db_params.query_time_info)
381 TRACE(TRACE_INFO, "slow query [%s] took [%d] seconds", query, elapsed);
382 return;
383 }
384
db_exec(Connection_T c,const char * q,...)385 gboolean db_exec(Connection_T c, const char *q, ...)
386 {
387 struct timeval before, after;
388 volatile gboolean result = FALSE;
389 va_list ap, cp;
390 char *query;
391
392 va_start(ap, q);
393 va_copy(cp, ap);
394 query = g_strdup_vprintf(q, cp);
395 va_end(cp);
396 va_end(ap);
397
398 TRACE(TRACE_DATABASE,"[%p] [%s]", c, query);
399 TRY
400 gettimeofday(&before, NULL);
401 Connection_execute(c, "%s", (const char *)query);
402 gettimeofday(&after, NULL);
403 result = TRUE;
404 CATCH(SQLException)
405 LOG_SQLERROR;
406 TRACE(TRACE_ERR,"failed query [%s]", query);
407 END_TRY;
408
409 if (result) log_query_time(query, before, after);
410 g_free(query);
411
412 return result;
413 }
414
db_query(Connection_T c,const char * q,...)415 ResultSet_T db_query(Connection_T c, const char *q, ...)
416 {
417 struct timeval before, after;
418 ResultSet_T r = NULL;
419 volatile gboolean result = FALSE;
420 va_list ap, cp;
421 char *query;
422
423 va_start(ap, q);
424 va_copy(cp, ap);
425 query = g_strdup_vprintf(q, cp);
426 va_end(cp);
427 va_end(ap);
428
429 g_strstrip(query);
430
431 TRACE(TRACE_DATABASE,"[%p] [%s]", c, query);
432 TRY
433 gettimeofday(&before, NULL);
434 r = Connection_executeQuery(c, "%s", (const char *)query);
435 gettimeofday(&after, NULL);
436 result = TRUE;
437 CATCH(SQLException)
438 LOG_SQLERROR;
439 TRACE(TRACE_ERR,"failed query [%s]", query);
440 END_TRY;
441
442 if (result) log_query_time(query, before, after);
443 g_free(query);
444
445 return r;
446 }
447
db_update(const char * q,...)448 gboolean db_update(const char *q, ...)
449 {
450 Connection_T c; volatile gboolean result = FALSE;
451 va_list ap, cp;
452 struct timeval before, after;
453 INIT_QUERY;
454
455 va_start(ap, q);
456 va_copy(cp, ap);
457 vsnprintf(query, DEF_QUERYSIZE-1, q, cp);
458 va_end(cp);
459 va_end(ap);
460
461 c = db_con_get();
462 TRACE(TRACE_DATABASE,"[%p] [%s]", c, query);
463 TRY
464 gettimeofday(&before, NULL);
465 db_begin_transaction(c);
466 Connection_execute(c, "%s", (const char *)query);
467 db_commit_transaction(c);
468 result = TRUE;
469 gettimeofday(&after, NULL);
470 CATCH(SQLException)
471 LOG_SQLERROR;
472 db_rollback_transaction(c);
473 FINALLY
474 db_con_close(c);
475 END_TRY;
476
477 if (result) log_query_time(query, before, after);
478
479 return result;
480 }
481
db_stmt_prepare(Connection_T c,const char * q,...)482 PreparedStatement_T db_stmt_prepare(Connection_T c, const char *q, ...)
483 {
484 va_list ap, cp;
485 char *query;
486 PreparedStatement_T s;
487
488 va_start(ap, q);
489 va_copy(cp, ap);
490 query = g_strdup_vprintf(q, cp);
491 va_end(cp);
492 va_end(ap);
493
494 TRACE(TRACE_DATABASE,"[%p] [%s]", c, query);
495 s = Connection_prepareStatement(c, "%s", (const char *)query);
496 g_free(query);
497 return s;
498 }
499
db_stmt_set_str(PreparedStatement_T s,int index,const char * x)500 int db_stmt_set_str(PreparedStatement_T s, int index, const char *x)
501 {
502 TRACE(TRACE_DATABASE,"[%p] %d:[%s]", s, index, x);
503 PreparedStatement_setString(s, index, x);
504 return TRUE;
505 }
db_stmt_set_int(PreparedStatement_T s,int index,int x)506 int db_stmt_set_int(PreparedStatement_T s, int index, int x)
507 {
508 TRACE(TRACE_DATABASE,"[%p] %d:[%d]", s, index, x);
509 PreparedStatement_setInt(s, index, x);
510 return TRUE;
511 }
db_stmt_set_u64(PreparedStatement_T s,int index,uint64_t x)512 int db_stmt_set_u64(PreparedStatement_T s, int index, uint64_t x)
513 {
514 TRACE(TRACE_DATABASE,"[%p] %d:[%" PRIu64 "]", s, index, x);
515 PreparedStatement_setLLong(s, index, (long long)x);
516 return TRUE;
517 }
db_stmt_set_blob(PreparedStatement_T s,int index,const void * x,int size)518 int db_stmt_set_blob(PreparedStatement_T s, int index, const void *x, int size)
519 {
520 if (size > 200)
521 TRACE(TRACE_DATABASE,"[%p] %d:[blob of length %d]", s, index, size);
522 else
523 TRACE(TRACE_DATABASE,"[%p] %d:[%s]", s, index, (char *)x);
524 PreparedStatement_setBlob(s, index, x, size);
525 return TRUE;
526 }
527
db_stmt_exec(PreparedStatement_T s)528 inline gboolean db_stmt_exec(PreparedStatement_T s)
529 {
530 PreparedStatement_execute(s);
531 return TRUE;
532 }
533
db_stmt_query(PreparedStatement_T s)534 inline ResultSet_T db_stmt_query(PreparedStatement_T s)
535 {
536 return PreparedStatement_executeQuery(s);
537 }
538
db_result_next(ResultSet_T r)539 int db_result_next(ResultSet_T r)
540 {
541 if (r)
542 return ResultSet_next(r);
543 else
544 return FALSE;
545 }
546
db_num_fields(ResultSet_T r)547 inline unsigned db_num_fields(ResultSet_T r)
548 {
549 return (unsigned)ResultSet_getColumnCount(r);
550 }
551
db_result_get(ResultSet_T r,unsigned field)552 const char * db_result_get(ResultSet_T r, unsigned field)
553 {
554 const char * val = ResultSet_getString(r, field+1);
555 return val ? val : "";
556 }
557
db_result_get_int(ResultSet_T r,unsigned field)558 inline int db_result_get_int(ResultSet_T r, unsigned field)
559 {
560 return ResultSet_getInt(r, field+1);
561 }
562
db_result_get_bool(ResultSet_T r,unsigned field)563 inline int db_result_get_bool(ResultSet_T r, unsigned field)
564 {
565 return (ResultSet_getInt(r, field+1) ? 1 : 0);
566 }
567
db_result_get_u64(ResultSet_T r,unsigned field)568 inline uint64_t db_result_get_u64(ResultSet_T r, unsigned field)
569 {
570 return (uint64_t)(ResultSet_getLLong(r, field+1));
571 }
572
db_result_get_blob(ResultSet_T r,unsigned field,int * size)573 const void * db_result_get_blob(ResultSet_T r, unsigned field, int *size)
574 {
575 const char * val = ResultSet_getBlob(r, field+1, size);
576 if (!val) {
577 *size = 0;
578 }
579 return val;
580 }
581
db_insert_result(Connection_T c,ResultSet_T r)582 uint64_t db_insert_result(Connection_T c, ResultSet_T r)
583 {
584 uint64_t id = 0;
585
586 if (! db_result_next(r)) { /* ignore */ }
587
588 /* In PostgreSQL 9.1 lastRowId is _not_ always zero
589 *
590 dbmail=# INSERT INTO dbmail_physmessage (internal_date) VALUES
591 dbmail-# (TO_TIMESTAMP('2013-07-20 07:22:34'::text, 'YYYY-MM-DD HH24:MI:SS')) RETURNING id;
592 id
593 ----------
594 29196224
595 (1 row)
596
597 INSERT 0 1
598 dbmail=# INSERT INTO dbmail_messages(mailbox_idnr, physmessage_id, unique_id,recent_flag, status) VALUES (10993, 29196223, 'acc98da420bfe6d3dc2c707a9863001c', 1, 5) RETURNING message_idnr;
599 message_idnr
600 --------------
601 36650725
602 (1 row)
603
604 INSERT 82105867 1
605 *
606 * Connection_lastRowId(c) is returning the OID instead of
607 * the message_idnr we are expecting.
608 * However, we are expecting only one row to be returned so
609 * we should always use db_result_get_u64(r, 0);
610 */
611 if (db_params.db_driver == DM_DRIVER_POSTGRESQL) {
612 id = db_result_get_u64(r, 0); // postgresql
613 }
614
615 // lastRowId is always zero for pgsql tables without OIDs
616 // or possibly for sqlite after calling executeQuery but
617 // before calling db_result_next
618
619 else if ((id = (uint64_t )Connection_lastRowId(c)) == 0) { // mysql
620 // but if we're using 'RETURNING id' clauses on inserts
621 // or we're using the sqlite backend, we can do this
622
623 if ((id = (uint64_t )Connection_lastRowId(c)) == 0) // sqlite
624 id = db_result_get_u64(r, 0); // postgresql - should not get this far
625 }
626 assert(id);
627 return id;
628 }
629
db_get_pk(Connection_T c,const char * table)630 uint64_t db_get_pk(Connection_T c, const char *table)
631 {
632 ResultSet_T r;
633 uint64_t id = 0;
634 r = db_query(c, "SELECT sq_%s%s.CURRVAL FROM DUAL", DBPFX, table);
635 if (db_result_next(r))
636 id = db_result_get_u64(r, 0);
637 assert(id);
638 return id;
639 }
640
db_begin_transaction(Connection_T c)641 int db_begin_transaction(Connection_T c)
642 {
643 TRACE(TRACE_DATABASE,"BEGIN");
644 Connection_beginTransaction(c);
645 return DM_SUCCESS;
646 }
647
db_commit_transaction(Connection_T c)648 int db_commit_transaction(Connection_T c)
649 {
650 TRACE(TRACE_DATABASE,"COMMIT");
651 Connection_commit(c);
652 return DM_SUCCESS;
653 }
654
655
db_rollback_transaction(Connection_T c)656 int db_rollback_transaction(Connection_T c)
657 {
658 TRACE(TRACE_DATABASE,"ROLLBACK");
659 Connection_rollback(c);
660 return DM_SUCCESS;
661 }
662
db_savepoint(Connection_T UNUSED c,const char UNUSED * id)663 int db_savepoint(Connection_T UNUSED c, const char UNUSED *id)
664 {
665 return 0;
666 }
667
db_savepoint_rollback(Connection_T UNUSED c,const char UNUSED * id)668 int db_savepoint_rollback(Connection_T UNUSED c, const char UNUSED *id)
669 {
670 return 0;
671 }
672
db_do_cleanup(const char UNUSED ** tables,int UNUSED num_tables)673 int db_do_cleanup(const char UNUSED **tables, int UNUSED num_tables)
674 {
675 return 0;
676 }
677
db_get_sqlite_sql(sql_fragment frag)678 static const char * db_get_sqlite_sql(sql_fragment frag)
679 {
680 switch(frag) {
681 case SQL_ENCODE_ESCAPE:
682 case SQL_TO_CHAR:
683 case SQL_STRCASE:
684 case SQL_PARTIAL:
685 return "%s";
686 break;
687 case SQL_TO_DATE:
688 return "DATE(%s)";
689 break;
690 case SQL_TO_DATETIME:
691 return "DATETIME(%s)";
692 break;
693 case SQL_TO_UNIXEPOCH:
694 return "STRFTIME('%%s',%s)";
695 break;
696 case SQL_CURRENT_TIMESTAMP:
697 return "STRFTIME('%%Y-%%m-%%d %%H:%%M:%%S','now','localtime')";
698 break;
699 case SQL_EXPIRE:
700 return "DATETIME('now','-%d DAYS')";
701 break;
702 case SQL_WITHIN:
703 return "DATETIME('now','-%d SECONDS')";
704 break;
705 case SQL_BINARY:
706 return "";
707 break;
708 case SQL_SENSITIVE_LIKE:
709 return "LIKE";
710 break;
711 case SQL_INSENSITIVE_LIKE:
712 return "LIKE";
713 break;
714 case SQL_IGNORE:
715 return "OR IGNORE";
716 break;
717 case SQL_RETURNING:
718 break;
719 case SQL_TABLE_EXISTS:
720 return "SELECT 1=1 FROM %s%s LIMIT 1 OFFSET 0";
721 break;
722 case SQL_ESCAPE_COLUMN:
723 return "";
724 break;
725 case SQL_COMPARE_BLOB:
726 return "%s=?";
727 break;
728 }
729 return NULL;
730 }
731
db_get_mysql_sql(sql_fragment frag)732 static const char * db_get_mysql_sql(sql_fragment frag)
733 {
734 switch(frag) {
735 case SQL_TO_CHAR:
736 return "DATE_FORMAT(%s, GET_FORMAT(DATETIME,'ISO'))";
737 break;
738 case SQL_TO_DATE:
739 return "DATE(%s)";
740 break;
741 case SQL_TO_DATETIME:
742 return "TIMESTAMP(%s)";
743 break;
744 case SQL_TO_UNIXEPOCH:
745 return "UNIX_TIMESTAMP(%s)";
746 break;
747 case SQL_CURRENT_TIMESTAMP:
748 return "NOW()";
749 break;
750 case SQL_EXPIRE:
751 return "NOW() - INTERVAL %d DAY";
752 break;
753 case SQL_WITHIN:
754 return "NOW() - INTERVAL %d SECOND";
755 break;
756 case SQL_BINARY:
757 return "BINARY";
758 break;
759 case SQL_SENSITIVE_LIKE:
760 return "LIKE BINARY";
761 break;
762 case SQL_INSENSITIVE_LIKE:
763 return "LIKE";
764 break;
765 case SQL_IGNORE:
766 return "IGNORE";
767 break;
768 case SQL_STRCASE:
769 case SQL_ENCODE_ESCAPE:
770 case SQL_PARTIAL:
771 return "%s";
772 break;
773 case SQL_RETURNING:
774 break;
775 case SQL_TABLE_EXISTS:
776 return "SELECT 1=1 FROM %s%s LIMIT 1 OFFSET 0";
777 break;
778 case SQL_ESCAPE_COLUMN:
779 return "`";
780 break;
781 case SQL_COMPARE_BLOB:
782 return "%s=?";
783 break;
784 }
785 return NULL;
786 }
787
db_get_pgsql_sql(sql_fragment frag)788 static const char * db_get_pgsql_sql(sql_fragment frag)
789 {
790 switch(frag) {
791 case SQL_TO_CHAR:
792 return "TO_CHAR(%s, 'YYYY-MM-DD HH24:MI:SS' )";
793 break;
794 case SQL_TO_DATE:
795 return "TO_DATE(%s::text,'YYYY-MM-DD')";
796 break;
797 case SQL_TO_DATETIME:
798 return "TO_TIMESTAMP(%s::text, 'YYYY-MM-DD HH24:MI:SS')";
799 break;
800 case SQL_TO_UNIXEPOCH:
801 return "ROUND(DATE_PART('epoch',%s))";
802 break;
803 case SQL_CURRENT_TIMESTAMP:
804 return "NOW()";
805 break;
806 case SQL_EXPIRE:
807 return "NOW() - INTERVAL '%d DAY'";
808 break;
809 case SQL_WITHIN:
810 return "NOW() - INTERVAL '%d SECOND'";
811 break;
812 case SQL_IGNORE:
813 case SQL_BINARY:
814 return "";
815 break;
816 case SQL_SENSITIVE_LIKE:
817 return "LIKE";
818 break;
819 case SQL_INSENSITIVE_LIKE:
820 return "ILIKE";
821 break;
822 case SQL_ENCODE_ESCAPE:
823 return "ENCODE(%s::bytea,'escape')";
824 break;
825 case SQL_STRCASE:
826 return "LOWER(%s)";
827 break;
828 case SQL_PARTIAL:
829 return "SUBSTRING(%s,0,255)";
830 break;
831 case SQL_RETURNING:
832 return "RETURNING %s";
833 break;
834 case SQL_TABLE_EXISTS:
835 return "SELECT 1=1 FROM %s%s LIMIT 1 OFFSET 0";
836 break;
837 case SQL_ESCAPE_COLUMN:
838 return "\"";
839 break;
840 case SQL_COMPARE_BLOB:
841 return "%s=?";
842 break;
843 }
844 return NULL;
845 }
846
db_get_oracle_sql(sql_fragment frag)847 static const char * db_get_oracle_sql(sql_fragment frag)
848 {
849 switch(frag) {
850 case SQL_TO_CHAR:
851 return "TO_CHAR(%s, 'YYYY-MM-DD HH24:MI:SS')";
852 break;
853 case SQL_TO_DATE:
854 return "TRUNC(TO_TIMESTAMP(%s))";
855 break;
856 case SQL_TO_DATETIME:
857 return "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS')";
858 break;
859 case SQL_TO_UNIXEPOCH:
860 return "DBMAIL_UTILS.UNIX_TIMESTAMP(%s)";
861 break;
862 case SQL_CURRENT_TIMESTAMP:
863 return "SYSTIMESTAMP";
864 break;
865 case SQL_EXPIRE:
866 return "SYSTIMESTAMP - NUMTODSINTERVAL(%d,'day')";
867 break;
868 case SQL_WITHIN:
869 return "SYSTIMESTAMP - NUMTODSINTERVAL(%d,'second')";
870 break;
871 case SQL_BINARY:
872 return "";
873 break;
874 case SQL_SENSITIVE_LIKE:
875 return "LIKE";
876 break;
877 case SQL_INSENSITIVE_LIKE:
878 return "LIKE";
879 break;
880 case SQL_IGNORE:
881 return "";
882 break;
883 case SQL_STRCASE:
884 case SQL_ENCODE_ESCAPE:
885 case SQL_PARTIAL:
886 return "%s";
887 break;
888 case SQL_RETURNING:
889 break;
890 case SQL_TABLE_EXISTS:
891 return "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='%s%s'";
892 break;
893 case SQL_ESCAPE_COLUMN:
894 return "\"";
895 break;
896 case SQL_COMPARE_BLOB:
897 return "DBMS_LOB.COMPARE(%s,?) = 0";
898 break;
899 }
900 return NULL;
901 }
902
903
904
db_get_sql(sql_fragment frag)905 const char * db_get_sql(sql_fragment frag)
906 {
907 switch(db_params.db_driver) {
908 case DM_DRIVER_SQLITE:
909 return db_get_sqlite_sql(frag);
910 case DM_DRIVER_MYSQL:
911 return db_get_mysql_sql(frag);
912 case DM_DRIVER_POSTGRESQL:
913 return db_get_pgsql_sql(frag);
914 case DM_DRIVER_ORACLE:
915 return db_get_oracle_sql(frag);
916
917 }
918
919 TRACE(TRACE_EMERG, "driver not in [sqlite|mysql|postgresql|oracle]");
920
921 return NULL;
922 }
923
db_returning(const char * s)924 char *db_returning(const char *s)
925 {
926 char *r = NULL, *t = NULL;
927
928 if ((t = (char *)db_get_sql(SQL_RETURNING)))
929 r = g_strdup_printf(t,s);
930 else
931 r = g_strdup("");
932 return r;
933 }
934
935 /////////////////////////////////////////////////////////////////////////////
936
937
938 /*
939 * check to make sure the database has been upgraded
940 */
check_table_exists(Connection_T c,const char * table)941 static ResultSet_T check_table_exists(Connection_T c, const char *table)
942 {
943 db_con_clear(c);
944 return db_query(c, db_get_sql(SQL_TABLE_EXISTS), DBPFX, table);
945 }
946
check_upgrade_step(int from_version,int to_version)947 static int check_upgrade_step(int from_version, int to_version)
948 {
949 const char *query = NULL;
950 volatile int result = 0;
951 Connection_T c = db_con_get();
952 PreparedStatement_T st; ResultSet_T r;
953
954 TRY
955 st = db_stmt_prepare(c,
956 "SELECT 1=1 FROM %supgrade_steps WHERE "
957 "from_version = ? "
958 "AND to_version = ?", DBPFX);
959 db_stmt_set_int(st, 1, from_version);
960 db_stmt_set_int(st, 2, to_version);
961 r = db_stmt_query(st);
962 if (db_result_next(r)) // found
963 result = to_version;
964 CATCH(SQLException)
965 LOG_SQLERROR;
966 FINALLY
967 db_con_clear(c);
968 END_TRY;
969
970 if (result) {
971 db_con_close(c);
972 return result;
973 }
974
975 switch(db_params.db_driver) {
976 case DM_DRIVER_SQLITE:
977 if (to_version == 32001) query = DM_SQLITE_32001;
978 if (to_version == 32002) query = DM_SQLITE_32002;
979 if (to_version == 32003) query = DM_SQLITE_32003;
980 if (to_version == 32004) query = DM_SQLITE_32004;
981 if (to_version == 32005) query = DM_SQLITE_32005;
982 break;
983 case DM_DRIVER_MYSQL:
984 if (to_version == 32001) query = DM_MYSQL_32001;
985 if (to_version == 32002) query = DM_MYSQL_32002;
986 if (to_version == 32003) query = DM_MYSQL_32003;
987 if (to_version == 32004) query = DM_MYSQL_32004;
988 if (to_version == 32005) query = DM_MYSQL_32005;
989 break;
990 case DM_DRIVER_POSTGRESQL:
991 if (to_version == 32001) query = DM_PGSQL_32001;
992 if (to_version == 32002) query = DM_PGSQL_32002;
993 if (to_version == 32003) query = DM_PGSQL_32003;
994 if (to_version == 32004) query = DM_PGSQL_32004;
995 if (to_version == 32005) query = DM_PGSQL_32005;
996 break;
997 default:
998 TRACE(TRACE_WARNING, "Migrations not supported for database driver");
999 db_con_close(c);
1000 return DM_EQUERY;
1001 break;
1002 }
1003
1004 if (! query) {
1005 TRACE(TRACE_INFO, "Unable to find migration query for upgrade step [%d]", to_version);
1006 db_con_close(c);
1007 return DM_EQUERY;
1008 }
1009
1010 TRACE(TRACE_INFO, "Running upgrade step %d -> %d", from_version, to_version);
1011 if (db_exec(c, query))
1012 result = to_version;
1013 else
1014 result = DM_EQUERY;
1015
1016 db_con_close(c);
1017
1018 return result;
1019 }
1020
db_check_version(void)1021 int db_check_version(void)
1022 {
1023 Connection_T c = db_con_get();
1024 volatile int ok = 0;
1025 volatile int db = 0;
1026
1027 TRY
1028 if (db_query(c, db_get_sql(SQL_TABLE_EXISTS), DBPFX, "users"))
1029 db = 1;
1030 CATCH(SQLException)
1031 LOG_SQLERROR;
1032 END_TRY;
1033
1034 db_con_clear(c);
1035
1036
1037 if ((! db) && (db_params.db_driver == DM_DRIVER_SQLITE)) {
1038 TRY
1039 db_exec(c, DM_SQLITECREATE);
1040 db = 1;
1041 CATCH(SQLException)
1042 LOG_SQLERROR;
1043 END_TRY;
1044 }
1045
1046 if (! db) {
1047 TRACE(TRACE_EMERG, "Try creating the database tables");
1048 _exit(1);
1049 }
1050
1051 db_con_clear(c);
1052
1053 do {
1054 if (! check_table_exists(c, "mimeparts"))
1055 break;
1056 if (! check_table_exists(c, "header"))
1057 break;
1058 ok = 1;
1059 break;
1060 } while (true);
1061
1062 if (! ok) {
1063 TRACE(TRACE_WARNING,"Schema version incompatible. Bailing out");
1064 return DM_EQUERY;
1065 }
1066
1067 db_con_clear(c);
1068
1069 do {
1070 if ((ok = check_upgrade_step(0, 32001)) == DM_EQUERY)
1071 break;
1072 if ((ok = check_upgrade_step(32001, 32002)) == DM_EQUERY)
1073 break;
1074 if ((ok = check_upgrade_step(32001, 32003)) == DM_EQUERY)
1075 break;
1076 if ((ok = check_upgrade_step(32001, 32004)) == DM_EQUERY)
1077 break;
1078 if ((ok = check_upgrade_step(32001, 32005)) == DM_EQUERY)
1079 break;
1080 break;
1081 } while (true);
1082
1083 db_con_close(c);
1084
1085 if (ok == 32005) {
1086 TRACE(TRACE_DEBUG, "Schema check successful");
1087 } else {
1088 TRACE(TRACE_WARNING,"Schema version incompatible [%d]. Bailing out",
1089 ok);
1090 return DM_EQUERY;
1091 }
1092
1093 return DM_SUCCESS;
1094 }
1095
1096 /* test existence of usermap table */
db_use_usermap(void)1097 int db_use_usermap(void)
1098 {
1099 volatile int use_usermap = TRUE;
1100 Connection_T c = db_con_get();
1101 TRY
1102 if (! db_query(c, db_get_sql(SQL_TABLE_EXISTS), DBPFX, "usermap"))
1103 use_usermap = FALSE;
1104 CATCH(SQLException)
1105 LOG_SQLERROR;
1106 FINALLY
1107 db_con_close(c);
1108 END_TRY;
1109
1110 TRACE(TRACE_DEBUG, "%s usermap lookups", use_usermap ? "enabling" : "disabling" );
1111 return use_usermap;
1112 }
1113
db_get_physmessage_id(uint64_t message_idnr,uint64_t * physmessage_id)1114 int db_get_physmessage_id(uint64_t message_idnr, uint64_t * physmessage_id)
1115 {
1116 PreparedStatement_T stmt;
1117 Connection_T c;
1118 ResultSet_T r;
1119 volatile int t = DM_SUCCESS;
1120 assert(physmessage_id != NULL);
1121 *physmessage_id = 0;
1122
1123 c = db_con_get();
1124 TRY
1125 stmt = db_stmt_prepare(c,
1126 "SELECT physmessage_id FROM %smessages WHERE message_idnr = ?",
1127 DBPFX);
1128 db_stmt_set_u64(stmt, 1, message_idnr);
1129 r = db_stmt_query(stmt);
1130
1131 if (db_result_next(r))
1132 *physmessage_id = db_result_get_u64(r, 0);
1133 //TRACE(TRACE_DEBUG,"Retrieved [%" PRIu64 "]",*physmessage_id);
1134 CATCH(SQLException)
1135 LOG_SQLERROR;
1136 t = DM_EQUERY;
1137 FINALLY
1138 db_con_close(c);
1139 END_TRY;
1140
1141 if (! *physmessage_id) return DM_EGENERAL;
1142
1143 return t;
1144 }
1145
1146
1147 /**
1148 * check if the user_idnr is the same as that of the DBMAIL_DELIVERY_USERNAME
1149 * \param user_idnr user idnr to check
1150 * \return
1151 * - -1 on error
1152 * - 0 of different user
1153 * - 1 if same user (user_idnr belongs to DBMAIL_DELIVERY_USERNAME
1154 */
1155
user_idnr_is_delivery_user_idnr(uint64_t user_idnr)1156 static int user_idnr_is_delivery_user_idnr(uint64_t user_idnr)
1157 {
1158 static int delivery_user_idnr_looked_up = 0;
1159 static uint64_t delivery_user_idnr;
1160 G_LOCK_DEFINE_STATIC(mutex);
1161
1162 if (delivery_user_idnr_looked_up == 0) {
1163 uint64_t idnr;
1164 TRACE(TRACE_DEBUG, "looking up user_idnr for [%s]", DBMAIL_DELIVERY_USERNAME);
1165 if (! auth_user_exists(DBMAIL_DELIVERY_USERNAME, &idnr)) {
1166 TRACE(TRACE_ERR, "error looking up user_idnr for %s", DBMAIL_DELIVERY_USERNAME);
1167 return DM_EQUERY;
1168 }
1169 G_LOCK(mutex);
1170 delivery_user_idnr = idnr;
1171 delivery_user_idnr_looked_up = 1;
1172 G_UNLOCK(mutex);
1173 }
1174
1175 if (delivery_user_idnr == user_idnr)
1176 return DM_EGENERAL;
1177 else
1178 return DM_SUCCESS;
1179 }
1180
1181 #define NOT_DELIVERY_USER \
1182 int result; \
1183 if ((result = user_idnr_is_delivery_user_idnr(user_idnr)) == DM_EQUERY) return DM_EQUERY; \
1184 if (result == DM_EGENERAL) return DM_EGENERAL;
1185
1186
dm_quota_user_get(uint64_t user_idnr,uint64_t * size)1187 int dm_quota_user_get(uint64_t user_idnr, uint64_t *size)
1188 {
1189 PreparedStatement_T stmt;
1190 Connection_T c;
1191 ResultSet_T r;
1192 assert(size != NULL);
1193
1194 c = db_con_get();
1195 TRY
1196 stmt = db_stmt_prepare(c,
1197 "SELECT curmail_size FROM %susers WHERE user_idnr = ?",
1198 DBPFX);
1199 db_stmt_set_u64(stmt, 1, user_idnr);
1200 r = db_stmt_query(stmt);
1201
1202 if (db_result_next(r))
1203 *size = db_result_get_u64(r, 0);
1204 CATCH(SQLException)
1205 LOG_SQLERROR;
1206 FINALLY
1207 db_con_close(c);
1208 END_TRY;
1209
1210 return DM_EGENERAL;
1211 }
1212
dm_quota_user_set(uint64_t user_idnr,uint64_t size)1213 int dm_quota_user_set(uint64_t user_idnr, uint64_t size)
1214 {
1215 NOT_DELIVERY_USER
1216 return db_update("UPDATE %susers SET curmail_size = %" PRIu64 " WHERE user_idnr = %" PRIu64 "",
1217 DBPFX, size, user_idnr);
1218 }
dm_quota_user_inc(uint64_t user_idnr,uint64_t size)1219 int dm_quota_user_inc(uint64_t user_idnr, uint64_t size)
1220 {
1221 NOT_DELIVERY_USER
1222 return db_update("UPDATE %susers SET curmail_size = curmail_size + %" PRIu64 " WHERE user_idnr = %" PRIu64 "",
1223 DBPFX, size, user_idnr);
1224 }
dm_quota_user_dec(uint64_t user_idnr,uint64_t size)1225 int dm_quota_user_dec(uint64_t user_idnr, uint64_t size)
1226 {
1227 NOT_DELIVERY_USER
1228 return db_update("UPDATE %susers SET curmail_size = CASE WHEN curmail_size >= %" PRIu64 " THEN curmail_size - %" PRIu64 " ELSE 0 END WHERE user_idnr = %" PRIu64 "",
1229 DBPFX, size, size, user_idnr);
1230 }
1231
dm_quota_user_validate(uint64_t user_idnr,uint64_t msg_size)1232 static int dm_quota_user_validate(uint64_t user_idnr, uint64_t msg_size)
1233 {
1234 uint64_t maxmail_size;
1235 Connection_T c; ResultSet_T r; volatile gboolean t = TRUE;
1236
1237 if (auth_getmaxmailsize(user_idnr, &maxmail_size) == -1) {
1238 TRACE(TRACE_ERR, "auth_getmaxmailsize() failed\n");
1239 return DM_EQUERY;
1240 }
1241
1242 if (maxmail_size <= 0)
1243 return TRUE;
1244
1245 c = db_con_get();
1246
1247 TRY
1248 r = db_query(c, "SELECT 1 FROM %susers WHERE user_idnr = %" PRIu64 " "
1249 "AND (curmail_size + %" PRIu64 " > %" PRIu64 ")",
1250 DBPFX, user_idnr, msg_size, maxmail_size);
1251 if (! r)
1252 t = DM_EQUERY;
1253 else if (db_result_next(r))
1254 t = FALSE;
1255 CATCH(SQLException)
1256 LOG_SQLERROR;
1257 FINALLY
1258 db_con_close(c);
1259 END_TRY;
1260
1261 return t;
1262 }
1263
dm_quota_rebuild_user(uint64_t user_idnr)1264 int dm_quota_rebuild_user(uint64_t user_idnr)
1265 {
1266 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1267 volatile uint64_t quotum = 0;
1268
1269 c = db_con_get();
1270 TRY
1271 r = db_query(c, "SELECT COALESCE(SUM(pm.messagesize),0) "
1272 "FROM %sphysmessage pm, %smessages m, %smailboxes mb "
1273 "WHERE m.physmessage_id = pm.id "
1274 "AND m.mailbox_idnr = mb.mailbox_idnr "
1275 "AND mb.owner_idnr = %" PRIu64 " " "AND m.status < %d",
1276 DBPFX,DBPFX,DBPFX,user_idnr, MESSAGE_STATUS_DELETE);
1277
1278 if (db_result_next(r))
1279 quotum = db_result_get_u64(r, 0);
1280 else
1281 TRACE(TRACE_WARNING, "SUM did not give result, assuming empty mailbox" );
1282
1283 CATCH(SQLException)
1284 LOG_SQLERROR;
1285 t = DM_EQUERY;
1286 FINALLY
1287 db_con_close(c);
1288 END_TRY;
1289
1290 if (t == DM_EQUERY) return t;
1291
1292 TRACE(TRACE_DEBUG, "found quotum usage of [%" PRIu64 "] bytes", quotum);
1293 /* now insert the used quotum into the users table */
1294 if (! dm_quota_user_set(user_idnr, quotum))
1295 return DM_EQUERY;
1296 return DM_SUCCESS;
1297 }
1298
1299 struct used_quota {
1300 uint64_t user_id;
1301 uint64_t curmail;
1302 };
1303
dm_quota_rebuild()1304 int dm_quota_rebuild()
1305 {
1306 Connection_T c; ResultSet_T r;
1307
1308 GList *quota = NULL;
1309 struct used_quota *q;
1310 volatile int i = 0;
1311 int result;
1312
1313 c = db_con_get();
1314 TRY
1315 r = db_query(c, "SELECT usr.user_idnr, SUM(pm.messagesize), usr.curmail_size FROM %susers usr "
1316 "LEFT JOIN %smailboxes mbx ON mbx.owner_idnr = usr.user_idnr "
1317 "LEFT JOIN %smessages msg ON msg.mailbox_idnr = mbx.mailbox_idnr "
1318 "LEFT JOIN %sphysmessage pm ON pm.id = msg.physmessage_id "
1319 "AND msg.status < %d "
1320 "GROUP BY usr.user_idnr, usr.curmail_size "
1321 "HAVING ((SUM(pm.messagesize) <> usr.curmail_size) OR "
1322 "(NOT (SUM(pm.messagesize) IS NOT NULL) "
1323 "AND usr.curmail_size <> 0))", DBPFX,DBPFX,
1324 DBPFX,DBPFX,MESSAGE_STATUS_DELETE);
1325
1326 while (db_result_next(r)) {
1327 i++;
1328 q = g_new0(struct used_quota,1);
1329 q->user_id = db_result_get_u64(r, 0);
1330 q->curmail = db_result_get_u64(r, 1);
1331 quota = g_list_prepend(quota, q);
1332 }
1333 CATCH(SQLException)
1334 LOG_SQLERROR;
1335 FINALLY
1336 db_con_close(c);
1337 END_TRY;
1338
1339 result = i;
1340 if (! i) {
1341 TRACE(TRACE_DEBUG, "quotum is already up to date");
1342 return DM_SUCCESS;
1343 }
1344
1345 /* now update the used quotum for all users that need to be updated */
1346 quota = g_list_first(quota);
1347 while (quota) {
1348 q = (struct used_quota *)quota->data;
1349 if (! dm_quota_user_set(q->user_id, q->curmail))
1350 result = DM_EQUERY;
1351
1352 if (! g_list_next(quota)) break;
1353 quota = g_list_next(quota);
1354 }
1355
1356 /* free allocated memory */
1357 g_list_destroy(quota);
1358
1359 return result;
1360 }
1361
1362
1363
db_get_notify_address(uint64_t user_idnr,char ** notify_address)1364 int db_get_notify_address(uint64_t user_idnr, char **notify_address)
1365 {
1366 Connection_T c; ResultSet_T r;
1367 const char *query_result = NULL;
1368 volatile int t = DM_EGENERAL;
1369
1370 c = db_con_get();
1371 TRY
1372 r = db_query(c, "SELECT notify_address "
1373 "FROM %sauto_notifications WHERE user_idnr = %" PRIu64 "",
1374 DBPFX,user_idnr);
1375 if (db_result_next(r)) {
1376 query_result = db_result_get(r, 0);
1377 if (query_result && (strlen(query_result) > 0)) {
1378 *notify_address = g_strdup(query_result);
1379 TRACE(TRACE_DEBUG, "notify address [%s]", *notify_address);
1380 }
1381 }
1382 t = DM_SUCCESS;
1383 CATCH(SQLException)
1384 LOG_SQLERROR;
1385 FINALLY
1386 db_con_close(c);
1387 END_TRY;
1388
1389 return t;
1390 }
1391
db_get_reply_body(uint64_t user_idnr,char ** reply_body)1392 int db_get_reply_body(uint64_t user_idnr, char **reply_body)
1393 {
1394 Connection_T c; ResultSet_T r; PreparedStatement_T s;
1395 const char *query_result;
1396 volatile int t = DM_EGENERAL;
1397 *reply_body = NULL;
1398
1399 c = db_con_get();
1400 TRY
1401 s = db_stmt_prepare(c, "SELECT reply_body FROM %sauto_replies "
1402 "WHERE user_idnr = ? "
1403 "AND %s BETWEEN start_date AND stop_date",
1404 DBPFX,
1405 db_get_sql(SQL_CURRENT_TIMESTAMP));
1406 db_stmt_set_u64(s, 1, user_idnr);
1407 r = db_stmt_query(s);
1408 if (db_result_next(r)) {
1409 query_result = db_result_get(r, 0);
1410 if (query_result && (strlen(query_result)>0)) {
1411 *reply_body = g_strdup(query_result);
1412 TRACE(TRACE_DEBUG, "reply_body [%s]", *reply_body);
1413 t = DM_SUCCESS;
1414 }
1415 }
1416 CATCH(SQLException)
1417 LOG_SQLERROR;
1418 FINALLY
1419 db_con_close(c);
1420 END_TRY;
1421 return t;
1422 }
1423
1424
db_get_useridnr(uint64_t message_idnr)1425 uint64_t db_get_useridnr(uint64_t message_idnr)
1426 {
1427 Connection_T c; ResultSet_T r;
1428 volatile uint64_t user_idnr = 0;
1429 c = db_con_get();
1430 TRY
1431 r = db_query(c, "SELECT %smailboxes.owner_idnr FROM %smailboxes, %smessages "
1432 "WHERE %smailboxes.mailbox_idnr = %smessages.mailbox_idnr "
1433 "AND %smessages.message_idnr = %" PRIu64 "", DBPFX,DBPFX,DBPFX,
1434 DBPFX,DBPFX,DBPFX,message_idnr);
1435 if (db_result_next(r))
1436 user_idnr = db_result_get_u64(r, 0);
1437 CATCH(SQLException)
1438 LOG_SQLERROR;
1439 FINALLY
1440 db_con_close(c);
1441 END_TRY;
1442 return user_idnr;
1443 }
1444
1445
1446
db_log_ip(const char * ip)1447 int db_log_ip(const char *ip)
1448 {
1449 Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = DM_SUCCESS;
1450 volatile uint64_t id = 0;
1451
1452 c = db_con_get();
1453 TRY
1454 db_begin_transaction(c);
1455 s = db_stmt_prepare(c, "SELECT idnr FROM %spbsp WHERE ipnumber = ?", DBPFX);
1456 db_stmt_set_str(s,1,ip);
1457 r = db_stmt_query(s);
1458 if (db_result_next(r))
1459 id = db_result_get_u64(r, 0);
1460 if (id) {
1461 /* this IP is already in the table, update the 'since' field */
1462 s = db_stmt_prepare(c, "UPDATE %spbsp SET since = %s WHERE idnr = ?",
1463 DBPFX, db_get_sql(SQL_CURRENT_TIMESTAMP));
1464 db_stmt_set_u64(s,1,id);
1465
1466 db_stmt_exec(s);
1467 } else {
1468 /* IP not in table, insert row */
1469 s = db_stmt_prepare(c, "INSERT INTO %spbsp (since, ipnumber) VALUES (%s, ?)",
1470 DBPFX, db_get_sql(SQL_CURRENT_TIMESTAMP));
1471 db_stmt_set_str(s,1,ip);
1472 db_stmt_exec(s);
1473 }
1474 db_commit_transaction(c);
1475 TRACE(TRACE_DEBUG, "ip [%s] logged", ip);
1476 CATCH(SQLException)
1477 LOG_SQLERROR;
1478 db_rollback_transaction(c);
1479 t = DM_EQUERY;
1480 FINALLY
1481 db_con_close(c);
1482 END_TRY;
1483
1484 return t;
1485 }
1486
db_cleanup(void)1487 int db_cleanup(void)
1488 {
1489 return db_do_cleanup(DB_TABLENAMES, DB_NTABLES);
1490 }
1491
db_empty_mailbox(uint64_t user_idnr,int only_empty)1492 int db_empty_mailbox(uint64_t user_idnr, int only_empty)
1493 {
1494 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1495 GList *mboxids = NULL;
1496 uint64_t *id;
1497 volatile unsigned i = 0;
1498 volatile int result = 0;
1499
1500 c = db_con_get();
1501
1502 TRY
1503 r = db_query(c, "SELECT mailbox_idnr FROM %smailboxes WHERE owner_idnr=%" PRIu64 "",
1504 DBPFX, user_idnr);
1505 while (db_result_next(r)) {
1506 i++;
1507 id = g_new0(uint64_t, 1);
1508 *id = db_result_get_u64(r, 0);
1509 mboxids = g_list_prepend(mboxids,id);
1510 }
1511 CATCH(SQLException)
1512 LOG_SQLERROR;
1513 t = DM_EQUERY;
1514 g_list_free(mboxids);
1515 FINALLY;
1516 db_con_close(c);
1517 END_TRY;
1518
1519 if (i == 0) {
1520 TRACE(TRACE_WARNING, "user [%" PRIu64 "] does not have any mailboxes?", user_idnr);
1521 return t;
1522 }
1523
1524 mboxids = g_list_first(mboxids);
1525 while (mboxids) {
1526 id = mboxids->data;
1527 if (db_delete_mailbox(*id, only_empty, 1)) {
1528 TRACE(TRACE_ERR, "error emptying mailbox [%" PRIu64 "]", *id);
1529 result = -1;
1530 break;
1531 }
1532 if (! g_list_next(mboxids)) break;
1533 mboxids = g_list_next(mboxids);
1534 }
1535
1536 g_list_destroy(mboxids);
1537
1538 return result;
1539 }
1540
db_icheck_physmessages(gboolean cleanup)1541 int db_icheck_physmessages(gboolean cleanup)
1542 {
1543 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1544 GList *ids = NULL;
1545
1546 c = db_con_get();
1547 TRY
1548 r = db_query(c, "SELECT p.id FROM %sphysmessage p LEFT JOIN %smessages m ON p.id = m.physmessage_id "
1549 "WHERE m.physmessage_id IS NULL", DBPFX, DBPFX);
1550 while(db_result_next(r)) {
1551 uint64_t *id = g_new0(uint64_t, 1);
1552 *id = db_result_get_u64(r, 0);
1553 ids = g_list_prepend(ids, id);
1554 }
1555 t = g_list_length(ids);
1556 if (cleanup) {
1557 while(ids) {
1558 db_begin_transaction(c);
1559 db_exec(c, "DELETE FROM %sphysmessage WHERE id = %" PRIu64 "", DBPFX, *(uint64_t *)ids->data);
1560 db_commit_transaction(c);
1561 if (! g_list_next(ids)) break;
1562 ids = g_list_next(ids);
1563 }
1564 }
1565 g_list_destroy(ids);
1566 CATCH(SQLException)
1567 LOG_SQLERROR;
1568 db_rollback_transaction(c);
1569 t = DM_EQUERY;
1570 FINALLY
1571 db_con_close(c);
1572 END_TRY;
1573
1574 return t;
1575 }
1576
db_icheck_partlists(gboolean cleanup)1577 int db_icheck_partlists(gboolean cleanup)
1578 {
1579 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1580 GList *ids = NULL;
1581
1582 c = db_con_get();
1583 TRY
1584 r = db_query(c, "SELECT COUNT(*), l.physmessage_id FROM %spartlists l LEFT JOIN %sphysmessage p ON p.id = l.physmessage_id "
1585 "WHERE p.id IS NULL GROUP BY l.physmessage_id", DBPFX, DBPFX);
1586
1587 while(db_result_next(r)) {
1588 uint64_t *id = g_new0(uint64_t, 1);
1589 *id = db_result_get_u64(r, 0);
1590 ids = g_list_prepend(ids, id);
1591 }
1592 t = g_list_length(ids);
1593 if (cleanup) {
1594 while(ids) {
1595 db_begin_transaction(c);
1596 db_exec(c, "DELETE FROM %spartlists WHERE physmessage_id = %" PRIu64 "", DBPFX, *(uint64_t *)ids->data);
1597 db_commit_transaction(c);
1598 if (! g_list_next(ids)) break;
1599 ids = g_list_next(ids);
1600 }
1601 }
1602 g_list_destroy(ids);
1603 CATCH(SQLException)
1604 LOG_SQLERROR;
1605 db_rollback_transaction(c);
1606 t = DM_EQUERY;
1607 FINALLY
1608 db_con_close(c);
1609 END_TRY;
1610
1611 return t;
1612 }
1613
db_icheck_mimeparts(gboolean cleanup)1614 int db_icheck_mimeparts(gboolean cleanup)
1615 {
1616 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1617 GList *ids = NULL;
1618
1619 c = db_con_get();
1620 TRY
1621 r = db_query(c, "SELECT p.id FROM %smimeparts p LEFT JOIN %spartlists l ON p.id = l.part_id "
1622 "WHERE l.part_id IS NULL", DBPFX, DBPFX);
1623 while(db_result_next(r)) {
1624 uint64_t *id = g_new0(uint64_t, 1);
1625 *id = db_result_get_u64(r, 0);
1626 ids = g_list_prepend(ids, id);
1627 }
1628 t = g_list_length(ids);
1629 if (cleanup) {
1630 while(ids) {
1631 db_begin_transaction(c);
1632 db_exec(c, "DELETE FROM %smimeparts WHERE id = %" PRIu64 "", DBPFX, *(uint64_t *)ids->data);
1633 db_commit_transaction(c);
1634 if (! g_list_next(ids)) break;
1635 ids = g_list_next(ids);
1636 }
1637 }
1638 g_list_destroy(ids);
1639 CATCH(SQLException)
1640 LOG_SQLERROR;
1641 db_rollback_transaction(c);
1642 t = DM_EQUERY;
1643 FINALLY
1644 db_con_close(c);
1645 END_TRY;
1646
1647 return t;
1648 }
1649
db_icheck_headernames(gboolean cleanup)1650 int db_icheck_headernames(gboolean cleanup)
1651 {
1652 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1653 GList *ids = NULL;
1654
1655 c = db_con_get();
1656 TRY
1657 r = db_query(c, "SELECT hn.id FROM %sheadername hn LEFT JOIN %sheader h ON hn.id = h.headername_id "
1658 "WHERE h.headername_id IS NULL", DBPFX, DBPFX);
1659 while(db_result_next(r)) {
1660 uint64_t *id = g_new0(uint64_t, 1);
1661 *id = db_result_get_u64(r, 0);
1662 ids = g_list_prepend(ids, id);
1663 }
1664 t = g_list_length(ids);
1665 if (cleanup) {
1666 while(ids) {
1667 db_begin_transaction(c);
1668 db_exec(c, "DELETE FROM %sheadername WHERE id = %" PRIu64 "", DBPFX, *(uint64_t *)ids->data);
1669 db_commit_transaction(c);
1670 if (! g_list_next(ids)) break;
1671 ids = g_list_next(ids);
1672 }
1673 }
1674 g_list_destroy(ids);
1675 CATCH(SQLException)
1676 LOG_SQLERROR;
1677 db_rollback_transaction(c);
1678 t = DM_EQUERY;
1679 FINALLY
1680 db_con_close(c);
1681 END_TRY;
1682
1683 return t;
1684 }
1685
db_icheck_headervalues(gboolean cleanup)1686 int db_icheck_headervalues(gboolean cleanup)
1687 {
1688 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1689 GList *ids = NULL;
1690
1691 c = db_con_get();
1692 TRY
1693 r = db_query(c, "SELECT hv.id FROM %sheadervalue hv LEFT JOIN %sheader h ON hv.id = h.headervalue_id "
1694 "WHERE h.headervalue_id IS NULL", DBPFX, DBPFX);
1695 while(db_result_next(r)) {
1696 uint64_t *id = g_new0(uint64_t, 1);
1697 *id = db_result_get_u64(r, 0);
1698 ids = g_list_prepend(ids, id);
1699 }
1700 t = g_list_length(ids);
1701 if (cleanup) {
1702 while(ids) {
1703 db_begin_transaction(c);
1704 db_exec(c, "DELETE FROM %sheadervalue WHERE id = %" PRIu64 "", DBPFX, *(uint64_t *)ids->data);
1705 db_commit_transaction(c);
1706 if (! g_list_next(ids)) break;
1707 ids = g_list_next(ids);
1708 }
1709 }
1710 g_list_destroy(ids);
1711 CATCH(SQLException)
1712 LOG_SQLERROR;
1713 db_rollback_transaction(c);
1714 t = DM_EQUERY;
1715 FINALLY
1716 db_con_close(c);
1717 END_TRY;
1718
1719 return t;
1720 }
1721
db_icheck_rfcsize(GList ** lost)1722 int db_icheck_rfcsize(GList **lost)
1723 {
1724 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1725 uint64_t *id;
1726
1727 c = db_con_get();
1728 TRY
1729 r = db_query(c, "SELECT id FROM %sphysmessage WHERE rfcsize=0", DBPFX);
1730 while (db_result_next(r)) {
1731 id = g_new0(uint64_t,1);
1732 *id = db_result_get_u64(r, 0);
1733 *(GList **)lost = g_list_prepend(*(GList **)lost, id);
1734 }
1735 CATCH(SQLException)
1736 LOG_SQLERROR;
1737 t = DM_EQUERY;
1738 FINALLY
1739 db_con_close(c);
1740 END_TRY;
1741
1742 return t;
1743 }
1744
db_update_rfcsize(GList * lost)1745 int db_update_rfcsize(GList *lost)
1746 {
1747 Connection_T c;
1748 uint64_t *pmsid;
1749 DbmailMessage *msg;
1750 if (! lost)
1751 return DM_SUCCESS;
1752
1753 lost = g_list_first(lost);
1754
1755 c = db_con_get();
1756 while(lost) {
1757 pmsid = (uint64_t *)lost->data;
1758
1759 if (! (msg = dbmail_message_new(NULL))) {
1760 db_con_close(c);
1761 return DM_EQUERY;
1762 }
1763
1764 if (! (msg = dbmail_message_retrieve(msg, *pmsid))) {
1765 TRACE(TRACE_WARNING, "error retrieving physmessage: [%" PRIu64 "]", *pmsid);
1766 fprintf(stderr,"E");
1767 } else {
1768 TRY
1769 db_begin_transaction(c);
1770 db_exec(c, "UPDATE %sphysmessage SET rfcsize = %" PRIu64 " WHERE id = %" PRIu64 "",
1771 DBPFX, (uint64_t)dbmail_message_get_size(msg,TRUE), *pmsid);
1772 db_commit_transaction(c);
1773 fprintf(stderr,".");
1774 CATCH(SQLException)
1775 db_rollback_transaction(c);
1776 fprintf(stderr,"E");
1777 END_TRY;
1778 }
1779 dbmail_message_free(msg);
1780 if (! g_list_next(lost)) break;
1781 lost = g_list_next(lost);
1782 }
1783
1784 db_con_close(c);
1785
1786 return DM_SUCCESS;
1787 }
1788
db_set_headercache(GList * lost)1789 int db_set_headercache(GList *lost)
1790 {
1791 uint64_t pmsgid;
1792 uint64_t *id;
1793 DbmailMessage *msg;
1794 Mempool_T pool;
1795 if (! lost)
1796 return DM_SUCCESS;
1797
1798 pool = mempool_open();
1799 lost = g_list_first(lost);
1800 while (lost) {
1801 id = (uint64_t *)lost->data;
1802 pmsgid = *id;
1803
1804 msg = dbmail_message_new(pool);
1805 if (! msg) {
1806 mempool_close(&pool);
1807 return DM_EQUERY;
1808 }
1809
1810 if (! (msg = dbmail_message_retrieve(msg, pmsgid))) {
1811 TRACE(TRACE_WARNING, "error retrieving physmessage: [%" PRIu64 "]", pmsgid);
1812 fprintf(stderr,"E");
1813 } else {
1814 if (dbmail_message_cache_headers(msg) != 0) {
1815 TRACE(TRACE_WARNING,"error caching headers for physmessage: [%" PRIu64 "]",
1816 pmsgid);
1817 fprintf(stderr,"E");
1818 } else {
1819 fprintf(stderr,".");
1820 }
1821 dbmail_message_free(msg);
1822 }
1823 if (! g_list_next(lost)) break;
1824 lost = g_list_next(lost);
1825 }
1826 mempool_close(&pool);
1827
1828 return DM_SUCCESS;
1829 }
1830
1831
db_icheck_headercache(GList ** lost)1832 int db_icheck_headercache(GList **lost)
1833 {
1834 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1835 uint64_t *id;
1836
1837 c = db_con_get();
1838 TRY
1839 r = db_query(c, "SELECT p.id "
1840 "FROM %sphysmessage p "
1841 "LEFT JOIN %sheader h ON p.id = h.physmessage_id "
1842 "WHERE h.physmessage_id IS NULL", DBPFX, DBPFX);
1843 while (db_result_next(r)) {
1844 id = g_new0(uint64_t,1);
1845 *id = db_result_get_u64(r, 0);
1846 *(GList **)lost = g_list_prepend(*(GList **)lost,id);
1847 }
1848 CATCH(SQLException)
1849 LOG_SQLERROR;
1850 t = DM_EQUERY;
1851 FINALLY
1852 db_con_close(c);
1853 END_TRY;
1854
1855 return t;
1856 }
1857
db_set_envelope(GList * lost)1858 int db_set_envelope(GList *lost)
1859 {
1860 uint64_t pmsgid;
1861 uint64_t *id;
1862 DbmailMessage *msg;
1863 Mempool_T pool;
1864 if (! lost)
1865 return DM_SUCCESS;
1866
1867 pool = mempool_open();
1868 lost = g_list_first(lost);
1869 while (lost) {
1870 id = (uint64_t *)lost->data;
1871 pmsgid = *id;
1872
1873 msg = dbmail_message_new(pool);
1874 if (! msg) {
1875 mempool_close(&pool);
1876 return DM_EQUERY;
1877 }
1878
1879 if (! (msg = dbmail_message_retrieve(msg, pmsgid))) {
1880 TRACE(TRACE_WARNING,"error retrieving physmessage: [%" PRIu64 "]", pmsgid);
1881 fprintf(stderr,"E");
1882 } else {
1883 dbmail_message_cache_envelope(msg);
1884 fprintf(stderr,".");
1885 }
1886 dbmail_message_free(msg);
1887 if (! g_list_next(lost)) break;
1888 lost = g_list_next(lost);
1889 }
1890
1891 mempool_close(&pool);
1892 return DM_SUCCESS;
1893 }
1894
1895
db_icheck_envelope(GList ** lost)1896 int db_icheck_envelope(GList **lost)
1897 {
1898 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
1899 uint64_t *id;
1900
1901 c = db_con_get();
1902 TRY
1903 r = db_query(c, "SELECT p.id FROM %sphysmessage p LEFT JOIN %senvelope e "
1904 "ON p.id = e.physmessage_id WHERE e.physmessage_id IS NULL", DBPFX, DBPFX);
1905 while (db_result_next(r)) {
1906 id = g_new0(uint64_t,1);
1907 *id = db_result_get_u64(r, 0);
1908 *(GList **)lost = g_list_prepend(*(GList **)lost,id);
1909 }
1910 CATCH(SQLException)
1911 LOG_SQLERROR;
1912 t = DM_EQUERY;
1913 FINALLY
1914 db_con_close(c);
1915 END_TRY;
1916
1917 return t;
1918 }
1919
1920
db_set_message_status(uint64_t message_idnr,MessageStatus_T status)1921 int db_set_message_status(uint64_t message_idnr, MessageStatus_T status)
1922 {
1923 return db_update("UPDATE %smessages SET status = %d WHERE message_idnr = %" PRIu64 "",
1924 DBPFX, status, message_idnr);
1925 }
1926
db_delete_message(uint64_t message_idnr)1927 int db_delete_message(uint64_t message_idnr)
1928 {
1929 return db_update("DELETE FROM %smessages WHERE message_idnr = %" PRIu64 "",
1930 DBPFX, message_idnr);
1931 }
1932
mailbox_delete(uint64_t mailbox_idnr)1933 static int mailbox_delete(uint64_t mailbox_idnr)
1934 {
1935 return db_update("DELETE FROM %smailboxes WHERE mailbox_idnr = %" PRIu64 "",
1936 DBPFX, mailbox_idnr);
1937 }
1938
mailbox_empty(uint64_t mailbox_idnr)1939 static int mailbox_empty(uint64_t mailbox_idnr)
1940 {
1941 return db_update("DELETE FROM %smessages WHERE mailbox_idnr = %" PRIu64 "",
1942 DBPFX, mailbox_idnr);
1943 }
1944
1945 /** get the total size of messages in a mailbox. Does not work recursively! */
db_get_mailbox_size(uint64_t mailbox_idnr,int only_deleted,uint64_t * mailbox_size)1946 int db_get_mailbox_size(uint64_t mailbox_idnr, int only_deleted, uint64_t * mailbox_size)
1947 {
1948 PreparedStatement_T stmt;
1949 Connection_T c;
1950 ResultSet_T r = NULL;
1951 volatile int t = DM_SUCCESS;
1952 assert(mailbox_size != NULL);
1953 *mailbox_size = 0;
1954
1955 c = db_con_get();
1956 TRY
1957 stmt = db_stmt_prepare(c,
1958 "SELECT COALESCE(SUM(pm.messagesize),0) FROM %smessages msg, %sphysmessage pm "
1959 "WHERE msg.physmessage_id = pm.id AND msg.mailbox_idnr = ? "
1960 "AND msg.status < %d %s", DBPFX,DBPFX, MESSAGE_STATUS_DELETE,
1961 only_deleted?"AND msg.deleted_flag = 1":"");
1962 db_stmt_set_u64(stmt, 1, mailbox_idnr);
1963 r = db_stmt_query(stmt);
1964
1965 if (db_result_next(r))
1966 *mailbox_size = db_result_get_u64(r, 0);
1967 CATCH(SQLException)
1968 ; // pass
1969 FINALLY
1970 db_con_close(c);
1971 END_TRY;
1972
1973 return t;
1974 }
1975
db_delete_mailbox(uint64_t mailbox_idnr,int only_empty,int update_curmail_size)1976 int db_delete_mailbox(uint64_t mailbox_idnr, int only_empty, int update_curmail_size)
1977 {
1978 uint64_t user_idnr = 0;
1979 int result;
1980 uint64_t mailbox_size = 0;
1981
1982 TRACE(TRACE_DEBUG,"mailbox_idnr [%" PRIu64 "] only_empty [%d] update_curmail_size [%d]", mailbox_idnr, only_empty, update_curmail_size);
1983 /* get the user_idnr of the owner of the mailbox */
1984 result = db_get_mailbox_owner(mailbox_idnr, &user_idnr);
1985 if (result == DM_EQUERY) {
1986 TRACE(TRACE_ERR, "cannot find owner of mailbox for mailbox [%" PRIu64 "]", mailbox_idnr);
1987 return DM_EQUERY;
1988 }
1989 if (result == 0) {
1990 TRACE(TRACE_ERR, "unable to find owner of mailbox [%" PRIu64 "]", mailbox_idnr);
1991 return DM_EGENERAL;
1992 }
1993
1994 if (update_curmail_size) {
1995 if (db_get_mailbox_size(mailbox_idnr, 0, &mailbox_size) == DM_EQUERY)
1996 return DM_EQUERY;
1997 }
1998
1999 if (! mailbox_is_writable(mailbox_idnr))
2000 return DM_EGENERAL;
2001
2002 if (only_empty) {
2003 if (! mailbox_empty(mailbox_idnr))
2004 return DM_EGENERAL;
2005 } else {
2006 if (! mailbox_delete(mailbox_idnr))
2007 return DM_EGENERAL;
2008 }
2009
2010 /* calculate the new quotum */
2011 if (! update_curmail_size)
2012 return DM_SUCCESS;
2013
2014 if (! dm_quota_user_dec(user_idnr, mailbox_size))
2015 return DM_EQUERY;
2016 return DM_SUCCESS;
2017 }
2018
db_get_message_lines(uint64_t message_idnr,long lines)2019 char * db_get_message_lines(uint64_t message_idnr, long lines)
2020 {
2021 DbmailMessage *msg;
2022 String_T stream = NULL;
2023 char *raw, *out;
2024 unsigned pos = 0;
2025 uint64_t physmessage_id = 0;
2026
2027 TRACE(TRACE_DEBUG, "request for [%ld] lines", lines);
2028
2029 /* first find the physmessage_id */
2030 if (db_get_physmessage_id(message_idnr, &physmessage_id) != DM_SUCCESS)
2031 return NULL;
2032
2033 msg = dbmail_message_new(NULL);
2034 if (! (msg = dbmail_message_retrieve(msg, physmessage_id)))
2035 return NULL;
2036
2037 stream = msg->crlf;
2038
2039 if (lines >=0) {
2040 unsigned n = 0;
2041 raw = (char *)p_string_str(stream);
2042 pos = find_end_of_header(raw);
2043 while (raw[pos] && n < lines) {
2044 if (raw[pos] == '\n')
2045 n++;
2046 pos++;
2047 }
2048 }
2049
2050 if (pos > 0) {
2051 raw = g_strndup(p_string_str(stream), pos);
2052 out = get_crlf_encoded_dots(raw);
2053 g_free(raw);
2054 } else {
2055 out = get_crlf_encoded_dots(p_string_str(stream));
2056 }
2057 dbmail_message_free(msg);
2058 return out;
2059 }
2060
db_update_pop(ClientSession_T * session_ptr)2061 int db_update_pop(ClientSession_T * session_ptr)
2062 {
2063 Connection_T c; volatile int t = DM_SUCCESS;
2064 uint64_t user_idnr = 0;
2065 INIT_QUERY;
2066
2067 c = db_con_get();
2068 TRY
2069 session_ptr->messagelst = p_list_first(session_ptr->messagelst);
2070 while (session_ptr->messagelst) {
2071 /* check if they need an update in the database */
2072 struct message *msg = (struct message *)p_list_data(session_ptr->messagelst);
2073 if ((msg) && (msg->virtual_messagestatus != msg->messagestatus)) {
2074 /* use one message to get the user_idnr that goes with the messages */
2075 if (user_idnr == 0) user_idnr = db_get_useridnr(msg->realmessageid);
2076
2077 /* yes they need an update, do the query */
2078 db_exec(c, "UPDATE %smessages set status=%d WHERE message_idnr=%" PRIu64 " AND status < %d",
2079 DBPFX, msg->virtual_messagestatus, msg->realmessageid,
2080 MESSAGE_STATUS_DELETE);
2081 }
2082
2083 if (! p_list_next(session_ptr->messagelst))
2084 break;
2085
2086 session_ptr->messagelst = p_list_next(session_ptr->messagelst);
2087 }
2088 CATCH(SQLException)
2089 LOG_SQLERROR;
2090 t = DM_EQUERY;
2091 FINALLY
2092 db_con_close(c);
2093 END_TRY;
2094
2095 if (t == DM_EQUERY) return t;
2096
2097 /* because the status of some messages might have changed (for instance
2098 * to status >= MESSAGE_STATUS_DELETE, the quotum has to be
2099 * recalculated */
2100 if (user_idnr != 0) {
2101 if (dm_quota_rebuild_user(user_idnr) == -1) {
2102 TRACE(TRACE_ERR, "Could not calculate quotum used for user [%" PRIu64 "]", user_idnr);
2103 return DM_EQUERY;
2104 }
2105 }
2106 return DM_SUCCESS;
2107 }
2108
db_findmailbox_owner(const char * name,uint64_t owner_idnr,uint64_t * mailbox_idnr)2109 static int db_findmailbox_owner(const char *name, uint64_t owner_idnr,
2110 uint64_t * mailbox_idnr)
2111 {
2112 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
2113 struct mailbox_match *mailbox_like = NULL;
2114 PreparedStatement_T stmt;
2115 int p;
2116 GString *qs;
2117
2118 assert(mailbox_idnr);
2119 *mailbox_idnr = 0;
2120
2121 c = db_con_get();
2122
2123 mailbox_like = mailbox_match_new(name);
2124 qs = g_string_new("");
2125 g_string_printf(qs, "SELECT mailbox_idnr FROM %smailboxes WHERE owner_idnr = ?", DBPFX);
2126
2127 if (mailbox_like->sensitive)
2128 g_string_append_printf(qs, " AND name %s ?", db_get_sql(SQL_SENSITIVE_LIKE));
2129 if (mailbox_like->insensitive)
2130 g_string_append_printf(qs, " AND name %s ?", db_get_sql(SQL_INSENSITIVE_LIKE));
2131
2132 p=1;
2133 TRY
2134 stmt = db_stmt_prepare(c, qs->str);
2135 db_stmt_set_u64(stmt, p++, owner_idnr);
2136 if (mailbox_like->sensitive)
2137 db_stmt_set_str(stmt, p++, mailbox_like->sensitive);
2138 if (mailbox_like->insensitive)
2139 db_stmt_set_str(stmt, p++, mailbox_like->insensitive);
2140
2141 r = db_stmt_query(stmt);
2142 if (db_result_next(r))
2143 *mailbox_idnr = db_result_get_u64(r, 0);
2144
2145 CATCH(SQLException)
2146 LOG_SQLERROR;
2147 t = DM_EQUERY;
2148 FINALLY
2149 db_con_close(c);
2150 END_TRY;
2151
2152 g_string_free(qs, TRUE);
2153 mailbox_match_free(mailbox_like);
2154
2155 if (t == DM_EQUERY) return FALSE;
2156 if (*mailbox_idnr == 0) return FALSE;
2157
2158 return TRUE;
2159 }
2160
2161
db_findmailbox(const char * fq_name,uint64_t owner_idnr,uint64_t * mailbox_idnr)2162 int db_findmailbox(const char *fq_name, uint64_t owner_idnr, uint64_t * mailbox_idnr)
2163 {
2164 char *mbox, *namespace, *username;
2165 char *simple_name;
2166 int i, result;
2167 size_t l;
2168
2169 assert(mailbox_idnr != NULL);
2170 *mailbox_idnr = 0;
2171
2172 mbox = g_strdup(fq_name);
2173
2174 /* remove trailing '/' if present */
2175 l = strlen(mbox);
2176 while (--l > 0 && mbox[l] == '/')
2177 mbox[l] = '\0';
2178
2179 /* remove leading '/' if present */
2180 for (i = 0; mbox[i] && mbox[i] == '/'; i++);
2181 memmove(&mbox[0], &mbox[i], (strlen(mbox) - i) * sizeof(char));
2182
2183 TRACE(TRACE_DEBUG, "looking for mailbox with FQN [%s].", mbox);
2184
2185 simple_name = mailbox_remove_namespace(mbox, &namespace, &username);
2186
2187 if (!simple_name) {
2188 g_free(mbox);
2189 TRACE(TRACE_NOTICE, "Could not remove mailbox namespace.");
2190 return FALSE;
2191 }
2192
2193 if (username) {
2194 TRACE(TRACE_DEBUG, "finding user with name [%s].", username);
2195 if (! auth_user_exists(username, &owner_idnr)) {
2196 TRACE(TRACE_INFO, "user [%s] not found.", username);
2197 g_free(mbox);
2198 g_free(username);
2199 return FALSE;
2200 }
2201 }
2202
2203 if (! (result = db_findmailbox_owner(simple_name, owner_idnr, mailbox_idnr)))
2204 TRACE(TRACE_INFO, "no mailbox [%s] for owner [%" PRIu64 "]", simple_name, owner_idnr);
2205
2206 g_free(mbox);
2207 g_free(username);
2208 return result;
2209 }
2210
mailboxes_by_regex(uint64_t user_idnr,int only_subscribed,const char * pattern,GList ** mailboxes)2211 static int mailboxes_by_regex(uint64_t user_idnr, int only_subscribed, const char * pattern, GList ** mailboxes)
2212 {
2213 Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
2214 uint64_t search_user_idnr = user_idnr;
2215 char *spattern;
2216 char *namespace, *username;
2217 GString *qs = NULL;
2218 volatile int n_rows = 0;
2219 PreparedStatement_T stmt;
2220 int prml;
2221
2222 assert(mailboxes != NULL);
2223 *mailboxes = NULL;
2224
2225 /* If the pattern begins with a #Users or #Public, pull that off and
2226 * find the new user_idnr whose mailboxes we're searching in. */
2227 spattern = mailbox_remove_namespace((char *)pattern, &namespace, &username);
2228 if (!spattern) {
2229 TRACE(TRACE_NOTICE, "invalid mailbox search pattern [%s]", pattern);
2230 g_free(username);
2231 return DM_SUCCESS;
2232 }
2233 if (username) {
2234 /* Replace the value of search_user_idnr with the namespace user. */
2235 if (! auth_user_exists(username, &search_user_idnr)) {
2236 TRACE(TRACE_NOTICE, "cannot search namespace because user [%s] does not exist", username);
2237 g_free(username);
2238 return DM_SUCCESS;
2239 }
2240 TRACE(TRACE_DEBUG, "searching namespace [%s] for user [%s] with pattern [%s]",
2241 namespace, username, spattern);
2242 g_free(username);
2243 }
2244
2245 c = db_con_get();
2246 TRY
2247 /* If there's neither % nor *, don't match on mailbox name. */
2248 struct mailbox_match *mailbox_like = NULL;
2249 if ( (! strchr(spattern, '%')) && (! strchr(spattern,'*')) )
2250 mailbox_like = mailbox_match_new(spattern);
2251
2252 qs = g_string_new("");
2253 g_string_printf(qs,
2254 "SELECT distinct(mbx.name), mbx.mailbox_idnr, mbx.owner_idnr "
2255 "FROM %smailboxes mbx "
2256 "LEFT JOIN %sacl acl ON mbx.mailbox_idnr = acl.mailbox_id "
2257 "LEFT JOIN %susers usr ON acl.user_id = usr.user_idnr ",
2258 DBPFX, DBPFX, DBPFX);
2259
2260 if (only_subscribed)
2261 g_string_append_printf(qs,
2262 "LEFT JOIN %ssubscription sub ON sub.mailbox_id = mbx.mailbox_idnr "
2263 "WHERE ( sub.user_id=? ) ", DBPFX);
2264 else
2265 g_string_append_printf(qs,
2266 "WHERE 1=1 ");
2267
2268 g_string_append_printf(qs,
2269 "AND ((mbx.owner_idnr=?) "
2270 "%s (acl.user_id=? AND acl.lookup_flag=1) "
2271 "OR (usr.userid=? AND acl.lookup_flag=1)) ",
2272 search_user_idnr==user_idnr?"OR":"AND"
2273 );
2274
2275 if (mailbox_like && mailbox_like->insensitive)
2276 g_string_append_printf(qs, " AND mbx.name %s ? ", db_get_sql(SQL_INSENSITIVE_LIKE));
2277 if (mailbox_like && mailbox_like->sensitive)
2278 g_string_append_printf(qs, " AND mbx.name %s ? ", db_get_sql(SQL_SENSITIVE_LIKE));
2279
2280 stmt = db_stmt_prepare(c, qs->str);
2281 prml = 1;
2282
2283 if (only_subscribed)
2284 db_stmt_set_u64(stmt, prml++, user_idnr);
2285
2286 db_stmt_set_u64(stmt, prml++, search_user_idnr);
2287 db_stmt_set_u64(stmt, prml++, user_idnr);
2288 db_stmt_set_str(stmt, prml++, DBMAIL_ACL_ANYONE_USER);
2289
2290 if (mailbox_like && mailbox_like->insensitive)
2291 db_stmt_set_str(stmt, prml++, mailbox_like->insensitive);
2292 if (mailbox_like && mailbox_like->sensitive)
2293 db_stmt_set_str(stmt, prml++, mailbox_like->sensitive);
2294
2295 r = db_stmt_query(stmt);
2296 while (db_result_next(r)) {
2297 n_rows++;
2298 char *mailbox_name;
2299 char *simple_mailbox_name = g_strdup(db_result_get(r, 0));
2300 uint64_t mailbox_idnr = db_result_get_u64(r, 1);
2301 uint64_t owner_idnr = db_result_get_u64(r,2);
2302
2303 /* add possible namespace prefix to mailbox_name */
2304 mailbox_name = mailbox_add_namespace(simple_mailbox_name, owner_idnr, user_idnr);
2305 TRACE(TRACE_DEBUG, "adding namespace prefix to [%s] got [%s]", simple_mailbox_name, mailbox_name);
2306 if (mailbox_name) {
2307 uint64_t *id = g_new0(uint64_t,1);
2308 *id = mailbox_idnr;
2309 *(GList **)mailboxes = g_list_prepend(*(GList **)mailboxes, id);
2310 }
2311 g_free(simple_mailbox_name);
2312 g_free(mailbox_name);
2313 }
2314 if (mailbox_like) mailbox_match_free(mailbox_like);
2315 CATCH(SQLException)
2316 LOG_SQLERROR;
2317 t = DM_EQUERY;
2318 FINALLY
2319 db_con_close(c);
2320 END_TRY;
2321
2322 if (qs)
2323 g_string_free(qs, TRUE);
2324
2325 if (t == DM_EQUERY) return t;
2326 if (n_rows == 0) return DM_SUCCESS;
2327
2328 *(GList **)mailboxes = g_list_reverse(*(GList **)mailboxes);
2329
2330 return DM_EGENERAL;
2331 }
2332
db_findmailbox_by_regex(uint64_t owner_idnr,const char * pattern,GList ** children,int only_subscribed)2333 int db_findmailbox_by_regex(uint64_t owner_idnr, const char *pattern, GList ** children, int only_subscribed)
2334 {
2335 *children = NULL;
2336
2337 /* list normal mailboxes */
2338 if (mailboxes_by_regex(owner_idnr, only_subscribed, pattern, children) < 0) {
2339 TRACE(TRACE_ERR, "error listing mailboxes");
2340 return DM_EQUERY;
2341 }
2342
2343 if (g_list_length(*children) == 0) {
2344 TRACE(TRACE_INFO, "did not find any mailboxes that "
2345 "match pattern. returning 0, nchildren = 0");
2346 return DM_SUCCESS;
2347 }
2348
2349 /* store matches */
2350 TRACE(TRACE_INFO, "found [%d] mailboxes for [%s]", g_list_length(*children), pattern);
2351 return DM_SUCCESS;
2352 }
2353
mailbox_is_writable(uint64_t mailbox_idnr)2354 int mailbox_is_writable(uint64_t mailbox_idnr)
2355 {
2356 int result = TRUE;
2357 MailboxState_T M = MailboxState_new(NULL, mailbox_idnr);
2358 if (MailboxState_getPermission(M) != IMAPPERM_READWRITE) {
2359 TRACE(TRACE_INFO, "read-only mailbox");
2360 result = FALSE;
2361 }
2362 MailboxState_free(&M);
2363 return result;
2364
2365 }
2366
db_imap_split_mailbox(const char * mailbox,uint64_t owner_idnr,const char ** errmsg)2367 GList * db_imap_split_mailbox(const char *mailbox, uint64_t owner_idnr, const char ** errmsg)
2368 {
2369 assert(mailbox);
2370 assert(errmsg);
2371
2372 GList *mailboxes = NULL;
2373 char *namespace, *username, *cpy, **chunks = NULL;
2374 char *simple_name;
2375 int i, is_users = 0, is_public = 0;
2376 uint64_t mboxid, public;
2377
2378 /* Scratch space as we build the mailbox names. */
2379 cpy = g_new0(char, strlen(mailbox) + 1);
2380
2381 simple_name = mailbox_remove_namespace((char *)mailbox, &namespace, &username);
2382
2383 if (username) {
2384 TRACE(TRACE_DEBUG, "finding user with name [%s].", username);
2385 if (! auth_user_exists(username, &owner_idnr)) {
2386 TRACE(TRACE_INFO, "user [%s] not found.", username);
2387 goto egeneral;
2388 }
2389 }
2390
2391 if (namespace) {
2392 if (strcasecmp(namespace, NAMESPACE_USER) == 0) {
2393 is_users = 1;
2394 } else if (strcasecmp(namespace, NAMESPACE_PUBLIC) == 0) {
2395 is_public = 1;
2396 }
2397 }
2398
2399 TRACE(TRACE_DEBUG, "Splitting mailbox [%s] simple name [%s] namespace [%s] username [%s]",
2400 mailbox, simple_name, namespace, username);
2401
2402 /* split up the name */
2403 if (! (chunks = g_strsplit(simple_name, MAILBOX_SEPARATOR, 0))) {
2404 TRACE(TRACE_ERR, "could not create chunks");
2405 *errmsg = "Server ran out of memory";
2406 goto egeneral;
2407 }
2408
2409 if (chunks[0] == NULL) {
2410 *errmsg = "Invalid mailbox name specified";
2411 goto egeneral;
2412 }
2413
2414 for (i = 0; chunks[i]; i++) {
2415
2416 /* Indicates a // in the mailbox name. */
2417 if ((! (strlen(chunks[i])) && chunks[i+1])) {
2418 *errmsg = "Invalid mailbox name specified";
2419 goto egeneral;
2420 }
2421 if (! (strlen(chunks[i]))) // trailing / in name
2422 break;
2423
2424 if (i == 0) {
2425 if (strcasecmp(chunks[0], "inbox") == 0) {
2426 /* Make inbox uppercase */
2427 strcpy(chunks[0], "INBOX");
2428 }
2429 /* The current chunk goes into the name. */
2430 strcat(cpy, chunks[0]);
2431 } else {
2432 /* The current chunk goes into the name. */
2433 strcat(cpy, MAILBOX_SEPARATOR);
2434 strcat(cpy, chunks[i]);
2435 }
2436
2437 TRACE(TRACE_DEBUG, "Preparing mailbox [%s]", cpy);
2438
2439 /* Only the PUBLIC user is allowed to own #Public itself. */
2440 if (i == 0 && is_public) {
2441 if (! auth_user_exists(PUBLIC_FOLDER_USER, &public)) {
2442 *errmsg = "Public user required for #Public folder access.";
2443 goto egeneral;
2444 }
2445 if (! db_findmailbox(cpy, public, &mboxid)) {
2446 *errmsg = "Public folder not found.";
2447 goto egeneral;
2448 }
2449
2450 } else {
2451 if (! db_findmailbox(cpy, owner_idnr, &mboxid)) {
2452 /* ignore */
2453 }
2454 }
2455
2456 /* Prepend a mailbox struct onto the list. */
2457 MailboxState_T M = MailboxState_new(NULL, mboxid);
2458 MailboxState_setName(M, cpy);
2459 MailboxState_setIsUsers(M, is_users);
2460 MailboxState_setIsPublic(M, is_public);
2461
2462 /* Only the PUBLIC user is allowed to own #Public folders. */
2463 if (is_public) {
2464 MailboxState_setOwner(M, public);
2465 } else {
2466 MailboxState_setOwner(M, owner_idnr);
2467 }
2468
2469 mailboxes = g_list_prepend(mailboxes, M);
2470 }
2471
2472 /* We built the path with prepends,
2473 * so we have to reverse it now. */
2474 mailboxes = g_list_reverse(mailboxes);
2475 *errmsg = "Everything is peachy keen";
2476
2477 g_strfreev(chunks);
2478 g_free(username);
2479 g_free(cpy);
2480
2481 return mailboxes;
2482
2483 egeneral:
2484 mailboxes = g_list_first(mailboxes);
2485 while (mailboxes) {
2486 MailboxState_T M = (MailboxState_T)mailboxes->data;
2487 MailboxState_free(&M);
2488 if (! g_list_next(mailboxes)) break;
2489 mailboxes = g_list_next(mailboxes);
2490 }
2491 g_list_free(g_list_first(mailboxes));
2492 g_strfreev(chunks);
2493 g_free(username);
2494 g_free(cpy);
2495 return NULL;
2496 }
2497
2498 /** Create a mailbox, recursively creating its parents.
2499 * \param mailbox Name of the mailbox to create
2500 * \param owner_idnr Owner of the mailbox
2501 * \param mailbox_idnr Fills the pointer with the mailbox id
2502 * \param message Returns a static pointer to the return message
2503 * \return
2504 * DM_SUCCESS Everything's good
2505 * DM_EGENERAL Cannot create mailbox
2506 * DM_EQUERY Database error
2507 */
db_mailbox_create_with_parents(const char * mailbox,mailbox_source source,uint64_t owner_idnr,uint64_t * mailbox_idnr,const char ** message)2508 int db_mailbox_create_with_parents(const char * mailbox, mailbox_source source,
2509 uint64_t owner_idnr, uint64_t * mailbox_idnr, const char * * message)
2510 {
2511 int skip_and_free = DM_SUCCESS;
2512 uint64_t created_mboxid = 0;
2513 int result;
2514 GList *mailbox_list = NULL, *mailbox_item = NULL;
2515
2516 assert(mailbox);
2517 assert(mailbox_idnr);
2518 assert(message);
2519
2520 TRACE(TRACE_INFO, "Creating mailbox [%s] source [%d] for user [%" PRIu64 "]",
2521 mailbox, source, owner_idnr);
2522
2523 /* Check if new name is valid. */
2524 if (!checkmailboxname(mailbox)) {
2525 *message = "New mailbox name contains invalid characters";
2526 TRACE(TRACE_NOTICE, "New mailbox name contains invalid characters. Aborting create.");
2527 return DM_EGENERAL;
2528 }
2529
2530 /* Check if mailbox already exists. */
2531 if (db_findmailbox(mailbox, owner_idnr, mailbox_idnr)) {
2532 *message = "Mailbox already exists";
2533 TRACE(TRACE_NOTICE, "Asked to create mailbox [%s] which already exists. Aborting create.", mailbox);
2534 return DM_EGENERAL;
2535 }
2536
2537 if ((mailbox_list = db_imap_split_mailbox(mailbox, owner_idnr, message)) == NULL) {
2538 TRACE(TRACE_ERR, "db_imap_split_mailbox returned with error");
2539 // Message pointer was set by db_imap_split_mailbox
2540 return DM_EGENERAL;
2541 }
2542
2543 if (source == BOX_BRUTEFORCE) {
2544 TRACE(TRACE_INFO, "Mailbox requested with BRUTEFORCE creation status; "
2545 "pretending that all permissions have been granted to create it.");
2546 }
2547
2548 mailbox_item = g_list_first(mailbox_list);
2549 while (mailbox_item) {
2550 MailboxState_T M = (MailboxState_T)mailbox_item->data;
2551
2552 /* Needs to be created. */
2553 if (MailboxState_getId(M) == 0) {
2554 if (MailboxState_isUsers(M) && MailboxState_getOwner(M) != owner_idnr) {
2555 *message = "Top-level mailboxes may not be created for others under #Users";
2556 skip_and_free = DM_EGENERAL;
2557 } else {
2558 uint64_t this_owner_idnr;
2559
2560 /* Only the PUBLIC user is allowed to own #Public. */
2561 if (MailboxState_isPublic(M)) {
2562 this_owner_idnr = MailboxState_getOwner(M);
2563 } else {
2564 this_owner_idnr = owner_idnr;
2565 }
2566
2567 /* Create it! */
2568 result = db_createmailbox(MailboxState_getName(M), this_owner_idnr, &created_mboxid);
2569
2570 if (result == DM_EGENERAL) {
2571 *message = "General error while creating";
2572 skip_and_free = DM_EGENERAL;
2573 } else if (result == DM_EQUERY) {
2574 *message = "Database error while creating";
2575 skip_and_free = DM_EQUERY;
2576 } else {
2577 /* Subscribe to the newly created mailbox. */
2578 if (source != BOX_IMAP) {
2579 if (! db_subscribe(created_mboxid, owner_idnr)) {
2580 *message = "General error while subscribing";
2581 skip_and_free = DM_EGENERAL;
2582 }
2583 }
2584 MailboxState_setPermission(M, IMAPPERM_READWRITE);
2585 }
2586
2587 /* If the PUBLIC user owns it, then the current user needs ACLs. */
2588 if (MailboxState_isPublic(M)) {
2589 result = acl_set_rights(owner_idnr, created_mboxid, "lrswipkxteacd");
2590 if (result == DM_EQUERY) {
2591 *message = "Database error while setting rights";
2592 skip_and_free = DM_EQUERY;
2593 }
2594 }
2595 }
2596
2597 if (!skip_and_free) {
2598 *message = "Folder created";
2599 MailboxState_setId(M, created_mboxid);
2600 }
2601 }
2602
2603 if (skip_and_free)
2604 break;
2605
2606 if (source != BOX_BRUTEFORCE) {
2607 TRACE(TRACE_DEBUG, "Checking if we have the right to "
2608 "create mailboxes under mailbox [%" PRIu64 "]", MailboxState_getId(M));
2609
2610 /* Mailbox does exist, failure if no_inferiors flag set. */
2611 result = db_noinferiors(MailboxState_getId(M));
2612 if (result == DM_EGENERAL) {
2613 *message = "Mailbox cannot have inferior names";
2614 skip_and_free = DM_EGENERAL;
2615 } else if (result == DM_EQUERY) {
2616 *message = "Internal database error while checking inferiors";
2617 skip_and_free = DM_EQUERY;
2618 }
2619
2620 /* Mailbox does exist, failure if ACLs disallow CREATE. */
2621 result = acl_has_right(M, owner_idnr, ACL_RIGHT_CREATE);
2622 if (result == 0) {
2623 *message = "Permission to create mailbox denied";
2624 skip_and_free = DM_EGENERAL;
2625 } else if (result < 0) {
2626 *message = "Internal database error while checking ACL";
2627 skip_and_free = DM_EQUERY;
2628 }
2629 }
2630
2631 if (skip_and_free) break;
2632
2633 if (! g_list_next(mailbox_item)) break;
2634 mailbox_item = g_list_next(mailbox_item);
2635 }
2636
2637 mailbox_item = g_list_first(mailbox_list);
2638 while (mailbox_item) {
2639 MailboxState_T M = (MailboxState_T)mailbox_item->data;
2640 MailboxState_free(&M);
2641 if (! g_list_next(mailbox_item)) break;
2642 mailbox_item = g_list_next(mailbox_item);
2643 }
2644 g_list_free(g_list_first(mailbox_list));
2645
2646 *mailbox_idnr = created_mboxid;
2647 return skip_and_free;
2648 }
2649
db_createmailbox(const char * name,uint64_t owner_idnr,uint64_t * mailbox_idnr)2650 int db_createmailbox(const char * name, uint64_t owner_idnr, uint64_t * mailbox_idnr)
2651 {
2652 char *simple_name;
2653 char *frag;
2654 assert(mailbox_idnr != NULL);
2655 *mailbox_idnr = 0;
2656 volatile int result = DM_SUCCESS;
2657 Connection_T c; ResultSet_T r; PreparedStatement_T s;
2658 INIT_QUERY;
2659
2660 if (auth_requires_shadow_user()) {
2661 TRACE(TRACE_DEBUG, "creating shadow user for [%" PRIu64 "]",
2662 owner_idnr);
2663 if ((db_user_find_create(owner_idnr) < 0)) {
2664 TRACE(TRACE_ERR, "unable to find or create sql shadow account for useridnr [%" PRIu64 "]",
2665 owner_idnr);
2666 return DM_EQUERY;
2667 }
2668 }
2669
2670 /* remove namespace information from mailbox name */
2671 if (!(simple_name = mailbox_remove_namespace((char *)name, NULL, NULL))) {
2672 TRACE(TRACE_NOTICE, "Could not remove mailbox namespace.");
2673 return DM_EGENERAL;
2674 }
2675
2676 frag = db_returning("mailbox_idnr");
2677 snprintf(query, DEF_QUERYSIZE-1,
2678 "INSERT INTO %smailboxes (name,owner_idnr,permission,seq)"
2679 " VALUES (?, ?, %d, 1) %s", DBPFX,
2680 IMAPPERM_READWRITE, frag);
2681 g_free(frag);
2682
2683 c = db_con_get();
2684 TRY
2685 db_begin_transaction(c);
2686 s = db_stmt_prepare(c,query);
2687 db_stmt_set_str(s,1,simple_name);
2688 db_stmt_set_u64(s,2,owner_idnr);
2689
2690 if (db_params.db_driver == DM_DRIVER_ORACLE) {
2691 db_stmt_exec(s);
2692 *mailbox_idnr = db_get_pk(c, "mailboxes");
2693 } else {
2694 r = db_stmt_query(s);
2695 *mailbox_idnr = db_insert_result(c, r);
2696 }
2697 db_commit_transaction(c);
2698 TRACE(TRACE_DEBUG, "created mailbox with idnr [%" PRIu64 "] for user [%" PRIu64 "]",
2699 *mailbox_idnr, owner_idnr);
2700 CATCH(SQLException)
2701 LOG_SQLERROR;
2702 db_rollback_transaction(c);
2703 result = DM_EQUERY;
2704 FINALLY
2705 db_con_close(c);
2706 END_TRY;
2707
2708 return result;
2709 }
2710
2711
db_mailbox_set_permission(uint64_t mailbox_id,int permission)2712 int db_mailbox_set_permission(uint64_t mailbox_id, int permission)
2713 {
2714 return db_update("UPDATE %smailboxes SET permission=%d WHERE mailbox_idnr=%" PRIu64 "",
2715 DBPFX, permission, mailbox_id);
2716 }
2717
2718
2719 /* Called from:
2720 * dm_message.c (dbmail_message_store -> _message_insert) (always INBOX)
2721 * modules/authldap.c (creates shadow INBOX) (always INBOX)
2722 * sort.c (delivers to a mailbox) (performs own ACL checking)
2723 *
2724 * Ok, this can very possibly return mailboxes owned by someone else;
2725 * so the caller must be wary to perform additional ACL checking.
2726 * Why? Sieve script:
2727 * fileinto "#Users/joeschmoe/INBOX";
2728 * Simple as that.
2729 */
db_find_create_mailbox(const char * name,mailbox_source source,uint64_t owner_idnr,uint64_t * mailbox_idnr)2730 int db_find_create_mailbox(const char *name, mailbox_source source,
2731 uint64_t owner_idnr, uint64_t * mailbox_idnr)
2732 {
2733 uint64_t mboxidnr;
2734 const char *message;
2735
2736 assert(mailbox_idnr != NULL);
2737 *mailbox_idnr = 0;
2738
2739 /* Did we fail to find the mailbox? */
2740 if (! db_findmailbox(name, owner_idnr, &mboxidnr)) {
2741 /* Who specified this mailbox? */
2742 if (source == BOX_COMMANDLINE
2743 || source == BOX_BRUTEFORCE
2744 || source == BOX_SORTING
2745 || source == BOX_DEFAULT) {
2746 /* Did we fail to create the mailbox? */
2747 if (db_mailbox_create_with_parents(name, source, owner_idnr, &mboxidnr, &message) != DM_SUCCESS) {
2748 TRACE(TRACE_ERR, "could not create mailbox [%s] because [%s]",
2749 name, message);
2750 return DM_EQUERY;
2751 }
2752 TRACE(TRACE_DEBUG, "mailbox [%s] created on the fly",
2753 name);
2754 // Subscription now occurs in db_mailbox_create_with_parents
2755 } else {
2756 /* The mailbox was specified by an untrusted
2757 * source, such as the address part, and will
2758 * not be autocreated. */
2759 return db_find_create_mailbox("INBOX", BOX_DEFAULT,
2760 owner_idnr, mailbox_idnr);
2761 }
2762
2763 }
2764 TRACE(TRACE_DEBUG, "mailbox [%s] found", name);
2765
2766 *mailbox_idnr = mboxidnr;
2767 return DM_SUCCESS;
2768 }
2769
2770
db_listmailboxchildren(uint64_t mailbox_idnr,uint64_t user_idnr,GList ** children)2771 int db_listmailboxchildren(uint64_t mailbox_idnr, uint64_t user_idnr, GList ** children)
2772 {
2773 char pattern[IMAP_MAX_MAILBOX_NAMELEN+5];
2774 Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = DM_SUCCESS;
2775 GString *qs;
2776 int prml;
2777
2778 *children = NULL;
2779
2780 memset(&pattern, 0, sizeof(pattern));
2781 /* retrieve the name of this mailbox */
2782 c = db_con_get();
2783 TRY
2784 r = db_query(c, "SELECT name FROM %smailboxes WHERE mailbox_idnr=%" PRIu64 " AND owner_idnr=%" PRIu64 "",
2785 DBPFX, mailbox_idnr, user_idnr);
2786 if (db_result_next(r))
2787 snprintf(pattern, sizeof(pattern)-1, "%s/%%", db_result_get(r,0));
2788 CATCH(SQLException)
2789 LOG_SQLERROR;
2790 t = DM_EQUERY;
2791 FINALLY
2792 db_con_clear(c);
2793 END_TRY;
2794
2795 if (t == DM_EQUERY) {
2796 db_con_close(c);
2797 return t;
2798 }
2799
2800 t = DM_SUCCESS;
2801 qs = g_string_new("");
2802 g_string_printf(qs, "SELECT mailbox_idnr FROM %smailboxes WHERE owner_idnr = ? ", DBPFX);
2803
2804
2805 TRY
2806 struct mailbox_match *mailbox_like = NULL;
2807 if (pattern[0])
2808 mailbox_like = mailbox_match_new(pattern);
2809 if (mailbox_like && mailbox_like->insensitive)
2810 g_string_append_printf(qs, " AND name %s ? ", db_get_sql(SQL_INSENSITIVE_LIKE));
2811 if (mailbox_like && mailbox_like->sensitive)
2812 g_string_append_printf(qs, " AND name %s ? ", db_get_sql(SQL_SENSITIVE_LIKE));
2813
2814 s = db_stmt_prepare(c, qs->str);
2815 prml = 1;
2816 db_stmt_set_u64(s, prml++, user_idnr);
2817 if (mailbox_like && mailbox_like->insensitive)
2818 db_stmt_set_str(s, prml++, mailbox_like->insensitive);
2819 if (mailbox_like && mailbox_like->sensitive)
2820 db_stmt_set_str(s, prml++, mailbox_like->sensitive);
2821
2822 /* now find the children */
2823 r = db_stmt_query(s);
2824 while (db_result_next(r)) {
2825 uint64_t *id = g_new0(uint64_t,1);
2826 *id = db_result_get_u64(r,0);
2827 *(GList **)children = g_list_prepend(*(GList **)children, id);
2828 }
2829 if (mailbox_like) mailbox_match_free(mailbox_like);
2830
2831 CATCH(SQLException)
2832 LOG_SQLERROR;
2833 t = DM_EQUERY;
2834 FINALLY
2835 db_con_close(c);
2836 END_TRY;
2837
2838 g_string_free(qs, TRUE);
2839 return t;
2840 }
2841
db_isselectable(uint64_t mailbox_idnr)2842 int db_isselectable(uint64_t mailbox_idnr)
2843 {
2844 Connection_T c; ResultSet_T r; volatile int t = FALSE;
2845
2846 c = db_con_get();
2847 TRY
2848 r = db_query(c, "SELECT no_select FROM %smailboxes WHERE mailbox_idnr = %" PRIu64 "",
2849 DBPFX, mailbox_idnr);
2850 if (db_result_next(r))
2851 t = db_result_get_bool(r, 0);
2852 CATCH(SQLException)
2853 LOG_SQLERROR;
2854 t = DM_EQUERY;
2855 FINALLY
2856 db_con_close(c);
2857 END_TRY;
2858
2859 if (t == DM_EQUERY) return t;
2860
2861 return t ? FALSE : TRUE;
2862 }
2863
db_noinferiors(uint64_t mailbox_idnr)2864 int db_noinferiors(uint64_t mailbox_idnr)
2865 {
2866 Connection_T c; ResultSet_T r; volatile int t = FALSE;
2867
2868 c = db_con_get();
2869 TRY
2870
2871 r = db_query(c, "SELECT no_inferiors FROM %smailboxes WHERE mailbox_idnr=%" PRIu64 "",
2872 DBPFX, mailbox_idnr);
2873 if (db_result_next(r))
2874 t = db_result_get_bool(r, 0);
2875 CATCH(SQLException)
2876 LOG_SQLERROR;
2877 t = DM_EQUERY;
2878 FINALLY
2879 db_con_close(c);
2880 END_TRY;
2881
2882 return t;
2883 }
2884
db_movemsg(uint64_t mailbox_to,uint64_t mailbox_from)2885 int db_movemsg(uint64_t mailbox_to, uint64_t mailbox_from)
2886 {
2887 Connection_T c; volatile long long int count = 0;
2888 c = db_con_get();
2889 TRY
2890 db_begin_transaction(c);
2891 db_exec(c, "UPDATE %smessages SET mailbox_idnr=%" PRIu64 " WHERE mailbox_idnr=%" PRIu64 "",
2892 DBPFX, mailbox_to, mailbox_from);
2893 count = Connection_rowsChanged(c);
2894 db_commit_transaction(c);
2895 CATCH(SQLException)
2896 LOG_SQLERROR;
2897 count = DM_EQUERY;
2898 FINALLY
2899 db_con_close(c);
2900 END_TRY;
2901
2902 if (count == DM_EQUERY) return count;
2903
2904 if (count > 0) {
2905 db_mailbox_seq_update(mailbox_to, 0);
2906 db_mailbox_seq_update(mailbox_from, 0);
2907 }
2908
2909 return DM_SUCCESS; /* success */
2910 }
2911
2912 #define EXPIRE_DAYS 3
db_mailbox_has_message_id(uint64_t mailbox_idnr,const char * messageid)2913 int db_mailbox_has_message_id(uint64_t mailbox_idnr, const char *messageid)
2914 {
2915 volatile int rows = 0;
2916 Connection_T c; ResultSet_T r; PreparedStatement_T s;
2917 char expire[DEF_FRAGSIZE], partial[DEF_FRAGSIZE];
2918 INIT_QUERY;
2919
2920 memset(expire,0,sizeof(expire));
2921 memset(partial,0,sizeof(partial));
2922
2923 g_return_val_if_fail(messageid!=NULL,0);
2924
2925 snprintf(expire, DEF_FRAGSIZE-1, db_get_sql(SQL_EXPIRE), EXPIRE_DAYS);
2926 snprintf(partial, DEF_FRAGSIZE-1, db_get_sql(SQL_PARTIAL), "v.headervalue");
2927 snprintf(query, DEF_QUERYSIZE-1,
2928 "SELECT m.message_idnr "
2929 "FROM %smessages m "
2930 "LEFT JOIN %sphysmessage p ON m.physmessage_id=p.id "
2931 "LEFT JOIN %sheader h ON p.id=h.physmessage_id "
2932 "LEFT JOIN %sheadername n ON h.headername_id=n.id "
2933 "LEFT JOIN %sheadervalue v ON h.headervalue_id=v.id "
2934 "WHERE m.mailbox_idnr=? "
2935 "AND m.status < %d "
2936 "AND n.headername IN ('resent-message-id','message-id') "
2937 "AND %s=? "
2938 "AND p.internal_date > %s", DBPFX, DBPFX, DBPFX, DBPFX, DBPFX,
2939 MESSAGE_STATUS_DELETE, partial, expire);
2940
2941 c = db_con_get();
2942 TRY
2943 s = db_stmt_prepare(c, query);
2944 db_stmt_set_u64(s, 1, mailbox_idnr);
2945 db_stmt_set_str(s, 2, messageid);
2946
2947 r = db_stmt_query(s);
2948 while (db_result_next(r))
2949 rows++;
2950 CATCH(SQLException)
2951 LOG_SQLERROR;
2952 FINALLY
2953 db_con_close(c);
2954 END_TRY;
2955
2956 return rows;
2957 }
2958
message_get_size(uint64_t message_idnr)2959 static uint64_t message_get_size(uint64_t message_idnr)
2960 {
2961 Connection_T c; ResultSet_T r;
2962 volatile uint64_t size = 0;
2963
2964 c = db_con_get();
2965 TRY
2966 r = db_query(c, "SELECT pm.messagesize FROM %sphysmessage pm, %smessages msg "
2967 "WHERE pm.id = msg.physmessage_id "
2968 "AND message_idnr = %" PRIu64 "",DBPFX,DBPFX, message_idnr);
2969 if (db_result_next(r))
2970 size = db_result_get_u64(r, 0);
2971 CATCH(SQLException)
2972 LOG_SQLERROR;
2973 FINALLY
2974 db_con_close(c);
2975 END_TRY;
2976
2977 return size;
2978 }
2979
db_copymsg(uint64_t msg_idnr,uint64_t mailbox_to,uint64_t user_idnr,uint64_t * newmsg_idnr,gboolean recent)2980 int db_copymsg(uint64_t msg_idnr, uint64_t mailbox_to, uint64_t user_idnr,
2981 uint64_t * newmsg_idnr, gboolean recent)
2982 {
2983 Connection_T c; ResultSet_T r;
2984 uint64_t msgsize;
2985 char *frag;
2986 int valid=FALSE;
2987 char unique_id[UID_SIZE];
2988
2989 /* Get the size of the message to be copied. */
2990 if (! (msgsize = message_get_size(msg_idnr))) {
2991 TRACE(TRACE_ERR, "error getting size for message [%" PRIu64 "]", msg_idnr);
2992 return DM_EQUERY;
2993 }
2994
2995 /* Check to see if the user has room for the message. */
2996 if ((valid = dm_quota_user_validate(user_idnr, msgsize)) == DM_EQUERY)
2997 return DM_EQUERY;
2998
2999 if (! valid) {
3000 TRACE(TRACE_INFO, "user [%" PRIu64 "] would exceed quotum", user_idnr);
3001 return -2;
3002 }
3003
3004 /* Copy the message table entry of the message. */
3005 frag = db_returning("message_idnr");
3006 memset(unique_id,0,sizeof(unique_id));
3007
3008 c = db_con_get();
3009 TRY
3010 db_begin_transaction(c);
3011 create_unique_id(unique_id, msg_idnr);
3012 if (db_params.db_driver == DM_DRIVER_ORACLE) {
3013 db_exec(c, "INSERT INTO %smessages ("
3014 "mailbox_idnr,physmessage_id,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag,unique_id,status)"
3015 " SELECT %" PRIu64 ",physmessage_id,seen_flag,answered_flag,deleted_flag,flagged_flag,%d,draft_flag,'%s',status"
3016 " FROM %smessages WHERE message_idnr = %" PRIu64 " %s",DBPFX, mailbox_to, recent, unique_id, DBPFX, msg_idnr, frag);
3017 *newmsg_idnr = db_get_pk(c, "messages");
3018 } else {
3019 r = db_query(c, "INSERT INTO %smessages ("
3020 "mailbox_idnr,physmessage_id,seen_flag,answered_flag,deleted_flag,flagged_flag,recent_flag,draft_flag,unique_id,status)"
3021 " SELECT %" PRIu64 ",physmessage_id,seen_flag,answered_flag,deleted_flag,flagged_flag,%d,draft_flag,'%s',status"
3022 " FROM %smessages WHERE message_idnr = %" PRIu64 " %s",DBPFX, mailbox_to, recent, unique_id, DBPFX, msg_idnr, frag);
3023 *newmsg_idnr = db_insert_result(c, r);
3024 }
3025 db_commit_transaction(c);
3026 CATCH(SQLException)
3027 LOG_SQLERROR;
3028 db_rollback_transaction(c);
3029 FINALLY
3030 db_con_close(c);
3031 END_TRY;
3032
3033 g_free(frag);
3034
3035 /* Copy the message keywords */
3036 c = db_con_get();
3037 TRY
3038 db_begin_transaction(c);
3039 db_exec(c, "INSERT INTO %skeywords (message_idnr, keyword) "
3040 "SELECT %" PRIu64 ",keyword from %skeywords WHERE message_idnr=%" PRIu64 "",
3041 DBPFX, *newmsg_idnr, DBPFX, msg_idnr);
3042 db_commit_transaction(c);
3043 CATCH(SQLException)
3044 LOG_SQLERROR;
3045 db_rollback_transaction(c);
3046 FINALLY
3047 db_con_close(c);
3048 END_TRY;
3049
3050 db_mailbox_seq_update(mailbox_to, *newmsg_idnr);
3051
3052 /* update quotum */
3053 if (! dm_quota_user_inc(user_idnr, msgsize))
3054 return DM_EQUERY;
3055
3056 return DM_EGENERAL;
3057 }
3058
db_getmailboxname(uint64_t mailbox_idnr,uint64_t user_idnr,char * name)3059 int db_getmailboxname(uint64_t mailbox_idnr, uint64_t user_idnr, char *name)
3060 {
3061 Connection_T c; ResultSet_T r;
3062 char *tmp_name = NULL, *tmp_fq_name;
3063 int result;
3064 size_t tmp_fq_name_len;
3065 uint64_t owner_idnr;
3066 INIT_QUERY;
3067
3068 result = db_get_mailbox_owner(mailbox_idnr, &owner_idnr);
3069 if (result <= 0) {
3070 TRACE(TRACE_ERR, "error checking ownership of mailbox");
3071 return DM_EQUERY;
3072 }
3073
3074 c = db_con_get();
3075 TRY
3076 r = db_query(c, "SELECT name FROM %smailboxes WHERE mailbox_idnr=%" PRIu64 "",
3077 DBPFX, mailbox_idnr);
3078 if (db_result_next(r))
3079 tmp_name = g_strdup(db_result_get(r, 0));
3080 CATCH(SQLException)
3081 LOG_SQLERROR;
3082 FINALLY
3083 db_con_close(c);
3084 END_TRY;
3085
3086 tmp_fq_name = mailbox_add_namespace(tmp_name, owner_idnr, user_idnr);
3087 g_free(tmp_name);
3088
3089 if (!tmp_fq_name) {
3090 TRACE(TRACE_ERR, "error getting fully qualified mailbox name");
3091 return DM_EQUERY;
3092 }
3093 tmp_fq_name_len = strlen(tmp_fq_name);
3094 if (tmp_fq_name_len >= IMAP_MAX_MAILBOX_NAMELEN)
3095 tmp_fq_name_len = IMAP_MAX_MAILBOX_NAMELEN - 1;
3096 strncpy(name, tmp_fq_name, tmp_fq_name_len);
3097 name[tmp_fq_name_len] = '\0';
3098 g_free(tmp_fq_name);
3099 return DM_SUCCESS;
3100 }
3101
db_setmailboxname(uint64_t mailbox_idnr,const char * name)3102 int db_setmailboxname(uint64_t mailbox_idnr, const char *name)
3103 {
3104 Connection_T c; PreparedStatement_T s; volatile int t = DM_SUCCESS;
3105
3106 c = db_con_get();
3107 TRY
3108 s = db_stmt_prepare(c, "UPDATE %smailboxes SET name = ? WHERE mailbox_idnr = ?", DBPFX);
3109 db_stmt_set_str(s,1,name);
3110 db_stmt_set_u64(s,2,mailbox_idnr);
3111 db_stmt_exec(s);
3112 CATCH(SQLException)
3113 LOG_SQLERROR;
3114 t = DM_EQUERY;
3115 FINALLY
3116 db_con_close(c);
3117 END_TRY;
3118
3119 return t;
3120 }
3121
3122
db_subscribe(uint64_t mailbox_idnr,uint64_t user_idnr)3123 int db_subscribe(uint64_t mailbox_idnr, uint64_t user_idnr)
3124 {
3125 Connection_T c; PreparedStatement_T s; ResultSet_T r; volatile int t = TRUE;
3126 c = db_con_get();
3127 TRY
3128 db_begin_transaction(c);
3129 s = db_stmt_prepare(c, "SELECT * FROM %ssubscription WHERE user_id=? and mailbox_id=?", DBPFX);
3130 db_stmt_set_u64(s,1,user_idnr);
3131 db_stmt_set_u64(s,2,mailbox_idnr);
3132 r = db_stmt_query(s);
3133 if (! db_result_next(r)) {
3134 s = db_stmt_prepare(c, "INSERT INTO %ssubscription (user_id, mailbox_id) VALUES (?, ?)", DBPFX);
3135 db_stmt_set_u64(s,1,user_idnr);
3136 db_stmt_set_u64(s,2,mailbox_idnr);
3137 db_stmt_exec(s);
3138 t = TRUE;
3139 }
3140 db_commit_transaction(c);
3141 CATCH(SQLException)
3142 LOG_SQLERROR;
3143 db_rollback_transaction(c);
3144 t = DM_EQUERY;
3145 FINALLY
3146 db_con_close(c);
3147 END_TRY;
3148
3149 return t;
3150 }
3151
db_unsubscribe(uint64_t mailbox_idnr,uint64_t user_idnr)3152 int db_unsubscribe(uint64_t mailbox_idnr, uint64_t user_idnr)
3153 {
3154 return db_update("DELETE FROM %ssubscription WHERE user_id=%" PRIu64 " AND mailbox_id=%" PRIu64 "", DBPFX, user_idnr, mailbox_idnr);
3155 }
3156
db_get_msgflag(const char * flag_name,uint64_t msg_idnr)3157 int db_get_msgflag(const char *flag_name, uint64_t msg_idnr)
3158 {
3159 Connection_T c; ResultSet_T r;
3160 char the_flag_name[DEF_FRAGSIZE]; /* should be sufficient ;) */
3161 int val = 0;
3162
3163 memset(the_flag_name, 0, sizeof(the_flag_name));
3164 /* determine flag */
3165 if (strcasecmp(flag_name, "seen") == 0)
3166 snprintf(the_flag_name, DEF_FRAGSIZE-1, "seen_flag");
3167 else if (strcasecmp(flag_name, "deleted") == 0)
3168 snprintf(the_flag_name, DEF_FRAGSIZE-1, "deleted_flag");
3169 else if (strcasecmp(flag_name, "answered") == 0)
3170 snprintf(the_flag_name, DEF_FRAGSIZE-1, "answered_flag");
3171 else if (strcasecmp(flag_name, "flagged") == 0)
3172 snprintf(the_flag_name, DEF_FRAGSIZE-1, "flagged_flag");
3173 else if (strcasecmp(flag_name, "recent") == 0)
3174 snprintf(the_flag_name, DEF_FRAGSIZE-1, "recent_flag");
3175 else if (strcasecmp(flag_name, "draft") == 0)
3176 snprintf(the_flag_name, DEF_FRAGSIZE-1, "draft_flag");
3177 else
3178 return DM_SUCCESS; /* non-existent flag is not set */
3179
3180 c = db_con_get();
3181 TRY
3182 r = db_query(c, "SELECT %s FROM %smessages WHERE message_idnr=%" PRIu64 " AND status < %d ",
3183 the_flag_name, DBPFX, msg_idnr, MESSAGE_STATUS_DELETE);
3184 if (db_result_next(r))
3185 val = db_result_get_int(r, 0);
3186 CATCH(SQLException)
3187 LOG_SQLERROR;
3188 FINALLY
3189 db_con_close(c);
3190 END_TRY;
3191
3192 return val;
3193 }
3194
db_set_msgkeywords(Connection_T c,uint64_t msg_idnr,GList * keywords,int action_type,MessageInfo * msginfo)3195 static long long int db_set_msgkeywords(Connection_T c, uint64_t msg_idnr, GList *keywords, int action_type, MessageInfo *msginfo)
3196 {
3197 PreparedStatement_T s;
3198 INIT_QUERY;
3199 volatile long long int count = 0;
3200
3201 if (g_list_length(g_list_first(keywords)) == 0)
3202 return 0;
3203
3204 if (action_type == IMAPFA_REMOVE) {
3205
3206
3207 s = db_stmt_prepare(c, "DELETE FROM %skeywords WHERE message_idnr=? AND keyword=?",
3208 DBPFX);
3209 db_stmt_set_u64(s,1,msg_idnr);
3210
3211 keywords = g_list_first(keywords);
3212 while (keywords) {
3213 if ((msginfo) && (g_list_find_custom(msginfo->keywords,
3214 (char *)keywords->data,
3215 (GCompareFunc)g_ascii_strcasecmp))) {
3216 db_stmt_set_str(s,2,(char *)keywords->data);
3217 db_stmt_exec(s);
3218 count++;
3219 }
3220
3221 if (! g_list_next(keywords)) break;
3222 keywords = g_list_next(keywords);
3223 }
3224 }
3225
3226 else if (action_type == IMAPFA_ADD || action_type == IMAPFA_REPLACE) {
3227 const char *ignore = db_get_sql(SQL_IGNORE);
3228 if (action_type == IMAPFA_REPLACE) {
3229 s = db_stmt_prepare(c, "DELETE FROM %skeywords WHERE message_idnr=?", DBPFX);
3230 db_stmt_set_u64(s, 1, msg_idnr);
3231 db_stmt_exec(s);
3232 }
3233
3234
3235 keywords = g_list_first(keywords);
3236 while (keywords) {
3237 if ((! msginfo) || (! g_list_find_custom(msginfo->keywords,
3238 (char *)keywords->data,
3239 (GCompareFunc)g_ascii_strcasecmp))) {
3240
3241 if (action_type == IMAPFA_ADD) { // avoid duplicate key errors in case of concurrent inserts
3242 s = db_stmt_prepare(c, "DELETE FROM %skeywords WHERE message_idnr=? AND keyword=?", DBPFX);
3243 db_stmt_set_u64(s, 1, msg_idnr);
3244 db_stmt_set_str(s, 2, (char *)keywords->data);
3245 db_stmt_exec(s);
3246 }
3247
3248 s = db_stmt_prepare(c, "INSERT %s INTO %skeywords (message_idnr,keyword) VALUES (?, ?)",
3249 ignore, DBPFX);
3250 db_stmt_set_u64(s, 1, msg_idnr);
3251 db_stmt_set_str(s, 2, (char *)keywords->data);
3252 db_stmt_exec(s);
3253 count++;
3254 }
3255 if (! g_list_next(keywords)) break;
3256 keywords = g_list_next(keywords);
3257 }
3258 }
3259 return count;
3260 }
3261
db_set_msgflag(uint64_t msg_idnr,int * flags,GList * keywords,int action_type,uint64_t seq,MessageInfo * msginfo)3262 int db_set_msgflag(uint64_t msg_idnr, int *flags, GList *keywords, int action_type, uint64_t seq, MessageInfo *msginfo)
3263 {
3264 Connection_T c;
3265 size_t i, pos = 0;
3266 volatile int seen = 0, count = 0;
3267 INIT_QUERY;
3268
3269 memset(query,0,DEF_QUERYSIZE);
3270 pos += snprintf(query, DEF_QUERYSIZE-1, "UPDATE %smessages SET ", DBPFX);
3271
3272 for (i = 0; flags && i < IMAP_NFLAGS; i++) {
3273 if (flags[i])
3274 TRACE(TRACE_DEBUG,"set %s", db_flag_desc[i]);
3275
3276 switch (action_type) {
3277 case IMAPFA_ADD:
3278 if (flags[i]) {
3279 if (msginfo) msginfo->flags[i] = 1;
3280 pos += snprintf(query + pos, DEF_QUERYSIZE - pos - 1, "%s%s=1", seen?",":"", db_flag_desc[i]);
3281 seen++;
3282 }
3283 break;
3284 case IMAPFA_REMOVE:
3285 if (flags[i]) {
3286 if (msginfo) msginfo->flags[i] = 0;
3287 pos += snprintf(query + pos, DEF_QUERYSIZE - pos - 1, "%s%s=0", seen?",":"", db_flag_desc[i]);
3288 seen++;
3289 }
3290 break;
3291
3292 case IMAPFA_REPLACE:
3293 if (flags[i]) {
3294 if (msginfo) msginfo->flags[i] = 1;
3295 pos += snprintf(query + pos, DEF_QUERYSIZE - pos - 1, "%s%s=1", seen?",":"", db_flag_desc[i]);
3296 } else if (i != IMAP_FLAG_RECENT) {
3297 if (msginfo) msginfo->flags[i] = 0;
3298 pos += snprintf(query + pos, DEF_QUERYSIZE - pos - 1, "%s%s=0", seen?",":"", db_flag_desc[i]);
3299 }
3300 seen++;
3301 break;
3302 }
3303 }
3304
3305 if (seq) {
3306 snprintf(query + pos, DEF_QUERYSIZE - pos - 1,
3307 " WHERE message_idnr = %" PRIu64 " AND status < %d AND seq <= %" PRIu64,
3308 msg_idnr, MESSAGE_STATUS_DELETE, seq);
3309 } else {
3310 snprintf(query + pos, DEF_QUERYSIZE - pos - 1,
3311 " WHERE message_idnr = %" PRIu64 " AND status < %d",
3312 msg_idnr, MESSAGE_STATUS_DELETE);
3313 }
3314
3315
3316 c = db_con_get();
3317 TRY
3318 db_begin_transaction(c);
3319 if (seen) {
3320 db_exec(c, query);
3321 if (Connection_rowsChanged(c))
3322 count = 1;
3323 }
3324 if (db_set_msgkeywords(c, msg_idnr, keywords, action_type, msginfo))
3325 count = 1;
3326
3327 db_commit_transaction(c);
3328 CATCH(SQLException)
3329 LOG_SQLERROR;
3330 count = DM_EQUERY;
3331 FINALLY
3332 db_con_close(c);
3333 END_TRY;
3334
3335 return count;
3336 }
3337
db_acl_has_acl(uint64_t userid,uint64_t mboxid)3338 static int db_acl_has_acl(uint64_t userid, uint64_t mboxid)
3339 {
3340 Connection_T c; ResultSet_T r; volatile int t = FALSE;
3341
3342 c = db_con_get();
3343 TRY
3344 r = db_query(c, "SELECT user_id, mailbox_id FROM %sacl WHERE user_id = %" PRIu64 " AND mailbox_id = %" PRIu64 "",DBPFX, userid, mboxid);
3345 if (db_result_next(r))
3346 t = TRUE;
3347 CATCH(SQLException)
3348 LOG_SQLERROR;
3349 t = DM_EQUERY;
3350 FINALLY
3351 db_con_close(c);
3352 END_TRY;
3353
3354 return t;
3355 }
3356
db_acl_create_acl(uint64_t userid,uint64_t mboxid)3357 static int db_acl_create_acl(uint64_t userid, uint64_t mboxid)
3358 {
3359 return db_update("INSERT INTO %sacl (user_id, mailbox_id) VALUES (%" PRIu64 ", %" PRIu64 ")",DBPFX, userid, mboxid);
3360 }
3361
db_acl_set_right(uint64_t userid,uint64_t mboxid,const char * right_flag,int set)3362 int db_acl_set_right(uint64_t userid, uint64_t mboxid, const char *right_flag,
3363 int set)
3364 {
3365 int result;
3366
3367 assert(set == 0 || set == 1);
3368
3369 TRACE(TRACE_DEBUG, "Setting ACL for user [%" PRIu64 "], mailbox [%" PRIu64 "].",
3370 userid, mboxid);
3371
3372 result = db_user_is_mailbox_owner(userid, mboxid);
3373 if (result < 0) {
3374 TRACE(TRACE_ERR, "error checking ownership of mailbox.");
3375 return DM_EQUERY;
3376 }
3377 if (result == TRUE)
3378 return DM_SUCCESS;
3379
3380 // if necessary, create ACL for user, mailbox
3381 result = db_acl_has_acl(userid, mboxid);
3382 if (result < 0) {
3383 TRACE(TRACE_ERR, "Error finding acl for user "
3384 "[%" PRIu64 "], mailbox [%" PRIu64 "]",
3385 userid, mboxid);
3386 return DM_EQUERY;
3387 }
3388
3389 if (result == FALSE) {
3390 if (db_acl_create_acl(userid, mboxid) == -1) {
3391 TRACE(TRACE_ERR, "Error creating ACL for "
3392 "user [%" PRIu64 "], mailbox [%" PRIu64 "]",
3393 userid, mboxid);
3394 return DM_EQUERY;
3395 }
3396 }
3397
3398 return db_update("UPDATE %sacl SET %s = %i WHERE user_id = %" PRIu64 " AND mailbox_id = %" PRIu64 "",DBPFX, right_flag, set, userid, mboxid);
3399 }
3400
db_acl_delete_acl(uint64_t userid,uint64_t mboxid)3401 int db_acl_delete_acl(uint64_t userid, uint64_t mboxid)
3402 {
3403 return db_update("DELETE FROM %sacl WHERE user_id = %" PRIu64 " AND mailbox_id = %" PRIu64 "",DBPFX, userid, mboxid);
3404 }
3405
db_acl_get_identifier(uint64_t mboxid,GList ** identifier_list)3406 int db_acl_get_identifier(uint64_t mboxid, GList **identifier_list)
3407 {
3408 Connection_T c; ResultSet_T r; volatile int t = TRUE;
3409
3410 c = db_con_get();
3411 TRY
3412 r = db_query(c, "SELECT %susers.userid FROM %susers, %sacl "
3413 "WHERE %sacl.mailbox_id = %" PRIu64 " "
3414 "AND %susers.user_idnr = %sacl.user_id",DBPFX,DBPFX,DBPFX,
3415 DBPFX,mboxid,DBPFX,DBPFX);
3416 while (db_result_next(r))
3417 *(GList **)identifier_list = g_list_prepend(*(GList **)identifier_list, g_strdup(db_result_get(r, 0)));
3418 CATCH(SQLException)
3419 LOG_SQLERROR;
3420 t = DM_EQUERY;
3421 FINALLY
3422 db_con_close(c);
3423 END_TRY;
3424
3425 return t;
3426 }
3427
db_get_mailbox_owner(uint64_t mboxid,uint64_t * owner_id)3428 int db_get_mailbox_owner(uint64_t mboxid, uint64_t * owner_id)
3429 {
3430 Connection_T c; ResultSet_T r; volatile int t = FALSE;
3431 assert(owner_id != NULL);
3432 *owner_id = 0;
3433
3434 c = db_con_get();
3435 TRY
3436 r = db_query(c, "SELECT owner_idnr FROM %smailboxes WHERE mailbox_idnr = %" PRIu64 "", DBPFX, mboxid);
3437 if (db_result_next(r))
3438 *owner_id = db_result_get_u64(r, 0);
3439 CATCH(SQLException)
3440 LOG_SQLERROR;
3441 t = DM_EQUERY;
3442 FINALLY
3443 db_con_close(c);
3444 END_TRY;
3445
3446 if (t == DM_EQUERY) return t;
3447 if (*owner_id == 0) return FALSE;
3448
3449 return TRUE;
3450 }
3451
db_user_is_mailbox_owner(uint64_t userid,uint64_t mboxid)3452 int db_user_is_mailbox_owner(uint64_t userid, uint64_t mboxid)
3453 {
3454 Connection_T c; ResultSet_T r; volatile int t = FALSE;
3455
3456 c = db_con_get();
3457 TRY
3458 r = db_query(c, "SELECT mailbox_idnr FROM %smailboxes WHERE mailbox_idnr = %" PRIu64 " AND owner_idnr = %" PRIu64 "", DBPFX, mboxid, userid);
3459 if (db_result_next(r)) t = TRUE;
3460 CATCH(SQLException)
3461 LOG_SQLERROR;
3462 t = DM_EQUERY;
3463 FINALLY
3464 db_con_close(c);
3465 END_TRY;
3466
3467 return t;
3468 }
3469
date2char_str(const char * column,Field_T * frag)3470 int date2char_str(const char *column, Field_T *frag)
3471 {
3472 assert(frag);
3473 memset(frag, 0, sizeof(Field_T));
3474 snprintf((char *)frag, sizeof(Field_T)-1, db_get_sql(SQL_TO_CHAR), column);
3475 return 0;
3476 }
3477
char2date_str(const char * date,Field_T * frag)3478 int char2date_str(const char *date, Field_T *frag)
3479 {
3480 char *qs;
3481
3482 assert(frag);
3483 memset(frag, 0, sizeof(Field_T));
3484
3485 qs = g_strdup_printf("'%s'", date);
3486 snprintf((char *)frag, sizeof(Field_T)-1, db_get_sql(SQL_TO_DATETIME), qs);
3487 g_free(qs);
3488
3489 return 0;
3490 }
3491
db_usermap_resolve(ClientBase_T * ci,const char * username,char * real_username)3492 int db_usermap_resolve(ClientBase_T *ci, const char *username, char *real_username)
3493 {
3494 char clientsock[DM_SOCKADDR_LEN];
3495 const char *userid = NULL, *sockok = NULL, *sockno = NULL, *login = NULL;
3496 volatile unsigned row = 0;
3497 volatile int result = TRUE;
3498 int score, bestscore = -1;
3499 char *bestlogin = NULL, *bestuserid = NULL;
3500 Connection_T c; ResultSet_T r; PreparedStatement_T s;
3501 INIT_QUERY;
3502
3503 memset(clientsock,0,DM_SOCKADDR_LEN);
3504
3505 TRACE(TRACE_DEBUG,"checking userid [%s] in usermap", username);
3506
3507 if (ci->tx==0) {
3508 strncpy(clientsock,"",1);
3509 } else {
3510 snprintf(clientsock, DM_SOCKADDR_LEN-1, "inet:%s:%s", ci->dst_ip, ci->dst_port);
3511 TRACE(TRACE_DEBUG, "client on inet socket [%s]", clientsock);
3512 }
3513
3514 /* user_idnr not found, so try to get it from the usermap */
3515 snprintf(query, DEF_QUERYSIZE-1, "SELECT login, sock_allow, sock_deny, userid FROM %susermap "
3516 "WHERE login in (?,'ANY') "
3517 "ORDER BY sock_allow, sock_deny",
3518 DBPFX);
3519
3520 c = db_con_get();
3521 TRY
3522 s = db_stmt_prepare(c, query);
3523 db_stmt_set_str(s,1,username);
3524
3525 r = db_stmt_query(s);
3526 /* find the best match on the usermap table */
3527 while (db_result_next(r)) {
3528 row++;
3529 login = db_result_get(r,0);
3530 sockok = db_result_get(r,1);
3531 sockno = db_result_get(r,2);
3532 userid = db_result_get(r,3);
3533 result = dm_sock_compare(clientsock, "", sockno);
3534 /* any match on sockno will be fatal */
3535 if (! result) break;
3536
3537 score = dm_sock_score(clientsock, sockok);
3538 if (score > bestscore) {
3539 bestlogin = g_strdup(login);
3540 bestuserid = g_strdup(userid);
3541 bestscore = score;
3542 }
3543 }
3544 CATCH(SQLException)
3545 LOG_SQLERROR;
3546 FINALLY
3547 db_con_close(c);
3548 END_TRY;
3549
3550 if (! result) {
3551 if (bestlogin) g_free(bestlogin);
3552 if (bestuserid) g_free(bestuserid);
3553 TRACE(TRACE_DEBUG,"access denied");
3554 return DM_EGENERAL;
3555 }
3556
3557 if (! row) {
3558 /* user does not exist */
3559 TRACE(TRACE_DEBUG, "login [%s] not found in usermap", username);
3560 return DM_SUCCESS;
3561 }
3562
3563
3564 TRACE(TRACE_DEBUG, "bestscore [%d]", bestscore);
3565 if (bestscore <= 0) {
3566 if (bestlogin) g_free(bestlogin);
3567 if (bestuserid) g_free(bestuserid);
3568 return DM_EGENERAL;
3569 }
3570
3571 /* use the best matching sockok */
3572 login = (const char *)bestlogin;
3573 userid = (const char *)bestuserid;
3574
3575 TRACE(TRACE_DEBUG,"best match: [%s] -> [%s]", login, userid);
3576
3577 if ((strncmp(login,"ANY",3)==0)) {
3578 if (dm_valid_format(userid)==0)
3579 snprintf(real_username,DM_USERNAME_LEN-1,userid,username);
3580 else {
3581 if (bestlogin) g_free(bestlogin);
3582 if (bestuserid) g_free(bestuserid);
3583 return DM_EQUERY;
3584 }
3585 } else {
3586 strncpy(real_username, userid, DM_USERNAME_LEN-1);
3587 }
3588
3589 TRACE(TRACE_DEBUG,"[%s] maps to [%s]", username, real_username);
3590
3591 if (bestlogin) g_free(bestlogin);
3592 if (bestuserid) g_free(bestuserid);
3593
3594 return DM_SUCCESS;
3595
3596 }
3597
db_user_active(uint64_t user_idnr)3598 gboolean db_user_active(uint64_t user_idnr)
3599 {
3600 Connection_T c; ResultSet_T r; PreparedStatement_T s;
3601 volatile int active = 1;
3602 c = db_con_get();
3603 TRY
3604 s = db_stmt_prepare(c, "SELECT active FROM %susers WHERE user_idnr = ?",
3605 DBPFX);
3606 db_stmt_set_u64(s, 1, user_idnr);
3607 r = db_stmt_query(s);
3608 if (db_result_next(r))
3609 active = db_result_get_int(r, 0);
3610 CATCH(SQLException)
3611 LOG_SQLERROR;
3612 FINALLY
3613 db_con_close(c);
3614 END_TRY;
3615 return active ? true : false;
3616 }
3617
db_user_set_active(uint64_t user_idnr,gboolean active)3618 int db_user_set_active(uint64_t user_idnr, gboolean active)
3619 {
3620 Connection_T c; PreparedStatement_T s;
3621 volatile int t = DM_SUCCESS;
3622 c = db_con_get();
3623 TRY
3624 s = db_stmt_prepare(c,
3625 "UPDATE %susers SET active = ? WHERE user_idnr = ?",
3626 DBPFX);
3627 db_stmt_set_int(s, 1, (int)active);
3628 db_stmt_set_u64(s, 2, user_idnr);
3629 db_stmt_exec(s);
3630 CATCH(SQLException)
3631 LOG_SQLERROR;
3632 t = DM_EQUERY;
3633 FINALLY
3634 db_con_close(c);
3635 END_TRY;
3636 return t;
3637 }
3638
db_user_get_security_action(uint64_t user_idnr)3639 int db_user_get_security_action(uint64_t user_idnr)
3640 {
3641 Connection_T c; ResultSet_T r; PreparedStatement_T s;
3642 volatile int action = 0;
3643 c = db_con_get();
3644 TRY
3645 s = db_stmt_prepare(c, "SELECT saction FROM %susers WHERE user_idnr = ?",
3646 DBPFX);
3647 db_stmt_set_u64(s, 1, user_idnr);
3648 r = db_stmt_query(s);
3649 if (db_result_next(r))
3650 action = db_result_get_int(r, 0);
3651 CATCH(SQLException)
3652 LOG_SQLERROR;
3653 FINALLY
3654 db_con_close(c);
3655 END_TRY;
3656 return action;
3657 }
3658
db_user_set_security_action(uint64_t user_idnr,long int action)3659 int db_user_set_security_action(uint64_t user_idnr, long int action)
3660 {
3661 Connection_T c; PreparedStatement_T s;
3662 volatile int t = DM_SUCCESS;
3663 c = db_con_get();
3664 TRY
3665 s = db_stmt_prepare(c,
3666 "UPDATE %susers SET saction = ? WHERE user_idnr = ?",
3667 DBPFX);
3668 db_stmt_set_int(s, 1, (int)action);
3669 db_stmt_set_u64(s, 2, user_idnr);
3670 db_stmt_exec(s);
3671 CATCH(SQLException)
3672 LOG_SQLERROR;
3673 t = DM_EQUERY;
3674 FINALLY
3675 db_con_close(c);
3676 END_TRY;
3677 return t;
3678 }
3679
db_user_set_security_password(uint64_t user_idnr,const char * password)3680 int db_user_set_security_password(uint64_t user_idnr, const char *password)
3681 {
3682 Connection_T c; PreparedStatement_T s;
3683 volatile int t = DM_SUCCESS;
3684 c = db_con_get();
3685 TRY
3686 s = db_stmt_prepare(c,
3687 "UPDATE %susers SET spasswd = ? WHERE user_idnr = ?",
3688 DBPFX);
3689 db_stmt_set_str(s, 1, password);
3690 db_stmt_set_u64(s, 2, user_idnr);
3691 db_stmt_exec(s);
3692 CATCH(SQLException)
3693 LOG_SQLERROR;
3694 t = DM_EQUERY;
3695 FINALLY
3696 db_con_close(c);
3697 END_TRY;
3698 return t;
3699 }
3700
3701 #define COLUMN_WIDTH 255
db_user_validate(ClientBase_T * ci,const char * pwfield,uint64_t * user_idnr,const char * password)3702 int db_user_validate(ClientBase_T *ci, const char *pwfield, uint64_t *user_idnr, const char *password)
3703 {
3704 int is_validated = 0;
3705 char salt[13], cryptres[35];
3706 volatile int t = FALSE;
3707 char dbpass[COLUMN_WIDTH+1];
3708 char encode[COLUMN_WIDTH+1];
3709 char hashstr[FIELDSIZE];
3710 Connection_T c; ResultSet_T r;
3711
3712 memset(salt,0,sizeof(salt));
3713 memset(cryptres,0,sizeof(cryptres));
3714 memset(hashstr, 0, sizeof(hashstr));
3715 memset(dbpass, 0, sizeof(dbpass));
3716 memset(encode, 0, sizeof(encode));
3717
3718 c = db_con_get();
3719 TRY
3720 r = db_query(c, "SELECT %s, encryption_type FROM %susers WHERE user_idnr = %" PRIu64 "",
3721 pwfield, DBPFX, *user_idnr);
3722 if (db_result_next(r)) {
3723 strncpy(dbpass, db_result_get(r, 0), sizeof(dbpass)-1);
3724 strncpy(encode, db_result_get(r, 1), sizeof(encode)-1);
3725 t = TRUE;
3726 } else {
3727 t = FALSE;
3728 }
3729 CATCH(SQLException)
3730 LOG_SQLERROR;
3731 t = DM_EQUERY;
3732 FINALLY
3733 db_con_close(c);
3734 END_TRY;
3735
3736 if (t == DM_EQUERY)
3737 return t;
3738
3739 if (! t) return FALSE;
3740 if (! strlen(dbpass)) {
3741 TRACE(TRACE_INFO, "Empty password for [%" PRIu64 "] in [%s]", *user_idnr, pwfield);
3742 return FALSE;
3743 }
3744
3745 if (SMATCH(encode, "")) {
3746 TRACE(TRACE_DEBUG, "validating using plaintext passwords");
3747 if (ci && ci->auth) // CRAM-MD5
3748 is_validated = Cram_verify(ci->auth, dbpass);
3749 else
3750 is_validated = (strcmp(dbpass, password) == 0) ? 1 : 0;
3751 } else if (password == NULL)
3752 return FALSE;
3753
3754 if (SMATCH(encode, "crypt")) {
3755 TRACE(TRACE_DEBUG, "validating using crypt() encryption");
3756 strncpy(salt, dbpass, 2);
3757 is_validated = (strcmp((const char *) crypt(password, salt), dbpass) == 0) ? 1 : 0;
3758 } else if (SMATCH(encode, "md5")) {
3759 /* get password */
3760 if (strncmp(dbpass, "$1$", 3)) { // no match
3761 TRACE(TRACE_DEBUG, "validating using MD5 digest comparison");
3762 dm_md5(password, hashstr);
3763 is_validated = (strncmp(hashstr, dbpass, 32) == 0) ? 1 : 0;
3764 } else {
3765 TRACE(TRACE_DEBUG, "validating using MD5 hash comparison");
3766 strncpy(salt, dbpass, 12);
3767 strncpy(cryptres, (char *) crypt(password, dbpass), 34);
3768 TRACE(TRACE_DEBUG, "salt : %s", salt);
3769 TRACE(TRACE_DEBUG, "hash : %s", dbpass);
3770 TRACE(TRACE_DEBUG, "crypt(): %s", cryptres);
3771 is_validated = (strncmp(dbpass, cryptres, 34) == 0) ? 1 : 0;
3772 }
3773 } else if (SMATCH(encode, "md5sum")) {
3774 TRACE(TRACE_DEBUG, "validating using MD5 digest comparison");
3775 dm_md5(password, hashstr);
3776 is_validated = (strncmp(hashstr, dbpass, 32) == 0) ? 1 : 0;
3777 } else if (SMATCH(encode, "md5base64")) {
3778 TRACE(TRACE_DEBUG, "validating using MD5 digest base64 comparison");
3779 dm_md5_base64(password, hashstr);
3780 is_validated = (strncmp(hashstr, dbpass, 32) == 0) ? 1 : 0;
3781 } else if (SMATCH(encode, "whirlpool")) {
3782 TRACE(TRACE_DEBUG, "validating using WHIRLPOOL hash comparison");
3783 dm_whirlpool(password, hashstr);
3784 is_validated = (strncmp(hashstr, dbpass, 128) == 0) ? 1 : 0;
3785 } else if (SMATCH(encode, "sha512")) {
3786 TRACE(TRACE_DEBUG, "validating using SHA-512 hash comparison");
3787 dm_sha512(password, hashstr);
3788 is_validated = (strncmp(hashstr, dbpass, 128) == 0) ? 1 : 0;
3789 } else if (SMATCH(encode, "sha256")) {
3790 TRACE(TRACE_DEBUG, "validating using SHA-256 hash comparison");
3791 dm_sha256(password, hashstr);
3792 is_validated = (strncmp(hashstr, dbpass, 64) == 0) ? 1 : 0;
3793 } else if (SMATCH(encode, "sha1")) {
3794 TRACE(TRACE_DEBUG, "validating using SHA-1 hash comparison");
3795 dm_sha1(password, hashstr);
3796 is_validated = (strncmp(hashstr, dbpass, 32) == 0) ? 1 : 0;
3797 } else if (SMATCH(encode, "tiger")) {
3798 TRACE(TRACE_DEBUG, "validating using TIGER hash comparison");
3799 dm_tiger(password, hashstr);
3800 is_validated = (strncmp(hashstr, dbpass, 48) == 0) ? 1 : 0;
3801 }
3802
3803 if (is_validated)
3804 db_user_log_login(*user_idnr);
3805
3806 return (is_validated ? 1 : 0);
3807 }
3808
db_user_delete_messages(uint64_t user_idnr,char * flags)3809 int db_user_delete_messages(uint64_t user_idnr, char *flags)
3810 {
3811 int flagcount = 0;
3812 int i = 0, j = 0;
3813 char *flag;
3814 char **parts;
3815 GList *keywords = NULL;
3816 int sysflags[IMAP_NFLAGS];
3817 String_T query = NULL;
3818 Mempool_T pool = NULL;
3819 Connection_T c; PreparedStatement_T st;
3820
3821 memset(sysflags, 0, sizeof(sysflags));
3822 parts = g_strsplit(flags, " ", 0);
3823 while ((flag = parts[i++])) {
3824 for (j = 0; j < IMAP_NFLAGS; j++) {
3825 if (MATCH(flag, imap_flag_desc_escaped[j])) {
3826 sysflags[j] = 1;
3827 flagcount++;
3828 break;
3829 }
3830 }
3831 if (j == IMAP_NFLAGS) {
3832 keywords = g_list_append(keywords, g_strdup(flag));
3833 flagcount++;
3834 }
3835 }
3836
3837 if (! flagcount)
3838 return 0;
3839
3840 pool = mempool_open();
3841 query = p_string_new(pool, "");
3842 p_string_printf(query, "UPDATE %smessages SET status=%d "
3843 "WHERE message_idnr IN ("
3844 "SELECT m.message_idnr FROM %smessages m "
3845 "JOIN %smailboxes b ON m.mailbox_idnr=b.mailbox_idnr "
3846 "LEFT OUTER JOIN %skeywords k ON k.message_idnr=m.message_idnr "
3847 "WHERE b.owner_idnr=? AND status IN (%d,%d) AND (1=0",
3848 DBPFX, MESSAGE_STATUS_DELETE, DBPFX, DBPFX, DBPFX,
3849 MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN);
3850
3851
3852 for (j = 0; j < IMAP_NFLAGS; j++) {
3853 if (! sysflags[j])
3854 continue;
3855 p_string_append_printf(query, " OR m.%s=1", db_flag_desc[j]);
3856 }
3857
3858 keywords = g_list_first(keywords);
3859 while (keywords) {
3860 p_string_append_printf(query, " OR lower(k.keyword)=lower(?)");
3861 if (! g_list_next(keywords))
3862 break;
3863 keywords = g_list_next(keywords);
3864 }
3865
3866 p_string_append(query, "))");
3867
3868 c = db_con_get();
3869 TRY
3870 db_begin_transaction(c);
3871 st = db_stmt_prepare(c, p_string_str(query));
3872 db_stmt_set_u64(st, 1, user_idnr);
3873 i = 2;
3874 keywords = g_list_first(keywords);
3875 while (keywords) {
3876 char *label = (char *)keywords->data;
3877 db_stmt_set_str(st, i++, label);
3878 if (! g_list_next(keywords))
3879 break;
3880 keywords = g_list_next(keywords);
3881 }
3882 db_stmt_exec(st);
3883 db_commit_transaction(c);
3884 CATCH(SQLException)
3885 LOG_SQLERROR;
3886 db_rollback_transaction(c);
3887 FINALLY
3888 db_con_close(c);
3889 END_TRY;
3890
3891 p_string_free(query, TRUE);
3892 g_list_destroy(keywords);
3893 mempool_close(&pool);
3894
3895 return 0;
3896 }
3897
db_user_security_trigger(uint64_t user_idnr)3898 int db_user_security_trigger(uint64_t user_idnr)
3899 {
3900 volatile uint64_t result = 0;
3901 uint64_t action = 0;
3902 char *flags = NULL;
3903 Connection_T c; ResultSet_T r; PreparedStatement_T s;
3904 config_get_security_actions(server_conf);
3905
3906 assert(user_idnr);
3907 c = db_con_get();
3908 TRY
3909 s = db_stmt_prepare(c, "SELECT saction FROM %susers WHERE user_idnr = ?", DBPFX);
3910 db_stmt_set_u64(s, 1, user_idnr);
3911 r = db_stmt_query(s);
3912 if (db_result_next(r))
3913 result = db_result_get_u64(r, 0);
3914 CATCH(SQLException)
3915 LOG_SQLERROR;
3916 FINALLY
3917 db_con_close(c);
3918 END_TRY;
3919
3920 if (! result) return 0;
3921 action = result;
3922
3923 flags = g_tree_lookup(server_conf->security_actions, &action);
3924
3925 if (flags) {
3926 TRACE(TRACE_DEBUG, "Found: user_idnr [%" PRIu64 "] security_action [%" PRIu64 "] flags [%s]",
3927 user_idnr, action, flags);
3928 }
3929
3930
3931 if (action == 1) {
3932 db_empty_mailbox(user_idnr, 0);
3933 } else if (flags) {
3934 db_user_delete_messages(user_idnr, flags);
3935 dm_quota_rebuild_user(user_idnr);
3936 } else {
3937 TRACE(TRACE_INFO, "NotFound: user_idnr [%" PRIu64 "] security_action [%" PRIu64 "]",
3938 user_idnr, action);
3939 }
3940
3941 return 0;
3942 }
3943
db_user_exists(const char * username,uint64_t * user_idnr)3944 int db_user_exists(const char *username, uint64_t * user_idnr)
3945 {
3946 Connection_T c; ResultSet_T r; PreparedStatement_T s;
3947
3948 assert(username);
3949 assert(user_idnr);
3950 *user_idnr = 0;
3951
3952 c = db_con_get();
3953 TRY
3954 s = db_stmt_prepare(c, "SELECT user_idnr FROM %susers WHERE lower(userid) = lower(?)", DBPFX);
3955 db_stmt_set_str(s,1,username);
3956 r = db_stmt_query(s);
3957 if (db_result_next(r))
3958 *user_idnr = db_result_get_u64(r, 0);
3959 CATCH(SQLException)
3960 LOG_SQLERROR;
3961 FINALLY
3962 db_con_close(c);
3963 END_TRY;
3964
3965 return (*user_idnr) ? 1 : 0;
3966 }
3967
db_user_create_shadow(const char * username,uint64_t * user_idnr)3968 int db_user_create_shadow(const char *username, uint64_t * user_idnr)
3969 {
3970 return db_user_create(username, "UNUSED", "md5", 0xffff, 0, user_idnr);
3971 }
3972
db_user_create(const char * username,const char * password,const char * enctype,uint64_t clientid,uint64_t maxmail,uint64_t * user_idnr)3973 int db_user_create(const char *username, const char *password, const char *enctype,
3974 uint64_t clientid, uint64_t maxmail, uint64_t * user_idnr)
3975 {
3976 INIT_QUERY;
3977 Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE;
3978 char *encoding = NULL, *frag;
3979 uint64_t id, existing_user_idnr = 0;
3980
3981 assert(user_idnr != NULL);
3982
3983 if (db_user_exists(username, &existing_user_idnr))
3984 return TRUE;
3985
3986 if (strlen(password) >= 128) {
3987 TRACE(TRACE_ERR, "password length is insane");
3988 return DM_EQUERY;
3989 }
3990
3991 encoding = g_strdup(enctype ? enctype : "");
3992
3993 c = db_con_get();
3994
3995 t = TRUE;
3996 memset(query,0,DEF_QUERYSIZE);
3997 TRY
3998 db_begin_transaction(c);
3999 frag = db_returning("user_idnr");
4000 if (*user_idnr==0) {
4001 snprintf(query, DEF_QUERYSIZE-1, "INSERT INTO %susers "
4002 "(userid,passwd,client_idnr,maxmail_size,"
4003 "encryption_type) VALUES "
4004 "(?,?,?,?,?) %s",
4005 DBPFX, frag);
4006 s = db_stmt_prepare(c, query);
4007 db_stmt_set_str(s, 1, username);
4008 db_stmt_set_str(s, 2, password);
4009 db_stmt_set_u64(s, 3, clientid);
4010 db_stmt_set_u64(s, 4, maxmail);
4011 db_stmt_set_str(s, 5, encoding);
4012 } else {
4013 snprintf(query, DEF_QUERYSIZE-1, "INSERT INTO %susers "
4014 "(userid,user_idnr,passwd,client_idnr,maxmail_size,"
4015 "encryption_type) VALUES "
4016 "(?,?,?,?,?,?) %s",
4017 DBPFX, frag);
4018 s = db_stmt_prepare(c, query);
4019 db_stmt_set_str(s, 1, username);
4020 db_stmt_set_u64(s, 2, *user_idnr);
4021 db_stmt_set_str(s, 3, password);
4022 db_stmt_set_u64(s, 4, clientid);
4023 db_stmt_set_u64(s, 5, maxmail);
4024 db_stmt_set_str(s, 6, encoding);
4025 }
4026 g_free(frag);
4027 if (db_params.db_driver == DM_DRIVER_ORACLE) {
4028 db_stmt_exec(s);
4029 id = db_get_pk(c, "users");
4030 } else {
4031 r = db_stmt_query(s);
4032 id = db_insert_result(c, r);
4033 }
4034 if (*user_idnr == 0) *user_idnr = id;
4035 db_commit_transaction(c);
4036 CATCH(SQLException)
4037 LOG_SQLERROR;
4038 db_rollback_transaction(c);
4039 t = DM_EQUERY;
4040 FINALLY
4041 db_con_close(c);
4042 END_TRY;
4043
4044 g_free(encoding);
4045 if (t == TRUE)
4046 TRACE(TRACE_DEBUG, "create shadow account userid [%s], user_idnr [%" PRIu64 "]", username, *user_idnr);
4047
4048 return t;
4049 }
4050
db_change_mailboxsize(uint64_t user_idnr,uint64_t new_size)4051 int db_change_mailboxsize(uint64_t user_idnr, uint64_t new_size)
4052 {
4053 return db_update("UPDATE %susers SET maxmail_size = %" PRIu64 " WHERE user_idnr = %" PRIu64 "", DBPFX, new_size, user_idnr);
4054 }
4055
db_user_delete(const char * username)4056 int db_user_delete(const char * username)
4057 {
4058 Connection_T c; PreparedStatement_T s; volatile int t = FALSE;
4059 c = db_con_get();
4060 TRY
4061 db_begin_transaction(c);
4062 s = db_stmt_prepare(c, "DELETE FROM %susers WHERE userid = ?", DBPFX);
4063 db_stmt_set_str(s, 1, username);
4064 db_stmt_exec(s);
4065 db_commit_transaction(c);
4066 t = TRUE;
4067 CATCH(SQLException)
4068 LOG_SQLERROR;
4069 db_rollback_transaction(c);
4070 FINALLY
4071 db_con_close(c);
4072 END_TRY;
4073 return t;
4074 }
4075
db_user_rename(uint64_t user_idnr,const char * new_name)4076 int db_user_rename(uint64_t user_idnr, const char *new_name)
4077 {
4078 Connection_T c; PreparedStatement_T s; volatile gboolean t = FALSE;
4079 c = db_con_get();
4080 TRY
4081 db_begin_transaction(c);
4082 s = db_stmt_prepare(c, "UPDATE %susers SET userid = ? WHERE user_idnr= ?", DBPFX);
4083 db_stmt_set_str(s, 1, new_name);
4084 db_stmt_set_u64(s, 2, user_idnr);
4085 db_stmt_exec(s);
4086 db_commit_transaction(c);
4087 t = TRUE;
4088 CATCH(SQLException)
4089 LOG_SQLERROR;
4090 db_rollback_transaction(c);
4091 t = DM_EQUERY;
4092 FINALLY
4093 db_con_close(c);
4094 END_TRY;
4095 return t;
4096 }
4097
db_user_find_create(uint64_t user_idnr)4098 int db_user_find_create(uint64_t user_idnr)
4099 {
4100 char *username;
4101 uint64_t idnr;
4102 int result;
4103
4104 assert(user_idnr > 0);
4105
4106 TRACE(TRACE_DEBUG,"user_idnr [%" PRIu64 "]", user_idnr);
4107
4108 if ((result = user_idnr_is_delivery_user_idnr(user_idnr)))
4109 return result;
4110
4111 if (! (username = auth_get_userid(user_idnr)))
4112 return DM_EQUERY;
4113
4114 TRACE(TRACE_DEBUG,"found username for user_idnr [%" PRIu64 " -> %s]",
4115 user_idnr, username);
4116
4117 if ((db_user_exists(username, &idnr) < 0)) {
4118 g_free(username);
4119 return DM_EQUERY;
4120 }
4121
4122 if ((idnr > 0) && (idnr != user_idnr)) {
4123 TRACE(TRACE_ERR, "user_idnr for sql shadow account "
4124 "differs from user_idnr [%" PRIu64 " != %" PRIu64 "]",
4125 idnr, user_idnr);
4126 g_free(username);
4127 return DM_EQUERY;
4128 }
4129
4130 if (idnr == user_idnr) {
4131 TRACE(TRACE_DEBUG, "shadow entry exists and valid");
4132 g_free(username);
4133 return DM_EGENERAL;
4134 }
4135
4136 result = db_user_create_shadow(username, &user_idnr);
4137 g_free(username);
4138 return result;
4139 }
4140
db_replycache_register(const char * to,const char * from,const char * handle)4141 int db_replycache_register(const char *to, const char *from, const char *handle)
4142 {
4143 char *tmp_to = NULL;
4144 char *tmp_from = NULL;
4145 char *tmp_handle = NULL;
4146 Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE;
4147 INIT_QUERY;
4148
4149 tmp_to = g_strndup(to, REPLYCACHE_WIDTH);
4150 tmp_from = g_strndup(from, REPLYCACHE_WIDTH);
4151 tmp_handle = g_strndup(handle, REPLYCACHE_WIDTH);
4152
4153 snprintf(query, DEF_QUERYSIZE-1, "SELECT lastseen FROM %sreplycache "
4154 "WHERE to_addr = ? AND from_addr = ? AND handle = ? ", DBPFX);
4155
4156 c = db_con_get();
4157 TRY
4158 s = db_stmt_prepare(c, query);
4159 db_stmt_set_str(s, 1, tmp_to);
4160 db_stmt_set_str(s, 2, tmp_from);
4161 db_stmt_set_str(s, 3, tmp_handle);
4162
4163 r = db_stmt_query(s);
4164 if (db_result_next(r))
4165 t = TRUE;
4166 CATCH(SQLException)
4167 LOG_SQLERROR;
4168 t = DM_EQUERY;
4169 END_TRY;
4170
4171 if (t == DM_EQUERY) {
4172 db_con_close(c);
4173 return t;
4174 }
4175
4176 memset(query,0,DEF_QUERYSIZE);
4177 if (t) {
4178 snprintf(query, DEF_QUERYSIZE-1,
4179 "UPDATE %sreplycache SET lastseen = %s "
4180 "WHERE to_addr = ? AND from_addr = ? "
4181 "AND handle = ?",
4182 DBPFX, db_get_sql(SQL_CURRENT_TIMESTAMP));
4183 } else {
4184 snprintf(query, DEF_QUERYSIZE-1,
4185 "INSERT INTO %sreplycache (to_addr, from_addr, handle, lastseen) "
4186 "VALUES (?,?,?, %s)",
4187 DBPFX, db_get_sql(SQL_CURRENT_TIMESTAMP));
4188 }
4189
4190 t = FALSE;
4191 db_con_clear(c);
4192 TRY
4193 db_begin_transaction(c);
4194 s = db_stmt_prepare(c, query);
4195 db_stmt_set_str(s, 1, tmp_to);
4196 db_stmt_set_str(s, 2, tmp_from);
4197 db_stmt_set_str(s, 3, tmp_handle);
4198 db_stmt_exec(s);
4199 db_commit_transaction(c);
4200 t = TRUE;
4201 CATCH(SQLException)
4202 LOG_SQLERROR;
4203 db_rollback_transaction(c);
4204 t = DM_EQUERY;
4205 FINALLY
4206 db_con_close(c);
4207 g_free(tmp_to);
4208 g_free(tmp_from);
4209 g_free(tmp_handle);
4210 END_TRY;
4211
4212 return t;
4213 }
4214
db_replycache_unregister(const char * to,const char * from,const char * handle)4215 int db_replycache_unregister(const char *to, const char *from, const char *handle)
4216 {
4217 Connection_T c; PreparedStatement_T s; volatile gboolean t = FALSE;
4218 INIT_QUERY;
4219
4220 snprintf(query, DEF_QUERYSIZE-1,
4221 "DELETE FROM %sreplycache "
4222 "WHERE to_addr = ? "
4223 "AND from_addr = ? "
4224 "AND handle = ? ",
4225 DBPFX);
4226
4227 c = db_con_get();
4228 TRY
4229 db_begin_transaction(c);
4230 s = db_stmt_prepare(c, query);
4231 db_stmt_set_str(s, 1, to);
4232 db_stmt_set_str(s, 2, from);
4233 db_stmt_set_str(s, 3, handle);
4234 db_stmt_exec(s);
4235 db_commit_transaction(c);
4236 t = TRUE;
4237 CATCH(SQLException)
4238 LOG_SQLERROR;
4239 FINALLY
4240 db_con_close(c);
4241 END_TRY;
4242
4243 return t;
4244 }
4245
4246
4247 //
4248 // Returns FALSE if the (to, from) pair hasn't been seen in days.
4249 //
db_replycache_validate(const char * to,const char * from,const char * handle,int days)4250 int db_replycache_validate(const char *to, const char *from,
4251 const char *handle, int days)
4252 {
4253 GString *tmp = g_string_new("");
4254 Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE;
4255 INIT_QUERY;
4256
4257 g_string_printf(tmp, db_get_sql(SQL_EXPIRE), days);
4258
4259 snprintf(query, DEF_QUERYSIZE-1,
4260 "SELECT lastseen FROM %sreplycache "
4261 "WHERE to_addr = ? AND from_addr = ? "
4262 "AND handle = ? AND lastseen > (%s)",
4263 DBPFX, tmp->str);
4264
4265 g_string_free(tmp, TRUE);
4266
4267 c = db_con_get();
4268 TRY
4269 s = db_stmt_prepare(c, query);
4270 db_stmt_set_str(s, 1, to);
4271 db_stmt_set_str(s, 2, from);
4272 db_stmt_set_str(s, 3, handle);
4273
4274 r = db_stmt_query(s);
4275 if (db_result_next(r))
4276 t = TRUE;
4277 CATCH(SQLException)
4278 LOG_SQLERROR;
4279 t = DM_EQUERY;
4280 FINALLY
4281 db_con_close(c);
4282 END_TRY;
4283
4284 return t;
4285 }
4286
db_user_log_login(uint64_t user_idnr)4287 int db_user_log_login(uint64_t user_idnr)
4288 {
4289 TimeString_T timestring;
4290 create_current_timestring(×tring);
4291 return db_update("UPDATE %susers SET last_login = '%s' WHERE user_idnr = %" PRIu64 "",DBPFX, timestring, user_idnr);
4292 }
4293
db_mailbox_seq_update(uint64_t mailbox_id,uint64_t message_id)4294 uint64_t db_mailbox_seq_update(uint64_t mailbox_id, uint64_t message_id)
4295 {
4296 Connection_T c; ResultSet_T r; PreparedStatement_T st1, st2, st3;
4297 volatile uint64_t seq = 0;
4298 c = db_con_get();
4299 TRY
4300 db_begin_transaction(c);
4301 st1 = db_stmt_prepare(c, "UPDATE %s %smailboxes SET seq=seq+1 WHERE mailbox_idnr = ?",
4302 db_get_sql(SQL_IGNORE), DBPFX);
4303 db_stmt_set_u64(st1, 1, mailbox_id);
4304 st2 = db_stmt_prepare(c, "SELECT seq FROM %smailboxes WHERE mailbox_idnr = ?", DBPFX);
4305 db_stmt_set_u64(st2, 1, mailbox_id);
4306 db_stmt_exec(st1);
4307 r = db_stmt_query(st2);
4308 if (db_result_next(r))
4309 seq = db_result_get_u64(r, 0);
4310 if (message_id) {
4311 st3 = db_stmt_prepare(c, "UPDATE %s %smessages SET seq = ? WHERE message_idnr = ? "
4312 "AND seq < ?",
4313 db_get_sql(SQL_IGNORE), DBPFX);
4314 db_stmt_set_u64(st3, 1, seq);
4315 db_stmt_set_u64(st3, 2, message_id);
4316 db_stmt_set_u64(st3, 3, seq);
4317 db_stmt_exec(st3);
4318 }
4319 db_commit_transaction(c);
4320 CATCH(SQLException)
4321 LOG_SQLERROR;
4322 FINALLY
4323 db_con_close(c);
4324 END_TRY;
4325 TRACE(TRACE_DEBUG, "mailbox_id [%" PRIu64 "] message_id [%" PRIu64 "] -> [%" PRIu64 "]",
4326 mailbox_id, message_id, seq);
4327 return seq;
4328 }
4329
db_message_set_seq(uint64_t message_id,uint64_t seq)4330 void db_message_set_seq(uint64_t message_id, uint64_t seq)
4331 {
4332 Connection_T c; PreparedStatement_T st;
4333 c = db_con_get();
4334 TRY
4335 st = db_stmt_prepare(c, "UPDATE %s %smessages SET seq = ? WHERE message_idnr = ? "
4336 "AND seq < ?", db_get_sql(SQL_IGNORE), DBPFX);
4337 db_stmt_set_u64(st, 1, seq);
4338 db_stmt_set_u64(st, 2, message_id);
4339 db_stmt_set_u64(st, 3, seq);
4340 db_stmt_exec(st);
4341 CATCH(SQLException)
4342 LOG_SQLERROR;
4343 FINALLY
4344 db_con_close(c);
4345 END_TRY;
4346 }
4347
db_move_message(uint64_t message_id,uint64_t mailbox_id)4348 int db_move_message(uint64_t message_id, uint64_t mailbox_id)
4349 {
4350 return db_update("UPDATE %smessages SET mailbox_idnr = %" PRIu64 " WHERE message_idnr = %" PRIu64 "",
4351 DBPFX, mailbox_id, message_id);
4352 }
4353
db_rehash_store(void)4354 int db_rehash_store(void)
4355 {
4356 GList *ids = NULL;
4357 Connection_T c; PreparedStatement_T s; ResultSet_T r; volatile int t = FALSE;
4358 const char *buf;
4359 char hash[FIELDSIZE];
4360
4361 c = db_con_get();
4362 TRY
4363 r = db_query(c, "SELECT id FROM %smimeparts", DBPFX);
4364 while (db_result_next(r)) {
4365 uint64_t *id = g_new0(uint64_t,1);
4366 *id = db_result_get_u64(r, 0);
4367 ids = g_list_prepend(ids, id);
4368 }
4369 CATCH(SQLException)
4370 LOG_SQLERROR;
4371 t = DM_EQUERY;
4372 END_TRY;
4373
4374 if (t == DM_EQUERY) {
4375 db_con_close(c);
4376 return t;
4377 }
4378
4379 db_con_clear(c);
4380
4381 t = FALSE;
4382 ids = g_list_first(ids);
4383 TRY
4384 db_begin_transaction(c);
4385 while (ids) {
4386 uint64_t *id = ids->data;
4387
4388 db_con_clear(c);
4389 s = db_stmt_prepare(c, "SELECT data FROM %smimeparts WHERE id=?", DBPFX);
4390 db_stmt_set_u64(s,1, *id);
4391 r = db_stmt_query(s);
4392 db_result_next(r);
4393 buf = db_result_get(r, 0);
4394 memset(hash, 0, sizeof(hash));
4395 dm_get_hash_for_string(buf, hash);
4396
4397 db_con_clear(c);
4398 s = db_stmt_prepare(c, "UPDATE %smimeparts SET hash=? WHERE id=?", DBPFX);
4399 db_stmt_set_str(s, 1, hash);
4400 db_stmt_set_u64(s, 2, *id);
4401 db_stmt_exec(s);
4402
4403 if (! g_list_next(ids)) break;
4404 ids = g_list_next(ids);
4405 }
4406 db_commit_transaction(c);
4407 CATCH(SQLException)
4408 LOG_SQLERROR;
4409 db_rollback_transaction(c);
4410 t = DM_EQUERY;
4411 FINALLY
4412 db_con_close(c);
4413 END_TRY;
4414
4415 g_list_destroy(ids);
4416
4417 return t;
4418 }
4419
db_append_msg(const char * msgdata,uint64_t mailbox_idnr,uint64_t user_idnr,char * internal_date,uint64_t * msg_idnr,gboolean recent)4420 int db_append_msg(const char *msgdata, uint64_t mailbox_idnr, uint64_t user_idnr,
4421 char* internal_date, uint64_t * msg_idnr, gboolean recent)
4422 {
4423 DbmailMessage *message;
4424 int result;
4425
4426 if (! mailbox_is_writable(mailbox_idnr)) return DM_EQUERY;
4427
4428 message = dbmail_message_new(NULL);
4429 message = dbmail_message_init_with_string(message, msgdata);
4430 dbmail_message_set_internal_date(message, (char *)internal_date);
4431
4432 if (dbmail_message_store(message) < 0) {
4433 dbmail_message_free(message);
4434 return DM_EQUERY;
4435 }
4436
4437 result = db_copymsg(message->msg_idnr, mailbox_idnr, user_idnr, msg_idnr, recent);
4438 db_delete_message(message->msg_idnr);
4439 dbmail_message_free(message);
4440
4441 switch (result) {
4442 case -2:
4443 TRACE(TRACE_DEBUG, "error copying message to user [%" PRIu64 "],"
4444 "maxmail exceeded", user_idnr);
4445 return -2;
4446 case -1:
4447 TRACE(TRACE_ERR, "error copying message to user [%" PRIu64 "]",
4448 user_idnr);
4449 return -1;
4450 }
4451
4452 TRACE(TRACE_NOTICE, "message id=%" PRIu64 " is inserted", *msg_idnr);
4453
4454 return (db_set_message_status(*msg_idnr, MESSAGE_STATUS_SEEN)?FALSE:TRUE);
4455 }
4456
4457