Handling Binary Large Objects (BLOBs) in MS SQL Server Using the InduSoft Web Studio Database Gateway (StADOSvr.exe) | InduSoft
Schneider Electric’s industrial software business and AVEVA have combined to create a new global leader in engineering and industrial software. For more details click here.
Call us! 512-349-0334 or (877) INDUSOFT

Handling Binary Large Objects (BLOBs) in MS SQL Server Using the InduSoft Web Studio Database Gateway (StADOSvr.exe)

Introduction and Discussion of BLOBs

InduSoft Web Studio users sometimes need to handle SCADA or HMI data that are stored in formats which are outside of the ranges of InduSoft Web Studio tag types. InduSoft Web Studio handles Integer, Real, Boolean, and String.  However, in some cases, data records are simply image files stored in a database in “Image” or “Large Binary” column format, and this data won’t map to any of the InduSoft Web Studio tag types.

It is therefore necessary to handle these types of data in their native file formats once they are retrieved from the database, and preserve and restore the “file type” when it is reconstituted from the binary data in the database record.  In order to utilize the data, an application must be installed on the runtime machine or client that can open the particular file type or extension that is reconstituted from the binary data.

An example of this issue can easily be seen when restoring different types of picture/media formats. Pictures, which are some of the most common types of large binary data saved in a database, can be reconstituted only if it is known what the file type is.  Some applications cannot display a .tiff picture, but can display a .png; while other applications or programs may not be able to display or play an .mp3 or .mp4 file but can display a .gif animation or an .avi file.  If the retrieved data is saved as a wrong file type, the associated application that can read the file type will not understand the data in the file.

Any type of binary data can be saved in a database, and it can be reconstituted as a standalone disk file only if it is known what type of file the binary data represents. When that data is copied from the database and saved as a disk file, the correct extension must be assigned to that file in order for it to “work”.

Using the Studio Database Gateway (StADOSvr.exe) to Handle BLOBs

In the InduSoft Web Studio Help Manual (Technical Reference) there is a section on setting up and using the Studio Database Gateway in various modes of database connectivity. For this particular application however, we will demonstrate how to programmatically access the database and handle the data through the use of VBScript and ADO (ActiveX Data Objects).  Further information about what ADOs are and how to utilize them is available online through many sources including the MSDN and W3Schools.

It is beyond the scope of the article to explain how the ActiveX Data Objects (ADOs, a Microsoft® Technology) programmatically work, and you should use the excellent resources discussed above in order to learn more about them, since there are many classes, properties, and configurations associated with them.  For our demo app however, we will discuss the well-documented scripts and logic flow through the application in order for you to understand how the objects are used and why the application works like it does, so that you can use the same functionality in your own applications.

When using ADOs, it is not necessary to create a DSN (Data Source Name) and it is also not necessary in this example to utilize any database configuration within the InduSoft Web Studio application. Additionally, since no InduSoft tags are being mapped to the columns in the database, it is not necessary to use a Database/ERP Worksheet either.

Downloading, Installation and Configuration

The BLOB.app application for InduSoft Web Studio is located in our Sample and Demo app library.  It is necessary to install Microsoft SQL Server, and if your machine already has an instance on it, then you don’t need to do anything else.  This application was tested using a new installation of Microsoft SQL Server Express 2014.  This version no longer creates a unique instance name and by taking the default installation settings, it will create a SQL Server instance with the hostname of the machine that it is installed on, unless one already exists.  You should install SQL Server using Mixed Mode for this demo application and set a password for the administrator credentials in order to facilitate seeing how this application runs on your machine.

How It Works

Since there is no Database/ERP Worksheet utilized in this application, it is necessary to start the StADOSvr.exe manually, and stop or kill the process when connectivity to the database is needed in order to drop the database and recreate it in SQL Server Management Studio.  Therefore in the Home Screen Screen Script in the “Screen_OnOpen()” subroutine:

blob1

…and in the Demo Screen Screen Script in the “Screen_OnOpen()” subroutine:

blob2

…is the Module Activity Function “WinExec()”stopping and starting the StADOSvr.exe process when the screens respectively open.

When the application first starts up, on the Home screen are full instructions on how to use the application along with an input field showing the default connection string that was used to test the app:

blob3

Change the connection string to your own SQL Server instance name and enter some credentials that will give access to create and drop databases and tables.

After you have entered your connection string (you can change it to be the default by modifying the Startup Script and changing the connection string to your own), then push the big button at the bottom. This will open the file “blobTable.sql” located in the “\SQL” folder of the application. This file will invoke the SQL Server Management Studio logon and the file will load as a query ready to be run.  After logging on, execute the query and when the table is (dropped, if it exists, and) created, you can close SQL Server Management Studio:

blob4

Next, push the “Demo” button and the Demo screen will open and also start the StADOSvr.exe application.  Several picture files are located in the “\Files” folder, and pushing the “Search” button will allow you to select one of them, which populates the field at the top of the screen. The “Insert BLOB to DB” button appears under the grid object which, when pushed, will call the function, “SaveFileDB”. This function writes the file to the database and the script in the button populates the grid with the database contents and refreshes it:

blob5

blob6

Three parameters are passed to the function: the connection string, the table name, and the file path.

blob7

 

Next, the “Save Selected BLOB as Picture File” button appears, and pushing it will call “LoadFileDB()”. The only parameter that needs to be passed is the connection string. It will query the database for the BLOB and the associated file name in the same record, and write the file to the disk after it is reassembled.  Once the new file exists, the Linked Picture location field contains a string tag that has the file name and location of the last file created, and displays the image after the screen is refreshed by an “Open()” statement:

blob8

 blob9

Finally, pushing the “Erase Database and Picture Files” function “DropTable()”, passing only the connection string, simply drops and recreates the table and deletes the recreated files in the “\Received” folder in order to clean up and reset everything:

blob10

blob11

You can see from the InduSoft Web Studio global functions that the SQL query is simply passed to the database engine using an ADO “ADODB.Recordset” property as a parameter, as is the connection string.

Here is a short video showing how the application works in real time.

Security Concerns and This Application

A certain amount of judicious care should be used when working with binary files in databases. It is possible that binary files can be inserted or assembled in a Large Binary record that may be executable when reconstituted, and therefore could be potentially malicious. Additionally, it also is possible that such data could escape or circumvent virus detection, since binary data in databases is just simply binary data without the disk operating system filename and extension construct to contain them.  It seems possible therefore, however unlikely, that a malicious stored procedure could be used to assemble and direct such binary data to a location and application that could possibly be activated by some common means or even by an innocuous application.

Additionally, it should be noted that this InduSoft Web Studio demo application may not reflect industry best practices, and should only be used for training purposes. These best practices include not sending usernames and passwords for database access in clear text using unsecured or unencrypted protocols, nor of not obfuscating them when exposed, since they are both in clear text in the Startup Script in order to populate the connection string tag.

InduSoft Web Studio has many security features available that should be utilized when using the Studio Database Gateway in your applications; along with a number of resources, webinars, eBooks, and On-Demand Engineering Assistance to help you in creating applications that are functional, operational, and secure.

Comments are closed.