Resolving the unknown: building custom xmlresolvers in the .net framework

WHERE THE OPERATOR Used with the generated sql where clause is always the equals operator using the support value.

.

DB: // xmlschema / xsdschema? bookstore.books.com

By only allowing certain string values ​​for the table and column names, you severely restrict the types of queries that can be generated and make the XmlResourceResolver implementation more secure to potential SQL injection security attacks.

Adding XML Documents Into SQL Server

XML documents can be stored in SQL Server 2000 as a column of type [n] varchar (max) / [n] varbinary (max). We will also look at the ability to store XML documents as the new XML data type that is introduced into SQL Server 2005, which is also supported in the SQL Server 2005 Express Edition. There are advantages and disadvantages to both these storage formats when working with XML documents.We are going to store XML Schemas (XSDs) in a database called xmlschemarepository in a Table Called Xmlschema. The T-Sql To Create this Table is shown bellow.

Create Table Xmlschema (Namespaces NVARCHAR (MAX) Primary Key, Xsdschema Nvarchar (max))

In SQL Server 2005 We can Also Create A Table with an An XML Data Type, Instead Using The Following T-SQL Statement.

Create Table Xmlschemaxmlcol (Namespaces NVARCHAR (MAX) Primary Key, Xsdschema XML)

Of The Following T-SQL STATEMENT for we can.................

INSERT INTO XMLSCHEMA VALUES (N'Bookstore.books.com ','

AttributeformDefault = "unqualified" ElementFormDefault = "Qualified"

Targetnamespace = "BookStore.books.com"

XMLns: xs = "http://www.w3.org/2001/xmlschema">

')

.

Select xsdschema from xmlschema where namespacs = 'bookstore.books.com'or for the xml data type column,

Select xsdschema from xmlschemaxmlcol where namespacs = 'bookstore.books.com'

Now We are ready to retrieve Xml Schemas from the client by extending the xmlresourceresolver to handle our new db: // scheme.

Extending the XmlResourceResolver to support the db: // Scheme

We are going to extend the xmlresourceresolver to becom The xmlschema table. The code below shows how to use the db: // Scheme to load an Xml Schema with a given namespace.

Public static void loadingXmlschemaFromDatabase ()

{

XMLTextReader DocReader = New XMLTextReader (@ "db: // xmlschema / xsdschema? Bookstore.books.com");

XmlResourceResolver resourceresolver = new xmlresourceReSolver ();

ResourceResolver.connectionInfo = ConnectionInfo;

DocReader.xmlresolver = ResourcesResolver;

While (DocReader.Read ())

{

IF (DocReader.IstartElement ("XS: Schema"))

{

DocReader.MovetoAttribute ("TargetNameSpace");

Console.writeline ("Retreived Schema with TargetNamespace: {0}",

DocReader.Value);

}

}

}

In Order to Achieve this, The Following Code Was Added to The Switch Statement In The XmlResourcesResolver Class.

Case "DB":

// Handled DB: // Scheme Requests Against A KNown Database

SqlDataReader DataReader = NULL;

MemoryStream Xmlstream = NULL;

String cmdtext = string.empty;

SQLCommand CMD;

String Tablename = OrigString.substring (Schemestart, Start - (Schemestart 1));

String columnname = Origstring.substring (start, end - start);

// Get the XML Schema Namespace

String name = absoluteuri.query.substring (absoluteuri.query.indexof ('?') 1); using (SqlConnection Connection = New SqlConnection))

{

Try

{

// security check on the allowed table names

// in Order to Prevent Any SQL Injection Attacks

Switch (TableName)

{

// Switch On XML Documents Stored in SQL Server 2000

Case "Xmlschema":

// security check on the allowed column names

Switch (columnname)

{

Case "xsdschema":

CMDText = @ "SELECT" ColumnName "from" Tablename "Where namespaces = @Name";

Break;

DEFAULT:

Throw New SecurityException ("Security SQL Injection: Invalid Column Name Used);

}

CMD = New SQLCOMMAND (CMDText, Connection);

Cmd.Parameters.Add (New Sqlparameter ("@ Name", Name)

Console.writeline (cmd.commandtext);

Connection.open ();

DataReader = cmd.executeReader ();

While (DataReader.Read ())

{

// Return A Memory Stream

UTF8ENCODING Uniencoding = new utf8encoding ();

Byte [] XMLBuffer =

Uniencoding.getbytes (DataReader [ColumnName] .tostring ());

Xmlstream = New MemoryStream (XMLBuffer, 0,

XMLBuffer.Length;

}

Break;

// Switch On XML Documents Stored in SQL Server 2005

Case "XMLschemaxmlcol":

// security check on the allowed column names

Switch (columnname)

{

Case "xsdschema":

CMDText = @ "SELECT" ColumnName "from" TableName

"Where namespaces = @name";

Break;

DEFAULT:

Throw New SecurityException ("Security SQL Injection:

Invalid Column Name Used ");

}

CMD = New SQLCOMMAND (CMDText, Connection);

Cmd.Parameters.Add (New Sqlparameter); console.writeline (cmd.commandtext);

Connection.open ();

DataReader = cmd.executeReader ();

While (DataReader.Read ())

{

// Return A Memory Stream

Console.writeline (DataReader [ColumnName] .tostring ());

SQLXML XML = DataReader.getsqlxml (0);

XMLStream = New

MemoryStream (DataReader [ColumnName] .tostring (). Length);

XmlTextWriter Writer = New XMLTextWriter (XMLStream,

Encoding.unicode);

Writer.writenode (Xml.createReader (), FALSE);

// flush the xmlwriter to the stream and close it.

Writer.flush ();

// set the position to the beginning of the stream.

XMLStream.seek (0, seekorigin.begin);

}

Break;

DEFAULT:

Throw new securityException ("Security SQL Injection: Invalid

COLUMN Name Used ");

}

}

Catch (Exception E)

{

Console.out.writeline (E.MESSAGE);

}

Finally

{

// Close Data Reader Object.

// the database connection closed by Going Out of Scope with "Using" Keyword

IF (DataReader! = NULL)

DataReader.Close ();

}

}

Return XMLStream;

.........................

Some benefits to storing the xml schema as an [n] varchar (max) or an [n] varbinary (max) include The Following:

This approach provides textual fidelity for XML documents when used to store XML. This is a requirement for applications that deal with legal documents such as insurance documents. It does not depend on XML support that is offered by the database and can be extended to support multiple database servers. It uses the processing power of the client system, thereby reducing the load on the server. By performing CPU-intensive XML processing on the middle-tier, the server is relieved of some load and is available for other important tasks. It Offers The Best Possible Performance for XML Document Level Insertion And Retrieval; That IS, Parts of Documents Cannot Be Retrieved Or Updated.Some Be Retrieved Or Updated.Some Benefits of Storing The XML Schema As an an XML Data Type Are:

You can offer significantly better querying capabilities using the W3C XQuery language, with the ability to perform fine-grained queries and to modify operations on XML data. XML documents stored as [n] varchar (max) in the database do not offer fine-grained updates, inserts, or deletes on an XML document. It is a simple and straightforward way of storing your XML data at the server while preserving document order and document structure. It offers the ability to create indexes on XML data type columns for significantly faster query -processing. it offs the ability to easily enforce XML Schema Validation and continraints on the xml data.

In Order to Load The XML Schema from The XML Data Type Xmlschemaxmlcol Table, IT IS SIMPLY A Matter of Amending The Uri As Shown In The Code Below.

XMLTextReader DocReader = New XMLTextReader (@ "db: // xmlschemaxmlcol / xsdschema? Bookstore.books.com");

Using this XmlResourceResolver class, XML documents and XML schemas can be embedded either into an assembly or a database. The following example validates the books.xml document that is embedded in the XmlResolvers assembly against the bookstore.books.com XML schema that is retrieved from A SQL Server Database.public static void loadingXMLFROMAMAMBLYRESOSCHEMAFROMDATABASE ()

{

XMLTextReader DocReader = New XMLTextReader (@ "res: // xmlresolvers? Books.xml");

XmlResourceResolver resourceresolver = new xmlresourceReSolver ();

DocReader.xmlresolver = ResourcesResolver;

ResourceResolver.connectionInfo = ConnectionInfo;

Xmlschemacollection Schemas = new xmlschemacolection ();

Schemas.validationEventHandler = New ValidationEventHandler (Schemas_ValidationEventHandler);

XMLTextReader Schemareader = New XMLTextReader (@ "db: // xmlschema / xsdschema? Bookstore.books.com");

Schemareader.xmlresolver = ResourcesResolver;

Schemas.Add ("BookStore.Books.com", Schemareader;

XMLValidatingReader ValidReader = New XmlvalidatingReader (DocRead);

ValidReader.validationType = validationType.schema;

ValidReader.Schemas.Add (Schemas);

ValidReader.validationEventHandler = New ValidationEventHndler (ValidReader_ValidationEventHandler);

While (ValidReader.Read ())

{}

}

Adding Local file system caching

A final feature we are going to add to this XmlResourceResolver is the ability to perform local file caching for http:.. // requests This is achieved with a hashtable of absolute URIs and retrieves a previously stored file, if there is one This caching approach is straightforward and does not provide an aging mechanism to the files, which I will leave as an exercise for you, the reader, to add just like your school days.The code below shows the CacheStream function, which takes a stream and an absolute URI as input, creates a local file, and then stores the path to the local file with the absolute URI as the key In this way remote http:. // requests such as for DTD or XML schemas found on the internet can be automatically stored locally On The First Access.

Private FileStream CacheStream (Stream InputStream, Uri Absoluteuteuri)

{

// The path and filename what the cached stream will be stored to.

String path = absoluteuri.absolutepath;

INT i = path.lastIndexof ("/");

IF (i> 0) Path = path.substring (i 1);

Path = cachelocation path;

Console.writeline ("CACHING FILE:" PATH);

Console.writeLine ();

// Create a new stream representing the file to be written to,

// and write the stream cache the stream

// from the extel location to the file.

FILESTREAM FILESTREAM = New FileStream (path, filemode.openorcreate,

FileAccess.write, fileshare.none;

StreamReader Sread = New StreamReader (InputStream);

StreamWriter Swrite = New StreamWriter (fileStream);

SWRITE.WRITE (SREAD.READTOEND ());

// add the information about the cached uri to the hashtable.

UriTable.Add (Absoluteuri.absoluteuri, PATH);

// Close Any Open streams.

Swrite.close ();

Sread.Close ();

Return New FileStream (Path, Filemode.open, FileAccess.Read, Fileshare.Read);

. For example, the W3C XML schema for schema contains two DTD references Run the code shown below, and then look in the local directory C:. / FileCache / and examine what local files have been downloaded and stored You will be surprised to find a couple of DTDs, each of which required the use of the XmlResolver to download. Going through the code in the debugger is a great way to see the XmlResolver class in action, during which it finds resource references that need further resolution.

Public static void loadingXMLFromNetworkAndcache ()

{

XmlTextReader docreader = new xmltextReader (@ "http://www.w3.org/2001/xmlschema.xsd");

XmlResourceResolver resourceresolver = new xmlresourceReSolver ();

ResourcesResolver.cachelocation = @ "c: / filecache /";

ResourcesReSolver.cancachestreams = true;

DocReader.xmlresolver = ResourcesResolver;

While (DocReader.Read ())

{

}

// read the Same Xml Document and this time the xml Document is Read

// from the file cache.

XMLTextReader DocReader2 = new xmltextReader (@ "http://www.w3.org/2001/xmlschema.xsd");

DocReader2.xmlresolver = ResourcesResolver;

While (DocReader2.read ())

{

}

}

You Can Also Wrap The XmlResourceResolver in AN Xmlsecureresolver in Order To Limit, for Example, Which Internet Sites can be accessed.

A Dynamic XMLResolver Implementation

Although the XmlResourceResolver provides support for several schemes, there may be times when you want to have a more dynamic behavior where, for example, a local configuration file determines which schemes are supported by which XmlResolver. In these cases it is useful to have a dynamic .

Class XMLDynamicResolver: XmlResolver

{

Hashtable resolvercollection = new hashtable ();

Public Void Add (String Scheme, XmlResolver Resolver)

{

ResolverCollection.Add (Scheme, Resolver);

}

Public override system.net.icredentials Credentials

{

Set {throw new global :: system.notsupportedException ();

}

Public Override Object GetEntity (Uri Absoluteuri, String Role, Type OfObjectToreTurn)

{

Console.Writeline ("Attempting to Retrieve: {0}", Absoluteuri;

Xmlresolver resolver = (xmlresolver) resolvercollection [absoluteuri.scheme];

Return Resolver.geeTentity (Absoluteuri, Role, OfObjectToreTurn);

}

Public Override Uri Resolveuri (Uri Baseuri, String Relativeuri)

{

URI Actualuri = Baseuri;

IF (actualuri == null)

Actualuri = New URI (TRUE, RELATIVEURI);

Xmlresolver resolver = (xmlresolver) resolvercollection [actualuri.scheme];

Return Resolver.Resolveuri (Baseuri, Relativeuri);

}

}

To use this XmlDynamicResolver you add an instance of the XmlResolver for the given scheme. The example below shows the addition of the XmlResourceResolver for the "db" and "res" schemes, and the XmlUrlResolver for the "http" and "file" schemes. "Yes," the XmlResourceResolver can in fact handle all four of these schemes, so this is a somewhat contrived example;. it does illustrate the class, however Note that in this case the XmlDynamicResolver is derived from the XmlResolver abstract class.public static void LoadxmlwithdyNamicResolver ()

{

// Register Schemes with the Dynamic XMLResolver

XMLDynamicResolver DynamicResolver = new xmldynamicResolver ();

DynamicResolver.Add ("DB", New XmlResourceResolver ());

DynamicResolver.Add ("res", new xmlresourceresolver ());

DynamicResolver.Add ("File", New XMLURLRESOLVER ());

DynamicResolver.Add ("http", new xmlurlverse ());

XMLTextReader DocReader = New XMLTextReader (@ "res: // xmlresolvers? Books.xml");

DocReader.xmlresolver = DynamicResolver;

While (DocReader.Read ())

{

IF (DocReader.IstartElement ("price"))

Console.writeline ("Book Price" DocReader.Readinnerxml ());

}

}

Conclusion

This article has provided an in-depth review of the versatile XmlResolver class. XmlResolvers resolve external data sources identified by a URI and then retrieve the data identified by that URI, and so provide an abstraction for accessing data sources such as files on a file system . System.Xml in the .NET Framework provides two implementations of the XmlResolver: the XmlUrlResolver for file: // and http: // requests, and the XmlSecureResolver for limiting access to specified resources.The beauty of the XmlResolver is that you can build them to retrieve XML from anywhere;.. all you need to do is return a stream in this article we walked through XmlResolver implementations that pull XML documents that are embedded in a CLR assembly or are stored in a SQL Server database If you want to see TWO More Examples of Xmlresolvers, Chris Lovett Describes An Xmlaspresolver for Resolving Files Store in Microsoft ASP.NET Virtual Directories and an Alternative Implementation of An XmlcachingResolver TH at performs in memory caching rather that local file caching. All interesting XML projects create some form of XmlResolver and it would be good to hear about your implementations. Send an e-mail message describing them to mfussell@microsoft.com. ¡ Viva La XmlResolver!