cancel
Showing results for 
Search instead for 
Did you mean: 

Generate an encrypted script in sybase

5,332

I wonder if it would generate an encrypted script in sybase? Example of the content of the script:

begin
    T declare integer;
    Set T = (select table_id from SYSTABLE where TABLE_NAME = 'TBTABLE');
    if not exists (select null from SYSCOLUMN where table_id = T and COLUMN_NAME = 'Field_1') then
       alter table add TBTABLE Field_1 char (1) null default 'N';
       comment on column TBTABLE.FIELD_1 is 'COMMENT';
       update set TBTABLE Field_1 = 'N' where Field_1 is null;
       alter table modify TBTABLE Field_1 not NULL;
    end if;
end

If yes, what tool can using and enjoying?

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

OK. Gentlemen, I implemented a program to encrypt and decrypt files (. Sql) with that when the user opens the file can not view its contents. Consider this topic as resolved. Thank you all.

VolkerBarth
Contributor
0 Kudos

Feel free to "accept" your own answer to mark it as "solved".

Answers (3)

Answers (3)

jeff_albion
Product and Topic Expert
Product and Topic Expert

From a client program (Delphi) you must send plain SQL text queries to the database server in the native database character set. As discussed in a previous question, we do not have any 'client side' encryption functions (for Delphi's direct usage to encrypt a file client-side without use of the database server). All user-accessible data encryption functions for SQL Anywhere exist inside the database server-side (via SQL).

So, where do you wish the "SQL" to be stored ultimately? On the client-side, or on/in the database server?

On the client, if you wished to store the script in a secure fashion for your Delphi program to read, I would recommend looking at encryption libraries for Delphi to encrypt the SQL script to disk, and then decrypt it again just before you're about to send it to the database server - I've heard that 'LockBox' (link) is a pretty popular encryption library for Delphi.


If you're looking to store the script in the database for execution, Justin's suggestion is the best one: if it's unchanging SQL code, try to code it as a stored procedure/function/view/event/etc. and hide the script contents via SET HIDDEN.

If you need to dynamically execute secure code that you want to store in the database, you would need to create your own logic: as Justin and Volker mention, you'll need to store the script contents via ENCRYPT against a table and try to execute it with "EXECUTE IMMEDIATE" after a DECRYPT - but you'll have to be careful how you code this, particularly if you want to return result sets from this code.

justin_willey
Participant

If you are looking to hide the contents of a function or stored procedure etc from end users - look at the SET HIDDEN option in ALTER FUNCTION, ALTER VIEW, ALTER EVENT, ALTER TRIGGER or ALTER PROCEDURE. Docs for ALTER FUNCTION here and also see this.

0 Kudos

Hello Justin Willey, My intention is to generate a file (. Sql) encrypted, the user can not view the contents of the file (script). Develop a program to run the files (. Sql) using the Delphi language, but do not want the user to view the structure of my database because of security issues for the company.

justin_willey
Participant
0 Kudos

Hi - if you are going to run the script using a Delphi program, then you can use any encryption method you like - you just need to get your program to decrypt it as it passes it to the database, but maybe I'm missing something?

You will then need protect the database schema against being viewed using the normal database security. If your program managee the user logins, then presumably you won't need to give the users their own database level logins - so they won't be able to view structure. You might also want to encrypt the client - sever communications to prevent those giving away anything important. Similarly you can encrypt the database file itself.

0 Kudos

Ok, got it. Can I develop a procedure or function to encrypt the file using any method. But I thought I'd make it through some of the Sybase tool, but apparently there is no such tool? Interactive SQL type (I write my script - file (. Sql), and then say -> Option -> save as?

Thank you for your attention.

justin_willey
Participant
0 Kudos

Hi - There is the SQLAnywhere ENCRYPT function, but then you'd have to pass the script backwards and forwards to the database- so probably simpler to use something in Delphi. http://dcx.sybase.com/index.html#1201/en/dbreference/encrypt-function.html*d5e15782

0 Kudos

Ok Develop a program to encrypt a file (. Sql)? I thought I could do it all using a feature of the Sybase database.

VolkerBarth
Contributor
0 Kudos

Obviously an encrypted script has to be decrypted before it can be applied (at least if that is not just obfuscation). - How would you think should SQL Anywhere handle this (i.e. ask for the key) with a builtin feature?

As Justin has suggested, a general solution might be to create a stored procedure that can be called with a file name, and that checks the file for any "magic value" to verify it's a valid script, and then decrypt and apply the contents. Obviously, you could then both use a fixed key (known within the stored procedure) or apply it somehow else... - Error handling might be the hard part here.

Note, I don'claim that is easier to build than a helper program to apply encrypted scripts, however it seems doable with the builtin SQL features.

Former Member
0 Kudos

Interestingly this feature, I could also use. @Volker-Barth have any examples that you can share?

VolkerBarth
Contributor
0 Kudos

No, it's just a suggestion - I haven't had the need for such a feature. However, I guess it would be rather easy to build this.

VolkerBarth
Contributor

Another approach would be to use the builtin DBFHIDE tool to obfuscate the script and use "DBISQL @data" to apply the script.

However, in my little tests, that seems somewhat limited - possibly this feature is not meant to obfuscate whole script files but only connection information and the like.