Our app slightly obfuscates text before insertion to the database. This is done by the application (C#) and not the database. (This is not encryption but just to stop people who are looking at the data in Management Studio; not really preventing shoulder surfing, more like "can't read text even with correct permissions")
Here's a simplified example.
- In the website I input HELLO into a textbox and click save.
- The save method then replaces each character with the next in the alphabet, so HELLO becomes IFMMP.
- The save/update command is then called and IFMMP is saved into the database.
- When the data is read back from the database, each character is replaced with the one before, so IFMMP becomes HELLO and gets displayed in the textbox.
IFMMP won't be much good for fulltext searching. We have hundreds of existing sprocs & triggers which we don't want to update for this.
The question is, how can I "un-obfuscate" the data before it gets added to the Fulltext catalogue?
Update: Looks like we need a custom WordBreaker or IFilter. Will update if get to a solution.