How to obfuscate SQL Sprocs?

Question

Is there a way to hide / protect / obfuscate MS SQL stored procedures?

Answer 1

I can vaguely understand the obfuscation code if it is extremely advanced in what it does, but I think that obfuscating your SQL may not be worth the trouble.

Anyway, many of the SQL I've seen here gets confused as standard.

Answer 2

If you have to hide it, what about the WITH WITH ENCRYPTION clause?

http://blog.sqlauthority.com/2007/07/01/sql-server-explanation-of-with-encryption-clause-for-stored-procedure-and-user-defined-functions/

Answer 3

See the ENCRYPTION parameter for the CREATE PROCEDURE statement.

http://msdn.microsoft.com/en-us/library/ms187926.aspx

Answer 4

Not. At least not in a way that was impossible. SQL Server 2000 "With ENCRYPTION" can be undone to get the source code. Pseudocode and T-SQL script that illustrates this: http://education.sqlfarms.com/education/ShowPost.aspx?PostID=783

Note. I have not tried it with SQL 2005 or higher, but I assume that it is also vulnerable. According to MSDN docs:

ENCRYPTION Indicates that SQL Server will convert the source code of the CREATE PROCEDURE statement to obfuscated format.

Emphasis on mine.

Answer 5

Easily reversible if you know, but scare most people picking code. hex encodes your sproc logic and then executes with EXEC (@hexEncodedString).
see this post .

Answer 6

When creating a stored procedure, you can use the ENCRYPTION clause.

This will rely on not leaving the original SQL on the client machine.

See here for more details:

http://msdn.microsoft.com/en-us/library/ms187926(SQL.90).aspx

Answer 7

One option is to place only the sensitive parts of the stored procedure in the CLR stored procedure and obfuscate the assembly using a professional obfuscation product.

http://msdn.microsoft.com/en-us/library/ms131094.aspx

Answer 8

Old post, I know. But I came from the search "Why should I mess up SQL?" I just installed a free product called ApexSQL Refactor (without affiliation) that offers an obfuscation component.

It offers several different options to make it easier to read your code. I was not sure why I needed such a function, as others noted the possibility of encrypting your stored procedures. In any case, this is an example of the output that it can return from the obfuscation function.

CrEAtE Procedure spInsertOrUpdateProduct @ProductNumber nVarChar(25), @ListPrice Money aS IF exIsTS(selECt * FROm Production.Product WHere ProductNumber=@ProductNumber AnD ListPrice>1000) uPdatE Production. Product sET ListPrice=(ListPrice-100) where ProductNumber= @ProductNumber elsE INSerT intO Production.Product(ProductNumber, ListPrice) SelECT @ProductNumber,@ListPrice GO SElEct * fRoM Production.Product gO iNsERT iNTo Production.UnitMeasure( UnitMeasureCode,Name,ModifiedDate) vAlUeS(N'FT2',N'Square Feet', '20080923'); Go 

Answer 9

You can always write regular code in C # (or VB) and save it outside the database in a DLL.

Then you need not worry about obfuscating your SQL.

Answer 10

If you are really worried that someone gets into the database and sees the source of the procedure, then, as S. Lott said, you can transfer the procedure to C #. I would recommend LINQ.

However, the database itself should probably be protected from people accessing the code for procedures that should not be. You can restrict user or group rights to only have EXECUTE access to proc, if necessary.