8

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

lc.
  • 113,939
  • 20
  • 158
  • 187
user32183
  • 133
  • 1
  • 2
  • 5

11 Answers11

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.

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • 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. ;) – Bombe Jan 07 '09 at 16:26
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/

Chris Farmer
  • 24,974
  • 34
  • 121
  • 164
6

See the ENCRYPTION option for the CREATE PROCEDURE statement.

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

Jeremy
  • 219
  • 1
  • 2
  • This method is not really helpful it can be easily decrypted see https://www.devart.com/dbforge/sql/sqldecryptor/download.html – Erfan Azhdari Mar 07 '20 at 15:04
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.

Rumi
  • 1,290
  • 1
  • 11
  • 17
Matt Rogish
  • 24,435
  • 11
  • 76
  • 92
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
InbetweenWeekends
  • 1,405
  • 3
  • 23
  • 28
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

Jon Galloway
  • 52,327
  • 25
  • 125
  • 193
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.

Community
  • 1
  • 1
jason saldo
  • 9,804
  • 5
  • 34
  • 41
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

Kev
  • 118,037
  • 53
  • 300
  • 385
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.

S.Lott
  • 384,516
  • 81
  • 508
  • 779
  • Yes, but those dll's can be just as easily decompiled, so you'd need to obfuscate, so you're back at square 1. – Ronnie Overby Dec 15 '11 at 15:29
  • Everything executable can be decompiled and reverse engineered. So you can't get off square 1. Why bother? – S.Lott Dec 15 '11 at 15:54
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.

regex
  • 3,585
  • 5
  • 31
  • 41
0

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

it replaces table and column names with letters

Harry Moreno
  • 10,231
  • 7
  • 64
  • 116