Oracle Wrap like utility in MS-SQL

Question

Oracle Wrap like utility in MS-SQL

gaurangsn

SSC Rookie

Points: 49
More actions
September 8, 2010 at 6:22 am

#225932

Hi All,
I am searching for wrap utility which can wrap my PL/SQL code before sending it customer.
Basic idea is to not show my SQL code to customer when we provide distribution kit.
I am aware of WITH ENCRYPTION option but that does not serve my purpose to hide code even when I distribute my SQL files.
There is similar utility available in Oracle called Wrap which serves above purpose.
My questions is do we have Wrap like utility in MS-SQL as well?
Thanks,
Gaurang

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

are you sending the actual scripts fo rhte client to run, or an executable which will then execute the script after the client provides connection information to the server?

you could obfuscate the commands, if you wanted, but it just makes it harder to figure out...you cannot prevent them from running a trace and reviewing any commands sent to the server; at execution time all commands are decrypted anyway.

totally obfuscate a command to send to the client:

Declare @cmds Nvarchar(MAX)

Declare @obfoo varbinary(MAX)

Set @cmds = '

PRINT ''This binary string will execute "SELECT * FROM SYS.OBJECTS":''

SELECT * FROM SYS.OBJECTS

'

Set @obfoo = CAST(@cmds as varbinary(MAX))

Select @obfoo

declare @_ as varbinary(max)

set @_ =0x0D000A005000520049004E0054002000270054006800690073002000620069006E00610072007900200073007400720069006E0067002000770069006C006C002000650078006500630075007400650020002200530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A00450043005400530022003A0027000D000A00530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A0045004300540053000D000A00

exec (@_)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gaurangsn SSC Rookie Points: 49 More actions · Answer 2

Hi Lowell

Thanks for reply.

Yes, I am sending SQL files not any executable to client.

If SQL code is viewable from trace that is fine with me.

At the moment my priority is to obfuscate code in my SQL Scripts which we distribute to customers.

Also the option you have suggested would not work for me using VARBINARY, as I have ALTER PROCEDURE, ALTER FUNCTION in my script.

Is there any other way to obfuscate whole SQL file.

Thanks,

Gaurang

Lowell SSC Guru Points: 323547 More actions · Answer 3

the example i gave you will work with absolutely any SQL command; try it before you dismiss it.

Declare @cmds Nvarchar(MAX)

Declare @obfoo varbinary(MAX)

Set @cmds = '

ALTER procedure [dbo].[sp_find]

@findcolumn varchar(50)

AS

BEGIN

SET NOCOUNT ON

--print object_name(@@PROCID)

SELECT

TableFound,

ColumnFound

FROM

(

SELECT

1 AS SortOrder,

sysobjects.name AS TableFound,

'''' AS ColumnFound

FROM sysobjects

WHERE sysobjects.xtype IN(''U'' ,''V'')

AND sysobjects.name LIKE ''%'' + @findcolumn + ''%''

UNION ALL

SELECT

2 AS SortOrder,

sysobjects.name AS TableFound,

syscolumns.name AS ColumnFound

FROM sysobjects

INNER JOIN syscolumns ON sysobjects.id=syscolumns.id

WHERE sysobjects.xtype IN(''U'' ,''V'')

AND syscolumns.name like ''%'' + @findcolumn + ''%''

) X

ORDER BY

SortOrder,

TableFound,

ColumnFound

END'

Set @obfoo = CAST(@cmds as varbinary(MAX))

Select @obfoo

declare @_ as varbinary(max)

set @_ =0x0D000A0041004C005400450052002000700072006F0063006500640075007200650020005B00640062006F005D002E005B00730070005F00660069006E0064005D000D000A00200020004000660069006E00640063006F006C0075006D006E002000760061007200630068006100720028003500300029000D000A00410053000D000A0042004500470049004E000D000A00200053004500540020004E004F0043004F0055004E00540020004F004E000D000A0020002D002D007000720069006E00740020006F0062006A006500630074005F006E0061006D006500280040004000500052004F0043004900440029000D000A002000530045004C004500430054000D000A002000200020005400610062006C00650046006F0075006E0064002C000D000A0020002000200043006F006C0075006D006E0046006F0075006E0064000D000A002000460052004F004D000D000A0020002000200028000D000A002000200020002000530045004C004500430054000D000A002000200020002000200020003100200041005300200053006F00720074004F0072006400650072002C000D000A002000200020002000200020007300790073006F0062006A0065006300740073002E006E0061006D00650020004100530020005400610062006C00650046006F0075006E0064002C000D000A0020002000200020002000200027002700200041005300200043006F006C0075006D006E0046006F0075006E0064000D000A002000200020002000460052004F004D0020007300790073006F0062006A0065006300740073000D000A0020002000200020005700480045005200450020007300790073006F0062006A0065006300740073002E0078007400790070006500200049004E00280027005500270020002C0027005600270029000D000A0020002000200020002000200041004E00440020007300790073006F0062006A0065006300740073002E006E0061006D00650020004C0049004B004500200027002500270020002B0020004000660069006E00640063006F006C0075006D006E0020002B0020002700250027000D000A00200020002000200055004E0049004F004E00200041004C004C000D000A002000200020002000530045004C004500430054000D000A002000200020002000200020003200200041005300200053006F00720074004F0072006400650072002C000D000A002000200020002000200020007300790073006F0062006A0065006300740073002E006E0061006D00650020004100530020005400610062006C00650046006F0075006E0064002C000D000A0020002000200020002000200073007900730063006F006C0075006D006E0073002E006E0061006D006500200041005300200043006F006C0075006D006E0046006F0075006E0064000D000A002000200020002000460052004F004D0020007300790073006F0062006A0065006300740073000D000A0020002000200020002000200049004E004E004500520020004A004F0049004E00200073007900730063006F006C0075006D006E00730020004F004E0020007300790073006F0062006A0065006300740073002E00690064003D0073007900730063006F006C0075006D006E0073002E00690064000D000A0020002000200020005700480045005200450020007300790073006F0062006A0065006300740073002E0078007400790070006500200049004E00280027005500270020002C0027005600270029000D000A0020002000200020002000200041004E004400200073007900730063006F006C0075006D006E0073002E006E0061006D00650020006C0069006B006500200027002500270020002B0020004000660069006E00640063006F006C0075006D006E0020002B0020002700250027000D000A0020002000200029002000200058000D000A002000200020004F0052004400450052002000420059000D000A002000200020002000200053006F00720074004F0072006400650072002C000D000A00200020002000200020005400610062006C00650046006F0075006E0064002C000D000A002000200020002000200043006F006C0075006D006E0046006F0075006E0064000D000A0045004E004400

exec (@_)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gaurangsn SSC Rookie Points: 49 More actions · Answer 4

Thanks Lowell

Apologies If I have made myself clear, but what I want is to obfuscate the code assigned to @cmds variable as well.

e.g. These is how it works in Oracle with Wrap utility.

-> ORIGINAL SQL SCRIPT

CREATE OR REPLACE PACKAGE BODY IVDK_Conv IS

v_cache t_caches_array;

FUNCTION get_cached_value (

pni_column_id IN ivd_conversion.repository_column_id%TYPE,

pvi_value IN ivd_conversion.input_value%TYPE)

RETURN ivd_conversion.conversion_value%TYPE IS

retval ivd_conversion.conversion_value%TYPE;

BEGIN

IF v_cache (pni_column_id).EXISTS (pvi_value) THEN

retval := v_cache (pni_column_id) (pvi_value);

ELSE

retval := pvi_value;

END IF;

RETURN retval;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END;

END IVDK_Conv;

-> OBFUSCATED SQL SCRIPT USING Wrap

CREATE OR REPLACE PACKAGE BODY IVDK_Conv wrapped

a000000

b2

abcd

b

11fa 536

sNRxkCTBFnhwjsTK/JfF6jyJ21wwgz1U10oF3y//PfiONI4tqd0WGMpnRYU36GDf8/SRWpeY

GW0Zyu2BOtqJEfSqK4l5qRR6k2bBE50gYITVlyLrtsiM2GRGD/xyHfjBHp1GE1us2YeUL9pu

Jq2/w+664CktRGb9v59wCXFn/dGVODqtcREFaizc9du4bW2rQfoI7PJSayP953oiRieIARd8

HT3sh+9VA+m+4T3sN5mZSVAcimXuFDA/Xt76DrkqLzzhPflN/vnnSdHJlBzBaYSZb2Iyu2d3

iMj7/VN98/6CX9PPZ9mUH0m/8bEREqunEap0WTID3sJdp8itL9BE1h3QNPdIKgm3H7DHIFDz

yb3aEWkluNWBlPAR+NwOPL8DZ2FUdWE9xz4+GBf9YpgUpkaN3CKQr0M+Sss6iYCgZjuJxayP

124kMIvUq8KqPpJM8xI59yYP5vn+sFjM/soc6tvMG+OHQ4+HwleNpjxc3MnpkVNiEbLkozm9

Xc8DNMdy0TjeoSrtcDwt7ja8nx767BrsyWMC8sUx3293Fboy7hzfH674XioM3kvtbxNFcFRF

FkD9E9vybO1QdYrvsQpYPAPaRBsSzagCC4lpelpplw5EFyIcsOtjeLI9FF235EdnvmgiNpVi

46JtDT60Lr43mqkcLcOyss0FVLT+Jmqg/gNNQ2VJkmHthC3dAYJB1WX6MXVFvv/hclh6D8zs

GffTW6oh9GXVCSsqIhif/SZlG3kGPArJN7FStzCX8L1i8z0ZAsXHFtsSpEL2boz1xkTf0f/5

W43WGyGES4f9bvW0j5vP78fbB0LkOtSJwjgiMXpYL8X8Nj/CYYVv0Et6mo3L8S7C6OSE/AU/

A91jFQaZBqT/bnAxrWi69e7Y0cHlh8Kx8h2/3WG/4agH7KN7IcKAC7HPWc/9YzuJdXpSpKDP

oyBJmgUj8Tzxn0EI7IJU9LBFSjnwW3hxMjzG/3TtQ2LEsR2eDevwGwqvmT2KWYEPApfsxDO7

teTOqBEDjEjzmlHAy+lvGosGTpdjynG3wvAHSdkKqeeFkQpCgDM/mVi1M81VIZaamH+yAvfU

kBimx8JkyHkLuYYRQyyks78Orf2lIibIqLFdmIzuDCjUYCxBKfAfj3BLnM8+qtVIDtYOpNim

osG5WUkVe3vF4g5D4fzh+lCNjIZySOoOaZ+yPSfMmO7Cdbrw52z3Jnjvko6Uml7WVxne5esn

Q7YRFEYYCwZTi8TCUysCHsFl5HCLsFOPlT4zxMD3g76p04pXAcvcsRpVSPLh+aOFUwyTdS7X

7/TGqjBL+Hq8Oig/rXpH

You can directly execute Obfuscated script on an Oracle DB.

I am looking for something similar in MS-SQL.

Thanks again,

Gaurang

gaurangsn SSC Rookie Points: 49 More actions · Answer 5

Hi Lowell

May I have misunderstood approach suggested by you.

I believe what you are suggesting is to create binary code for my SQL script and and then distribute only that part.

i.e.

declare @_ as varbinary(max)

set @_ =0x0D000A0041004C005400450052002000700072006F0063006500640075007200650020005B00640062006F005D002E005B00730070005F00660069006E0064005D000D000A00200020004000660069006E00640063006F006C0075006D006E002000760061007200630068006100720028003500300029000D000A00410053000D000A0042004500470049004E000D000A00200053004500540020004E004F0043004F0055004E00540020004F004E000D000A0020002D002D007000720069006E00740020006F0062006A006500630074005F006E0061006D006500280040004000500052004F0043004900440029000D000A002000530045004C004500430054000D000A002000200020005400610062006C00650046006F0075006E0064002C000D000A0020002000200043006F006C0075006D006E0046006F0075006E0064000D000A002000460052004F004D000D000A0020002000200028000D000A002000200020002000530045004C004500430054000D000A002000200020002000200020003100200041005300200053006F00720074004F0072006400650072002C000D000A002000200020002000200020007300790073006F0062006A0065006300740073002E006E0061006D00650020004100530020005400610062006C00650046006F0075006E0064002C000D000A0020002000200020002000200027002700200041005300200043006F006C0075006D006E0046006F0075006E0064000D000A002000200020002000460052004F004D0020007300790073006F0062006A0065006300740073000D000A0020002000200020005700480045005200450020007300790073006F0062006A0065006300740073002E0078007400790070006500200049004E00280027005500270020002C0027005600270029000D000A0020002000200020002000200041004E00440020007300790073006F0062006A0065006300740073002E006E0061006D00650020004C0049004B004500200027002500270020002B0020004000660069006E00640063006F006C0075006D006E0020002B0020002700250027000D000A00200020002000200055004E0049004F004E00200041004C004C000D000A002000200020002000530045004C004500430054000D000A002000200020002000200020003200200041005300200053006F00720074004F0072006400650072002C000D000A002000200020002000200020007300790073006F0062006A0065006300740073002E006E0061006D00650020004100530020005400610062006C00650046006F0075006E0064002C000D000A0020002000200020002000200073007900730063006F006C0075006D006E0073002E006E0061006D006500200041005300200043006F006C0075006D006E0046006F0075006E0064000D000A002000200020002000460052004F004D0020007300790073006F0062006A0065006300740073000D000A0020002000200020002000200049004E004E004500520020004A004F0049004E00200073007900730063006F006C0075006D006E00730020004F004E0020007300790073006F0062006A0065006300740073002E00690064003D0073007900730063006F006C0075006D006E0073002E00690064000D000A0020002000200020005700480045005200450020007300790073006F0062006A0065006300740073002E0078007400790070006500200049004E00280027005500270020002C0027005600270029000D000A0020002000200020002000200041004E004400200073007900730063006F006C0075006D006E0073002E006E0061006D00650020006C0069006B006500200027002500270020002B0020004000660069006E00640063006F006C0075006D006E0020002B0020002700250027000D000A0020002000200029002000200058000D000A002000200020004F0052004400450052002000420059000D000A002000200020002000200053006F00720074004F0072006400650072002C000D000A00200020002000200020005400610062006C00650046006F0075006E0064002C000D000A002000200020002000200043006F006C0075006D006E0046006F0075006E0064000D000A0045004E004400

exec (@_)

In above case it might work for me.

thanks for your inputs.

Gaurang

Lowell SSC Guru Points: 323547 More actions · Answer 6

yes that was exactly what i was trying to show you; i just included the whole code so you could see how to obfuscate a specific script;

you can see that you could have a stack of exec @_ commands, or that the @_variable can contain contain multiple statements as well.

for your procs,functions and views, you could even include the WITH ENCRYPTION clause, to make it even harder to see the end results after the script is run;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

gaurangsn SSC Rookie Points: 49 More actions · Answer 7

Yupp...:-)

Got the idea you were suggesting, little late though.

Thanks Lowell.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 8

Gosh... this seems like a lot of work to keep the honest man honest and does nothing to actually prevent the viewing of the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

Except that entire thing is defeated by:

declare @a as varchar(max)

set @a = convert(varchar(max),@obfoo)

print @a