Fixed sql query to manticore

Signed-off-by: Janos SUTO <sj@acts.hu>
mhoffmann75 · Dec 29, 2022 · 9cfe921 · 9cfe921
1 parent 092301e
commit 9cfe921
Show file tree

Hide file tree

Showing 7 changed files with 95 additions and 69 deletions.
diff --git a/src/config.h b/src/config.h
@@ -114,7 +114,6 @@
 #define SQL_PREPARED_STMT_GET_META_ID_BY_MESSAGE_ID  "SELECT id, piler_id FROM " SQL_METADATA_TABLE " WHERE message_id=?"
 #define SQL_PREPARED_STMT_INSERT_INTO_RCPT_TABLE     "INSERT INTO " SQL_RECIPIENT_TABLE " (`id`,`to`,`todomain`) VALUES(?,?,?)"
 #define SQL_PREPARED_STMT_INSERT_INTO_SPHINX_TABLE   "INSERT INTO " SQL_SPHINX_TABLE " (`id`, `from`, `to`, `fromdomain`, `todomain`, `subject`, `body`, `arrived`, `sent`, `size`, `direction`, `folder`, `attachments`, `attachment_types`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
-#define SQL_PREPARED_STMT_INSERT_INTO_RT_TABLE       "INSERT INTO piler1 (id, sender, rcpt, senderdomain, rcptdomain, subject, body, arrived, sent, size, direction, folder, attachments, attachment_types) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
 #define SQL_PREPARED_STMT_INSERT_INTO_META_TABLE     "INSERT INTO " SQL_METADATA_TABLE " (`from`,`fromdomain`,`subject`,`spam`,`arrived`,`sent`,`retained`,`size`,`hlen`,`direction`,`attachments`,`piler_id`,`message_id`,`reference`,`digest`,`bodydigest`,`vcode`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
 #define SQL_PREPARED_STMT_INSERT_INTO_ATTACHMENT_TABLE     "INSERT INTO " SQL_ATTACHMENT_TABLE " (`piler_id`,`attachment_id`,`sig`,`name`,`type`,`size`,`ptr`) VALUES(?,?,?,?,?,?,?)"
 #define SQL_PREPARED_STMT_GET_ATTACHMENT_ID_BY_SIGNATURE   "SELECT `id` FROM `" SQL_ATTACHMENT_TABLE "` WHERE `sig`=? AND `ptr`=0 AND `size`=?"

diff --git a/src/message.c b/src/message.c
@@ -31,12 +31,6 @@ int store_index_data(struct session_data *sdata, struct parser_state *state, str
    if(*subj == ' ') subj++;
 
 
-   if(cfg->rtindex){
-      if(prepare_sphx_statement(sdata, &sql, SQL_PREPARED_STMT_INSERT_INTO_RT_TABLE) == ERR) return rc;
-   } else {
-      if(prepare_sql_statement(sdata, &sql, SQL_PREPARED_STMT_INSERT_INTO_SPHINX_TABLE) == ERR) return rc;
-   }
-
    fix_email_address_for_sphinx(state->b_from);
    fix_email_address_for_sphinx(state->b_sender);
    fix_email_address_for_sphinx(state->b_to);
@@ -49,27 +43,76 @@ int store_index_data(struct session_data *sdata, struct parser_state *state, str
       sender_domain = state->b_sender_domain;
    }
 
-   p_bind_init(&sql);
+   if(cfg->rtindex){
+      // Manticore doesn't support prepared statements using sphinxQL
+      // so we have to go through a painful query assembly escaping
+      // the untrusted input
+      //
+      char a[4*MAXBUFSIZE+4*SMALLBUFSIZE];
+      char *query=NULL;
+
+      snprintf(a, sizeof(a)-1, "INSERT INTO %s (id, arrived, sent, size, direction, folder, attachments, attachment_types, senderdomain, rcptdomain, sender, rcpt, subject, body) VALUES (%llu, %ld, %ld, %d, %d, %d, %d, '%s', '%s', '%s', '", cfg->sphinxdb, id, sdata->now, sdata->sent, sdata->tot_len, sdata->direction, data->folder, state->n_attachments, sdata->attachments, sender_domain, state->b_to_domain);
+
+      int ret = append_string_to_buffer(&query, a);
+
+      unsigned long len = strlen(sender);
+      char *s = calloc(1, 2*len+1);
+      mysql_real_escape_string(&(sdata->sphx), s, sender, len);
+      ret += append_string_to_buffer(&query, s);
+      free(s);
+      ret += append_string_to_buffer(&query, "','");
+
+      len = strlen(state->b_to);
+      s = calloc(1, 2*len+1);
+      mysql_real_escape_string(&(sdata->sphx), s, state->b_to, len);
+      ret += append_string_to_buffer(&query, s);
+      free(s);
+      ret += append_string_to_buffer(&query, "','");
+
+      len = strlen(subj);
+      s = calloc(1, 2*len+1);
+      mysql_real_escape_string(&(sdata->sphx), s, subj, len);
+      ret += append_string_to_buffer(&query, s);
+      free(s);
+      ret += append_string_to_buffer(&query, "','");
+
+      len = strlen(state->b_body);
+      s = calloc(1, 2*len+1);
+      mysql_real_escape_string(&(sdata->sphx), s, state->b_body, len);
+      ret += append_string_to_buffer(&query, s);
+      free(s);
+      ret += append_string_to_buffer(&query, "')");
+
+      if(mysql_real_query(&(sdata->sphx), query, strlen(query)) == OK) rc = OK;
+      else syslog(LOG_PRIORITY, "ERROR: %s failed to store index data for id=%llu, errno=%d, append ret=%d", sdata->ttmpfile, id, mysql_errno(&(sdata->sphx)), ret);
+
+      free(query);
+   }
+   else {
+      if(prepare_sql_statement(sdata, &sql, SQL_PREPARED_STMT_INSERT_INTO_SPHINX_TABLE) == ERR) return rc;
 
-   sql.sql[sql.pos] = (char *)&id; sql.type[sql.pos] = TYPE_LONGLONG; sql.pos++;
-   sql.sql[sql.pos] = sender; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
-   sql.sql[sql.pos] = state->b_to; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
-   sql.sql[sql.pos] = sender_domain; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
-   sql.sql[sql.pos] = state->b_to_domain; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
-   sql.sql[sql.pos] = subj; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
-   sql.sql[sql.pos] = state->b_body; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
-   sql.sql[sql.pos] = (char *)&sdata->now; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
-   sql.sql[sql.pos] = (char *)&sdata->sent; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
-   sql.sql[sql.pos] = (char *)&sdata->tot_len; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
-   sql.sql[sql.pos] = (char *)&sdata->direction; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
-   sql.sql[sql.pos] = (char *)&data->folder; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
-   sql.sql[sql.pos] = (char *)&state->n_attachments; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
-   sql.sql[sql.pos] = sdata->attachments; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      p_bind_init(&sql);
 
-   if(p_exec_stmt(sdata, &sql) == OK) rc = OK;
-   else syslog(LOG_PRIORITY, "ERROR: %s failed to store index data for id=%llu, sql_errno=%d", sdata->ttmpfile, id, sdata->sql_errno);
+      sql.sql[sql.pos] = (char *)&id; sql.type[sql.pos] = TYPE_LONGLONG; sql.pos++;
+      sql.sql[sql.pos] = sender; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      sql.sql[sql.pos] = state->b_to; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      sql.sql[sql.pos] = sender_domain; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      sql.sql[sql.pos] = state->b_to_domain; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      sql.sql[sql.pos] = subj; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      sql.sql[sql.pos] = state->b_body; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+      sql.sql[sql.pos] = (char *)&sdata->now; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
+      sql.sql[sql.pos] = (char *)&sdata->sent; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
+      sql.sql[sql.pos] = (char *)&sdata->tot_len; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
+      sql.sql[sql.pos] = (char *)&sdata->direction; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
+      sql.sql[sql.pos] = (char *)&data->folder; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
+      sql.sql[sql.pos] = (char *)&state->n_attachments; sql.type[sql.pos] = TYPE_LONG; sql.pos++;
+      sql.sql[sql.pos] = sdata->attachments; sql.type[sql.pos] = TYPE_STRING; sql.pos++;
+
+      if(p_exec_stmt(sdata, &sql) == OK) rc = OK;
+      else syslog(LOG_PRIORITY, "ERROR: %s failed to store index data for id=%llu, sql_errno=%d", sdata->ttmpfile, id, sdata->sql_errno);
 
-   close_prepared_statement(&sql);
+      close_prepared_statement(&sql);
+   }
 
    return rc;
 }

diff --git a/src/misc.c b/src/misc.c
@@ -755,3 +755,29 @@ char *strcasestr(const char *s, const char *find){
    return((char*)s);
 }
 #endif
+
+
+int append_string_to_buffer(char **buffer, char *str){
+   int arglen;
+   char *s=NULL;
+
+   arglen = strlen(str);
+
+   if(!*buffer){
+      *buffer = malloc(arglen+1);
+      memset(*buffer, 0, arglen+1);
+      memcpy(*buffer, str, arglen);
+   }
+   else {
+      int len = strlen(*buffer);
+      s = realloc(*buffer, len + arglen+1);
+      if(!s) return 1;
+
+      *buffer = s;
+
+      memset(*buffer+len, 0, arglen+1);
+      memcpy(*buffer+len, str, arglen);
+   }
+
+   return 0;
+}
diff --git a/src/misc.h b/src/misc.h
@@ -54,4 +54,6 @@ int init_ssl_to_server(struct data *data);
    char *strcasestr(const char *s, const char *find);
 #endif
 
+int append_string_to_buffer(char **buffer, char *str);
+
 #endif /* _MISC_H */
diff --git a/src/mysql.c b/src/mysql.c
@@ -254,23 +254,6 @@ int prepare_sql_statement(struct session_data *sdata, struct sql *sql, char *s){
 }
 
 
-int prepare_sphx_statement(struct session_data *sdata, struct sql *sql, char *s){
-
-   sql->stmt = mysql_stmt_init(&(sdata->sphx));
-   if(!(sql->stmt)){
-      syslog(LOG_PRIORITY, "%s: error: mysql_stmt_init()", sdata->ttmpfile);
-      return ERR;
-   }
-
-   if(mysql_stmt_prepare(sql->stmt, s, strlen(s))){
-      syslog(LOG_PRIORITY, "%s: error: mysql_stmt_prepare() %s => sql: %s", sdata->ttmpfile, mysql_stmt_error(sql->stmt), s);
-      return ERR;
-   }
-
-   return OK;
-}
-
-
 void close_prepared_statement(struct sql *sql){
    if(sql->stmt) mysql_stmt_close(sql->stmt);
 }

diff --git a/src/pilerexport.c b/src/pilerexport.c
@@ -137,32 +137,6 @@ int append_email_to_buffer(char **buffer, char *email){
 }
 
 
-int append_string_to_buffer(char **buffer, char *str){
-   int arglen;
-   char *s=NULL;
-
-   arglen = strlen(str);
-
-   if(!*buffer){
-      *buffer = malloc(arglen+1);
-      memset(*buffer, 0, arglen+1);
-      memcpy(*buffer, str, arglen);
-   }
-   else {
-      int len = strlen(*buffer);
-      s = realloc(*buffer, len + arglen+1);
-      if(!s) return 1;
-
-      *buffer = s;
-
-      memset(*buffer+len, 0, arglen+1);
-      memcpy(*buffer+len, str, arglen);
-   }
-
-   return 0;
-}
-
-
 uint64 run_query(struct session_data *sdata, struct session_data *sdata2, char *where_condition, uint64 last_id, int *num, struct config *cfg){
    MYSQL_ROW row;
    uint64 id=0;

diff --git a/src/sql.h b/src/sql.h
@@ -11,7 +11,6 @@ int open_sphx(struct session_data *sdata, struct config *cfg);
 void close_database(struct session_data *sdata);
 void close_sphx(struct session_data *sdata);
 int prepare_sql_statement(struct session_data *sdata, struct sql *sql, char *s);
-int prepare_sphx_statement(struct session_data *sdata, struct sql *sql, char *s);
 void p_query(struct session_data *sdata, char *s);
 int p_exec_stmt(struct session_data *sdata, struct sql *sql);
 int p_store_results(struct sql *sql);