Fossil SCM

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.

drh 2008-07-24 02:04 trunk
Commit f46fe42d6d0703562250d3808de63c73ebb3169e
Parent 1f8d2501b03cb07…
4 files changed +91 +15 -1 +13 +23 -3
~ src/db.c ~ src/rebuild.c ~ src/schema.c ~ src/tkt.c
M src/db.c
+91
--- src/db.c
+++ src/db.c
@@ -890,10 +890,95 @@
890890
 sqlite3_result_int(context, 0);
891891
 }else{
892892
 sqlite3_result_int(context, 1);
893893
 }
894894
 }
895
+
896
+/*
897
+** Convert the input string into an SHA1. Make a notation in the
898
+** CONCEALED table so that the hash can be undo using the db_reveal()
899
+** function at some later time.
900
+**
901
+** The value returned is stored in static space and will be overwritten
902
+** on subsequent calls.
903
+*/
904
+char *db_conceal(const char *zContent, int n){
905
+ static char zHash[42];
906
+ Blob out;
907
+ sha1sum_step_text(zContent, n);
908
+ sha1sum_finish(&out);
909
+ strcpy(zHash, blob_str(&out));
910
+ blob_reset(&out);
911
+ db_multi_exec(
912
+ "INSERT OR IGNORE INTO concealed VALUES(%Q,%Q)",
913
+ zHash, zContent
914
+ );
915
+ return zHash;
916
+}
917
+
918
+/*
919
+** Attempt to look up the input in the CONCEALED table. If found,
920
+** and if the okRdAddr permission is enabled then return the
921
+** original value for which the input is a hash. If okRdAddr is
922
+** false or if the lookup fails, return the original input.
923
+**
924
+** In either case, the string returned is stored in space obtained
925
+** from malloc and should be freed by the calling function.
926
+*/
927
+char *db_reveal(const char *zKey){
928
+ char *zOut;
929
+ if( g.okRdAddr ){
930
+ zOut = db_text(0, "SELECT content FROM concealed WHERE hash=%Q", zKey);
931
+ }else{
932
+ zOut = 0;
933
+ }
934
+ if( zOut==0 ){
935
+ zOut = mprintf("%s", zKey);
936
+ }
937
+ return zOut;
938
+}
939
+
940
+/*
941
+** The conceal() SQL function. Compute an SHA1 hash of the argument
942
+** and return that hash as a 40-character lower-case hex number.
943
+*/
944
+static void sha1_function(
945
+ sqlite3_context *context,
946
+ int argc,
947
+ sqlite3_value **argv
948
+){
949
+ const char *zIn;
950
+ int nIn;
951
+ char *zOut;
952
+ assert(argc==1);
953
+
954
+ zIn = (const char*)sqlite3_value_text(argv[0]);
955
+ if( zIn ){
956
+ nIn = sqlite3_value_bytes(argv[0]);
957
+ zOut = db_conceal(zIn, nIn);
958
+ sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
959
+ }
960
+}
961
+
962
+/*
963
+** The reveal() SQL function invokes the db_reveal() function.
964
+*/
965
+static void reveal_function(
966
+ sqlite3_context *context,
967
+ int argc,
968
+ sqlite3_value **argv
969
+){
970
+ const char *zIn;
971
+ char *zOut;
972
+ assert(argc==1);
973
+
974
+ zIn = (const char*)sqlite3_value_text(argv[0]);
975
+ if( zIn ){
976
+ zOut = db_reveal(zIn);
977
+ sqlite3_result_text(context, zOut, -1, free);
978
+ }
979
+}
895980
896981
 /*
897982
 ** This function registers auxiliary functions when the SQLite
898983
 ** database connection is first established.
899984
 */
@@ -902,10 +987,16 @@
902987
 if( once ){
903988
 sqlite3_create_function(g.db, "print", -1, SQLITE_UTF8, 0,db_sql_print,0,0);
904989
 sqlite3_create_function(
905990
 g.db, "file_is_selected", 1, SQLITE_UTF8, 0, file_is_selected,0,0
906991
 );
992
+ sqlite3_create_function(
993
+ g.db, "conceal", 1, SQLITE_UTF8, 0, sha1_function,0,0
994
+ );
995
+ sqlite3_create_function(
996
+ g.db, "reveal", 1, SQLITE_UTF8, 0, reveal_function,0,0
997
+ );
907998
 if( g.fSqlTrace ){
908999
 sqlite3_trace(g.db, db_sql_trace, 0);
9091000
 }
9101001
 once = 0;
9111002
 }
9121003
--- src/db.c
+++ src/db.c
@@ -890,10 +890,95 @@
890 sqlite3_result_int(context, 0);
891 }else{
892 sqlite3_result_int(context, 1);
893 }
894 }
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
895
896 /*
897 ** This function registers auxiliary functions when the SQLite
898 ** database connection is first established.
899 */
@@ -902,10 +987,16 @@
902 if( once ){
903 sqlite3_create_function(g.db, "print", -1, SQLITE_UTF8, 0,db_sql_print,0,0);
904 sqlite3_create_function(
905 g.db, "file_is_selected", 1, SQLITE_UTF8, 0, file_is_selected,0,0
906 );
 
 
 
 
 
 
907 if( g.fSqlTrace ){
908 sqlite3_trace(g.db, db_sql_trace, 0);
909 }
910 once = 0;
911 }
912
--- src/db.c
+++ src/db.c
@@ -890,10 +890,95 @@
890 sqlite3_result_int(context, 0);
891 }else{
892 sqlite3_result_int(context, 1);
893 }
894 }
895
896 /*
897 ** Convert the input string into an SHA1. Make a notation in the
898 ** CONCEALED table so that the hash can be undo using the db_reveal()
899 ** function at some later time.
900 **
901 ** The value returned is stored in static space and will be overwritten
902 ** on subsequent calls.
903 */
904 char *db_conceal(const char *zContent, int n){
905 static char zHash[42];
906 Blob out;
907 sha1sum_step_text(zContent, n);
908 sha1sum_finish(&out);
909 strcpy(zHash, blob_str(&out));
910 blob_reset(&out);
911 db_multi_exec(
912 "INSERT OR IGNORE INTO concealed VALUES(%Q,%Q)",
913 zHash, zContent
914 );
915 return zHash;
916 }
917
918 /*
919 ** Attempt to look up the input in the CONCEALED table. If found,
920 ** and if the okRdAddr permission is enabled then return the
921 ** original value for which the input is a hash. If okRdAddr is
922 ** false or if the lookup fails, return the original input.
923 **
924 ** In either case, the string returned is stored in space obtained
925 ** from malloc and should be freed by the calling function.
926 */
927 char *db_reveal(const char *zKey){
928 char *zOut;
929 if( g.okRdAddr ){
930 zOut = db_text(0, "SELECT content FROM concealed WHERE hash=%Q", zKey);
931 }else{
932 zOut = 0;
933 }
934 if( zOut==0 ){
935 zOut = mprintf("%s", zKey);
936 }
937 return zOut;
938 }
939
940 /*
941 ** The conceal() SQL function. Compute an SHA1 hash of the argument
942 ** and return that hash as a 40-character lower-case hex number.
943 */
944 static void sha1_function(
945 sqlite3_context *context,
946 int argc,
947 sqlite3_value **argv
948 ){
949 const char *zIn;
950 int nIn;
951 char *zOut;
952 assert(argc==1);
953
954 zIn = (const char*)sqlite3_value_text(argv[0]);
955 if( zIn ){
956 nIn = sqlite3_value_bytes(argv[0]);
957 zOut = db_conceal(zIn, nIn);
958 sqlite3_result_text(context, zOut, -1, SQLITE_TRANSIENT);
959 }
960 }
961
962 /*
963 ** The reveal() SQL function invokes the db_reveal() function.
964 */
965 static void reveal_function(
966 sqlite3_context *context,
967 int argc,
968 sqlite3_value **argv
969 ){
970 const char *zIn;
971 char *zOut;
972 assert(argc==1);
973
974 zIn = (const char*)sqlite3_value_text(argv[0]);
975 if( zIn ){
976 zOut = db_reveal(zIn);
977 sqlite3_result_text(context, zOut, -1, free);
978 }
979 }
980
981 /*
982 ** This function registers auxiliary functions when the SQLite
983 ** database connection is first established.
984 */
@@ -902,10 +987,16 @@
987 if( once ){
988 sqlite3_create_function(g.db, "print", -1, SQLITE_UTF8, 0,db_sql_print,0,0);
989 sqlite3_create_function(
990 g.db, "file_is_selected", 1, SQLITE_UTF8, 0, file_is_selected,0,0
991 );
992 sqlite3_create_function(
993 g.db, "conceal", 1, SQLITE_UTF8, 0, sha1_function,0,0
994 );
995 sqlite3_create_function(
996 g.db, "reveal", 1, SQLITE_UTF8, 0, reveal_function,0,0
997 );
998 if( g.fSqlTrace ){
999 sqlite3_trace(g.db, db_sql_trace, 0);
1000 }
1001 once = 0;
1002 }
1003
M src/rebuild.c
+15 -1
--- src/rebuild.c
+++ src/rebuild.c
@@ -60,10 +60,23 @@
6060
 @ owner text, -- Owner of this report format (not used)
6161
 @ title text, -- Title of this report
6262
 @ cols text, -- A color-key specification
6363
 @ sqlcode text -- An SQL SELECT statement for this report
6464
 @ );
65
+@
66
+@ -- Some ticket content (such as the originators email address or contact
67
+@ -- information) needs to be obscured to protect privacy. This is achieved
68
+@ -- by storing an SHA1 hash of the content. For display, the hash is
69
+@ -- mapped back into the original text using this table.
70
+@ --
71
+@ -- This table contains sensitive information and should not be shared
72
+@ -- with unauthorized users.
73
+@ --
74
+@ CREATE TABLE IF NOT EXISTS concealed(
75
+@ hash TEXT PRIMARY KEY,
76
+@ content TEXT
77
+@ );
6578
 ;
6679
6780
 /*
6881
 ** Variables used for progress information
6982
 */
@@ -178,11 +191,12 @@
178191
 for(;;){
179192
 zTable = db_text(0,
180193
 "SELECT name FROM sqlite_master"
181194
 " WHERE type='table'"
182195
 " AND name NOT IN ('blob','delta','rcvfrom','user',"
183
- "'config','shun','private','reportfmt')"
196
+ "'config','shun','private','reportfmt',"
197
+ "'concealed')"
184198
 );
185199
 if( zTable==0 ) break;
186200
 db_multi_exec("DROP TABLE %Q", zTable);
187201
 free(zTable);
188202
 }
189203
--- src/rebuild.c
+++ src/rebuild.c
@@ -60,10 +60,23 @@
60 @ owner text, -- Owner of this report format (not used)
61 @ title text, -- Title of this report
62 @ cols text, -- A color-key specification
63 @ sqlcode text -- An SQL SELECT statement for this report
64 @ );
 
 
 
 
 
 
 
 
 
 
 
 
 
65 ;
66
67 /*
68 ** Variables used for progress information
69 */
@@ -178,11 +191,12 @@
178 for(;;){
179 zTable = db_text(0,
180 "SELECT name FROM sqlite_master"
181 " WHERE type='table'"
182 " AND name NOT IN ('blob','delta','rcvfrom','user',"
183 "'config','shun','private','reportfmt')"
 
184 );
185 if( zTable==0 ) break;
186 db_multi_exec("DROP TABLE %Q", zTable);
187 free(zTable);
188 }
189
--- src/rebuild.c
+++ src/rebuild.c
@@ -60,10 +60,23 @@
60 @ owner text, -- Owner of this report format (not used)
61 @ title text, -- Title of this report
62 @ cols text, -- A color-key specification
63 @ sqlcode text -- An SQL SELECT statement for this report
64 @ );
65 @
66 @ -- Some ticket content (such as the originators email address or contact
67 @ -- information) needs to be obscured to protect privacy. This is achieved
68 @ -- by storing an SHA1 hash of the content. For display, the hash is
69 @ -- mapped back into the original text using this table.
70 @ --
71 @ -- This table contains sensitive information and should not be shared
72 @ -- with unauthorized users.
73 @ --
74 @ CREATE TABLE IF NOT EXISTS concealed(
75 @ hash TEXT PRIMARY KEY,
76 @ content TEXT
77 @ );
78 ;
79
80 /*
81 ** Variables used for progress information
82 */
@@ -178,11 +191,12 @@
191 for(;;){
192 zTable = db_text(0,
193 "SELECT name FROM sqlite_master"
194 " WHERE type='table'"
195 " AND name NOT IN ('blob','delta','rcvfrom','user',"
196 "'config','shun','private','reportfmt',"
197 "'concealed')"
198 );
199 if( zTable==0 ) break;
200 db_multi_exec("DROP TABLE %Q", zTable);
201 free(zTable);
202 }
203
M src/schema.c
+13
--- src/schema.c
+++ src/schema.c
@@ -145,10 +145,23 @@
145145
 @ owner text, -- Owner of this report format (not used)
146146
 @ title text, -- Title of this report
147147
 @ cols text, -- A color-key specification
148148
 @ sqlcode text -- An SQL SELECT statement for this report
149149
 @ );
150
+@
151
+@ -- Some ticket content (such as the originators email address or contact
152
+@ -- information) needs to be obscured to protect privacy. This is achieved
153
+@ -- by storing an SHA1 hash of the content. For display, the hash is
154
+@ -- mapped back into the original text using this table.
155
+@ --
156
+@ -- This table contains sensitive information and should not be shared
157
+@ -- with unauthorized users.
158
+@ --
159
+@ CREATE TABLE concealed(
160
+@ hash TEXT PRIMARY KEY,
161
+@ content TEXT
162
+@ );
150163
 ;
151164
152165
 const char zRepositorySchema2[] =
153166
 @ -- Filenames
154167
 @ --
155168
--- src/schema.c
+++ src/schema.c
@@ -145,10 +145,23 @@
145 @ owner text, -- Owner of this report format (not used)
146 @ title text, -- Title of this report
147 @ cols text, -- A color-key specification
148 @ sqlcode text -- An SQL SELECT statement for this report
149 @ );
 
 
 
 
 
 
 
 
 
 
 
 
 
150 ;
151
152 const char zRepositorySchema2[] =
153 @ -- Filenames
154 @ --
155
--- src/schema.c
+++ src/schema.c
@@ -145,10 +145,23 @@
145 @ owner text, -- Owner of this report format (not used)
146 @ title text, -- Title of this report
147 @ cols text, -- A color-key specification
148 @ sqlcode text -- An SQL SELECT statement for this report
149 @ );
150 @
151 @ -- Some ticket content (such as the originators email address or contact
152 @ -- information) needs to be obscured to protect privacy. This is achieved
153 @ -- by storing an SHA1 hash of the content. For display, the hash is
154 @ -- mapped back into the original text using this table.
155 @ --
156 @ -- This table contains sensitive information and should not be shared
157 @ -- with unauthorized users.
158 @ --
159 @ CREATE TABLE concealed(
160 @ hash TEXT PRIMARY KEY,
161 @ content TEXT
162 @ );
163 ;
164
165 const char zRepositorySchema2[] =
166 @ -- Filenames
167 @ --
168
M src/tkt.c
+23 -3
--- src/tkt.c
+++ src/tkt.c
@@ -96,10 +96,16 @@
9696
 ** ticket whose name is given by the "name" CGI parameter.
9797
 ** Load the values for all fields into the interpreter.
9898
 **
9999
 ** Only load those fields which do not already exist as
100100
 ** variables.
101
+**
102
+** Fields of the TICKET table that begin with "private_" are
103
+** expanded using the db_reveal() function. This function will
104
+** decode the content so that it is legable if g.okRdAddr is true.
105
+** Otherwise, db_reveal() is a no-op and the content remains
106
+** obscured.
101107
 */
102108
 static void initializeVariablesFromDb(void){
103109
 const char *zName;
104110
 Stmt q;
105111
 int i, n, size, j;
@@ -110,20 +116,26 @@
110116
 if( db_step(&q)==SQLITE_ROW ){
111117
 n = db_column_count(&q);
112118
 for(i=0; i<n; i++){
113119
 const char *zVal = db_column_text(&q, i);
114120
 const char *zName = db_column_name(&q, i);
115
- if( zVal==0 ) zVal = "";
121
+ char *zRevealed = 0;
122
+ if( zVal==0 ){
123
+ zVal = "";
124
+ }else if( strncmp(zName, "private_", 8)==0 ){
125
+ zVal = zRevealed = db_reveal(zVal);
126
+ }
116127
 for(j=0; j<nField; j++){
117128
 if( strcmp(azField[j],zName)==0 ){
118129
 azValue[j] = mprintf("%s", zVal);
119130
 break;
120131
 }
121132
 }
122133
 if( Th_Fetch(zName, &size)==0 ){
123
- Th_Store(db_column_name(&q,i), zVal);
134
+ Th_Store(zName, zVal);
124135
 }
136
+ free(zRevealed);
125137
 }
126138
 }else{
127139
 db_finalize(&q);
128140
 db_prepare(&q, "PRAGMA table_info(ticket)");
129141
 while( db_step(&q)==SQLITE_ROW ){
@@ -353,11 +365,15 @@
353365
 }
354366
355367
 /*
356368
 ** Subscript command: submit_ticket
357369
 **
358
-** Construct and submit a new ticket artifact.
370
+** Construct and submit a new ticket artifact. The fields of the artifact
371
+** are the names of the columns in the TICKET table. The content is
372
+** taken from TH variables. If the content is unchanged, the field is
373
+** omitted from the artifact. Fields whose names begin with "private_"
374
+** are concealed using the db_conceal() function.
359375
 */
360376
 static int submitTicketCmd(
361377
 Th_Interp *interp,
362378
 void *pUuid,
363379
 int argc,
@@ -384,10 +400,14 @@
384400
 fossilize(azAppend[i], -1));
385401
 }else{
386402
 zValue = Th_Fetch(azField[i], &nValue);
387403
 if( zValue ){
388404
 while( nValue>0 && isspace(zValue[nValue-1]) ){ nValue--; }
405
+ if( strncmp(azField[i], "private_", 8)==0 ){
406
+ zValue = db_conceal(zValue, nValue);
407
+ nValue = strlen(zValue);
408
+ }
389409
 if( strncmp(zValue, azValue[i], nValue)
390410
 || strlen(azValue[i])!=nValue ){
391411
 blob_appendf(&tktchng, "J %s %z\n",
392412
 azField[i], fossilize(zValue,nValue));
393413
 }
394414
--- src/tkt.c
+++ src/tkt.c
@@ -96,10 +96,16 @@
96 ** ticket whose name is given by the "name" CGI parameter.
97 ** Load the values for all fields into the interpreter.
98 **
99 ** Only load those fields which do not already exist as
100 ** variables.
 
 
 
 
 
 
101 */
102 static void initializeVariablesFromDb(void){
103 const char *zName;
104 Stmt q;
105 int i, n, size, j;
@@ -110,20 +116,26 @@
110 if( db_step(&q)==SQLITE_ROW ){
111 n = db_column_count(&q);
112 for(i=0; i<n; i++){
113 const char *zVal = db_column_text(&q, i);
114 const char *zName = db_column_name(&q, i);
115 if( zVal==0 ) zVal = "";
 
 
 
 
 
116 for(j=0; j<nField; j++){
117 if( strcmp(azField[j],zName)==0 ){
118 azValue[j] = mprintf("%s", zVal);
119 break;
120 }
121 }
122 if( Th_Fetch(zName, &size)==0 ){
123 Th_Store(db_column_name(&q,i), zVal);
124 }
 
125 }
126 }else{
127 db_finalize(&q);
128 db_prepare(&q, "PRAGMA table_info(ticket)");
129 while( db_step(&q)==SQLITE_ROW ){
@@ -353,11 +365,15 @@
353 }
354
355 /*
356 ** Subscript command: submit_ticket
357 **
358 ** Construct and submit a new ticket artifact.
 
 
 
 
359 */
360 static int submitTicketCmd(
361 Th_Interp *interp,
362 void *pUuid,
363 int argc,
@@ -384,10 +400,14 @@
384 fossilize(azAppend[i], -1));
385 }else{
386 zValue = Th_Fetch(azField[i], &nValue);
387 if( zValue ){
388 while( nValue>0 && isspace(zValue[nValue-1]) ){ nValue--; }
 
 
 
 
389 if( strncmp(zValue, azValue[i], nValue)
390 || strlen(azValue[i])!=nValue ){
391 blob_appendf(&tktchng, "J %s %z\n",
392 azField[i], fossilize(zValue,nValue));
393 }
394
--- src/tkt.c
+++ src/tkt.c
@@ -96,10 +96,16 @@
96 ** ticket whose name is given by the "name" CGI parameter.
97 ** Load the values for all fields into the interpreter.
98 **
99 ** Only load those fields which do not already exist as
100 ** variables.
101 **
102 ** Fields of the TICKET table that begin with "private_" are
103 ** expanded using the db_reveal() function. This function will
104 ** decode the content so that it is legable if g.okRdAddr is true.
105 ** Otherwise, db_reveal() is a no-op and the content remains
106 ** obscured.
107 */
108 static void initializeVariablesFromDb(void){
109 const char *zName;
110 Stmt q;
111 int i, n, size, j;
@@ -110,20 +116,26 @@
116 if( db_step(&q)==SQLITE_ROW ){
117 n = db_column_count(&q);
118 for(i=0; i<n; i++){
119 const char *zVal = db_column_text(&q, i);
120 const char *zName = db_column_name(&q, i);
121 char *zRevealed = 0;
122 if( zVal==0 ){
123 zVal = "";
124 }else if( strncmp(zName, "private_", 8)==0 ){
125 zVal = zRevealed = db_reveal(zVal);
126 }
127 for(j=0; j<nField; j++){
128 if( strcmp(azField[j],zName)==0 ){
129 azValue[j] = mprintf("%s", zVal);
130 break;
131 }
132 }
133 if( Th_Fetch(zName, &size)==0 ){
134 Th_Store(zName, zVal);
135 }
136 free(zRevealed);
137 }
138 }else{
139 db_finalize(&q);
140 db_prepare(&q, "PRAGMA table_info(ticket)");
141 while( db_step(&q)==SQLITE_ROW ){
@@ -353,11 +365,15 @@
365 }
366
367 /*
368 ** Subscript command: submit_ticket
369 **
370 ** Construct and submit a new ticket artifact. The fields of the artifact
371 ** are the names of the columns in the TICKET table. The content is
372 ** taken from TH variables. If the content is unchanged, the field is
373 ** omitted from the artifact. Fields whose names begin with "private_"
374 ** are concealed using the db_conceal() function.
375 */
376 static int submitTicketCmd(
377 Th_Interp *interp,
378 void *pUuid,
379 int argc,
@@ -384,10 +400,14 @@
400 fossilize(azAppend[i], -1));
401 }else{
402 zValue = Th_Fetch(azField[i], &nValue);
403 if( zValue ){
404 while( nValue>0 && isspace(zValue[nValue-1]) ){ nValue--; }
405 if( strncmp(azField[i], "private_", 8)==0 ){
406 zValue = db_conceal(zValue, nValue);
407 nValue = strlen(zValue);
408 }
409 if( strncmp(zValue, azValue[i], nValue)
410 || strlen(azValue[i])!=nValue ){
411 blob_appendf(&tktchng, "J %s %z\n",
412 azField[i], fossilize(zValue,nValue));
413 }
414

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button