Microsoft SQL Server Driver for PHP Team Blog

The survey is about to close...

Have you submitted your response to the survey yet?  

If you have,  thank you for taking the time to provide us with your feedback.

If you have not yet taken the survey OR have not finished it yet, it's time to get it completed so that your voice can be heard.  

The survey, which is available here will close on 21st October 2009.  

Thank you,

Raghu Ram

Principal Group Program Manager,

SQL Connectivity,

Microsoft Corp,

Redmond, WA

SQL Server Driver for PHP 1.1 is now available

It is my pleasure to announce that the Microsoft SQL Server Driver for PHP 1.1 has released! 

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. 

This release marks a very big milestone in our continued pursuit for interoperability while also providing support for PHP ver 5.3.   This driver will enable developers to build PHP applications with relational database capabilities to both SQL Server as well as SQL Azure databases.   There are some key performance improvements as well as new features such as support for UTF-8 encoding and scrollable result sets.   

Downloads

We would like to hear your opinion....

Dear SQL Server developers and users:

It is this time of the year when we in the SQL Connectivity product team take a step back, review the priorities and goals for the long term and identify areas that will benefit from investments.

We view YOU and your organization as a key stakeholder in this process and would like to gather your inputs in this survey, which should take no more than 5 - 10 minutes and a few other surveys that we will conduct in the next few months. The feedback you provide is very valuable and be rest assured that each and every response will be read and will provide the background for some of the key decisions that we will make that will benefit our user community - developers, DBAs and all those who use SQL Server or are looking for ways to expand the scope of the power of SQL Server within your organizations.

This survey will be open for your submissions until October 21, 2009 and is available here.  

Thank you,

Raghu Ram

Principal Group Program Manager,

SQL Connectivity,

Microsoft Corp,

Redmond, WA

SQL Server Driver for PHP 1.1 Community Technology Preview is now available

Today we are excited to announce that the Community Technology Preview (CTP) of the Microsoft® SQL Server Driver for PHP 1.1.   This version of the driver provides new capabilities for building PHP applications and support for SQL Azure as well as support for PHP version 5.3, enabling developers to build PHP applications with relational capabilities using SQL Azure Database.    Details of the SQL Azure Database CTP is available in a blogpost here.

 SQL Server Driver for PHP 1.1 CTP will include:

·         Support for SQL Azure

·         Support for PHP version 5.3

·         Support for UTF-8

·         Support for Scrollable results, row countMigration to SQL Server 2008 Native Client framework that provides for performance enhancements.

You can download the CTP of the SQL Server Driver for PHP 1.1 here.

The SQL Connectivity PHP team would welcome your feedback on this release.  

Thanks,

Raghu Ram

Principal Group Program Manager

SQL Connectivity

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