Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

    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;

If yes, what tool can using and enjoying?

asked 08 Oct '12, 13:32

Walmir%20Taques's gravatar image

Walmir Taques
accept rate: 12%

edited 13 Sep '13, 15:18

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.

permanent link

answered 09 Oct '12, 13:15

Walmir%20Taques's gravatar image

Walmir Taques
accept rate: 12%

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

(10 Oct '12, 04:26) Volker Barth

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.

permanent link

answered 08 Oct '12, 14:07

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

edited 08 Oct '12, 14:09

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.

(08 Oct '12, 14:36) Walmir Taques
Replies hidden

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.

(08 Oct '12, 14:53) Justin Willey

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.

(08 Oct '12, 15:12) Walmir Taques
Replies hidden

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.*d5e15782

(08 Oct '12, 15:15) Justin Willey

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

(09 Oct '12, 11:07) Walmir Taques
Replies hidden

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.

(09 Oct '12, 11:16) Volker Barth

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

(09 Oct '12, 11:31) LGregianin

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.

(09 Oct '12, 11:53) Volker Barth
showing 3 of 8 show all flat view

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.

permanent link

answered 09 Oct '12, 16:43

Jeff%20Albion's gravatar image

Jeff Albion
accept rate: 25%

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.

permanent link

answered 09 Oct '12, 11:57

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 08 Oct '12, 13:32

question was seen: 4,984 times

last updated: 13 Sep '13, 15:18