Exporting Attachments Part 2

In my post Export PeopleSoft Attachments using PL/SQL, I mentioned that PeopleSoft provides the File Attachment API for storing, retrieving, and viewing file attachments. These API functions comprise the recommended method for working with attachments. If you want to process an attachment, use the GetAttachment function to move that attachment into a file so you can access it from your app server. The ViewAttachment function, on the other hand, will send a copy of an attachment to a client browser. The ViewAttachment function is the only method provided by the Attachment API that allows a user to view the contents of an attachment. What if you want more control over the way PeopleSoft displays attachments? For example, let's say you use the File Attachment API to allow selected users to upload audio files (news, recorded training sessions, etc) and you have another page that allows other users to listen to those recordings. Should the "view" page display a "View Attachment" link or should it play the selected clip as embedded audio?

If you store attachments in the database using a RECORD:// style URL, then you can extract those attachments using PeopleCode. Here is an IScript that demonstrates this:

Function IScript_GetAttachment()
   Local any &data;
   Local string &file_name = "attachment.doc";   
   Local SQL &cursor = CreateSQL("SELECT FILE_DATA FROM PS_EO_PE_MENU_FILE WHERE ATTACHSYSFILENAME = :1 ORDER BY FILE_SEQ", &file_name);

   REM Set the following header if you want a download prompt. Don't set it if you want inline content;
   %Response.SetHeader("content-disposition", "attachment;filename=" | &file_name);

   While &cursor.Fetch(&data);
      %Response.WriteBinary(&data);
   End-While;
End-Function;

This method allows you to embed audio/video, provide user configurable background images, display inline PDF files, etc.

If you follow my blog closely, you may remember that my IScripts post claims there is no way to write binary data to an HTTP response. Notice the use of the %Response.WriteBinary method above? I was wrong. I was reading the comments of the post Browse the App Server Filesystem via iScript and noticed Joe's reference to %Response.WriteBinary(). Even though it isn't documented in PeopleBooks, I tried it and it worked. Thanks Joe!

Note that I hard coded my SQL statement. I did this for simplicity in this blog post. For production systems, I encourage you to use SQL definitions. Of course, if you implement this solution, you will need to change the SQL select table name to the name of your attachment table. Likewise, you will probably derive your file name from a parameter rather than hard code it as shown here.

As with any custom development, make sure you write secure code. For example, when executing SQL based on parameters, use SQL binds. Failure to use SQL binds can result in SQL injection flaws. Likewise, if you accept an attachment from a user and then display that attachment using a means other than the provided ViewAttachment function, then make sure you either validate that input or validate the output. For example, let's say you have a page that allows users to upload JavaScript or other HTML that will be executed on other pages. Failure to restrict this type of usage provides malicious users with an opportunity to create HTML injection and cross site scripting vulnerabilities.