This system I call "DTS" for Data Transformation System. As I wrote in the previous post, this system is nothing more than an ASP.NET page that loads DLL's (based on the DSNAME parameter in the URL). Then the DLL is responsible for providing a data-table with the content the user requested. The ASP.NET page will then convert the data-table into JSON which the Google Visualization API can use.
Currently I only have 1 plugin (DLL) provided which allows you to connect to a Microsoft SQL Server.
The zip file provided below contains instructions on how to install this system on Windows 2008 using SQL Express 2008. You will also find ALL the source included. Remember this is only a proof of concept and there is probably a great deal of work that can be done to make this system better.
Next week I will explain how to setup and configure new DLL's to allow you to connect to other data sources.
Comments/Thoughts are welcome!
Carsten
8 comments:
Carsten, this is very useful for the projects that I am working on. However, my team is concerned about security using your aspx page. There are two concerns:
1) The SQL is embedded in the HTML, leaving the aspx page vulnerable to SQL injection attacks
2) There is no authentication/authorization. Basically, anyone could use the aspx page to query anything in the database.
Have you addressed these issues in the months since you've released this package? Just thought that I'd check with you before tackling the issues myself.
Thanks for a convenient package!
Hi A_H_Roth,
As this was only a proof of concept, I havent really looked into the security of this system. I mainly use it for internal use security really isnt a problem.
For question #1, you arent required to put the Query in the HTML request. In the SQL database is a table called DefaultParameters, in there you can say if DSNAME = X, then load default parameters. The defaultparemeter could contain your query.
For question #2, you can simply enable Windows Authentication then assigns the users to a group. It wont be a solution for everyone, but it works for my needs.
Hope this helps.
Carsten
Thanks for your response, Carsten. This is helpful.
BTW, I've had to convert your project from VS.NET 2008 to 2005 (So that I could run on WindowsSvr2003 and IIS6). It runs pretty well.
I first used http://www.emmet-gray.com/Articles/ProjectConverter.htm to convert the VS solution and project files from 2008 to 2005.
That left me only one block of code that I had to rewrite. In Default.aspx.vb, I had to change the following block:
---START 2008---
For i = 0 To DefaultParms.Count - 1
' 'If Key already exists (Query URL Perhaps), then delete whatever exists and use Default Parameters
If Params.ContainsKey(DefaultParms.Keys(i)) = True Then
Params.Remove(DefaultParms.Keys(i))
Params.Add(DefaultParms.Keys(i), DefaultParms.Values(i))
Else
Params.Add(DefaultParms.Keys(i), DefaultParms.Values(i))
End If
Next
---END 2008---
...to the following:
---START 2005---
For Each key As String In DefaultParms.Keys
If Params.ContainsKey(key) = True Then
Params.Remove(key)
Params.Add(key, DefaultParms(key))
Else
Params.Add(key, DefaultParms(key))
End If
Next
---END 2005---
As far as IIS 6 settings for the DTS directory, I used the following:
* Made the directory its own application (script & executables permissions)
* Used default app pool
* Set ASP.NET version to 2.0
Hopefully, this will be useful to anyone else who needs it.
Oops. Left out one important step:
In a text editor, you'll have to open DTS.vbproj. Change the following line:
...to this:
...that is...
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\WebApplications\Microsoft.WebApplication.targets" />
...to this:
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v8.0\WebApplications\Microsoft.WebApplication.targets" />
I know I'm posting a lot here, but I've been working pretty intensely with your code. I've made some changes that allow parametrized queries to be stored in the DefaultParameters table. In other words, you could store a "Query" as "EXEC myStoredProcedure @param='{0}'". If the data definition was declared as "MYDS", then, in the URL to the ASPX page, you would just need to do something like "http://myhost.com/DTS/Default.aspx?DSNAME=MYDS&Param1=SomeParameter". The number of parameters allowed is unlimited (Param1, Param2, Param3, ....).
If you're interested, let me know where I can email you the modified code. My best email is a_h_roth at yahoo dot com.
Not a bad idea!
Post a Comment