Microsoft SQL Server Driver for PHP Team Blog

The SQL Server Driver for PHP is now available in the Web Platform Installer 2.0 beta

April 2009 Cumulative Update Now Available!

It is my pleasure to announce that Microsoft has released an update to version 1.0 of the Microsoft SQL Server Driver for PHP.  The update to the driver is the April 2009 Cumulative Update and it contains a series of targeted fixes, the majority of which were reported by the community via the MSDN Forum.  For more details on the specific fixes, please see the readme included in the download package.

There are two non-technical fixes we've made in the update worth noting.

1. The extension is now the Microsoft SQL Server Driver for PHP, version 1.0.  We'd received a number of questions about whether or not the extension supported SQL Server 2008 because the extension had been named the SQL Server 2005 Driver for PHP.  The extension supports both SQL Server 2008 and SQL Server 2005, including all editions from Express to Enterprise.
2. We've improved the error message returned when the ODBC driver that the extension relies on is not installed.  The error message now calls out that the cause of the error and includes the URL that developers can use to download and install the ODBC driver.

The updated binary version of the extension is available for download on the MSDN download site and replaces the v1.0 RTM release.

We have also published the updates to the source on the CodePlex site.  We've modified the source code specifically so that the extension can be compiled and used with the current builds of PHP 5.3.

Enjoy!

David Sceppa

Program Manager - Microsoft SQL Server Driver for PHP

Paging Data with the SQL Server Driver for PHP

One of the most common tasks of any web application involves allowing a user to ‘page’ through a large amount of data. In other words, given a large result set, the task is to present the user with a manageable subset and allow him/her to navigate through the result set by viewing ‘pages’ (i.e. subsets) of data. In this post, I’ll demonstrate two ways to do this with the existing functionality of the SQL Server Driver for PHP. Also note that the strategies discussed here can be applied to paging data regardless of the programming language or database.

Before diving into the details of the two examples, I want to point out two assumptions I have made:

1)      Data on the server is dynamic (i.e. data may be updated between our query executions).

2)      We want the most recent data with each page view.

With these assumptions in mind, the examples below demonstrate two similar strategies for paging. If your paging needs do not share these assumptions, you may want to consider another strategy (such as executing queries against an ordered temporary table or bringing a complete result set into client memory) that meets your needs.

The first example allows users to navigate through data with “Previous Page” and “Next Page” links, while the second example allows users to navigate directly to any page.

Note that both examples use the AdventureWorks database and that I’ve removed some of the connection code and error handling for the sake of brevity. Complete scripts are attached to this post if you want to see the code in-tact.

This example presents users with ten products at a time and allows him/her to navigate through pages with “Previous Page” and “Next Page” links. The basic strategy is to retrieve an “extra” row (an 11^th row in my example) and use it as a flag for enabling the “Next Page” link. This avoids multiple trips to the database to display one page of data.

This first section of code sets the number of rows to display on a page and executes a query that returns and “extra” row.  The query uses the ROW_NUMBER() function and OVER clause in the context of a common table expression to produce an efficient query. Common table expressions were added to T-SQL in SQL Server 2005 and are extremely helpful for paging scenarios such as this, along with scenarios where you want to execute recursive queries or want to reference a complex result multiple times within a single statement. The code for connecting to the database has been removed and the code assumes “lowRowNum” and “highRowNum” values are being passed in the $_REQUEST variable:

Working on version 1.1 of the SQL Server Driver for PHP

Program Manager - Microsoft SQL Server Driver for PHP

Using the FILESTREAM Functionality of SQL Server 2008

In this post I want to demonstrate how the streaming capabilities of the SQL Server 2005 Driver for PHP work seamlessly with the new FILESTREAM functionality in SQL Server 2008.

The FILESTREAM functionality in SQL Server 2008 enables applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data. You can learn more about the FILESTREAM feature by starting here: Designing and Implementing FILESTREAM Storage.

To follow this demonstration, you will need to do two things first:

1)      Enable FILESTREAM on an instance of SQL Server. Instructions for doing this are here: How to: Enable FILESTREAM.

2)      Create a FILESTREAM-enabled database. Instructions for doing this are here: How to: Create a FILESTREAM-Enabled Database. The database I will use for demonstration purposes can be created with this script:

USE [master];
GO
 
IF EXISTS (SELECT * FROM sys.databases
           WHERE name = 'FilestreamDB')
    DROP DATABASE FilestreamDB;
GO
 
CREATE DATABASE FilestreamDB
ON
PRIMARY (NAME = F_Stream1, FILENAME = 'c:\data\FilestreamDB.mdf'),
    FILEGROUP FileStreamGroup1 CONTAINS

         FILESTREAM(NAME = F_Stream2, FILENAME = 'c:\data\filestream1')
    LOG ON (NAME = F_StreamLog1, FILENAME = 'c:\data\FilestreamDB_log.ldf')
GO
 
USE FilestreamDB;
GO
 
Create Table StreamTable (
     ID uniqueidentifier rowguidcol NOT NULL unique,
     myFileStream varbinary(max) FILESTREAM
)

Once you have completed these two steps, the coding patterns for sending and retrieving stream data with the SQL Server 2005 Driver for PHP are no different than those for sending/retrieving stream data from other column types (see How to: Send Stream Data and Retrieving Stream Data for more information).

Let’s look at an example of sending and retrieving FILESTREAM data.

Sending FILESTREAM Data

Although the code pattern for sending stream data to a FILESTREAM column is the same as it is for sending stream data to any column, the Transact-SQL that defines the query may new. A FILESTREAM-enabled database requires a uniqueidentifier column, and there is no function (such as SCOPE_IDENTITY()) to retrieve the generated GUID after performing an INSERT. So, in the code below, I’ve modified the Transact-SQL so I can select the GUID for use later. Also note that, in the parameter array, I’ve specified the PHP type as a binary stream and the SQL type as varbinary(‘max’) (see How to: Specify PHP Data Types and How to: Specify SQL Server Data Types for more information).

Working with the New SQL Server 2008 Data Types

One question we have heard a lot since releasing the SQL Server 2005 Driver for PHP is “Does the driver work with SQL Server 2008?” The short answer is “Yes!” However, because the driver relies on the SQL Server 2005 ODBC driver to handle low-level communications with the server, we have heard some follow up questions about how our driver works with the new data types that are available in SQL Server 2008. In this post, I will demonstrate how the SQL Server 2005 Driver for PHP interacts with these new data types. If you are not familiar with the new SQL Server 2008 data types, you can read an overview here: SQL Server 2008: New Data Types.

New Data Types Summary

Here’s a quick summary of the new data types in SQL Server 2008 (with links to more detailed explanations):

·         date: A date.

·         time: A time.

·         datetimeoffset : A date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

·         datetime2 : A date that is combined with a time of day that is based on 24-hour clock. This type that has a larger date range, a larger default fractional precision than the legacy datetime type, and it allows for optional user-specified precision.

·         hierarchyid : A variable length, system data type used to represent position in a hierarchy.

·         geometry : A .NET Common Language Runtime (CLR) data type that represents data in a Euclidean (flat) coordinate system.

·         geography : A .NET common language runtime (CLR) data type that represents data in a round-earth coordinate system.

Retrieving New Data Types

You can retrieve any of the types mentioned above as strings. With the date, time, datetimeoffset, and datetime2 types, this happens by default when you retrieve these types with sqlsrv_fetch_array or the combination of sqlsrv_fetch/sqlsrv_get_field.  Note that the legacy datetime and smalldatetime types are returned as  PHP Datetime objects by default. We’d be interested in your feedback about the preferred default return types for the new data types, as well as the legacy types.

The process for retrieving the hierarchyid, geometry, and geography types as strings has a couple of twists, so let’s take a look at some examples. All the examples below are run against the NewTypesDB database; a SQL script that creates and populates this database is attached to this post. Also attached is a PHP script that contains the examples shown here, plus examples of how to retrieve the date, time, datetimeoffset, and datetime2 types.

To retrieve the geometry and geography types as strings, the .ToString() method must be called on each type in the Transact-SQL that defines the query.  This method converts the data to the SQL Server nvarchar(max) data type, which the SQL Server 2005 Driver for PHP converts to a PHP stream by default.  So, one option is to retrieve these types with sqlsrv_fetch_array, which will convert the streams to strings automatically:

Note: SQL Server does not return a column name by default when the .ToString() method is called on a column. In the query below, I have specified column names in the Transact-SQL.

$tsql = "SELECT myGeometry.ToString()AS geom,
         myGeography.ToString() AS geog
         FROM GeoTable";
$stmt = sqlsrv_query($conn, $tsql);
 
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
     print("<pre>");
     print_r($row);
     print("</pre>");
}

Another option would be to retrieve these types with the combination of sqlsrv_fetch/sqlsrv_get_field and specify the PHP return type as a string:

$tsql = "SELECT myGeometry.ToString()AS geom,
         myGeography.ToString() AS geog
         FROM GeoTable";
$stmt = sqlsrv_query($conn, $tsql);
 
while(sqlsrv_fetch($stmt))
{
     echo sqlsrv_get_field($stmt, 0,

          SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR))."</br>";
     echo sqlsrv_get_field($stmt, 1,

          SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR))."</br>";
}

And, finally, a third option would be to retrieve these types as streams (the default):

October 2008 Cumulative Update Now Available!

How and Why to Use Parameterized Queries

I recently attended ZendCon 08 in Santa Clara, CA with David Sceppa, Program Manager for the SQL Server 2005 Driver for PHP. We hosted a "Birds of a Feather" (BOF) session and were happy to get feedback and field lots of good questions from developers who are using our driver. During the session, our discussion touched on how and why to use parameterized queries. The focus of this post is to recap that conversation and to demonstrate how to use parameterized queries with the SQL Server 2005 Driver for PHP.

A parameterized query is a query in which placeholders are used for parameters and the parameter values are supplied at execution time. The most important reason to use parameterized queries is to avoid SQL injection attacks.

Let's take a look at what can happen if we don't use parameterized queries. Consider the following code that concatenates user input with SQL syntax:

$name = $_REQUEST['name'];

$email = $_REQUEST['email'];

$sql = "INSERT INTO CustomerTable (Name, Email)

        VALUES ('$name', '$email')";

Now suppose a user enters the following data:

The resulting SQL query (defined by $sql) is the following:

This is a perfectly valid SQL query, and, as you can see, the results of executing this on the server (with a function such as sqlsrv_query) would not be desired. This does assume that the user has some knowledge of your database (or that he guessed the table name correctly) and that credentials used to access the server have sufficient permissions to drop a table. However far-fetched these assumptions may seem, when you construct SQL queries by concatenating user input with SQL syntax you run the risk of the user supplying input that may cause your query to do something that you had not expected.

The simplest and most effective way to avoid the scenario described above is to use parameterized queries. Here is how the code above would look when using a parameterized query:

Now, to execute the query, we just pass an open connection ($conn), the SQL query ($sql), and the parameter array ($params) to the sqlsrv_query function:

(The sqlsrv_query function returns a PHP statement resource.)

The difference here (as opposed to concatenating user input with SQL syntax) is that a query plan is constructed on the server before the query is executed with parameter values. In other words, a query plan is constructed on the server for this query:

INSERT INTO CustomerTable (Name, Email) VALUES (?, ?)

When you execute this query using parameterized values and the same user input , only the INSERT query is executed. The server accepts the user input of

bswan@microsoft.com'; DROP TABLE CustomerTable; PRINT 'Gotcha!'--

and inserts that entire value into the Email field. Using a parameterized query prevents the user input from leading to SQL injection. Plus, using a parameterized query allows you to handle less malicious scenarios, such as where the user supplies a value like "O'Leary" without forcing you to replace single quotes with double single quotes.

For more information about avoiding SQL injection attacks, see SQL Injection. For more information about how to execute parameterized queries, see How to: Perform Parameterized Queries, How to: Execute a Single Query, and How to: Execute a Query Multiple Times in the driver documentation.

Regardless of which database server or driver you use, a best practice for writing code is to use parameterized queries for security reasons.

Please let me know if this post has been helpful, and/or post questions or other comments.

Thanks.

Brian Swan
Programming Writer, SQL Server Driver for PHP

Greetings from ZendCon08!

Brian Swan and I are here at ZendCon08 to talk with PHP developers about the SQL Server Driver for PHP.  Come visit us in the Microsoft booth and say, "Hi!"

We've also signed up for an un-con session Wednesday at 11:00 to get more feedback from the community.  For more information, see the un-con wiki page:  http://www.zendcon.com/ZendCon08/public/wiki/UnCon_Talks

David Sceppa

Program Manager - Microsoft SQL Server Driver for PHP

New SQL Server Driver for PHP Whitepaper Available

A new technical article covering the SQL Server 2005 Driver for PHP is now available on MSDN: Accessing SQL Server Databases with PHP. This article highlights many of the features of the driver by examining portions of the Example Application that is available in the documentation. Here’s the table of contents to pique your interest: 

- Introduction

- Loading the Driver

- Configuring the Driver

- Creating a Connection

- Connection Pooling

- Executing a Query

- Retrieving Data from the Server

- Retrieving Data as an Array

- Retrieving Images

- Sending Data to the Server

- Sending Images to the Server

- Moving Through Groups of Result Sets

- Handling Errors and Warnings

- Resources

- Conclusion

Brian Swan

Unicode Support and Endianness

When you convert strings between different encodings in PHP using iconv or mb_convert_encoding, PHP will assume big endian format if you do not specify endianness.  In other words, you must specify "ucs-2le" instead of just "ucs-2".  The example in our documentation for working with UTF-8 strings did not specify endianness so the strings were converted to and from Unicode in big endian format.  We're in the process of correcting that error and apologize for the confusion.

For more information on endianness and the origin of the term "endian", see Michael Kaplan's blog post or the Wikipedia entry on endianness.

David Sceppa

Program Manager - Microsoft SQL Server Driver for PHP

A new look to the blog

A number of people contacted me to say that the previous color scheme made the blog difficult to read.  This should be a little easier on the eyes.

Microsoft SQL Server 2005 Driver for PHP v1.0 has released

It is my pleasure to announce that version 1.0 of the Microsoft SQL Server 2005 Driver for PHP has released!  This release marks another step in Microsoft’s continued commitment to interoperability.  To keep up with our announcements and customer feedback, please check out our team blog.

The SQL Server Driver for PHP team would like to thank everyone who has provided feedback and bug reports throughout the development cycle for the driver.  We've come a long way since the initial CTP in October of 2007 thanks to your feedback.

Where do I get the bits?

The driver is available for download on the MSDN download site.  We encourage you to download the driver and explore the sample application described in the .chm file.  If you'd prefer to access our documentation on-line, it's available on the MSDN site.  We welcome your feedback, feature requests and bug reports on our MSDN Forum.

What about the source?

Microsoft has published the source code to the driver on the CodePlex site.  There, you can download the source code and provide feedback in the form of bug reports and feature requests.

We understand that many developers will download the source code from CodePlex to create their own build(s) of the driver.  However, Microsoft supports only the signed version of the driver from the MSDN download site.  If you're using a custom built version of the driver and encounter problems, please reproduce the problem with the Microsoft-built driver before contacting Microsoft support.

How will Microsoft respond to feedback and update the driver?

We will be responsive to feedback on the CodePlex, MSDN Forum and through our blog, weighing in on both feature requests and bug reports.  We currently plan to update the driver available on the MSDN download site approximately once every three months.  When we update the driver on the MSDN download site, we will also push the corresponding changes to the source code up to the CodePlex site.

With each successive update, we will revisit this plan.  We've seen too many projects over-reach in their plans to be responsive to the community.  We would prefer to start with a more conservative approach and make sure we're successfully delivering on that before expanding.

David Sceppa

What's new in the May 2008 CTP?

A new Community Technology Preview (CTP) version of the SQL Server 2005 Driver for PHP is now available for download. The May CTP version (1.0.821.1) is a significant improvement over the previous CTP version (1.0.515.1 of February 2008) and it includes several new and/or refined features.

The list of major changes includes:

·         A new API, sqlsrv_fetch_object, offers a more sophisticated alternative to sqlsrv_fetch_array by enabling retrieval of data rows as PHP objects.

·         Another new API, sqlsrv_num_fields, returns the number of fields in an active result set (thus reducing the need for calling sqlsrv_field_metadata).

·         A redesigned configuration facility anchored on two APIs,  sqlsrv_get_config  and sqlsrv_configure, allows you to explore and/or modify the error handling settings and to configure of the logging system.

·         By default, the driver treats most (i.e. all but four) of ODBC warnings as errors. This behavior can be turned on and off via sqlsrv_configure.

·         Connection options offer several new attributes, including the ability to enable (or disable) connection pooling. (Note: connection pooling is enabled by default.)

·         New query options (in both sqlsrv_query and sqlsrv_prepare) allow a better control of query execution. QueryTimeout aims to avoid execution deadlocks and SendStreamParamsAtExec eliminates the need for subsequent calls to sqlsrv_send_stream_data.

·         User Data Type (UDT) is now supported.

·         Results set fields are no longer starting at index 1. Both sqlsrv_get_field and sqlsrv_fetch_array have first data field at index 0.

·         All APIs (including sqlsrv_close and sqlsrv_free_stmt) will now return false when called with an invalid parameter.

Additional improvements aim to provide better indications of error conditions and reduce the potential for resource and/or memory leakage.

For additional details, please consult “What’s New” topic at:

http://msdn.microsoft.com/en-us/library/cc296192.aspx

The CTP release and documentation are available for download at:

http://www.microsoft.com/downloads/details.aspx?familyid=85f99a70-5df5-4558-991f-8aee8506833c&displaylang=en

More info is available on the MSDN site and the PHP blog site: http://blogs.msdn.com/sqlphp/ 

For any questions regarding the SQL Server 2005 Driver for PHP driver, please feel free to post a question on the   MSDN forum site for the PHP driver.

- Serban Iliescu

  Test Lead, SQL Server Driver for PHP

The May '08 CTP is now available!

I'm pleased to announce that the May '08 CTP for the SQL Server 2005 Driver for PHP is now available for download here.  Keep in mind that this is a technology preview and is not intended for production purposes.  The documentation for the driver is available on our MSDN site.  We look forward to your feedback on the new MSDN forum specifically for the driver.

Please stay tuned, as we will soon be blogging about some of the new functionality in the May '08 CTP.

David Sceppa

Program Manager, SQL Server Driver for PHP