Detection of virus infected file SQL Remote replication

[JimDiaz]

Lets say a user inserted a small file into a SQL Anywhere database that was infected with a worm and or virus and for some reason it was not detected on the users local computer. If SQL Remote replicated the file, would or could a virus scanner detect the virus? Lets assume file based replication and the message size was large enough that the file was replicated in a single message.

To go further I would also ask if SAP recommends the scanning of file based replication messages as well as if there is a best practice associated with keeping viruses out of blob or long binary database fields.

Thanks in advance...

Jim

[Vlad]

I do not like the practice, when files are stored in the database. This is expensive and difficult to manage. I would also assume that if the replication channel is encrypted, but the message size as you said is large enough, probably the network antivirus will not find the malware.

So, my suggestion is to avoid uploading of the files that might contain viruses (unless you are developing the nextgen Dro**ox killer + do not store files in the database, but use a network share folder + maintain metadata in the DB.

[VolkerBarth]

but use a network share folder + maintain metadata in the DB.

Given the fact that the OP uses SQL Remote to replicate files between remotes and the consolidated database (and therefore possibly share them with other remotes as well, I dunno), how would you do that easily when not storing them in the database? - In those cases there generally is no network share to access from all users, otherwise they would not need to use replication at all but could use a central database...

So you would need to build your own file transfer facility outside SQL Remote and furthermore make sure it follows SQL Anywhere's transaction semantics... - very difficult to achieve, I' say.

I do not like the practice, when files are stored in the database. This is expensive and difficult to manage.

Even for a normal database without a replication/synchronization setup, I would argue that it really depends on the situation whether files should be stored within or outside the database. There are enough pros and cons for both methods, so I would not be able to do a general recommendation for either one.

---

And with both methods one should prevent users from storing virus-infected files, but that's no database task IMHO:)

[Vlad]

Thank you for your reply, Volker. That is why my comment wasn't the answer, but just my opinion.

In my world 🙂 I would use the network shared folder, plus the web server that serves the users' requests. Actually file system can be scaled either as RAID (http://www.thecloudcalculator.com/calculators/disk-raid-and-iops.html), or the clustered file system. Web servers are usually independent from each other, so you can increase their number also "indefinitely".

If we are talking about ten users and five files then... well 🙂 My few words is in this universe I can and I like to Google. The articles like this one (http://programmers.stackexchange.com/questions/150669/is-it-a-bad-practice-to-store-large-files-10-m... give me ideas that sometimes the DB should not be used as a file storage.

And the second thing, I have no experience in the SQL Replication outside of few tutorials 😞

[JimDiaz]

The files are required to replicate to and from the various remote sites. Currently we hash the files prior to storage to remove duplicates and store application specific metadata in the database. We also limit file size and types depending on the specific application module requirements. Our database serves both legacy client server and more recent three-tier MVC application with a browser front end. I believe we will implement a custom server side solution (middle tier most likely) solution for the virus issue.

The question of in database file storage is an interesting one, I believe file size may be the correct answer storing files < a certain size in the database and >= the size in an external directory, however I don't believe a share would be necessary with the file system capabilities in SQL Anywhere. For replication one approach may be to replicate the files and than store them in the file system using an after insert trigger but this would need development and testing.

Thanks for the comments...

Jim

[regdomaratzki]

I've no idea how a virus scanner detects a virus inside a file, so I've no idea how to answer your first question. I can say that a blob stored in the database and replicated will be broken into chunks that are equal to the maximum message size and then the content of the message is compressed. It's unlikely that the original bit pattern of the blob will be in the message file that is placed in the operating system and available to a virus scanner.

It is a database's job to store data, and SQL Remote's job to send that data to the databases you've told us to send it to. SAP SQL Anywhere does nothing to ensure that the data you are using is free of viruses. If that is a concern, you should use a virus scanner on the files before placing them in the database.

Reg

[VolkerBarth]

Just to add: You can usually try with the particular EICAR files whether your Antivirus will recognize known virus-infected files.

We've done so when checking whether a HTTP based file transfer would notice/block these files. IIRC, the behaviour was different when the "real" files or archives were uploaded in contrast to embedding the "virus" signature in a message block - the later would go unrecognized (not unexpectedly)...

[JimDiaz]

Thanks we will use this when testing our in house solution to this problem.

Jim

[JimDiaz]

I believe the answer from SAP is

If SQL Remote replicated the file, would or could a virus scanner detect the virus? NO ; Does SAP recommends the scanning of file based replication messages? UNANSWERED; Does SAP recommend a best practice associated with keeping viruses out of blob or long binary database fields? NOT OUR JOB

Sounds like an opportunity...

[Vlad]

NOT OUR JOB

I have read your answer several times, and I still cannot understand whether it sounds offensive or not. My opinion is that the database is responsible to store bytes in the most efficient way. If it scans yours every particular query with an anti-virus, you will immediately start complaining regarding the performance.

The same can be applied to the replication mechanism, when you want to get the efficient data transferring + security.

Believe me, if the replication transferred records with a single-byte packets, none of anti-viruses would be able to catch a virus. The scanning of the DB per operation is inefficient as well.

Well... what else can I recommend you to help you with the topic? Please, google first: http://security.stackexchange.com/questions/26710/security-malware-implications-of-storing-data-in-a...

[Former Member]

Scanning the replicating messages won't help. As Reg. mentions above, they are in the database log format and so are unrecognizable to a Virus scanner.

As far as best practices go, here are some of the things you could do to mitigate/prevent this problem. This is not meant to be an exhaustive list.

• Validate (ie. scan) input/ouput before it gets to the database

• Setting appropriate permissions for users in the OS (ie. limit/prevent them from doing malicious things)

• Use the database sandboxing feature

• Implement a 'quarantine' area for uploaded files where they would be separately validated before being 'approved' for use when downloaded

[Breck_Carter]

> you will immediately start complaining regarding the performance

Ha! ...in this particular case it is a leap too far ( from "cannot understand" to predicting a clueless complaint 🙂