Fossil

Check-in [f46fe42d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Store private ticket fields (ex: the originators email address) as their SHA1 hash so that malefactors cannot read them. Add the new "concealed" table to the repository database and store mappings from SHA1 hashes back to email addresses in that table. Ticket [a24ec6005f]. Note: run "rebuild" on repositories after updating to this version of fossil in order to create the "concealed" table. Need to add the ability to manage the concealed table from the web interface and the ability to sync concealed content between trusted repositories.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f46fe42d6d0703562250d3808de63c73ebb3169e
User & Date: drh 2008-07-24 02:04:36
References
2008-07-24
02:07 Fixed ticket [a24ec600]: Email in bug tickets <b>are</b> public visible (at least indirect). plus 2 other changes artifact: 1df152ba user: drh
Context
2008-07-24
12:29
Clarify the formatting rules for <verbatim>. Ticket [aee2a1a927f] check-in: 2c3ccaa0 user: drh tags: trunk
02:04
Store private ticket fields (ex: the originators email address) as their SHA1 hash so that malefactors cannot read them. Add the new "concealed" table to the repository database and store mappings from SHA1 hashes back to email addresses in that table. Ticket [a24ec6005f]. Note: run "rebuild" on repositories after updating to this version of fossil in order to create the "concealed" table. Need to add the ability to manage the concealed table from the web interface and the ability to sync concealed content between trusted repositories. check-in: f46fe42d user: drh tags: trunk
2008-07-23
20:57
Make sure new artifacts are entered into the unclustered table. Ticket [4b72e10dca]. check-in: 1f8d2501 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/db.c.

888
889
890
891
892
893
894





















































































895
896
897
898
899
900
901
902
903
904
905
906






907
908
909
910
911
912
913
      }
    }
    sqlite3_result_int(context, 0);
  }else{
    sqlite3_result_int(context, 1);
  }
}






















































































/*
** This function registers auxiliary functions when the SQLite
** database connection is first established.
*/
LOCAL void db_connection_init(void){
  static int once = 1;
  if( once ){
    sqlite3_create_function(g.db, "print", -1, SQLITE_UTF8, 0,db_sql_print,0,0);
    sqlite3_create_function(
      g.db, "file_is_selected", 1, SQLITE_UTF8, 0, file_is_selected,0,0
    );






    if( g.fSqlTrace ){
      sqlite3_trace(g.db, db_sql_trace, 0);
    }
    once = 0;
  }
}








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>












>
>
>
>
>
>







888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
      }
    }
    sqlite3_result_int(context, 0);
  }else{
    sqlite3_result_int(context, 1);
  }
}

/*
** Convert the input string into an SHA1.  Make a notation in the
** CONCEALED table so that the hash can be undo using the db_reveal()
** function at some later time.
**
** The value returned is stored in static space and will be overwritten
** on subsequent calls.
*/
char *db_conceal(const char *zContent, int n){
  static char zHash[42];
  Blob out;
  sha1sum_step_text(zContent, n);
  sha1sum_finish(&out);
  strcpy(zHash, blob_str(&out));
  blob_reset(&out);
  db_multi_exec(
     "INSERT OR IGNORE INTO concealed VALUES(%Q,%Q)",
     zHash, zContent
  );
  return zHash;
}

/*
** Attempt to look up the input in the CONCEALED table.  If found,
** and if the okRdAddr permission is enabled then return the
** original value for which the input is a hash.  If okRdAddr is
** false or if the lookup fails, return the original input.
**
** In either case, the string returned is stored in space obtained
** from malloc and should be freed by the calling function.
*/
char *db_reveal(const char *zKey){
  char *zOut;
  if( g.okRdAddr ){
    zOut = db_text(0, "SELECT content FROM concealed WHERE hash=%Q", zKey);
  }else{
    zOut = 0;
  }
  if( zOut==0 ){
    zOut = mprintf("%s", zKey);
  }
  return zOut;
}

/*
** The conceal() SQL function.  Compute an SHA1 hash of the argument
** and return that hash as a 40-character lower-case hex number.
*/
static void sha1_function(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const char *zIn;
  int nIn;
  char *zOut;
  assert(argc==1);
  
  zIn = (const char*)sqlite3_value_text(argv[0]);
  if( zIn ){
    nIn = sqlite3_value_bytes(argv[0]);
    zOut = db_conceal(zIn, nIn);
    sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
  }
}

/*
** The reveal() SQL function invokes the db_reveal() function.
*/
static void reveal_function(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const char *zIn;
  char *zOut;
  assert(argc==1);
  
  zIn = (const char*)sqlite3_value_text(argv[0]);
  if( zIn ){
    zOut = db_reveal(zIn);
    sqlite3_result_text(context, zOut, -1, free);
  }
}

/*
** This function registers auxiliary functions when the SQLite
** database connection is first established.
*/
LOCAL void db_connection_init(void){
  static int once = 1;
  if( once ){
    sqlite3_create_function(g.db, "print", -1, SQLITE_UTF8, 0,db_sql_print,0,0);
    sqlite3_create_function(
      g.db, "file_is_selected", 1, SQLITE_UTF8, 0, file_is_selected,0,0
    );
    sqlite3_create_function(
      g.db, "conceal", 1, SQLITE_UTF8, 0, sha1_function,0,0
    );
    sqlite3_create_function(
      g.db, "reveal", 1, SQLITE_UTF8, 0, reveal_function,0,0
    );
    if( g.fSqlTrace ){
      sqlite3_trace(g.db, db_sql_trace, 0);
    }
    once = 0;
  }
}

Changes to src/rebuild.c.

58
59
60
61
62
63
64













65
66
67
68
69
70
71
...
176
177
178
179
180
181
182
183

184
185
186
187
188
189
190
@ CREATE TABLE IF NOT EXISTS reportfmt(
@    rn integer primary key,  -- Report number
@    owner text,              -- Owner of this report format (not used)
@    title text,              -- Title of this report
@    cols text,               -- A color-key specification
@    sqlcode text             -- An SQL SELECT statement for this report
@ );













;

/*
** Variables used for progress information
*/
static int totalSize;       /* Total number of artifacts to process */
static int processCnt;      /* Number processed so far */
................................................................................
  processCnt = 0;
  db_multi_exec(zSchemaUpdates);
  for(;;){
    zTable = db_text(0,
       "SELECT name FROM sqlite_master"
       " WHERE type='table'"
       " AND name NOT IN ('blob','delta','rcvfrom','user',"
                         "'config','shun','private','reportfmt')"

    );
    if( zTable==0 ) break;
    db_multi_exec("DROP TABLE %Q", zTable);
    free(zTable);
  }
  db_multi_exec(zRepositorySchema2);
  ticket_create_table(0);







>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
>







58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
...
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
@ CREATE TABLE IF NOT EXISTS reportfmt(
@    rn integer primary key,  -- Report number
@    owner text,              -- Owner of this report format (not used)
@    title text,              -- Title of this report
@    cols text,               -- A color-key specification
@    sqlcode text             -- An SQL SELECT statement for this report
@ );
@
@ -- Some ticket content (such as the originators email address or contact
@ -- information) needs to be obscured to protect privacy.  This is achieved
@ -- by storing an SHA1 hash of the content.  For display, the hash is
@ -- mapped back into the original text using this table.  
@ --
@ -- This table contains sensitive information and should not be shared
@ -- with unauthorized users.
@ --
@ CREATE TABLE IF NOT EXISTS concealed(
@   hash TEXT PRIMARY KEY,
@   content TEXT
@ );
;

/*
** Variables used for progress information
*/
static int totalSize;       /* Total number of artifacts to process */
static int processCnt;      /* Number processed so far */
................................................................................
  processCnt = 0;
  db_multi_exec(zSchemaUpdates);
  for(;;){
    zTable = db_text(0,
       "SELECT name FROM sqlite_master"
       " WHERE type='table'"
       " AND name NOT IN ('blob','delta','rcvfrom','user',"
                         "'config','shun','private','reportfmt',"
                         "'concealed')"
    );
    if( zTable==0 ) break;
    db_multi_exec("DROP TABLE %Q", zTable);
    free(zTable);
  }
  db_multi_exec(zRepositorySchema2);
  ticket_create_table(0);

Changes to src/schema.c.

143
144
145
146
147
148
149













150
151
152
153
154
155
156
@ CREATE TABLE reportfmt(
@    rn integer primary key,  -- Report number
@    owner text,              -- Owner of this report format (not used)
@    title text,              -- Title of this report
@    cols text,               -- A color-key specification
@    sqlcode text             -- An SQL SELECT statement for this report
@ );













;

const char zRepositorySchema2[] =
@ -- Filenames
@ --
@ CREATE TABLE filename(
@   fnid INTEGER PRIMARY KEY,    -- Filename ID







>
>
>
>
>
>
>
>
>
>
>
>
>







143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
@ CREATE TABLE reportfmt(
@    rn integer primary key,  -- Report number
@    owner text,              -- Owner of this report format (not used)
@    title text,              -- Title of this report
@    cols text,               -- A color-key specification
@    sqlcode text             -- An SQL SELECT statement for this report
@ );
@
@ -- Some ticket content (such as the originators email address or contact
@ -- information) needs to be obscured to protect privacy.  This is achieved
@ -- by storing an SHA1 hash of the content.  For display, the hash is
@ -- mapped back into the original text using this table.  
@ --
@ -- This table contains sensitive information and should not be shared
@ -- with unauthorized users.
@ --
@ CREATE TABLE concealed(
@   hash TEXT PRIMARY KEY,
@   content TEXT
@ );
;

const char zRepositorySchema2[] =
@ -- Filenames
@ --
@ CREATE TABLE filename(
@   fnid INTEGER PRIMARY KEY,    -- Filename ID

Changes to src/tkt.c.

94
95
96
97
98
99
100






101
102
103
104
105
106
107
...
108
109
110
111
112
113
114

115




116
117
118
119
120
121
122
123
124

125
126
127
128
129
130
131
...
351
352
353
354
355
356
357
358




359
360
361
362
363
364
365
...
382
383
384
385
386
387
388




389
390
391
392
393
394
395
/*
** Query the database for all TICKET fields for the specific
** ticket whose name is given by the "name" CGI parameter.
** Load the values for all fields into the interpreter.
**
** Only load those fields which do not already exist as
** variables.






*/
static void initializeVariablesFromDb(void){
  const char *zName;
  Stmt q;
  int i, n, size, j;

  zName = PD("name","-none-");
................................................................................
  db_prepare(&q, "SELECT datetime(tkt_mtime) AS tkt_datetime, *"
                 "  FROM ticket WHERE tkt_uuid GLOB '%q*'", zName);
  if( db_step(&q)==SQLITE_ROW ){
    n = db_column_count(&q);
    for(i=0; i<n; i++){
      const char *zVal = db_column_text(&q, i);
      const char *zName = db_column_name(&q, i);

      if( zVal==0 ) zVal = "";




      for(j=0; j<nField; j++){
        if( strcmp(azField[j],zName)==0 ){
          azValue[j] = mprintf("%s", zVal);
          break;
        }
      }
      if( Th_Fetch(zName, &size)==0 ){
        Th_Store(db_column_name(&q,i), zVal);
      }

    }
  }else{
    db_finalize(&q);
    db_prepare(&q, "PRAGMA table_info(ticket)");
    while( db_step(&q)==SQLITE_ROW ){
      const char *zField = db_column_text(&q, 1);
      if( Th_Fetch(zField, &size)==0 ){
................................................................................
  azAppend[idx] = mprintf("%.*s", argl[2], argv[2]);
  return TH_OK;
}

/*
** Subscript command:   submit_ticket
**
** Construct and submit a new ticket artifact.




*/
static int submitTicketCmd(
  Th_Interp *interp, 
  void *pUuid, 
  int argc, 
  const unsigned char **argv, 
  int *argl
................................................................................
    if( azAppend[i] ){
      blob_appendf(&tktchng, "J +%s %z\n", azField[i],
                   fossilize(azAppend[i], -1));
    }else{
      zValue = Th_Fetch(azField[i], &nValue);
      if( zValue ){
        while( nValue>0 && isspace(zValue[nValue-1]) ){ nValue--; }




        if( strncmp(zValue, azValue[i], nValue)
                || strlen(azValue[i])!=nValue ){
          blob_appendf(&tktchng, "J %s %z\n",
             azField[i], fossilize(zValue,nValue));
        }
      }
    }







>
>
>
>
>
>







 







>
|
>
>
>
>







|

>







 







|
>
>
>
>







 







>
>
>
>







94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
...
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
...
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
...
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
/*
** Query the database for all TICKET fields for the specific
** ticket whose name is given by the "name" CGI parameter.
** Load the values for all fields into the interpreter.
**
** Only load those fields which do not already exist as
** variables.
**
** Fields of the TICKET table that begin with "private_" are
** expanded using the db_reveal() function.  This function will
** decode the content so that it is legable if g.okRdAddr is true.
** Otherwise, db_reveal() is a no-op and the content remains
** obscured.
*/
static void initializeVariablesFromDb(void){
  const char *zName;
  Stmt q;
  int i, n, size, j;

  zName = PD("name","-none-");
................................................................................
  db_prepare(&q, "SELECT datetime(tkt_mtime) AS tkt_datetime, *"
                 "  FROM ticket WHERE tkt_uuid GLOB '%q*'", zName);
  if( db_step(&q)==SQLITE_ROW ){
    n = db_column_count(&q);
    for(i=0; i<n; i++){
      const char *zVal = db_column_text(&q, i);
      const char *zName = db_column_name(&q, i);
      char *zRevealed = 0;
      if( zVal==0 ){
        zVal = "";
      }else if( strncmp(zName, "private_", 8)==0 ){
        zVal = zRevealed = db_reveal(zVal);
      }
      for(j=0; j<nField; j++){
        if( strcmp(azField[j],zName)==0 ){
          azValue[j] = mprintf("%s", zVal);
          break;
        }
      }
      if( Th_Fetch(zName, &size)==0 ){
        Th_Store(zName, zVal);
      }
      free(zRevealed);
    }
  }else{
    db_finalize(&q);
    db_prepare(&q, "PRAGMA table_info(ticket)");
    while( db_step(&q)==SQLITE_ROW ){
      const char *zField = db_column_text(&q, 1);
      if( Th_Fetch(zField, &size)==0 ){
................................................................................
  azAppend[idx] = mprintf("%.*s", argl[2], argv[2]);
  return TH_OK;
}

/*
** Subscript command:   submit_ticket
**
** Construct and submit a new ticket artifact.  The fields of the artifact
** are the names of the columns in the TICKET table.  The content is
** taken from TH variables.  If the content is unchanged, the field is
** omitted from the artifact.  Fields whose names begin with "private_"
** are concealed using the db_conceal() function.
*/
static int submitTicketCmd(
  Th_Interp *interp, 
  void *pUuid, 
  int argc, 
  const unsigned char **argv, 
  int *argl
................................................................................
    if( azAppend[i] ){
      blob_appendf(&tktchng, "J +%s %z\n", azField[i],
                   fossilize(azAppend[i], -1));
    }else{
      zValue = Th_Fetch(azField[i], &nValue);
      if( zValue ){
        while( nValue>0 && isspace(zValue[nValue-1]) ){ nValue--; }
        if( strncmp(azField[i], "private_", 8)==0 ){
          zValue = db_conceal(zValue, nValue);
          nValue = strlen(zValue);
        }
        if( strncmp(zValue, azValue[i], nValue)
                || strlen(azValue[i])!=nValue ){
          blob_appendf(&tktchng, "J %s %z\n",
             azField[i], fossilize(zValue,nValue));
        }
      }
    }