SQL connection string encoded in a Uri?

  • Thread starter Thread starter Lou
  • Start date Start date
L

Lou

I've been looking for an established way to encode a connection string into a
Uri. Uri's are used for file access using File://path. Has anyone seen a
Sql://server/db/table?query approach?

Thanks
 
Lou said:
I've been looking for an established way to encode a connection string into a
Uri. Uri's are used for file access using File://path. Has anyone seen a
Sql://server/db/table?query approach?
Click to expand...
Sure, JDBC uses this method -- not URIs, precisely, but syntax that
resembles them. For example, a connection string for SQL Server using the
jTDS driver looks like this:

jdbc:jtds:sqlserver://<server>\<instance>:<port>/<database>

The question is, why would you want to do this for .NET connection strings?
Those have an established format too, it just doesn't resemble a URI. The
general format is just simple key=value pairs, separated by semicolons:

Provider=<provider>;<param>=<value>;...

There's any number of ways you can stuff this in a URI, but you'll have to
roll your own mechanism for encoding and decoding them (the Uri class is
only of limited help here). But again, why would you? Is there some place
you can store URIs but not general strings?

Note that it's a very bad idea to allow arbitrary queries and pass those
around as URIs, for reasons I hopefully don't have to elaborate. You may
want to look at a REST approach to resources instead, but without tying
those to specific database tables. Take a look at ADO.NET Data Services, for
example: http://msdn.microsoft.com/data/bb931106
 
Jeroen,

Thanks, so I'm not crazy. What I'm after here is a way to store a location
for a data source or sink. I want to keep a uniform link for ANY source or
sink. Why should there be a way to connect to a web site/FTP/Mail/File etc
and not have a way to use that same Uri (emphasis on Universal) without
someway of including SQL in the mix?

I'm prepared to 'roll my own' on this. It's an internal thing. I suppose
since most SQL server access is managed intermurrally having a URI wouldn't
provide universal access but I'm looking for consistency of declaration.
Otherwise I'd have to check to see if the string was a connection string or a
URI. Not so terrible I guess but not quite esthetic. :-)

Lou
 
Lou said:
What I'm after here is a way to store a location for a data source or
sink. I want to keep a uniform link for ANY source or sink. Why should
there be a way to connect to a web site/FTP/Mail/File etc and not have a
way to use that same Uri (emphasis on Universal) without someway of
including SQL in the mix?
Click to expand...
Well, one obvious mismatch is that an SQL source produces typed, tabular
data (possibly with multiple result sets), while any sort of file URI
provides an arbitrary byte stream. You need to reconcile those somehow, with
a restriction on the formats produced by either.

The second problem is that it's one thing to allow arbitrary *files*, and
quote another to allow arbitrary *queries* (or even just tables). The latter
may unduly tax the database and/or be disallowed by your DBA, even. If you
mix in parameters, you need a good way to supply those without opening up
SQL injection holes.

Third, while you mention "source or sink", sinks are really very different
animals from sources as far as URIs are concerned. It's very
protocol-dependent what to do with URIs intended to "sink" data: for HTTP
it'll be an HTTP verb (POST or possibly PUT), for FTP it's a PUT command
(but quite unlike HTTP), for mailto: there is actually no source (they're
all "sink" URIs), for file: URIs there is no (standardized) way at all to
create rather than read files. SQL, finally, would have obvious sources
(queries) but not so obvious sinks (a table, yes, but you need to have the
columns and types mapped out in advance). So don't go generalizing just yet!
I'm prepared to 'roll my own' on this. It's an internal thing. I suppose
since most SQL server access is managed intermurrally having a URI wouldn't
provide universal access but I'm looking for consistency of declaration.
Otherwise I'd have to check to see if the string was a connection string or a
URI. Not so terrible I guess but not quite esthetic. :-)
Click to expand...
System.Uri provides support for arbitrary schemes, so something like this
will work:

new Uri("mssql://myserver/bookBase/books")

or (with more creativity)

new Uri("mssql://myserver/bookBase/books/{title,isbn}")

to produce arbitrary tuples or heck, go all out with your own language

new
Uri("mssql://myserver/bookBase/books(book),authors(author)/{book.title,
author.name(author)}?book.author=author.id");

That's "SELECT book.title, author.name AS author FROM books book, authors
author WHERE book.author = author.id", can't you tell? I'll leave figuring
out a syntax for outer joins as an exercise...

Since you have to do your own parsing, this is quite a lot of work.
Arbitrary query strings are easier, but you still have issues of escaping to
contend with:

new Uri("mssql://myserver/bookBase?SELECT [title] + '?' FROM books");

This URI is actually

sql://myserver/bookBase?SELECT%20%5Btitle%5D%20+%20'?'%20FROM%20books

And you may have to supply it this way externally, or in an even more
obfuscated form, depending on where you store it. Not obvious or convenient.

Now, instead of *all this*, you could also expose your SQL data as a web
service and consume it as any other HTTP source producing XML. There are so
many ways of doing that that it's not funny: SQL Server can even do this
itself (see, for instance, http://msdn.microsoft.com/library/ms345123), but
a more scalable solution is to use an IIS web service. ADO.NET Data Services
which I mentioned before are yet another option. A WCF service spitting out
XML using SQL Server's FOR XML options is another. If XML is not your thing,
a simple HTTP handler which returns CSV (or sexy JSON) from a DataReader is
absolutely trivial to build.

Any of these solutions are almost certainly superior (in the sense of
maintainability, scalability and being future-proof) to rolling your own SQL
URI implementations. Whatever works for you, though.
 
Jeron,

Actually, you've answered my original question which is whether it's
possible (acceptable) to distort a URI to encode a SQL source/Sink location.
I'm not actually thinking of using it as a 'message' per se. I just want to
be able to handle requests for data sources and sinks uniformly. To give you
a bit of a scenario: in my company, and probably in many, there are
occasionally requests that go out to field people to provide data in some
form. Sometimes it's Excel, sometimes it's in a formatted form etc. This
data is then returned by email and compiled into another spreadsheet (yes, a
fairly primitive thought process.) After a couple of these experiences I
decided to build something that would take a source document like a
spreadsheet, take the column names and types, store them and use it to
recreate a sink in whatever destination is identified by some description.
Uri's seemed to be appropriate but there are some holes, most notably SQL
resources. So, thanks for that.

I can see, however, from the issues you've pointed out that there's probably
a good reason why SQL:// is not 'out there' much as a URI to retrieve SQL
data. It's ironic since SQL data stores are probably where most of the
world's data resides. I'm sure query formatting probably could be devised to
get data, even in arbitrarily complex ways. I wonder why that formatting
hasn't been addressed as since just about everything else has been serialized
in some way and passed through some standard parser to get to the various
flavors of SQL Queries.

I suspect it has to do with security on the one hand and that typically data
is needed in context and that context is managed by an application which has
its own ways of obtaining that data.

Jeroen Mostert said:
Lou said:
What I'm after here is a way to store a location for a data source or
sink. I want to keep a uniform link for ANY source or sink. Why should
there be a way to connect to a web site/FTP/Mail/File etc and not have a
way to use that same Uri (emphasis on Universal) without someway of
including SQL in the mix?
Click to expand...
Well, one obvious mismatch is that an SQL source produces typed, tabular
data (possibly with multiple result sets), while any sort of file URI
provides an arbitrary byte stream. You need to reconcile those somehow, with
a restriction on the formats produced by either.

The second problem is that it's one thing to allow arbitrary *files*, and
quote another to allow arbitrary *queries* (or even just tables). The latter
may unduly tax the database and/or be disallowed by your DBA, even. If you
mix in parameters, you need a good way to supply those without opening up
SQL injection holes.

Third, while you mention "source or sink", sinks are really very different
animals from sources as far as URIs are concerned. It's very
protocol-dependent what to do with URIs intended to "sink" data: for HTTP
it'll be an HTTP verb (POST or possibly PUT), for FTP it's a PUT command
(but quite unlike HTTP), for mailto: there is actually no source (they're
all "sink" URIs), for file: URIs there is no (standardized) way at all to
create rather than read files. SQL, finally, would have obvious sources
(queries) but not so obvious sinks (a table, yes, but you need to have the
columns and types mapped out in advance). So don't go generalizing just yet!
I'm prepared to 'roll my own' on this. It's an internal thing. I suppose
since most SQL server access is managed intermurrally having a URI wouldn't
provide universal access but I'm looking for consistency of declaration.
Otherwise I'd have to check to see if the string was a connection string or a
URI. Not so terrible I guess but not quite esthetic. :-)
Click to expand...
System.Uri provides support for arbitrary schemes, so something like this
will work:

new Uri("mssql://myserver/bookBase/books")

or (with more creativity)

new Uri("mssql://myserver/bookBase/books/{title,isbn}")

to produce arbitrary tuples or heck, go all out with your own language

new
Uri("mssql://myserver/bookBase/books(book),authors(author)/{book.title,
author.name(author)}?book.author=author.id");

That's "SELECT book.title, author.name AS author FROM books book, authors
author WHERE book.author = author.id", can't you tell? I'll leave figuring
out a syntax for outer joins as an exercise...

Since you have to do your own parsing, this is quite a lot of work.
Arbitrary query strings are easier, but you still have issues of escaping to
contend with:

new Uri("mssql://myserver/bookBase?SELECT [title] + '?' FROM books");

This URI is actually

sql://myserver/bookBase?SELECT%20%5Btitle%5D%20+%20'?'%20FROM%20books

And you may have to supply it this way externally, or in an even more
obfuscated form, depending on where you store it. Not obvious or convenient.

Now, instead of *all this*, you could also expose your SQL data as a web
service and consume it as any other HTTP source producing XML. There are so
many ways of doing that that it's not funny: SQL Server can even do this
itself (see, for instance, http://msdn.microsoft.com/library/ms345123), but
a more scalable solution is to use an IIS web service. ADO.NET Data Services
which I mentioned before are yet another option. A WCF service spitting out
XML using SQL Server's FOR XML options is another. If XML is not your thing,
a simple HTTP handler which returns CSV (or sexy JSON) from a DataReader is
absolutely trivial to build.

Any of these solutions are almost certainly superior (in the sense of
maintainability, scalability and being future-proof) to rolling your own SQL
URI implementations. Whatever works for you, though.
Click to expand...
 
Lou said:
Actually, you've answered my original question which is whether it's
possible (acceptable) to distort a URI to encode a SQL source/Sink location.
I'm not actually thinking of using it as a 'message' per se. I just want to
be able to handle requests for data sources and sinks uniformly. To give you
a bit of a scenario: in my company, and probably in many, there are
occasionally requests that go out to field people to provide data in some
form. Sometimes it's Excel, sometimes it's in a formatted form etc. This
data is then returned by email and compiled into another spreadsheet (yes, a
fairly primitive thought process.) After a couple of these experiences I
decided to build something that would take a source document like a
spreadsheet, take the column names and types, store them and use it to
recreate a sink in whatever destination is identified by some description.
Uri's seemed to be appropriate but there are some holes, most notably SQL
resources. So, thanks for that.
Click to expand...
The big impedance mismatch I see is that URIs (Uniform Resource Identifiers)
are meant to uniquely label things, often more specifically by use of a
location (the ever popular URLs). What they're not good at is additionally
embedding the format of the thing you're labeling, and they're almost never
used for that.

For example, http://www.microsoft.com describes a resource accessed through
the HTTP protocol, but it doesn't tell you anything about HTML -- that's
outside the scope. Similarly, an sql:// URI could specify a table in a
database on a server, but it's not natural to use it to describe the table's
structure as well.

So you could use URIs for the sources and destinations of the messages as
you describe them, but you would need to lay down the format of those
messages quite rigidly, or a way to figure out the format for each URI
scheme. If you've only ever got one format that's not so hard, but otherwise
you need some good error handling strategies ("whoops, the table's structure
doesn't match my spreadsheet, I'll guess I'll just cut a few columns").
I can see, however, from the issues you've pointed out that there's probably
a good reason why SQL:// is not 'out there' much as a URI to retrieve SQL
data. It's ironic since SQL data stores are probably where most of the
world's data resides. I'm sure query formatting probably could be devised to
get data, even in arbitrarily complex ways. I wonder why that formatting
hasn't been addressed as since just about everything else has been serialized
in some way and passed through some standard parser to get to the various
flavors of SQL Queries.
Click to expand...
What you're really clamoring for (a uniform way to access databases) has
already been described: it's ANSI SQL. Squeezing all of it into URI form
would be a hell of a job (and you'd end up with some ridiculously big and
unreadable URIs). You could turn the URI into a simple "inject any
system-specific query here" endpoint, but if you do that there's really no
benefit over just exposing a script or application over HTTP to do the same.

Recapping: I could see URIs being used to point to a table or view on an
arbitrary server, but anything more complicated than that goes against the
nature of URIs.
 
Back
Top