#filestream

The getchar() Of Today Is:

knightos! this one is cheating a little bit, because knightos's libc doesn't actually have a getchar() function. knightos is designed to run on texas instruments calculators, and the normal ideas of standard input and output don't make a whole lot of sense on a calculator.

in fact the knightos libc is very unorthodox in general - it's not posix-compliant and many things you'd expect to have available are missing. it's very interesting to look through !

since there's no getchar(), instead we're looking at the code for the read_byte() function, which is knightos's nearest equivalent to the fgetc() function that getchar() usually calls. it takes a stream id, which is akin to a file descriptor under posix, and reads the next char from the stream.

My company needed to encrypt our SQL Server 2008 Standard databases. Standard edition doesn’t support TDE, but Enterprise edition does. I evaluated a couple of different third party encryption programs (NetLib Encryptionizer and ActiveCrypt DbDefence), but neither was satisfactory, and would still leave us with an aging SQL Server product. This led to the decision to purchase SQL Server 2014 Enterprise. Once the software arrived, I installed it and turned on encryption, but noticed that the filestreams weren’t being encrypted in the filesystem folder of the Windows server. I searched Google for a solution, and found a page with the following note:

Transparent Data Encryption and FILESTREAM DATA

FILESTREAM data is not encrypted even when TDE is enabled.

I wanted to make SQL Server 2014 work, but didn’t want to go back to researching third-party software. So I built my own solution using Visual Studio 2010, VB.NET, the .NET 4.0 framework, and SQL Server 2014.

Here is the process, step by step, for encrypting filestreams using my solution. Most of my process can be duplicated by copying and pasting the code below, being careful to modify it to match your object names and record structures. I’ve marked several object names in boldface to make them easier to find and replace, but you’ll have to watch for other differences between these examples and your target environment.

Step 1 – You need the keys to fit the lock.

You need two things for AES encryption to secure a file: a 128-bit passkey, and a 128-bit initialization vector. The passkey is something you choose once—just select 16 very random characters for a high entropy key. That same passkey will be used to encrypt and decrypt every file. But every file should use a different init vector. Since I used a uniqueidentifier data type (GUID) as the primary key in my filestream record, that gives me 128 bits that are unique to each file. Perfect! My filestream record looks like this:

create table [dbo].[FileAttachment] ( [AttachmentId] uniqueidentifier rowguidcol not null ,[Attachment] varbinary(max) filestream not null ,[Filename] varchar(255) not null ,[Description] varchar(100) null ,[Uploaded] datetime not null ,constraint [Pk_FileAttachments_AttachmentId] primary key clustered ( [AttachmentId] asc ) );

If you’re not using a GUID in your record structure, you’ll have to find another way to choose a pseudorandom init vector for each file that you want to encrypt, and you will have to adjust some of the code below. Keep in mind that you need to use the same init vectors to decrypt each file.

Step 2 – Paste this code and compile it.

Open your Visual Studio solution. Create a new Visual Basic SQL CLR Database Project and give it a name. Inside that project, add a new User-Defined Function item and give it a name. The code editor will open for your new function. Overwrite that whole chunk of code with this:

Partial Public Class UserDefinedFunctions

   ' This function either encrypts or decrypts a block of data.  Designed primarily to encrypt filestreams.  The maximum data size this will handle is unknown.  SQL Server says varbinary(max) has a maximum file size of whatever the filesystem will handle, but I don't know how much this CLR function can handle before it blows up.    '    ' Parameters:    '    ' InputData: This is the file being uploaded into the filestreams.    ' Encrypt: True = encrypt, False = decrypt    ' Passkey: 16 byte encryption key. If fewer or more bytes (characters) are passed in, this function still receives exactly 16 bytes and either pads with spaces if too few, or truncates if too many.    ' InitVector: 16 byte encryption initialization vector.    '    ' Returns: Either the encrypted or decrypted file contents, depending on the value of the inbound Encrypt parameter.    '    '

   <Microsoft.SqlServer.Server.SqlFunction()> _    Public Shared Function CryptoBytes(InputData As SqlTypes.SqlBytes, Encrypt As SqlTypes.SqlBoolean, PassKey As SqlTypes.SqlString, InitVector As SqlTypes.SqlGuid) As SqlTypes.SqlBytes

       Dim SecretKey As Byte() = System.Text.Encoding.Unicode.GetBytes(PassKey.ToString) ' 128 bits (16 bytes)

       Using Aes As New System.Security.Cryptography.RijndaelManaged()

           Aes.Key = SecretKey ' Must be 128 bits            Aes.IV = InitVector.ToByteArray ' Must be 128 bits

           Dim Transformer As System.Security.Cryptography.ICryptoTransform = CType(IIf(CType(Encrypt, Boolean), Aes.CreateEncryptor, Aes.CreateDecryptor), Security.Cryptography.ICryptoTransform) ' Choose which method to use, either encryption or decryption

           Using OutputData As New System.IO.MemoryStream()                Using OutStream As New System.Security.Cryptography.CryptoStream(OutputData, Transformer, System.Security.Cryptography.CryptoStreamMode.Write)                    OutStream.Write(InputData.Buffer, 0, CType(InputData.Length, Integer))                    OutStream.Close()                    CryptoBytes = New SqlTypes.SqlBytes(OutputData.ToArray) ' Return value                End Using            End Using

       End Using

   End Function

End Class

Then build the project for Release (not Debug). You should have a DLL in \bin\Release under your project folder. Copy that DLL and paste it to your desktop on your SQL Server.

Step 3 – Enable and connect your code.

This script does two things.  First, it enables CLR on your database.  Then it creates a SQL Server function as an interface to the CLR function you created in the previous step.  You will never call the CLR function directly.  Instead, you’ll call the SQL Server function which invokes the CLR function for you

use YourDatabaseName; go

sp_configure 'show advanced options', 1; go reconfigure; go sp_configure 'clr enabled', 1; go reconfigure; go

create assembly YourDLL from 'C:\Users\YourProfile\Desktop\YourDLL.dll' with permission_set = safe go

create function [dbo].[Function1] ( @InputData varbinary(max) ,@Encrypt bit ,@PassKey nchar(16) ,@InitVector uniqueidentifier ) returns varbinary(max) as external name YourDLL.[YourDLL.UserDefinedFunctions].Function1; go

After performing this step, you can safely delete the DLL from your desktop on the SQL Server.  The DLL is loaded into your database, and is included in any database backups (.bak) that you create.  You can even restore the database to another server and the DLL will go with it.

Step 4 – Lock up your key.

Now that you have an encryption mechanism in place, you need to choose a 128-bit key and hide it somewhere. I hardcoded my key into a standard User-Defined Function within my database. Since the database itself is encrypted with TDE, it’s a safe place to put the key for the external filestreams. Here is the code to execute on your database to create the key:

create function [dbo].[Key128]() returns nchar(16) with encryption as begin declare @Result nchar(16) set @Result = '1234567890123456'; -- must be 16 characters return @Result end go

Notice the “with encryption” clause in the code above. This obfuscates the function so that it cannot be opened for reading or modifying.  Attempting to view the function results in this error:

In order to change the encryption key, you need to drop the function and re-create it with the new hardcoded key value. Of course, if you change the key after you encrypt your filestreams, you may never be able to decrypt them again. Either find a way to use multiple keys, or decrypt everything with your old key before re-encrypting with your new key.

Step 5 – Add encryption to your stored procedures.

All you have to do now is call the function whenever you reference your filestream column, specifying ‘true’ to encrypt, or ‘false’ to decrypt.

To encrypt: [dbo].[Function1](<filestream>,'true',[dbo].[Key128](),<initvector>)

To decrypt: [dbo].[Function1](<filestream>,'false',[dbo].[Key128](),<initvector>)

Here is my stored procedure to create/save one filestream record:

create procedure [dbo].[AttachmentSave]

@Attachment varbinary(max), @Filename varchar(255), @Description varchar(100) = null

as

declare @AttachmentId uniqueidentifier = newid();

insert [dbo].[FileAttachment] ( [AttachmentId] ,[Attachment] ,[Filename] ,[Description] ,[Uploaded] ) values ( @AttachmentId ,[dbo].[Function1](@Attachment,'true',[dbo].[Key128](),@AttachmentId) ,@Filename ,@Description ,getdate() ) ;

And here is my stored procedure to read one filestream:

create procedure [dbo].[AttachmentRead]

@AttachmentId uniqueidentifier

as

select [dbo].[Function1](Attachment,'false',[dbo].[Key128](),AttachmentId) as [Attachment] from [dbo].[FileAttachment] where AttachmentId = @AttachmentId ;

Step 6 - Encrypt all your existing filestreams.

With your solution in place, it would seem that there is a simple way to encrypt all existing filestreams, and indeed this would work with a small set of filestreams:

update FileAttachment set Attachment = [dbo].[Function1](Attachment,'true',[dbo].[Key128](),AttachmentId) ;

But if you have thousands of filestreams to encrypt, this statement could bring your server to a grinding halt while it tries to complete your request within one gigantic transaction.

A safer way to do this is with a simple loop. Here’s a paste-and-go example. Remember to modify this code to match your environment:

set nocount on

-- Make a list of all the files that need to be encrypted

declare @Unencrypted table (AttachmentId uniqueidentifier);

insert @Unencrypted (AttachmentId) select AttachmentId from dbo.FileAttachment;

declare @AttachmentId uniqueidentifier; declare @Total float = (select count(1) from @Unencrypted); declare @Counter float = 0;

-- Process the attachments one at a time

while exists (select 1 from @Unencrypted) begin

-- Get the next attachment id that still needs to be encrypted select top 1 @AttachmentId = AttachmentId from @Unencrypted;

-- Encrypt 1 file begin transaction update FileAttachment set Attachment = [dbo].[Function1](Attachment,'true',[dbo].[Key128](),AttachmentId) where AttachmentId = @AttachmentId; commit transaction

-- Delete the id from the list (i.e. mark it completed) delete @Unencrypted where AttachmentId = @AttachmentId

-- Show progress set @Counter = @Counter + 1;

print convert(varchar,@Counter) + ' of ' + convert(varchar,@Total) + ' = ' + convert(varchar,(@Counter / @Total)*100) + '%';

end;

You might notice that this process doubles the number of files in your filestream folder. This is because SQL Server won’t delete your unencrypted filestreams until its filestream garbage collection process kicks off. Under normal circumstances, with routine scheduled database backups and maintenance, your unencrypted filestreams will disappear on their own.

Conclusion

At this point, everything in your filestreams folder should be encrypted except for whatever is on hold for automatic garbage collection.  As long as you keep the encryption key in a safe place, along with all the init vectors for each file, your files will be secure and accessible with the encryption features built by Microsoft and prepackaged into .NET 4.0 and SQL Server 2014 Enterprise.

Updated 7/17/2015 - Replaced AesCryptoServiceProvider with RijndaelManaged to eliminate the MayLeakOnAbort warning when attempting to create the assembly.  Updated the T-SQL script accordingly.

The FileStream type has a Length property. We test whether this is accurate and efficient. We look at an example of using Length on FileStream. And then we benchmark the property. Property Note: FileStream has a Length property that returns the byte count. This property is not cached in memory. It is

Download FileStream Image Broadway

So, a few days after I started college here, I tried to download a few movies by using the very popular Torrent system. However, I soon discovered that my French university's ISP had blocked all ports you can use for P2P downloads.

I tried everything, proxies, free VPN clients (Your Freedom, Cyberghost, OpenVPN, VPNbook, etc.) Nothing worked and all ports remained blocked, which means that my torrents didn't go past the "Connecting to peers" phase and didn't actually download anything. The only one that worked was FrozenWay but the connection was limited to 128kp/s, that is to say nothing, you'd need 4 hours to download 60 megabytes or something.

BUT TODAY, I found the solution, so I thought I would share it with people here because this might help people with a bad Internet or limited connection at home (I myself have a 3G dongle with only16GB per month before the connection is throttled.) It's called downloading a torrent online through your http and not through P2P.

1. Download FlashGet or any kind of free download manager (I use GetFlash, but I suppose other softwares or applets like JDownloader will work as well. I used to have JDownloader, but as it is built on Java, it was kind of slow to respond and a bit unstable as well, although it might just be because I have a Notebook with 1GB RAM.)

2. Go to filestream.me. You'll need to create an account (or just use your Twitter or Facebook account to connect, but I registered an account.)

3. Go to your favorite torrenting website, like Kickass.to. Here, you have to options. You can either download the torrent, or you can copy the link to the download or to the magnet link (right-click on the "Download torrent" or "Magnet" button and select "copy link URL")

4. Go back to filestream.me and login. There you can either paste the link you copied in step 3 or upload the torrent file you downloaded in the box, then click "download."

5. Once the download is complete (that should only take a few seconds), you can select one of the options that appear upon completion: either "download as .zip" or "copy link to clipboard".

6. Select "Copy link to clipboard."

7. Open GetFlash and click on the + symbol to add a new download URL. Paste your download link and confirm.

8. Wait till download is over to extract your .zip file with Winrar or any other equivalent.

9. Don't forget to delete your download link in your Filestream store once the download with GetFlash is complete, this will make room for other files in the future, as you can only have 5 as a free user.

Note: with the free version of Filestream, you will only be able to download torrents up to 1GB, you won't be able to download several files at the same time and you will not be able to keep more than five links at a time in your Filestream store.

You don't have to use GetFlash if you don't want to, you can download the .zip directly through your browser, but know that if your connection fails, what you've downloaded so far will be impossible, whereas in GetFlash, you should be able to stop your download and restart it later without losing what you've already downloaded.

Here you go, that might not be news to everyone but since it helped me, I thought it might help others.