smsd/services/sql.c

   1 /**
   2  * libsql database service
   3  *
   4  * Part of Gammu project
   5  *
   6  * Copyright (C) 2009-2011 Michal Čihař
   7  *           (c) 2010 Miloslav Semler
   8  *
   9  * Licensed under GNU GPL version 2 or later
  10  */
  11
  12 #define _XOPEN_SOURCE
  13 #define _BSD_SOURCE
  14 #include <time.h>
  15 #include <gammu.h>
  16 #include "../../helper/strptime.h"
  17
  18 #include <stdarg.h>
  19 #include <stdlib.h>
  20 #include <string.h>
  21 #include <stdio.h>
  22 #include <errno.h>
  23 #include <time.h>
  24 #include <assert.h>
  25 #ifdef WIN32
  26 #include <windows.h>
  27 #endif
  28
  29 #include "../core.h"
  30 #include "../../helper/string.h"
  31
  32 /**
  33  * Returns name of the SQL dialect to use.
  34  */
  35 const char *SMSDSQL_SQLName(GSM_SMSDConfig * Config)
  36 {
  37         if (Config->sql != NULL) {
  38                 return Config->sql;
  39         } else {
  40                 return Config->driver;
  41         }
  42 }
  43
  44 /* FIXME: I know this is broken, need to figure out better way */
  45 const char now_plus_odbc[] = "{fn CURRENT_TIMESTAMP()} + INTERVAL %d SECOND";
  46 const char now_plus_mysql[] = "(NOW() + INTERVAL %d SECOND) + 0";
  47 const char now_plus_pgsql[] = "now() + interval '%d seconds'";
  48 const char now_plus_sqlite[] = "datetime('now', '+%d seconds')";
  49 const char now_plus_freetds[] = "DATEADD('second', %d, CURRENT_TIMESTAMP)";
  50 const char now_plus_access[] = "now()+#00:00:%d#";
  51 const char now_plus_fallback[] = "NOW() + INTERVAL %d SECOND";
  52
  53
  54 static const char *SMSDSQL_NowPlus(GSM_SMSDConfig * Config, int seconds)
  55 {
  56         const char *driver_name;
  57         static char result[100];
  58
  59         driver_name = SMSDSQL_SQLName(Config);
  60
  61         if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
  62                 sprintf(result, now_plus_mysql, seconds);
  63         } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
  64                 sprintf(result, now_plus_pgsql, seconds);
  65         } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
  66                 sprintf(result, now_plus_sqlite, seconds);
  67         } else if (strcasecmp(driver_name, "freetds") == 0) {
  68                 sprintf(result, now_plus_freetds, seconds);
  69         } else if (strcasecmp(driver_name, "access") == 0) {
  70                 sprintf(result, now_plus_access, seconds);
  71         } else if (strcasecmp(driver_name, "odbc") == 0) {
  72                 sprintf(result, now_plus_odbc, seconds);
  73         } else {
  74                 sprintf(result, now_plus_fallback, seconds);
  75         }
  76         return result;
  77 }
  78
  79 const char escape_char_odbc[] = "";
  80 const char escape_char_mysql[] = "`";
  81 const char escape_char_pgsql[] = "\"";
  82 const char escape_char_sqlite[] = "";
  83 const char escape_char_freetds[] = "";
  84 const char escape_char_fallback[] = "";
  85
  86 static const char *SMSDSQL_EscapeChar(GSM_SMSDConfig * Config)
  87 {
  88         const char *driver_name;
  89
  90         driver_name = SMSDSQL_SQLName(Config);
  91
  92         if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
  93                 return escape_char_mysql;
  94         } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
  95                 return escape_char_pgsql;
  96         } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
  97                 return escape_char_sqlite;
  98         } else if (strcasecmp(driver_name, "freetds") == 0 || strcasecmp(driver_name, "mssql") == 0 || strcasecmp(driver_name, "sybase") == 0) {
  99                 return escape_char_freetds;
 100         } else if (strcasecmp(Config->driver, "odbc") == 0) {
 101                 return escape_char_odbc;
 102         } else {
 103                 return escape_char_fallback;
 104         }
 105 }
 106
 107 const char top_clause_access[] = "TOP";
 108 const char top_clause_fallback[] = "";
 109
 110 static const char *SMSDSQL_TopClause(GSM_SMSDConfig * Config, const char *count)
 111 {
 112         const char *driver_name;
 113         static char result[100];
 114
 115         driver_name = SMSDSQL_SQLName(Config);
 116
 117         if (strcasecmp(driver_name, "access") == 0) {
 118                 strcpy(result, top_clause_access);
 119                 strcat(result, " ");
 120                 strcat(result, count);
 121                 return result;
 122         } else {
 123                 return top_clause_fallback;
 124         }
 125 }
 126
 127 const char limit_clause_access[] = "";
 128 const char limit_clause_fallback[] = "LIMIT";
 129
 130 static const char *SMSDSQL_LimitClause(GSM_SMSDConfig * Config, const char *count)
 131 {
 132         const char *driver_name;
 133         static char result[100];
 134
 135         driver_name = SMSDSQL_SQLName(Config);
 136
 137         if (strcasecmp(driver_name, "access") == 0) {
 138                 return limit_clause_access;
 139         } else {
 140                 strcpy(result, limit_clause_fallback);
 141                 strcat(result, " ");
 142                 strcat(result, count);
 143                 return result;
 144         }
 145 }
 146
 147 const char now_odbc[] = "{fn CURRENT_TIMESTAMP()}";
 148 const char now_mysql[] = "NOW()";
 149 const char now_pgsql[] = "now()";
 150 const char now_sqlite[] = "datetime('now')";
 151 const char now_freetds[] = "CURRENT_TIMESTAMP";
 152 const char now_access[] = "now()";
 153 const char now_fallback[] = "NOW()";
 154
 155 const char currtime_odbc[] = "{fn CURTIME()}";
 156 const char currtime_mysql[] = "CURTIME()";
 157 const char currtime_pgsql[] = "localtime";
 158 const char currtime_sqlite[] = "time('now')";
 159 const char currtime_freetds[] = "CURRENT_TIME";
 160 const char currtime_fallback[] = "CURTIME()";
 161
 162 static const char *SMSDSQL_CurrentTime(GSM_SMSDConfig * Config)
 163 {
 164         const char *driver_name;
 165
 166         driver_name = SMSDSQL_SQLName(Config);
 167
 168         if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
 169                 return currtime_mysql;
 170         } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
 171                 return currtime_pgsql;
 172         } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
 173                 return currtime_sqlite;
 174         } else if (strcasecmp(driver_name, "freetds") == 0 || strcasecmp(driver_name, "mssql") == 0 || strcasecmp(driver_name, "sybase") == 0) {
 175                 return currtime_freetds;
 176         } else if (strcasecmp(Config->driver, "odbc") == 0) {
 177                 return currtime_odbc;
 178         } else {
 179                 return currtime_fallback;
 180         }
 181 }
 182 static const char *SMSDSQL_Now(GSM_SMSDConfig * Config)
 183 {
 184         const char *driver_name;
 185
 186         driver_name = SMSDSQL_SQLName(Config);
 187
 188         if (strcasecmp(driver_name, "mysql") == 0 || strcasecmp(driver_name, "native_mysql") == 0) {
 189                 return now_mysql;
 190         } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
 191                 return now_pgsql;
 192         } else if (strncasecmp(driver_name, "sqlite", 6) == 0) {
 193                 return now_sqlite;
 194         } else if (strcasecmp(driver_name, "freetds") == 0 || strcasecmp(driver_name, "mssql") == 0 || strcasecmp(driver_name, "sybase") == 0) {
 195                 return now_freetds;
 196         } else if (strcasecmp(Config->driver, "access") == 0) {
 197                 return now_access;
 198         } else if (strcasecmp(Config->driver, "odbc") == 0) {
 199                 return now_odbc;
 200         } else {
 201                 return now_fallback;
 202         }
 203 }
 204 static SQL_Error SMSDSQL_Query(GSM_SMSDConfig * Config, const char *query, SQL_result * res)
 205 {
 206         SQL_Error error = SQL_TIMEOUT;
 207         int attempts = 1;
 208         struct GSM_SMSDdbobj *db = Config->db;
 209
 210         for (attempts = 1; attempts <= Config->backend_retries; attempts++) {
 211                 SMSD_Log(DEBUG_SQL, Config, "Execute SQL: %s", query);
 212                 error = db->Query(Config, query, res);
 213                 if (error == SQL_OK) {
 214                         return error;
 215                 }
 216
 217                 if (error != SQL_TIMEOUT){
 218                         SMSD_Log(DEBUG_INFO, Config, "SQL failure: %d", error);
 219                         return error;
 220                 }
 221
 222                 SMSD_Log(DEBUG_INFO, Config, "SQL failed (timeout): %s", query);
 223                 /* We will try to reconnect */
 224                 SMSD_Log(DEBUG_INFO, Config, "reconnecting to database!");
 225                 while (error != SQL_OK && attempts < Config->backend_retries) {
 226                         SMSD_Log(DEBUG_INFO, Config, "Reconnecting after %d seconds...", attempts * attempts);
 227                         sleep(attempts * attempts);
 228                         db->Free(Config);
 229                         error = db->Connect(Config);
 230                         attempts++;
 231                 }
 232         }
 233         return error;
 234 }
 235
 236 void SMSDSQL_Time2String(GSM_SMSDConfig * Config, time_t timestamp, char *static_buff, size_t size)
 237 {
 238         struct tm *timestruct;
 239         const char *driver_name;
 240
 241         driver_name = SMSDSQL_SQLName(Config);
 242
 243         if (timestamp == -2) {
 244                 strcpy(static_buff, "0000-00-00 00:00:00");
 245         } else if (strcasecmp(driver_name, "pgsql") == 0 || strcasecmp(driver_name, "native_pgsql") == 0) {
 246                 timestruct = gmtime(&timestamp);
 247                 strftime(static_buff, size, "%Y-%m-%d %H:%M:%S GMT", timestruct);
 248         } else if (strcasecmp(driver_name, "access") == 0) {
 249                 timestruct = gmtime(&timestamp);
 250                 strftime(static_buff, size, "'%Y-%m-%d %H:%M:%S'", timestruct);
 251         } else if (strcasecmp(Config->driver, "odbc") == 0) {
 252                 timestruct = gmtime(&timestamp);
 253                 strftime(static_buff, size, "{ ts '%Y-%m-%d %H:%M:%S' }", timestruct);
 254         } else {
 255                 timestruct = localtime(&timestamp);
 256                 strftime(static_buff, size, "%Y-%m-%d %H:%M:%S", timestruct);
 257         }
 258 }
 259
 260 static SQL_Error SMSDSQL_NamedQuery(GSM_SMSDConfig * Config, const char *sql_query, GSM_SMSMessage *sms,
 261         const SQL_Var *params, SQL_result * res)
 262 {
 263         char buff[65536], *ptr, c, static_buff[8192];
 264         char *buffer2, *end;
 265         const char *to_print, *q = sql_query;
 266         int int_to_print;
 267         int numeric;
 268         int n, argc = 0;
 269         struct GSM_SMSDdbobj *db = Config->db;
 270
 271         if (params != NULL) {
 272                 while (params[argc].type != SQL_TYPE_NONE) argc++;
 273         }
 274
 275         ptr = buff;
 276
 277         do {
 278                 if (*q != '%') {
 279                         *ptr++ = *q;
 280                         continue;
 281                 }
 282                 c = *(++q);
 283                 if( c >= '0' && c <= '9'){
 284                         n = strtoul(q, &end, 10) - 1;
 285                         if (n < argc && n >= 0) {
 286                                 switch(params[n].type){
 287                                         case SQL_TYPE_INT:
 288                                                 ptr += sprintf(ptr, "%i", params[n].v.i);
 289                                                 break;
 290                                         case SQL_TYPE_STRING:
 291                                                 buffer2 = db->QuoteString(Config, params[n].v.s);
 292                                                 memcpy(ptr, buffer2, strlen(buffer2));
 293                                                 ptr += strlen(buffer2);
 294                                                 free(buffer2);
 295                                                 break;
 296                                         default:
 297                                                 SMSD_Log(DEBUG_ERROR, Config, "SQL: unknown type: %i (application bug) in query: `%s`", params[n].type, sql_query);
 298                                                 return SQL_BUG;
 299                                                 break;
 300                                 }
 301                         } else {
 302                                 SMSD_Log(DEBUG_ERROR, Config, "SQL: wrong number of parameter: %i (max %i) in query: `%s`", n+1, argc, sql_query);
 303                                 return SQL_BUG;
 304                         }
 305                         q = end - 1;
 306                         continue;
 307                 }
 308                 numeric = 0;
 309                 to_print = NULL;
 310                 switch (c) {
 311                         case 'I':
 312                                 to_print = Config->Status->IMEI;
 313                                 break;
 314                         case 'P':
 315                                 to_print = Config->PhoneID;
 316                                 break;
 317                         case 'N':
 318                                 snprintf(static_buff, sizeof(static_buff), "Gammu %s, %s, %s", GAMMU_VERSION, GetOS(), GetCompiler());
 319                                 to_print = static_buff;
 320                                 break;
 321                         case 'A':
 322                                 to_print = Config->CreatorID;
 323                                 break;
 324                         default:
 325                                 if (sms != NULL) {
 326                                         switch (c) {
 327                                                 case 'R':
 328                                                         EncodeUTF8(static_buff, sms->Number);
 329                                                         to_print = static_buff;
 330                                                         break;
 331                                                 case 'F':
 332                                                         EncodeUTF8(static_buff, sms->SMSC.Number);
 333                                                         to_print = static_buff;
 334                                                         break;
 335                                                 case 'u':
 336                                                         if (sms->UDH.Type != UDH_NoUDH) {
 337                                                                 EncodeHexBin(static_buff, sms->UDH.Text, sms->UDH.Length);
 338                                                                 to_print = static_buff;
 339                                                         }else{
 340                                                                 to_print = "";
 341                                                         }
 342                                                         break;
 343                                                 case 'x':
 344                                                         int_to_print =  sms->Class;
 345                                                         numeric = 1;
 346                                                         break;
 347                                                 case 'c':
 348                                                         to_print = GSM_SMSCodingToString(sms->Coding);
 349                                                         break;
 350                                                 case 't':
 351                                                         int_to_print =  sms->MessageReference;
 352                                                         numeric = 1;
 353                                                         break;
 354                                                 case 'E':
 355                                                         switch (sms->Coding) {
 356                                                                 case SMS_Coding_Unicode_No_Compression:
 357                                                                 case SMS_Coding_Default_No_Compression:
 358                                                                         EncodeHexUnicode(static_buff, sms->Text, UnicodeLength(sms->Text));
 359                                                                         break;
 360                                                                 case SMS_Coding_8bit:
 361                                                                         EncodeHexBin(static_buff, sms->Text, sms->Length);
 362                                                                         break;
 363                                                                 default:
 364                                                                         *static_buff = '\0';
 365                                                                         break;
 366                                                         }
 367                                                         to_print = static_buff;
 368                                                         break;
 369                                                 case 'T':
 370                                                         switch (sms->Coding) {
 371                                                                 case SMS_Coding_Unicode_No_Compression:
 372                                                                 case SMS_Coding_Default_No_Compression:
 373                                                                         EncodeUTF8(static_buff, sms->Text);
 374                                                                         to_print = static_buff;
 375                                                                         break;
 376                                                                 default:
 377                                                                         to_print = "";
 378                                                                         break;
 379                                                         }
 380                                                         break;
 381                                                 case 'V':
 382                                                         if (sms->SMSC.Validity.Format == SMS_Validity_RelativeFormat) {
 383                                                                 int_to_print = sms->SMSC.Validity.Relative;
 384                                                         } else {
 385                                                                 int_to_print =  -1;
 386                                                         }
 387                                                         numeric = 1;
 388                                                         break;
 389                                                 case 'C':
 390                                                         SMSDSQL_Time2String(Config, Fill_Time_T(sms->SMSCTime), static_buff, sizeof(static_buff));
 391                                                         to_print = static_buff;
 392                                                         break;
 393                                                 case 'd':
 394                                                         SMSDSQL_Time2String(Config, Fill_Time_T(sms->DateTime), static_buff, sizeof(static_buff));
 395                                                         to_print = static_buff;
 396                                                         break;
 397                                                 case 'e':
 398                                                         int_to_print = sms->DeliveryStatus;
 399                                                         numeric = 1;
 400                                                         break;
 401                                                 default:
 402                                                         SMSD_Log(DEBUG_ERROR, Config, "SQL: uexpected char '%c' in query: %s", c, sql_query);
 403                                                         return SQL_BUG;
 404
 405                                         } /* end of switch */
 406                                 } else {
 407                                         SMSD_Log(DEBUG_ERROR, Config, "Syntax error in query.. uexpected char '%c' in query: %s", c, sql_query);
 408                                         return SQL_BUG;
 409                                 }
 410                                 break;
 411                 } /* end of switch */
 412                 if (numeric) {
 413                         ptr += sprintf(ptr, "%i", int_to_print);
 414                 } else if (to_print != NULL) {
 415                         buffer2 = db->QuoteString(Config, to_print);
 416                         memcpy(ptr, buffer2, strlen(buffer2));
 417                         ptr += strlen(buffer2);
 418                         free(buffer2);
 419                 } else {
 420                         memcpy(ptr, "NULL", 4);
 421                         ptr += 4;
 422                 }
 423         } while (*(++q) != '\0');
 424         *ptr = '\0';
 425         return SMSDSQL_Query(Config, buff, res);
 426
 427 }
 428
 429 static GSM_Error SMSDSQL_CheckTable(GSM_SMSDConfig * Config, const char *table)
 430 {
 431         SQL_result res;
 432         char buffer[200];
 433         SQL_Error error;
 434         struct GSM_SMSDdbobj *db = Config->db;
 435         const char *escape_char;
 436
 437         escape_char = SMSDSQL_EscapeChar(Config);
 438
 439         sprintf(buffer, "SELECT %s %sID%s FROM %s %s", SMSDSQL_TopClause(Config, "1"), escape_char, escape_char, table, SMSDSQL_LimitClause(Config, "1"));
 440         error = SMSDSQL_Query(Config, buffer, &res);
 441         if (error != SQL_OK) {
 442                 SMSD_Log(DEBUG_ERROR, Config, "Table %s not found, disconnecting!", table);
 443                 db->Free(Config);
 444                 return ERR_UNKNOWN;
 445         }
 446         db->FreeResult(Config, &res);
 447         return ERR_NONE;
 448 }
 449
 450 /* Disconnects from a database */
 451 static GSM_Error SMSDSQL_Free(GSM_SMSDConfig * Config)
 452 {
 453         int i;
 454         SMSD_Log(DEBUG_SQL, Config, "Disconnecting from SQL database.");
 455         Config->db->Free(Config);
 456         /* free configuration */
 457         for(i = 0; i < SQL_QUERY_LAST_NO; i++){
 458                 free(Config->SMSDSQL_queries[i]);
 459                 Config->SMSDSQL_queries[i] = NULL;
 460         }
 461         return ERR_NONE;
 462 }
 463
 464 /* Connects to database */
 465 static GSM_Error SMSDSQL_Init(GSM_SMSDConfig * Config)
 466 {
 467         SQL_result res;
 468         int version;
 469         GSM_Error error;
 470         struct GSM_SMSDdbobj *db;
 471         const char *escape_char;
 472         char buffer[100];
 473
 474 #ifdef WIN32
 475         _tzset();
 476 #else
 477         tzset();
 478 #endif
 479
 480         db = Config->db;
 481
 482         if (db->Connect(Config) != SQL_OK)
 483                 return ERR_UNKNOWN;
 484
 485         error = SMSDSQL_CheckTable(Config, "outbox");
 486         if (error != ERR_NONE)
 487                 return error;
 488         error = SMSDSQL_CheckTable(Config, "outbox_multipart");
 489         if (error != ERR_NONE)
 490                 return error;
 491         error = SMSDSQL_CheckTable(Config, "sentitems");
 492         if (error != ERR_NONE)
 493                 return error;
 494         error = SMSDSQL_CheckTable(Config, "inbox");
 495         if (error != ERR_NONE)
 496                 return error;
 497
 498         escape_char = SMSDSQL_EscapeChar(Config);
 499
 500         sprintf(buffer, "SELECT %sVersion%s FROM gammu", escape_char, escape_char);
 501         if (SMSDSQL_Query(Config, buffer, &res) != SQL_OK) {
 502                 db->Free(Config);
 503                 return ERR_UNKNOWN;
 504         }
 505         if (db->NextRow(Config, &res) != 1) {
 506                 SMSD_Log(DEBUG_ERROR, Config, "Failed to seek to first row!");
 507                 db->FreeResult(Config, &res);
 508                 db->Free(Config);
 509                 return ERR_UNKNOWN;
 510         }
 511         version = db->GetNumber(Config, &res, 0);
 512         db->FreeResult(Config, &res);
 513         if (SMSD_CheckDBVersion(Config, version) != ERR_NONE) {
 514                 db->Free(Config);
 515                 return ERR_UNKNOWN;
 516         }
 517
 518         SMSD_Log(DEBUG_INFO, Config, "Connected to Database %s: %s on %s", Config->driver, Config->database, Config->host);
 519
 520         return ERR_NONE;
 521 }
 522
 523 static GSM_Error SMSDSQL_InitAfterConnect(GSM_SMSDConfig * Config)
 524 {
 525         SQL_result res;
 526         struct GSM_SMSDdbobj *db = Config->db;
 527         SQL_Var vars[3] = {{SQL_TYPE_STRING, {NULL}}, {SQL_TYPE_STRING, {NULL}}, {SQL_TYPE_NONE, {NULL}}};
 528
 529         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_DELETE_PHONE], NULL, NULL, &res) != SQL_OK) {
 530                 SMSD_Log(DEBUG_INFO, Config, "Error deleting from database (%s)", __FUNCTION__);
 531                 return ERR_UNKNOWN;
 532         }
 533         db->FreeResult(Config, &res);
 534
 535         SMSD_Log(DEBUG_INFO, Config, "Inserting phone info");
 536         vars[0].v.s = Config->enable_send ? "yes" : "no";
 537         vars[1].v.s = Config->enable_receive ? "yes" : "no";
 538
 539         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_INSERT_PHONE], NULL, vars, &res) != SQL_OK) {
 540                 SMSD_Log(DEBUG_INFO, Config, "Error inserting into database (%s)", __FUNCTION__);
 541                 return ERR_UNKNOWN;
 542         }
 543         db->FreeResult(Config, &res);
 544
 545         return ERR_NONE;
 546 }
 547
 548 /* Save SMS from phone (called Inbox sms - it's in phone Inbox) somewhere */
 549 static GSM_Error SMSDSQL_SaveInboxSMS(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char **Locations)
 550 {
 551         SQL_result res, res2;
 552         SQL_Var vars[3];
 553         struct GSM_SMSDdbobj *db = Config->db;
 554         const char *q, *status;
 555
 556         char smstext[3 * GSM_MAX_SMS_LENGTH + 1];
 557         char destinationnumber[3 * GSM_MAX_NUMBER_LENGTH + 1];
 558         char smsc_message[3 * GSM_MAX_NUMBER_LENGTH + 1];
 559         int i;
 560         time_t t_time1, t_time2;
 561         gboolean found;
 562         long diff;
 563         unsigned long long new_id;
 564         size_t locations_size = 0, locations_pos = 0;
 565         const char *state, *smsc;
 566
 567         *Locations = NULL;
 568
 569         for (i = 0; i < sms->Number; i++) {
 570                 EncodeUTF8(destinationnumber, sms->SMS[i].Number);
 571                 EncodeUTF8(smsc_message, sms->SMS[i].SMSC.Number);
 572                 if (sms->SMS[i].PDU == SMS_Status_Report) {
 573                         EncodeUTF8(smstext, sms->SMS[i].Text);
 574                         SMSD_Log(DEBUG_INFO, Config, "Delivery report: %s to %s", smstext, destinationnumber);
 575
 576                         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_SELECT], &sms->SMS[i], NULL, &res) != SQL_OK) {
 577                                 SMSD_Log(DEBUG_INFO, Config, "Error reading from database (%s)", __FUNCTION__);
 578                                 return ERR_UNKNOWN;
 579                         }
 580
 581                         found = FALSE;
 582                         while (db->NextRow(Config, &res)) {
 583                                 smsc = db->GetString(Config, &res, 4);
 584                                 state = db->GetString(Config, &res, 1);
 585                                 SMSD_Log(DEBUG_NOTICE, Config, "Checking for delivery report, SMSC=%s, state=%s", smsc, state);
 586
 587                                 if (strcmp(smsc, smsc_message) != 0) {
 588                                         if (Config->skipsmscnumber[0] == 0 || strcmp(Config->skipsmscnumber, smsc)) {
 589                                                 continue;
 590                                         }
 591                                 }
 592
 593                                 if (strcmp(state, "SendingOK") == 0 || strcmp(state, "DeliveryPending") == 0) {
 594                                         t_time1 = db->GetDate(Config, &res, 2);
 595                                         if (t_time1 < 0) {
 596                                                 SMSD_Log(DEBUG_ERROR, Config, "Invalid SendingDateTime -1 for SMS TPMR=%i", sms->SMS[i].MessageReference);
 597                                                 return ERR_UNKNOWN;
 598                                         }
 599                                         t_time2 = Fill_Time_T(sms->SMS[i].DateTime);
 600                                         diff = t_time2 - t_time1;
 601
 602                                         if (diff > -Config->deliveryreportdelay && diff < Config->deliveryreportdelay) {
 603                                                 found = TRUE;
 604                                                 break;
 605                                         } else {
 606                                                 SMSD_Log(DEBUG_NOTICE, Config,
 607                                                          "Delivery report would match, but time delta is too big (%ld), consider increasing DeliveryReportDelay", diff);
 608                                         }
 609                                 }
 610                         }
 611
 612                         if (found) {
 613                                 if (!strcmp(smstext, "Delivered")) {
 614                                         q = Config->SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_UPDATE_DELIVERED];
 615                                 } else {
 616                                         q = Config->SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_UPDATE];
 617                                 }
 618
 619                                 if (!strcmp(smstext, "Delivered")) {
 620                                         status = "DeliveryOK";
 621                                 } else if (!strcmp(smstext, "Failed")) {
 622                                         status = "DeliveryFailed";
 623                                 } else if (!strcmp(smstext, "Pending")) {
 624                                         status = "DeliveryPending";
 625                                 } else if (!strcmp(smstext, "Unknown")) {
 626                                         status = "DeliveryUnknown";
 627                                 } else {
 628                                         status = "";
 629                                 }
 630
 631                                 vars[0].type = SQL_TYPE_STRING;
 632                                 vars[0].v.s = status;                   /* Status */
 633                                 vars[1].type = SQL_TYPE_INT;
 634                                 vars[1].v.i = (long)db->GetNumber(Config, &res, 0); /* ID */
 635                                 vars[2].type = SQL_TYPE_NONE;
 636
 637                                 if (SMSDSQL_NamedQuery(Config, q, &sms->SMS[i], vars, &res2) != SQL_OK) {
 638                                         SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
 639                                         return ERR_UNKNOWN;
 640                                 }
 641                                 db->FreeResult(Config, &res2);
 642                         }
 643                         db->FreeResult(Config, &res);
 644                         continue;
 645                 }
 646
 647                 if (sms->SMS[i].PDU != SMS_Deliver)
 648                         continue;
 649
 650                 if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_SAVE_INBOX_SMS_INSERT], &sms->SMS[i], NULL, &res) != SQL_OK) {
 651                         SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
 652                         return ERR_UNKNOWN;
 653                 }
 654
 655                 new_id = db->SeqID(Config, "inbox_ID_seq");
 656                 if (new_id == 0) {
 657                         SMSD_Log(DEBUG_INFO, Config, "Failed to get inserted row ID (%s)", __FUNCTION__);
 658                         return ERR_UNKNOWN;
 659                 }
 660                 SMSD_Log(DEBUG_NOTICE, Config, "Inserted message id %lu", (long)new_id);
 661
 662                 db->FreeResult(Config, &res);
 663
 664                 if (new_id != 0) {
 665                         if (locations_pos + 10 >= locations_size) {
 666                                 locations_size += 40;
 667                                 *Locations = (char *)realloc(*Locations, locations_size);
 668                                 assert(*Locations != NULL);
 669                                 if (locations_pos == 0) {
 670                                         *Locations[0] = 0;
 671                                 }
 672                         }
 673                         locations_pos += sprintf((*Locations) + locations_pos, "%lu ", (long)new_id);
 674                 }
 675
 676                 if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_UPDATE_RECEIVED], &sms->SMS[i], NULL, &res2) != SQL_OK) {
 677                         SMSD_Log(DEBUG_INFO, Config, "Error updating number of received messages (%s)", __FUNCTION__);
 678                         return ERR_UNKNOWN;
 679                 }
 680                 db->FreeResult(Config, &res2);
 681
 682         }
 683
 684         return ERR_NONE;
 685 }
 686
 687 static GSM_Error SMSDSQL_RefreshSendStatus(GSM_SMSDConfig * Config, char *ID)
 688 {
 689         SQL_result res;
 690         struct GSM_SMSDdbobj *db = Config->db;
 691         SQL_Var vars[2] = {
 692                 {SQL_TYPE_STRING, {ID}},
 693                 {SQL_TYPE_NONE, {NULL}}};
 694
 695         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_REFRESH_SEND_STATUS], NULL, vars, &res) != SQL_OK) {
 696                 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
 697                 return ERR_UNKNOWN;
 698         }
 699
 700         if (db->AffectedRows(Config, &res) == 0) {
 701                 db->FreeResult(Config, &res);
 702                 return ERR_UNKNOWN;
 703         }
 704
 705         db->FreeResult(Config, &res);
 706         return ERR_NONE;
 707 }
 708
 709 /* Find one multi SMS to sending and return it (or return ERR_EMPTY)
 710  * There is also set ID for SMS
 711  */
 712 static GSM_Error SMSDSQL_FindOutboxSMS(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char *ID)
 713 {
 714         SQL_result res;
 715         struct GSM_SMSDdbobj *db = Config->db;
 716         int i;
 717         time_t timestamp;
 718         const char *coding;
 719         const char *text;
 720         size_t text_len;
 721         const char *text_decoded;
 722         const char *destination;
 723         const char *udh;
 724         const char *q;
 725         size_t udh_len;
 726         SQL_Var vars[3];
 727
 728         vars[0].type = SQL_TYPE_INT;
 729         vars[0].v.i = 1;
 730         vars[1].type = SQL_TYPE_NONE;
 731
 732         while (TRUE) {
 733                 if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_FIND_OUTBOX_SMS_ID], NULL, vars, &res) != SQL_OK) {
 734                         SMSD_Log(DEBUG_INFO, Config, "Error reading from database (%s)", __FUNCTION__);
 735                         return ERR_UNKNOWN;
 736                 }
 737
 738                 if (db->NextRow(Config, &res) != 1) {
 739                         db->FreeResult(Config, &res);
 740                         return ERR_EMPTY;
 741                 }
 742
 743                 sprintf(ID, "%ld", (long)db->GetNumber(Config, &res, 0));
 744                 timestamp = db->GetDate(Config, &res, 1);
 745
 746                 db->FreeResult(Config, &res);
 747
 748                 if (timestamp == -1) {
 749                         SMSD_Log(DEBUG_INFO, Config, "Invalid date for InsertIntoDB.");
 750                         return ERR_UNKNOWN;
 751                 }
 752
 753                 SMSDSQL_Time2String(Config, timestamp, Config->DT, sizeof(Config->DT));
 754                 if (SMSDSQL_RefreshSendStatus(Config, ID) == ERR_NONE) {
 755                         break;
 756                 }
 757         }
 758
 759         sms->Number = 0;
 760         for (i = 0; i < GSM_MAX_MULTI_SMS; i++) {
 761                 GSM_SetDefaultSMSData(&sms->SMS[i]);
 762                 sms->SMS[i].SMSC.Number[0] = 0;
 763                 sms->SMS[i].SMSC.Number[1] = 0;
 764         }
 765
 766         for (i = 1; i < GSM_MAX_MULTI_SMS + 1; i++) {
 767                 vars[0].type = SQL_TYPE_STRING;
 768                 vars[0].v.s = ID;
 769                 vars[1].type = SQL_TYPE_INT;
 770                 vars[1].v.i = i;
 771                 vars[2].type = SQL_TYPE_NONE;
 772                 if (i == 1) {
 773                         q = Config->SMSDSQL_queries[SQL_QUERY_FIND_OUTBOX_BODY];
 774                 } else {
 775                         q = Config->SMSDSQL_queries[SQL_QUERY_FIND_OUTBOX_MULTIPART];
 776                 }
 777                 if (SMSDSQL_NamedQuery(Config, q, NULL, vars, &res) != SQL_OK) {
 778                         SMSD_Log(DEBUG_ERROR, Config, "Error reading from database (%s)", __FUNCTION__);
 779                         return ERR_UNKNOWN;
 780                 }
 781
 782                 if (db->NextRow(Config, &res) != 1) {
 783                         db->FreeResult(Config, &res);
 784                         return ERR_NONE;
 785                 }
 786
 787                 coding = db->GetString(Config, &res, 1);
 788                 text = db->GetString(Config, &res, 0);
 789                 if (text == NULL) {
 790                         text_len = 0;
 791                 } else {
 792                         text_len = strlen(text);
 793                 }
 794                 text_decoded = db->GetString(Config, &res, 4);
 795                 udh = db->GetString(Config, &res, 2);
 796                 if (udh == NULL) {
 797                         udh_len = 0;
 798                 } else {
 799                         udh_len = strlen(udh);
 800                 }
 801
 802                 sms->SMS[sms->Number].Coding = GSM_StringToSMSCoding(coding);
 803                 if (sms->SMS[sms->Number].Coding == 0) {
 804                         if (text == NULL || text_len == 0) {
 805                                 SMSD_Log(DEBUG_NOTICE, Config, "Assuming default coding for text message");
 806                                 sms->SMS[sms->Number].Coding = SMS_Coding_Default_No_Compression;
 807                         } else {
 808                                 SMSD_Log(DEBUG_NOTICE, Config, "Assuming 8bit coding for binary message");
 809                                 sms->SMS[sms->Number].Coding = SMS_Coding_8bit;
 810                         }
 811                 }
 812
 813                 if (text == NULL || text_len == 0) {
 814                         if (text_decoded == NULL) {
 815                                 SMSD_Log(DEBUG_ERROR, Config, "Message without text!");
 816                                 return ERR_UNKNOWN;
 817                         } else {
 818                                 SMSD_Log(DEBUG_NOTICE, Config, "Message: %s", text_decoded);
 819                                 DecodeUTF8(sms->SMS[sms->Number].Text, text_decoded, strlen(text_decoded));
 820                         }
 821                 } else {
 822                         switch (sms->SMS[sms->Number].Coding) {
 823                                 case SMS_Coding_Unicode_No_Compression:
 824
 825                                 case SMS_Coding_Default_No_Compression:
 826                                         DecodeHexUnicode(sms->SMS[sms->Number].Text, text, text_len);
 827                                         break;
 828
 829                                 case SMS_Coding_8bit:
 830                                         DecodeHexBin(sms->SMS[sms->Number].Text, text, text_len);
 831                                         sms->SMS[sms->Number].Length = text_len / 2;
 832                                         break;
 833
 834                                 default:
 835                                         break;
 836                         }
 837                 }
 838
 839                 if (i == 1) {
 840                         destination = db->GetString(Config, &res, 6);
 841                         if (destination == NULL) {
 842                                 SMSD_Log(DEBUG_ERROR, Config, "Message without recipient!");
 843                                 return ERR_UNKNOWN;
 844                         }
 845                         DecodeUTF8(sms->SMS[sms->Number].Number, destination, strlen(destination));
 846                 } else {
 847                         CopyUnicodeString(sms->SMS[sms->Number].Number, sms->SMS[0].Number);
 848                 }
 849
 850                 sms->SMS[sms->Number].UDH.Type = UDH_NoUDH;
 851                 if (udh != NULL && udh_len != 0) {
 852                         sms->SMS[sms->Number].UDH.Type = UDH_UserUDH;
 853                         sms->SMS[sms->Number].UDH.Length = udh_len / 2;
 854                         DecodeHexBin(sms->SMS[sms->Number].UDH.Text, udh, udh_len);
 855                 }
 856
 857                 sms->SMS[sms->Number].Class = db->GetNumber(Config, &res, 3);
 858                 sms->SMS[sms->Number].PDU = SMS_Submit;
 859                 sms->Number++;
 860
 861                 if (i == 1) {
 862                         strcpy(Config->CreatorID, db->GetString(Config, &res, 10));
 863                         Config->relativevalidity = db->GetNumber(Config, &res, 8);
 864
 865                         Config->currdeliveryreport = db->GetBool(Config, &res, 9);
 866
 867                         /* Is this a multipart message? */
 868                         if (!db->GetBool(Config, &res, 7)) {
 869                                 db->FreeResult(Config, &res);
 870                                 break;
 871                         }
 872
 873                 }
 874                 db->FreeResult(Config, &res);
 875         }
 876
 877         return ERR_NONE;
 878 }
 879
 880 /* After sending SMS is moved to Sent Items or Error Items. */
 881 static GSM_Error SMSDSQL_MoveSMS(GSM_MultiSMSMessage * sms UNUSED, GSM_SMSDConfig * Config, char *ID, gboolean alwaysDelete UNUSED, gboolean sent UNUSED)
 882 {
 883         SQL_result res;
 884         SQL_Var vars[2];
 885         struct GSM_SMSDdbobj *db = Config->db;
 886
 887         vars[0].type = SQL_TYPE_STRING;
 888         vars[0].v.s = ID;
 889         vars[1].type = SQL_TYPE_NONE;
 890
 891         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_DELETE_OUTBOX], NULL, vars, &res) != SQL_OK) {
 892                 SMSD_Log(DEBUG_INFO, Config, "Error deleting from database (%s)", __FUNCTION__);
 893                 return ERR_UNKNOWN;
 894         }
 895         db->FreeResult(Config, &res);
 896
 897         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_DELETE_OUTBOX_MULTIPART], NULL, vars, &res) != SQL_OK) {
 898                 SMSD_Log(DEBUG_INFO, Config, "Error deleting from database (%s)", __FUNCTION__);
 899                 return ERR_UNKNOWN;
 900         }
 901         db->FreeResult(Config, &res);
 902
 903         return ERR_NONE;
 904 }
 905
 906 /* Adds SMS to Outbox */
 907 static GSM_Error SMSDSQL_CreateOutboxSMS(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char *NewID)
 908 {
 909         char creator[200];
 910         int i;
 911         unsigned int ID = 0;
 912         SQL_result res;
 913         SQL_Var vars[6];
 914         struct GSM_SMSDdbobj *db = Config->db;
 915         const char *report, *multipart, *q;
 916
 917         sprintf(creator, "Gammu %s",GAMMU_VERSION); /* %1 */
 918         multipart = (sms->Number == 1) ? "FALSE" : "TRUE"; /* %3 */
 919
 920         for (i = 0; i < sms->Number; i++) {
 921                 report = (sms->SMS[i].PDU == SMS_Status_Report) ? "yes": "default"; /* %2 */
 922                 if (i == 0) {
 923                         q = Config->SMSDSQL_queries[SQL_QUERY_CREATE_OUTBOX];
 924                 } else {
 925                         q = Config->SMSDSQL_queries[SQL_QUERY_CREATE_OUTBOX_MULTIPART];
 926                 }
 927
 928                 vars[0].type = SQL_TYPE_STRING;
 929                 vars[0].v.s = creator;
 930                 vars[1].type = SQL_TYPE_STRING;
 931                 vars[1].v.s = report;
 932                 vars[2].type = SQL_TYPE_STRING;
 933                 vars[2].v.s = multipart;
 934                 vars[3].type = SQL_TYPE_INT;
 935                 vars[3].v.i = i+1;
 936                 vars[4].type = SQL_TYPE_INT;
 937                 vars[4].v.i = ID;
 938                 vars[5].type = SQL_TYPE_NONE;
 939
 940                 if (SMSDSQL_NamedQuery(Config, q, &sms->SMS[i], vars, &res) != SQL_OK) {
 941                         SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
 942                         return ERR_UNKNOWN;
 943                 }
 944                 if (i == 0) {
 945                         ID = db->SeqID(Config, "outbox_ID_seq");
 946                         if (ID == 0) {
 947                                 SMSD_Log(DEBUG_INFO, Config, "Failed to get inserted row ID (%s)", __FUNCTION__);
 948                                 return ERR_UNKNOWN;
 949                         }
 950                 }
 951                 db->FreeResult(Config, &res);
 952         }
 953         SMSD_Log(DEBUG_INFO, Config, "Written message with ID %u", ID);
 954         if (NewID != NULL)
 955                 sprintf(NewID, "%d", ID);
 956         return ERR_NONE;
 957 }
 958
 959 static GSM_Error SMSDSQL_AddSentSMSInfo(GSM_MultiSMSMessage * sms, GSM_SMSDConfig * Config, char *ID, int Part, GSM_SMSDSendingError err, int TPMR)
 960 {
 961         SQL_result res;
 962         struct GSM_SMSDdbobj *db = Config->db;
 963
 964         const char *message_state;
 965         SQL_Var vars[6];
 966         char smsc[GSM_MAX_NUMBER_LENGTH + 1];
 967         char destination[GSM_MAX_NUMBER_LENGTH + 1];
 968
 969         EncodeUTF8(smsc, sms->SMS[Part - 1].SMSC.Number);
 970         EncodeUTF8(destination, sms->SMS[Part - 1].Number);
 971
 972         if (err == SMSD_SEND_OK) {
 973                 SMSD_Log(DEBUG_NOTICE, Config, "Transmitted %s (%s: %i) to %s", Config->SMSID,
 974                          (Part == sms->Number ? "total" : "part"), Part, DecodeUnicodeString(sms->SMS[0].Number));
 975         }
 976
 977         if (err == SMSD_SEND_OK) {
 978                 if (sms->SMS[Part - 1].PDU == SMS_Status_Report) {
 979                         message_state = "SendingOK";
 980                 } else {
 981                         message_state = "SendingOKNoReport";
 982                 }
 983         } else if (err == SMSD_SEND_SENDING_ERROR) {
 984                 message_state = "SendingError";
 985         } else if (err == SMSD_SEND_ERROR) {
 986                 message_state = "Error";
 987         } else {
 988                 SMSD_Log(DEBUG_INFO, Config, "Unknown SMS state: %d, assuming Error", err);
 989                 message_state = "Error";
 990         }
 991
 992         /* 1 = ID, 2 = SequencePosition, 3 = Status, 4 = TPMR, 5 = insertintodb */
 993         vars[0].type = SQL_TYPE_STRING;
 994         vars[0].v.s = ID;
 995         vars[1].type = SQL_TYPE_INT;
 996         vars[1].v.i = Part;
 997         vars[2].type = SQL_TYPE_STRING;
 998         vars[2].v.s = message_state;
 999         vars[3].type = SQL_TYPE_INT;
1000         vars[3].v.i = TPMR;
1001         vars[4].type = SQL_TYPE_STRING;
1002         vars[4].v.s = Config->DT;
1003         vars[5].type = SQL_TYPE_NONE;
1004
1005         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_ADD_SENT_INFO], &sms->SMS[Part - 1], vars, &res) != SQL_OK) {
1006                 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
1007                 return ERR_UNKNOWN;
1008         }
1009         db->FreeResult(Config, &res);
1010
1011         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_UPDATE_SENT], &sms->SMS[Part - 1], NULL, &res) != SQL_OK) {
1012                 SMSD_Log(DEBUG_INFO, Config, "Error updating number of sent messages (%s)", __FUNCTION__);
1013                 return ERR_UNKNOWN;
1014         }
1015         db->FreeResult(Config, &res);
1016
1017         return ERR_NONE;
1018 }
1019
1020 static GSM_Error SMSDSQL_RefreshPhoneStatus(GSM_SMSDConfig * Config)
1021 {
1022         SQL_result res;
1023         SQL_Var vars[3] = {
1024                 {SQL_TYPE_INT, {NULL}},
1025                 {SQL_TYPE_INT, {NULL}},
1026                 {SQL_TYPE_NONE, {NULL}}};
1027         struct GSM_SMSDdbobj *db = Config->db;
1028         vars[0].v.i = Config->Status->Charge.BatteryPercent;
1029         vars[1].v.i = Config->Status->Network.SignalPercent;
1030
1031         if (SMSDSQL_NamedQuery(Config, Config->SMSDSQL_queries[SQL_QUERY_REFRESH_PHONE_STATUS], NULL, vars, &res) != SQL_OK) {
1032                 SMSD_Log(DEBUG_INFO, Config, "Error writing to database (%s)", __FUNCTION__);
1033                 return ERR_UNKNOWN;
1034         }
1035         db->FreeResult(Config, &res);
1036
1037         return ERR_NONE;
1038 }
1039
1040 /*
1041  * better strcat... shows where is the bug
1042  */
1043 #define STRCAT_MAX 80
1044 GSM_Error SMSDSQL_option(GSM_SMSDConfig *Config, int optint, const char *option, ...)
1045 {
1046         size_t len[STRCAT_MAX], to_alloc = 0;
1047         int i, j;
1048         va_list ap;
1049         const char *arg;
1050         const char *args[STRCAT_MAX];
1051         char *buffer, *ptr;
1052
1053         /* read from config */
1054         buffer = INI_GetValue(Config->smsdcfgfile, "sql", option, FALSE);
1055         /* found? */
1056         if (buffer != NULL){
1057                 Config->SMSDSQL_queries[optint] = strdup(buffer); /* avoid to double free */
1058                 return ERR_NONE;
1059         }
1060
1061         /* not found.. we use default query */
1062         va_start(ap, option);
1063         for(i = 0; i < STRCAT_MAX; i++){
1064                 arg = va_arg(ap, const char *);
1065                 if (arg == NULL)
1066                         break;
1067                 len[i] = strlen(arg);
1068                 args[i] = arg;
1069                 to_alloc += len[i];
1070         }
1071         va_end(ap);
1072
1073         if (i == STRCAT_MAX) {
1074                 SMSD_Log(DEBUG_ERROR, Config, "STRCAT_MAX too small.. consider increase this value for option %s", option);
1075                 return ERR_UNKNOWN;
1076         }
1077
1078         buffer = malloc(to_alloc+1);
1079         if (buffer == NULL){
1080                 SMSD_Log(DEBUG_ERROR, Config, "Insufficient memory problem for option %s", option);
1081                 return ERR_UNKNOWN;
1082         }
1083         ptr = buffer;
1084         for (j = 0; j < i; j++) {
1085                 memcpy(ptr, args[j], len[j]);
1086                 ptr += len[j];
1087         }
1088         *ptr = '\0';
1089         Config->SMSDSQL_queries[optint] = buffer;
1090         return ERR_NONE;
1091 }
1092
1093
1094 /**
1095  * Reads common options for database backends.
1096  */
1097 GSM_Error SMSDSQL_ReadConfiguration(GSM_SMSDConfig *Config)
1098 {
1099         int locktime;
1100         const char *escape_char;
1101
1102         Config->user = INI_GetValue(Config->smsdcfgfile, "smsd", "user", FALSE);
1103         if (Config->user == NULL) {
1104                 Config->user="root";
1105         }
1106
1107         Config->password = INI_GetValue(Config->smsdcfgfile, "smsd", "password", FALSE);
1108         if (Config->password == NULL) {
1109                 Config->password="";
1110         }
1111
1112         Config->host = INI_GetValue(Config->smsdcfgfile, "smsd", "host", FALSE);
1113         if (Config->host == NULL) {
1114                 /* Backward compatibility */
1115                 Config->host = INI_GetValue(Config->smsdcfgfile, "smsd", "pc", FALSE);
1116         }
1117         if (Config->host == NULL) {
1118                 Config->host="localhost";
1119         }
1120
1121         Config->database = INI_GetValue(Config->smsdcfgfile, "smsd", "database", FALSE);
1122         if (Config->database == NULL) {
1123                 Config->database="sms";
1124         }
1125
1126         Config->driverspath = INI_GetValue(Config->smsdcfgfile, "smsd", "driverspath", FALSE);
1127
1128         Config->sql = INI_GetValue(Config->smsdcfgfile, "smsd", "sql", FALSE);
1129
1130         Config->dbdir = INI_GetValue(Config->smsdcfgfile, "smsd", "dbdir", FALSE);
1131
1132         if (Config->driver == NULL) {
1133                 SMSD_Log(DEBUG_ERROR, Config, "No database driver selected. Must be native_mysql, native_pgsql, ODBC or DBI one.");
1134                 return ERR_UNKNOWN;
1135         }
1136
1137         Config->db = NULL;
1138 #ifdef HAVE_MYSQL_MYSQL_H
1139         if (!strcasecmp(Config->driver, "native_mysql")) {
1140                 Config->db = &SMSDMySQL;
1141         }
1142 #endif
1143 #ifdef HAVE_POSTGRESQL_LIBPQ_FE_H
1144         if (!strcasecmp(Config->driver, "native_pgsql")) {
1145                 Config->db = &SMSDPgSQL;
1146         }
1147 #endif
1148 #ifdef ODBC_FOUND
1149         if (!strcasecmp(Config->driver, "odbc")) {
1150                 Config->db = &SMSDODBC;
1151                 if (Config->sql == NULL) {
1152                         SMSD_Log(DEBUG_INFO, Config, "Using generic SQL for ODBC, this might fail. In such case please set SQL configuration option.");
1153                 }
1154         }
1155 #endif
1156         if (Config->db == NULL) {
1157 #ifdef LIBDBI_FOUND
1158                 Config->db = &SMSDDBI;
1159 #else
1160                 SMSD_Log(DEBUG_ERROR, Config, "Unknown DB driver");
1161                 return ERR_UNKNOWN;
1162 #endif
1163         }
1164
1165         escape_char = SMSDSQL_EscapeChar(Config);
1166 #define ESCAPE_FIELD(x) escape_char, x, escape_char
1167
1168         locktime = Config->loopsleep * 8; /* reserve 8 sec per message */
1169         locktime = locktime < 60 ? 60 : locktime; /* Minimum time reserve is 60 sec */
1170
1171         if (SMSDSQL_option(Config, SQL_QUERY_DELETE_PHONE, "delete_phone",
1172                 "DELETE FROM phones WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1173                 return ERR_UNKNOWN;
1174         }
1175
1176         if (SMSDSQL_option(Config, SQL_QUERY_INSERT_PHONE, "insert_phone",
1177                 "INSERT INTO phones (",
1178                         ESCAPE_FIELD("IMEI"),
1179                         ", ", ESCAPE_FIELD("ID"),
1180                         ", ", ESCAPE_FIELD("Send"),
1181                         ", ", ESCAPE_FIELD("Receive"),
1182                         ", ", ESCAPE_FIELD("InsertIntoDB"),
1183                         ", ", ESCAPE_FIELD("TimeOut"),
1184                         ", ", ESCAPE_FIELD("Client"),
1185                         ", ", ESCAPE_FIELD("Battery"),
1186                         ", ", ESCAPE_FIELD("Signal"),
1187                         ") VALUES (%I, %P, %1, %2, ",
1188                         SMSDSQL_Now(Config),
1189                         ", ",
1190                         SMSDSQL_NowPlus(Config, 10),
1191                         ", %N, -1, -1)", NULL) != ERR_NONE) {
1192                 return ERR_UNKNOWN;
1193         }
1194
1195         if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_SELECT, "save_inbox_sms_select",
1196                 "SELECT ",
1197                         ESCAPE_FIELD("ID"),
1198                         ", ", ESCAPE_FIELD("Status"),
1199                         ", ", ESCAPE_FIELD("SendingDateTime"),
1200                         ", ", ESCAPE_FIELD("DeliveryDateTime"),
1201                         ", ", ESCAPE_FIELD("SMSCNumber"), " "
1202                         "FROM sentitems WHERE ",
1203                         ESCAPE_FIELD("DeliveryDateTime"), " IS NULL AND ",
1204                         ESCAPE_FIELD("SenderID"), " = %P AND ",
1205                         ESCAPE_FIELD("TPMR"), " = %t AND ",
1206                         ESCAPE_FIELD("DestinationNumber"), " = %R", NULL) != ERR_NONE) {
1207                 return ERR_UNKNOWN;
1208         }
1209
1210         if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_UPDATE_DELIVERED, "save_inbox_sms_update_delivered",
1211                 "UPDATE sentitems "
1212                         "SET ", ESCAPE_FIELD("DeliveryDateTime"), " = %C"
1213                         ", ", ESCAPE_FIELD("Status"), " = %1"
1214                         ", ", ESCAPE_FIELD("StatusError"), " = %e"
1215                         " WHERE ", ESCAPE_FIELD("ID"), " = %2"
1216                         " AND ", ESCAPE_FIELD("TPMR"), " = %t", NULL) != ERR_NONE) {
1217                 return ERR_UNKNOWN;
1218         }
1219
1220         if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_UPDATE, "save_inbox_sms_update",
1221                 "UPDATE sentitems "
1222                         "SET ", ESCAPE_FIELD("Status"), " = %1"
1223                         ", ", ESCAPE_FIELD("StatusError"), " = %e"
1224                         " WHERE ", ESCAPE_FIELD("ID"), " = %2"
1225                         " AND ", ESCAPE_FIELD("TPMR"), " = %t", NULL) != ERR_NONE) {
1226                 return ERR_UNKNOWN;
1227         }
1228
1229         if (SMSDSQL_option(Config, SQL_QUERY_SAVE_INBOX_SMS_INSERT, "save_inbox_sms_insert",
1230                 "INSERT INTO inbox "
1231                         "(", ESCAPE_FIELD("ReceivingDateTime"),
1232                         ", ", ESCAPE_FIELD("Text"),
1233                         ", ", ESCAPE_FIELD("SenderNumber"),
1234                         ", ", ESCAPE_FIELD("Coding"),
1235                         ", ", ESCAPE_FIELD("SMSCNumber"),
1236                         ", ", ESCAPE_FIELD("UDH"),
1237                         ", ", ESCAPE_FIELD("Class"),
1238                         ", ", ESCAPE_FIELD("TextDecoded"),
1239                         ", ", ESCAPE_FIELD("RecipientID"), ")"
1240                         " VALUES (%d, %E, %R, %c, %F, %u, %x, %T, %P)", NULL) != ERR_NONE) {
1241                 return ERR_UNKNOWN;
1242         }
1243
1244         if (SMSDSQL_option(Config, SQL_QUERY_UPDATE_RECEIVED, "update_received",
1245                 "UPDATE phones SET ",
1246                         ESCAPE_FIELD("Received"), " = ", ESCAPE_FIELD("Received"), " + 1"
1247                         " WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1248                 return ERR_UNKNOWN;
1249         }
1250
1251         if (SMSDSQL_option(Config, SQL_QUERY_REFRESH_SEND_STATUS, "refresh_send_status",
1252                 "UPDATE outbox SET ",
1253                         ESCAPE_FIELD("SendingTimeOut"), " = ", SMSDSQL_NowPlus(Config, locktime),
1254                         " WHERE ", ESCAPE_FIELD("ID"), " = %1"
1255                         " AND (", ESCAPE_FIELD("SendingTimeOut"), " < ", SMSDSQL_Now(Config),
1256                         " OR ", ESCAPE_FIELD("SendingTimeOut"), " IS NULL)", NULL) != ERR_NONE) {
1257                 return ERR_UNKNOWN;
1258         }
1259
1260         if (SMSDSQL_option(Config, SQL_QUERY_FIND_OUTBOX_SMS_ID, "find_outbox_sms_id",
1261                 "SELECT ", SMSDSQL_TopClause(Config, "%1"),
1262                         ESCAPE_FIELD("ID"),
1263                         ", ", ESCAPE_FIELD("InsertIntoDB"),
1264                         ", ", ESCAPE_FIELD("SendingDateTime"),
1265                         ", ", ESCAPE_FIELD("SenderID"),
1266                         " FROM outbox WHERE ",
1267                         ESCAPE_FIELD("SendingDateTime"), " < ", SMSDSQL_Now(Config),
1268                         " AND ", ESCAPE_FIELD("SendingTimeOut"), " < ", SMSDSQL_Now(Config),
1269                         " AND ", ESCAPE_FIELD("SendBefore"), " >= ", SMSDSQL_CurrentTime(Config),
1270                         " AND ", ESCAPE_FIELD("SendAfter"), " <= ", SMSDSQL_CurrentTime(Config),
1271                         " AND ( ", ESCAPE_FIELD("SenderID"), " is NULL OR ", ESCAPE_FIELD("SenderID"), " = '' OR ", ESCAPE_FIELD("SenderID"), " = %P )"
1272                         " ORDER BY ", ESCAPE_FIELD("InsertIntoDB"), " ASC ", SMSDSQL_LimitClause(Config, "%1"), NULL) != ERR_NONE) {
1273                 return ERR_UNKNOWN;
1274         }
1275
1276         if (SMSDSQL_option(Config, SQL_QUERY_FIND_OUTBOX_BODY, "find_outbox_body",
1277                 "SELECT ",
1278                         ESCAPE_FIELD("Text"),
1279                         ", ", ESCAPE_FIELD("Coding"),
1280                         ", ", ESCAPE_FIELD("UDH"),
1281                         ", ", ESCAPE_FIELD("Class"),
1282                         ", ", ESCAPE_FIELD("TextDecoded"),
1283                         ", ", ESCAPE_FIELD("ID"),
1284                         ", ", ESCAPE_FIELD("DestinationNumber"),
1285                         ", ", ESCAPE_FIELD("MultiPart"),
1286                         ", ", ESCAPE_FIELD("RelativeValidity"),
1287                         ", ", ESCAPE_FIELD("DeliveryReport"),
1288                         ", ", ESCAPE_FIELD("CreatorID"),
1289                         " FROM outbox WHERE ",
1290                         ESCAPE_FIELD("ID"), "=%1", NULL) != ERR_NONE) {
1291                 return ERR_UNKNOWN;
1292         }
1293
1294         if (SMSDSQL_option(Config, SQL_QUERY_FIND_OUTBOX_MULTIPART, "find_outbox_multipart",
1295                 "SELECT ",
1296                         ESCAPE_FIELD("Text"),
1297                         ", ", ESCAPE_FIELD("Coding"),
1298                         ", ", ESCAPE_FIELD("UDH"),
1299                         ", ", ESCAPE_FIELD("Class"),
1300                         ", ", ESCAPE_FIELD("TextDecoded"),
1301                         ", ", ESCAPE_FIELD("ID"),
1302                         ", ", ESCAPE_FIELD("SequencePosition"),
1303                         " FROM outbox_multipart WHERE ",
1304                         ESCAPE_FIELD("ID"), "=%1 AND ",
1305                         ESCAPE_FIELD("SequencePosition"), "=%2", NULL) != ERR_NONE) {
1306                 return ERR_UNKNOWN;
1307         }
1308
1309         if (SMSDSQL_option(Config, SQL_QUERY_DELETE_OUTBOX, "delete_outbox",
1310                 "DELETE FROM outbox WHERE ", ESCAPE_FIELD("ID"), "=%1", NULL) != ERR_NONE) {
1311                 return ERR_UNKNOWN;
1312         }
1313
1314         if (SMSDSQL_option(Config, SQL_QUERY_DELETE_OUTBOX_MULTIPART, "delete_outbox_multipart",
1315                 "DELETE FROM outbox_multipart WHERE ", ESCAPE_FIELD("ID"), "=%1", NULL) != ERR_NONE) {
1316                 return ERR_UNKNOWN;
1317         }
1318
1319         if (SMSDSQL_option(Config, SQL_QUERY_CREATE_OUTBOX, "create_outbox",
1320                 "INSERT INTO outbox "
1321                         "(", ESCAPE_FIELD("CreatorID"),
1322                         ", ", ESCAPE_FIELD("SenderID"),
1323                         ", ", ESCAPE_FIELD("DeliveryReport"),
1324                         ", ", ESCAPE_FIELD("MultiPart"),
1325                         ", ", ESCAPE_FIELD("InsertIntoDB"),
1326                         ", ", ESCAPE_FIELD("Text"),
1327                         ", ", ESCAPE_FIELD("DestinationNumber"),
1328                         ", ", ESCAPE_FIELD("RelativeValidity"),
1329                         ", ", ESCAPE_FIELD("Coding"),
1330                         ", ", ESCAPE_FIELD("UDH"),
1331                         ", ", ESCAPE_FIELD("Class"),
1332                         ", ", ESCAPE_FIELD("TextDecoded"), ") VALUES "
1333                         "(%1, %P, %2, %3, ", SMSDSQL_Now(Config),
1334                         ", %E, %R, %V, %c, %u, %x, %T)", NULL) != ERR_NONE) {
1335                 return ERR_UNKNOWN;
1336         }
1337
1338         if (SMSDSQL_option(Config, SQL_QUERY_CREATE_OUTBOX_MULTIPART, "create_outbox_multipart",
1339                 "INSERT INTO outbox_multipart "
1340                         "(", ESCAPE_FIELD("SequencePosition"),
1341                         ", ", ESCAPE_FIELD("Text"),
1342                         ", ", ESCAPE_FIELD("Coding"),
1343                         ", ", ESCAPE_FIELD("UDH"),
1344                         ", ", ESCAPE_FIELD("Class"),
1345                         ", ", ESCAPE_FIELD("TextDecoded"),
1346                         ", ", ESCAPE_FIELD("ID"), ") VALUES (%4, %E, %c, %u, %x, %T, %5)", NULL) != ERR_NONE) {
1347                 return ERR_UNKNOWN;
1348         }
1349
1350         if (SMSDSQL_option(Config, SQL_QUERY_ADD_SENT_INFO, "add_sent_info",
1351                 "INSERT INTO sentitems "
1352                         "(", ESCAPE_FIELD("CreatorID"),
1353                         ", ", ESCAPE_FIELD("ID"),
1354                         ", ", ESCAPE_FIELD("SequencePosition"),
1355                         ", ", ESCAPE_FIELD("Status"),
1356                         ", ", ESCAPE_FIELD("SendingDateTime"),
1357                         ", ", ESCAPE_FIELD("SMSCNumber"),
1358                         ", ", ESCAPE_FIELD("TPMR"),
1359                         ", ", ESCAPE_FIELD("SenderID"),
1360                         ", ", ESCAPE_FIELD("Text"),
1361                         ", ", ESCAPE_FIELD("DestinationNumber"),
1362                         ", ", ESCAPE_FIELD("Coding"),
1363                         ", ", ESCAPE_FIELD("UDH"),
1364                         ", ", ESCAPE_FIELD("Class"),
1365                         ", ", ESCAPE_FIELD("TextDecoded"),
1366                         ", ", ESCAPE_FIELD("InsertIntoDB"),
1367                         ", ", ESCAPE_FIELD("RelativeValidity"),
1368                         ") "
1369                         " VALUES (%A, %1, %2, %3, ",
1370                         SMSDSQL_Now(Config),
1371                         ", %F, %4, %P, %E, %R, %c, %u, %x, %T, %5, %V)", NULL) != ERR_NONE) {
1372                 return ERR_UNKNOWN;
1373         }
1374
1375         if (SMSDSQL_option(Config, SQL_QUERY_UPDATE_SENT, "update_sent",
1376                 "UPDATE phones SET ",
1377                         ESCAPE_FIELD("Sent"), "= ", ESCAPE_FIELD("Sent"), " + 1"
1378                         " WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1379                 return ERR_UNKNOWN;
1380         }
1381
1382         if (SMSDSQL_option(Config, SQL_QUERY_REFRESH_PHONE_STATUS, "refresh_phone_status",
1383                 "UPDATE phones SET ",
1384                         ESCAPE_FIELD("TimeOut"), "= ", SMSDSQL_NowPlus(Config, 10),
1385                         ", ", ESCAPE_FIELD("Battery"), " = %1"
1386                         ", ", ESCAPE_FIELD("Signal"), " = %2"
1387                         " WHERE ", ESCAPE_FIELD("IMEI"), " = %I", NULL) != ERR_NONE) {
1388                 return ERR_UNKNOWN;
1389         }
1390 #undef ESCAPE_FIELD
1391
1392         return ERR_NONE;
1393 }
1394
1395 time_t SMSDSQL_ParseDate(GSM_SMSDConfig * Config, const char *date)
1396 {
1397         char *parse_res;
1398         struct tm timestruct;
1399         GSM_DateTime DT;
1400
1401         if (strcmp(date, "0000-00-00 00:00:00") == 0) {
1402                 return -2;
1403         }
1404
1405         parse_res = strptime(date, "%Y-%m-%d %H:%M:%S", &timestruct);
1406
1407         if (parse_res != NULL && *parse_res == 0) {
1408                 DT.Year = timestruct.tm_year + 1900;
1409                 DT.Month = timestruct.tm_mon + 1;
1410                 DT.Day = timestruct.tm_mday;
1411                 DT.Hour = timestruct.tm_hour;
1412                 DT.Minute = timestruct.tm_min;
1413                 DT.Second = timestruct.tm_sec;
1414
1415                 return Fill_Time_T(DT);
1416         }
1417         /* Used during testing */
1418         if (Config != NULL) {
1419                 SMSD_Log(DEBUG_ERROR, Config, "Failed to parse date: %s", date);
1420         }
1421         return -1;
1422 }
1423
1424 GSM_SMSDService SMSDSQL = {
1425         SMSDSQL_Init,
1426         SMSDSQL_Free,
1427         SMSDSQL_InitAfterConnect,
1428         SMSDSQL_SaveInboxSMS,
1429         SMSDSQL_FindOutboxSMS,
1430         SMSDSQL_MoveSMS,
1431         SMSDSQL_CreateOutboxSMS,
1432         SMSDSQL_AddSentSMSInfo,
1433         SMSDSQL_RefreshSendStatus,
1434         SMSDSQL_RefreshPhoneStatus,
1435         SMSDSQL_ReadConfiguration
1436 };
1437
1438 /* How should editor hadle tabs in this file? Add editor commands here.
1439  * vim: noexpandtab sw=8 ts=8 sts=8:
1440  */