How to Obfuscate SQL Sprocs?

O

11

8

Is there a way to hide/protect/obfuscate MS SQL Stored Procedures?

Oys answered 7/1, 2009 at 16:3 Comment(0)

C

10

I can vaguely understand obfuscating code if it's extremely advanced in what it does, but I think obfuscating your SQL may not be worth the hassle.

Anyway, a lot of the SQL I've seen around here comes obfuscated as standard.

Commutable answered 7/1, 2009 at 16:6 Comment(1)

Haha, only because you don’t get the genuis of all these fine SQL statements it doesn’t mean that they are obfuscated. You genius-fu is simply too weak. ;) – Jamin 7/1, 2009 at 16:26

T

10

If you must hide it, how about the "WITH ENCRYPTION" clause?

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

Titleholder answered 7/1, 2009 at 16:9 Comment(0)

V

6

See the ENCRYPTION option for the CREATE PROCEDURE statement.

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

Vernalize answered 7/1, 2009 at 16:10 Comment(1)

This method is not really helpful it can be easily decrypted see devart.com/dbforge/sql/sqldecryptor/download.html – Er 7/3, 2020 at 15:4

S

4

No. At least, not in a way that is irreversible. SQL Server 2000's "WITH ENCRYPTION" can be reversed to get the original plaintext. The pseudo-code and a T-SQL script that illustrates this is here: http://education.sqlfarms.com/education/ShowPost.aspx?PostID=783

Note: I haven't tried it with SQL 2005 or above, but my guess is it is just as vulnerable.. As the MSDN docs state:

ENCRYPTION Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format.

Emphasis is mine.

Softhearted answered 7/1, 2009 at 16:20 Comment(0)

O

2

One option would be to place just the sensitive portions of the stored procedure in a CLR stored procedure, and obfuscate that assembly using a professional obfuscation product.

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

Orchidaceous answered 7/1, 2009 at 16:27 Comment(0)

T

2

Easily reversible if you know but intimidating to to most people poking around code. hex encode you sproc logic and then execute with EXEC(@hexEncodedString).
see this post.

Tuneberg answered 7/1, 2009 at 16:46 Comment(0)

F

2

Old post, I know. But I got here from searching 'Why should I obfuscate SQL?' I just installed a free product called ApexSQL Refactor (no affiliation) which offers an obfuscation component.

It offers several different options for making your code hard to read. I wasn't sure why I'd want such a feature given, as others noted the ability to encrypt your stored procedures. Anyway, this is an example of the output it can return from it's 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

Flee answered 18/3, 2016 at 14:24 Comment(0)

J

1

You could use the ENCRYPTION clause when creating the stored procedure.

This would rely on not leaving the source SQL on the customer machine though.

See here for more info:

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

Jennefer answered 7/1, 2009 at 16:12 Comment(0)

L

0

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

Then you don't have to worry about obfuscating your SQL.

Lakieshalakin answered 7/1, 2009 at 16:8 Comment(2)

Yes, but those dll's can be just as easily decompiled, so you'd need to obfuscate, so you're back at square 1. – Backhouse 15/12, 2011 at 15:29

Everything executable can be decompiled and reverse engineered. So you can't get off square 1. Why bother? – Lakieshalakin 15/12, 2011 at 15:54

H

0

If you're really worried about someone getting into the DB and seeing the source for the procedure, then as S. Lott said, you can port the procedure to C#. I would recommend LINQ.

However, the database itself should probably be protected from people accessing the code for procedures that shouldn't be. You can restrict a user or group's rights to only have EXECUTE access to a proc if needed.

Halfback answered 7/1, 2009 at 16:11 Comment(0)

C

0

I use this tool to obfuscate sql https://harrymoreno.com/sql-obfuscator/

it replaces table and column names with letters

Chesty answered 9/8, 2023 at 19:43 Comment(0)

Recommended topics

Hot tags