SQL Server 2000, XML & URL Queries

[wcwilson]

Hi,

Just wanted to pass on a cool way to to add persistence to your app without having to write servlets (with an http data source). SQL Server (with IIS) has a way to embed a query in http (get or post) and get a response back in XML. I have been doing that with a modified contacts app and it works great. I replaced calls to a servlet using JDOM that I had been using and can write all my data access within the lzx.

I created a database in SQL Server 2000 based on the data that was in the contactsdata.xml.

So with the following query, I can retrieve all the data from the db:

http://wwilson/laszlo?sql=select+*+from+result+for+xml+auto&root=resultset

I think there is a way to do the same with Oracle, but I haven't tried it yet.

--Wayne

[hqm]

Can you also do arbitrary SQL commands

http://wwilson/laszlo?sql=drop+table+result&root=resultset

if so, that would be a security problem...

[wcwilson]

You could, but you would probably set up the "URL query" login not to have those type of privileges.

--Wayne

[hqm]

Still, you ought to be careful about allowing the client, which you have no control over, to make or modify arbitrary queries, unless you are really deploying this thing in a known friendly environment. One of the advantages to moving the SQL logic to the server is you have full control over it, even if it does spread the code out into more than once place.

[adamhill]

There is one more step you can try. With SQLXML you can define queries in *files* and even use XSLT to do pre and post filtering.

It will be a bit of work, but with a combo of SQL permissions and clever filtering you can have a *mostly* secure app.

adam...

[swany]

note: I haven't tried this with laszlo. this is
based on experience with other environments.

Well you can always create a shim on the server side as well. Create a script on the server that takes a number of paramters. This basically amounts to writing middle tiered data access stored procedures. The script on the server can contact the database via HTTP. Use an arbitrary (non port 80) to listen for db requests and set up ip filters so only the web servers themselves can open that port on the database server. This way you don't need to get involved with java if you don't want to as long as you have some scripting language on the web server (php, asp, perl, etc).

So basically instead of saying:
http://wwilson/laszlo?sql=select * from contacts where contact_id=1

You could do:
http://wwilson/getdata?proc=get_contacts&p1=1

In your server side language you would have some sort of construct (if/switch/whatever) based on proc.

if (proc == 'get_contacts')
{ <-- some function to sanitize input.
check for SQL injection etc
-->
sanitize_input(p1);

<-- construct query -->
sql = 'select * from contacts where' +
' contact_id = ' + p1;

<-- use the server side script to execute the
query and get the results back in xml format.
you need to write a procedure that opens the
web connection (there is plenty of xmlrpc stuff
that does this, or you can write your own) and
gets the results back -->

results=get_http(server,port,$sql)

<-- finally send the results back to the calling
web client
-->

print(results);
}

[emaender]

Wayne -

Hey, I tried doing the same thing. I am able to hit my IIS virtual directory from IE using the following path:

http://emaender/nwind?sql=SELECT%20*%20FROM%20Employees%20FOR%20XM L%20AUTO&root=root

From IE everything works fine, and I can see the full XML dataset returned in my browser window.

However, when I try to do the same from my Laszlo application, I'm getting an error in the LPS window that reads:
"Discarding unexpected response: HTTP:/1.1 100 Continue"

In the debug window I get this error at the same time:
"HttpException: Unable to parse status code from status line: 'HTTP/1.1 400.100 Bad Request'"

Did you run into any of these issues? Any idea what the problem might be?

thanks,
Eric

[tspratt]

I was surprised, but for a simple query, calling a stored procedure with no parameters, the JSP call was consistently 5 times faster than the SqlXml call. (300ms compared to 1500ms)

There may be other factors that are affecting the performance I am seeing.

If anyone sees different behavior I would be intersted in hearing.

Tracy

[mondomjm]

As was mentioned, opening up the SQL server for ad-hoc http queries is a bad idea. You can explicitly allow the virtual SQL directory to only accept "template" requests. Templates are created inside the virtual directory, and can be used to wrapper SQL or stored procs. By using templates to wrapper stored procs only, you can minimize exposure to outside threats as much as you could with any other scripting language.