How to hide your data within a SQL Server Database

Want to bring your attention to a little and very simple SQL hack.

Do not think anybody can use it in production, but it is still surprisingly interesting and might be dangerous.

At first, will create a table, insert couple of records and verify how it looks:

Use TestDB; GO CREATE TABLE tbl_Hidden_Data (     ID INT IDENTITY(1,1),     Hidden_Text VARCHAR(100) ); GO INSERT INTO tbl_Hidden_Data(Hidden_Text) VALUES ('My little hidden Secret'),      ('SELECT ''Invisible Pink Unicorn is hidden here'' as [Result of Hidden Command]'); GO SELECT * FROM tbl_Hidden_Data; GO

Both records are visible and available for anybody who has select permission on that new table.



Now will do a trick, add leading zero-character:

UPDATE tbl_Hidden_Data SET Hidden_Text = CHAR(0) + Hidden_Text;

Will try to extract data again:

SELECT *      , LEN(Hidden_Text) as Text_Length      , DATALENGTH(Hidden_Text) as Text_Size FROM tbl_Hidden_Data;

Test is there, you can tell it by data length numbers, but you do not see it:





 Only the way to see the text again is to suppress the leading zero-character:

SELECT *, SUBSTRING(Hidden_Text,2,@@TEXTSIZE) as Visible_Text FROM tbl_Hidden_Data;

Now you can see the hidden text.



Interesting that despite of leading zero-character we still can use hidden string for executions:

DECLARE @SQL VARCHAR(100); SELECT @SQL = Hidden_Text FROM tbl_Hidden_Data WHERE ID = 2; EXEC (@SQL);

That statement will be successfully executed and return a value


(That might be the dangerous part, when executed invisible statements produce some results)


Do not forget to cleanup after yourself:

DROP TABLE tbl_Hidden_Data;