Overview
| SHA1 Hash: | f46fe42d6d0703562250d3808de63c73ebb3169e |
|---|---|
| Date: | 2008-07-24 02:04:36 |
| User: | drh |
| 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 |
| Timelines: | ancestors | descendants | both | trunk |
| Other Links: | files | ZIP archive | manifest |
Tags And Properties
- branch=trunk inherited from [a28c83647d]
- sym-trunk inherited from [a28c83647d]
Changes
[hide diffs]Modified src/db.c from [d924508e76] to [984f70bf72].
@@ -892,19 +892,110 @@
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;
Modified src/rebuild.c from [ec319a8700] to [0d4b328967].
@@ -60,10 +60,23 @@
@ 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
*/
@@ -178,11 +191,12 @@
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')"
+ "'config','shun','private','reportfmt',"
+ "'concealed')"
);
if( zTable==0 ) break;
db_multi_exec("DROP TABLE %Q", zTable);
free(zTable);
}
Modified src/schema.c from [f558a43ecd] to [d06483adac].
@@ -144,10 +144,23 @@ @ 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
Modified src/tkt.c from [6ac9641c6a] to [a48490bf20].
@@ -96,10 +96,16 @@
** 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;
@@ -110,20 +116,26 @@
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 = "";
+ 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(db_column_name(&q,i), zVal);
+ Th_Store(zName, zVal);
}
+ free(zRevealed);
}
}else{
db_finalize(&q);
db_prepare(&q, "PRAGMA table_info(ticket)");
while( db_step(&q)==SQLITE_ROW ){
@@ -353,11 +365,15 @@
}
/*
** Subscript command: submit_ticket
**
-** Construct and submit a new ticket artifact.
+** 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,
@@ -384,10 +400,14 @@
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));
}