Using SQL Server s SELECT...FOR XML Clause | Applied ADO.NET: Building Data-Driven Solutions

Using SQL Server's SELECT...FOR XML Clause

Most of today's databases support Extensible Markup Language (XML). In SQL Server, for example, you can execute SQL queries to return results as XML rather than standard rowsets. You can execute these queries directly or from within stored procedures. To retrieve results directly, you use the FOR XML clause of the SELECT statement. Within the FOR XML clause, you specify one of three XML modes: RAW, AUTO, or EXPLICIT. (You can also work with XML data in Access, Oracle, MySQL, and other databases, but the techniques vary.)

For example, this SQL Server SELECT statement retrieves information from the Customers and Orders tables in the Northwind database:

 SELECT Customers.CustomerID, ContactName, CompanyName,        Orders.CustomerID, OrderDate FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID AND (Customers.CustomerID = "NALFKI"     OR Customers.CustomerID = "NXYZAA") ORDER BY Customers.CustomerID FOR XML AUTO

Understanding SQL Injection

SQL injection is the process of using SQL queries and failing because of invalid user input. SQL injection is usually caused by the code written by developers. For example, let's say a form has a TextBox1 control and developers use the text of TextBox1 as one of the SQL query variables:

 string str = "SELECT * FROM Table WHERE Name ='" + TextBox1.Text + "'";

Now, what happens when Amie O'Donell enters her name in TextBox1? The execution of the SQL query will fail because of invalid SQL syntax. You have to be careful when building and executing direct SQL queries. You can simply avoid this by checking if the user input has an invalid character in it.

Another common scenario is when the database server is expecting a number value and the user enters a string value or vice versa. This could lead to a failure if you don't have a check in the code. For example, you could use the ToString method to make sure the input is a valid query (using the Convert class to convert from one data type to another).