The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

Running SQL Remote 8.0.3 with the FILE message type I would like to modify the message file names that are generated by SQL Remote.

The main reason is to prevent "suspicious" file extensions (as is possible since V 10 with the "invalid_extensions" option). So when the consolidated database creates message files like "remote1.exd", "remote1.exe", "remote1.exf", chances are high that the second file might not get transferred to the client because of the suspicious EXE extension.

In our case this is a problem, as we use HTTPS to do the real file transfer, and files with extensions like "cs", "exe", vbs" (that will occur over the time) will block the transfer. Obviously the underlying protocol (WinINet) seems to prevent such file downloads and uploads.

Therefore I'd like to rename each message file by adding a "neutral" extension like ".dat". In the above case, the files would then be named "remote1.exd.dat", "remote1.exe.dat"...

This seems to work but I would like to know if there are any caveats or if it is alright to do so.

Current test results:

According to my tests, the message file name seems to be of minor importance to SQL Remote: When several files are in the message queue, SQL Remote will open them in file name order (so "remote1.exd" will be received before "remote1.exe"). But the guaranteed message tracking system makes sure messages are applied in the correct order. So a "wrong file name" might just disturb the order in which files are received but not the order in which messages are applied.

Furthermore, just adding an second extension won't change the file order, so even the message receiving phase should be o.k.

asked 23 Apr '10, 22:06

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

Just to clarify: The renaming would take part outside of SQL Remote. The "surrounding app" would rename the generated files before they get transferred.

(23 Apr '10, 22:10) Volker Barth

How about just adding a fixed 4th letter in front of the current three letters of the extension? As in .Xexf, .Xexe etcetera.

(25 Apr '10, 12:10) Breck Carter

@Breck: If the fime name as such is not relevant (that's what my question is about:) then your suggestion is a good one. I guess 4-character-long extensions are uncommon and as such not critical. However, as SQL Remote uses 1-3 characters, adding (or appending) a fixed character might lead to new problems, say when an uncritical "ls" extension would be renamed to "Xls" (if that is suspicious). So adding a digit might be less problematic.

(26 Apr '10, 07:52) Volker Barth

@Breck: Besides my above remarks, I also thought about that suggestion: Our current client-side SQL Remote wrapper expects that the inbox only contains files with a file title that equals the consolidated publisher's name. So adding a character to the extension would be o.k. In contrast, adding an additional extension would turn the original extension into a part of the file title (from "cons" to "cons.exf") and as such would violate that constraint so I would first have to adapt all client software.

(26 Apr '10, 07:59) Volker Barth

Answer as given by Nick Elson [Sybase iAnywhere] in the Replication newsgroup:

FWIW neither directory order (neither random or sorted) or file name order should be fatal to the reciever. If the files were to all come out of the directory scan (ftp NLIST, etc) in the same order they were created than processing of those messages can go a little quicker, but SQL Remote is designed to inspect the contents of the files and reopen them in the correct order (when directory scan order is not predicatble).

One caveat I am aware of:

I would not do the renames in a location exposed to either the sending (at the source) or the recieving (at the destination) phases; just in case the order of operations at the directory level may get confused. File name lookup and file opens/reads/writes/delete can be sensitive to timing and may need the file name to be stable at more that one point.

That is not to say I can guarantee the renaming the files with .dat will always work. Renaming the files back after transfering them is an obvious option that should help guarantee this will always work.

Some folks have also zipped up these directories for this.

permanent link

answered 27 Apr '10, 10:54

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 28 Apr '10, 11:45

@Nick: In our case, the files that are created by SQL Remote are moved immediately to the https resource the clients connect to. The rename operaton will only take part on the https resource so that shouldn't interfere with SQL Remote's file handling (which could otherwise lead to those problems you point to).

We already do zip the files in the https resource overnight, so they are transferred in compressed format. However, the files created over the course of the current day are the ones that now and then lead to problems because of their particular extension.

(27 Apr '10, 10:56) Volker Barth

Answer as given by Reg Domaratzki [Sybase iAnywhere] in the Replication newsgroup:

First Point : We will never test this, so we can't guarantee it will work. However ....

I can't think of a reason this would cause any problems, unless you attempt to change the name of a file that is currently opened by the sending or receiving phase of SQL Remote, or rename a file to another file name that SQL Remote will want to use in the future.

I would suggest appending a character to the three-character extension, instead of appending ".dat" to all files. Paranoid me wonders what would happen if you had a remote user named "rem1" and "rem1.exe". The remote user "rem1" generates a message called "rem1.exe" and the "rem1.exe" user generates a file named "rem1.exe.dat". You then rename the "rem1.exe" file to "rem1.exe.dat" and delete a file. By picking a four letter extension, you'll guarantee that you won't generate a file name that SQL Remote will want to use.

And my response:

Adding some more characters to the file extension seems to be the easiest solution; however, as SQL Remote uses 1-3 character extentions, adding just 1 or 2 characters could still lead to duplicate file names: For example, if we would always add "0" to all extensions, a freshly created "cons.00" would conflict with the renamed file that would originally be called "cons.0".

Therefore I guess we will use a 3-character suffix so that all renamed files have different names. Alternatively, we could use a 1-character suffix consisting of a character that is not used by SQL Remote, such as "_" or "-". But that might be less obvious.

permanent link

answered 28 Apr '10, 11:45

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

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: 23 Apr '10, 22:06

question was seen: 1,248 times

last updated: 28 Apr '10, 11:45