Hack 40. Create a Simple XML Query Handler for Database Access

Hack 40. Create a Simple XML Query Handler for Database Access

XSLT can read XML data from URLs directly. This hack is a quick script that exposes your entire database to XSLT from a URL.

XSLT is a great language for reporting or data conversion. It can even read data directly from URLs. However, without extensions, it can't natively access a database. This hack opens up your entire database through your web server (a cool, albeit questionable, idea). It exports queries that are specified on the URL as XML.

5.8.1. The Code

Save the script in Example 5-24 as query.php.

Example 5-24. An insecure script

<?php
$dbuser = "root";
$dbpassword = "password";
$dbserver = "localhost";
$dbname = "test";

$db = mysql_connect( $dbserver, $dbuser, $dbpassword );
mysql_select_db( $dbname );

$query = "SELECT * FROM user";
if ( $_GET["query"] )
  $query = $_GET["query"];

$res = mysql_query( $query, $db );

header( "content-type: text/xml" );
?>
<result>
<?php while( $row = mysql_fetch_assoc($res) ) { ?>
 <row>
 <?php foreach( $row as $key => $value ) { ?>
  <data field="<?php echo( $key ); ?>"><?php echo( htmlentities( $value ) ); ?> 
	</data>

    <?php } ?>
</row>
<?php } ?>
</result>

This simpleand incredibly insecurescript starts by defining the connection parameters to the database. Then the script creates an SQL query and executes it against the database.

You can augment the SQL query by adding a query parameter to the URL.

Once the query is run, the script generates XML as output with the returned fields from each row as elements in the output XML.

5.8.2. Running the Hack

Copy the file to your PHP server and test it by navigating your browser to the URL.

Before running this hack, make sure that your client and server are within your firewall and are not accessible to the Internet. Otherwise, you are opening your entire database to the outside world!

To specify a query, add a query argument to the URL: http://localhost/phphacks/xmlquery/query.php?query=SELECT%20*%20FROM%20user.

This query selects the entire user table from my test database. The XML that's returned looks like this:

	<result>
		<row>
			<data field="id">1</data>
			<data field="name">jack</data>
		 </row>
		<row>
			 <data field="id">2</data>
			 <data field="name">lori</data>
		 </row>
		<row>
			 <data field="id">3</data>
			 <data field="name">megan</data>
		 </row>
	   </result>

On Internet Explorer, you will see a formatted XML display. On other browsers, you might have to view the returned source to see the result. Now you can point your XSLT stylesheet to this URL and have XML access to all the data you want.

5.8.3. See Also

• "Give Your Customers Formatting Control with XSL" [Hack #7]