Obscuring data in database

At times, we may share database for development purpose. And we may need to obscure certain data. One example may be to obscure all email address in a particular table. Following sql statement obscures the local part of email address (before @ sign) and prefixes the domain name with two underscores (__).

update  Employeee_Table emp
set emp.email_id = concat(Translate(SUBSTR(emp.email_id, 1,  Instr(emp.email_id, '@')-1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '84cBF75ZKnbEHofTQRSs6etN9zrW03yUmIkvxVijwJOdl1uAhMqCD2pGLXgaPY'), replace((SUBSTR(emp.email_id, Instr(emp.email_id, '@'))),'@','@__'))

Translate function, available in both - IBM DB2 and Oracle, changes character by character. So, in above query all 'A's will be changed '8', 'B's to 4, 'C's to 'c', 'D's to 'B' and so on. Replace function replaces '@' with '@__'

You can randomize string for your use at http://textmechanic.com/String-Randomizer.html

Obscuring domain name would have made key more predictable. So, either we should choose to use different keys for local part and domain name or just obscure local part.

Prefixing '_' with domain name helps in avoiding any accidental email to be sent to correct domain name.

Also, this isn't any encryption and should not be used where security is important. This is just to obscure data. 

Calling an interactive bash script from another script

Lots of time we need to call existing scripts from new scripts. There are scripts which are interactive and have no option to avoid interactive mode by passing values inline as parameters. Some of them may just need a Yes or No while some may need important inputs in interactive mode.

Let us take a example (a bad one though). Suppose we need to call a script - stopMyServer.sh.

$./stopMyServer.sh
### Do you really want to stop Server? [Yes | No]
Yes
### Please enter the reason code to stop the server
### 1. Scheduled Downtime
### 2. Critical Patch
### 3. Performance degradation
1
### Thanks. Shutting down the server. ###

#!/bin/bash

echo Calling stopMyServer.sh

# EOF is meaningless and can be substituted by any word. Any 
# strings passed between the set of EOF (or any word used 
# instead of EOF) will be passed to interactive script. One 
# line for every interactive pause

./stopMyServer.sh <<EOF
Yes
1
EOF

echo stopMyServer.sh was called

Date Arithmetic in Unix Scripting

Unix has date function which is good to add or subtract days / time from current date but has lots of limitation. I found no easy way to do serious date-time arithmetic using it. One possible way is to convert date-time to seconds (since epoch) and then manipulate it.

#Assign a date to variable. 
startDateTime="2013-04-28 00:00:00"

#Converts date to epoch and assign it to variable. 
#One should be careful as it may accept it as date in only few predefined formats
startDateInSecs=`date -d "$StartDateTime" +"%s"`

#Perform manipulation by adding or subtracting time. 
#For example to add 2 hours
let newDateInSecs=$((StartDateInSecs + 2*3600))

#Then print or assign the date to a variable in required format.
NewDateTime=`date -d @"$newDateInSecs" +"%Y-%m-%d %H:%M:%S"`
echo NewDateTime

#Do note @ before variable $newDateInSecs. That shows date-time is in seconds since epoch