-
Notifications
You must be signed in to change notification settings - Fork 375
Features
- Azure Active Directory
- Emulate prepare in PDO_SQLSRV driver
- Idle Connection Resiliency
- Querying sql_variant columns
- Connection pooling in Linux and Mac
- lastInsertID
- Using Always Encrypted with the PHP driver
- Always Encrypted: Limitations
- Optional "Driver" Connection option
- Binding Decimals as Parameters
- Using Azure Key Vault for Column Encryption
- Retrieving DateTime values as strings or PHP DateTime objects
- Statement option to format fetched decimal or numeric values
Azure Active Directory (Azure AD) is a central user ID management technology that operates as an alternative to SQL Server authentication. Azure AD allows connections to Microsoft Azure SQL Database and SQL Data Warehouse with federated identities in Azure AD using a username and password or using Windows Integrated Authentication. For details please check the online docs for Azure AD.
Support for Azure AD access token is introduced in 5.4.0-preview, and ODBC Driver version 17+ is required in Linux / macOS. Users can connect using a new connection option AccessToken with a valid access token byte string, but the connection string must not contain keywords for user ID, password, and Authentication. For more technical details, please read Authenticating with an Access Token.
The examples below assume the PHP variable $accToken contains the valid access token byte string in the format extracted from an OAuth JSON response:
// Using an access token to connect: do not pass in $uid or $pwd
$connectionInfo = "Database = $azureAdDatabase; AccessToken = $accToken;";
$conn = new PDO("sqlsrv:server = $azureAdServer; $connectionInfo");
// Using an access token to connect: do not use UID or PWD connection options
$connectionInfo = array("Database"=>$azureAdDatabase, "AccessToken"=>$accToken);
$conn = sqlsrv_connect($azureAdServer, $connectionInfo);
When PDO::ATTR_EMULATE_PREPARES is on for a prepared statement, the PDO_SQLSRV driver internally replaces the placeholders in a SQL query prepared by PDO::prepare() with parameters that are bounded with PDOStatement::bindParam(), and a complete SQL string with no placeholders left is sent to the database.
Please check the online docs for details and an example of how to use Emulate Prepare
Connection resiliency is the idea that a connection that is broken can be reestablished, within certain constraints. If a connection to Microsoft SQL Server fails, connection resiliency allows the client to automatically attempt to reestablish the connection. Connection resiliency is a property of the data source; only SQL Server 2014 and later and Azure SQL Database support connection resiliency.
Please check the online docs for Connection resiliency.
sql_variant is a data type that stores values of various SQL Server-supported data types. A column of type sql_variant may contain rows of different data types. For instance, a column defined as sql_variant can store int, binary, and char values.
For an example for SQLSRV driver, please check sqlsrv_query. Likewise, the PDO_SQLSRV example can be found in PDO::query.
A connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all connections are being used, a new connection is made and added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.
For more details and examples about connection pooling, please check online docs Connection Pooling
Transparent Network IP Resolution is a revision of the existing MultiSubnetFailover feature that affects the connection sequence of the driver in the case where the first resolved IP of the hostname does not respond and there are multiple IPs associated with the hostname. It interacts with MultiSubnetFailover to provide different connection sequences.
For details, please check online docs about Transparent Network IP Resolution (TNIR)
Version 5.0 of the Microsoft PHP Drivers for SQL Server changes the behaviour of PDO::lastInsertID to be consistent with the behaviour outlined in the PDO documentation. PDO::lastInsertID returns the ID of the last inserted sequence or row, and accepts an optional string $name as its only parameter:
public string PDO::lastInsertId ([ string $name = NULL ] )
The $name argument is the identifier of the sequence object for which the ID is returned.
Before version 5.0 of the drivers, the behaviour was as follows. The $name argument was treated as a table name and not a sequence name. Therefore, passing a sequence object to lastInsertID produced no output. Passing a table name produced the last inserted ID for that table.
For more information and an example, please check PDO::lastInsertId
Always Encrypted allows client application to encrypt data and never reveal the encryption keys or data to the SQL Server. To understand it or for more background information, please see Always Encrypted.
- Configure Always Encrypted in your database. Follow the directions in Getting Started with Always Encrypted to configure Always Encrypted in your database.
- A keystore that houses the master and column encryption keys is required. Currently the PHP drivers support the Windows Certificate Store or Azure Key Vault. Always Encrypted is supported on Windows, Linux, and macOS through Azure Key Vault.
- MSODBC driver 17, available here.
For details of how to use the Always Encrypted feature, please check the online docs Using Always Encrypted with the PHP Drivers for SQL Server.
For the lists of limitations in SQL Server and the SQLSRV and PDO_SQLSRV drivers regarding Always Encrypted, please check online docs here.
SQLSRV and PDO_SQLSRV rely on Microsoft ODBC Driver for SQL Server to communicate with SQL Server. If you want to specify which ODBC Driver to use, you can set the "Driver" option with these possible values are:
ODBC Driver 17 for SQL Server ODBC Driver 13 for SQL Server ODBC Driver 11 for SQL Server (Windows only).
By default, when the Driver keyword is not specified, the Microsoft Drivers for PHP for SQL Server attempt to find the supported Microsoft ODBC driver(s) in the system, starting with the latest version of ODBC and so on. If none found, the exception "This extension requires the Microsoft ODBC Driver for SQL Server." is thrown.
SQLSRV:
$connectionOptions = array("Database"=>$database,"UID"=>$userName, "PWD"=>$userPassword, "Driver"=>"ODBC Driver 17 for SQL Server");
$conn = sqlsrv_connect($server, $connectionOptions);
PDO_SQLSRV:
$connectionOptions = "Driver = ODBC Driver 17 for SQL Server";
$conn = new PDO("sqlsrv:server = $server; $connectionOptions", $uid, $pwd);
Both SQLSRV and PDO_SQLSRV support binding decimals as parameters (see the examples in sqlsrv_query and PDOStatement::bindParam).
However, PHP has warned that floating point numbers have limited precision. To illustrate, see the following scenario:
<?php
$number = 999999999999.99999;
printf("%f", $number);
echo PHP_EOL;
$number = 9223372036854.80000;
printf("%f", $number);
echo PHP_EOL;
?>
The output is
1000000000000.000000
9223372036854.800781
Therefore, to ensure that the accuracy is preserved when binding decimals as parameters, it is recommended to use strings as inputs. Below are some examples:
$input = 9223372036854.80000;
$stmt = $conn->prepare("INSERT INTO $tbname (col) VALUES (?)");
// by default it is PDO::PARAM_STR, rounding of the input value
// occurs if PDO::PARAM_INT is specified
$stmt->bindParam(1, $input);
$stmt->execute();
$input = "9223372036854.80000";
$params = array($input);
$stmt = sqlsrv_prepare($conn, "INSERT INTO $tbname (col) VALUES (?)", $params);
sqlsrv_execute($stmt);
The above suggestion applies to decimal and numeric data types in SQL Server.
You can use Azure Key Vault (AKV) to store the master and column encryption keys required for Always Encrypted functionality to work. With AKV, Always Encrypted is supported on Windows, Linux, and macOS. To get started with AKV, see the documentation. To connect to AKV from a PHP script, you can use the credentials for an Azure account consisting of either an Azure Active Directory username and password, or an application client ID and client secret. The encryption keys can be easily created in SQL Server Management Studio or using PowerShell; details here. Support for AKV is provided by the underlying ODBC Driver (version 17 is required), as detailed here.
To use AKV from a PHP script, please check the online docs Using Azure Key Vault.
When using SQLSRV, by default, datetime, Date, Time, DateTime2, and DateTimeOffset types are returned as PHP DateTime types. To retrieve these data types as strings, set the connection option ReturnDatesAsStrings to true.
Starting with 5.4.0-preview, the option ReturnDatesAsStrings is added to the statement level, which always override the corresponding connection option. For example,
<?php
$serverName = 'MyServer';
$connectionInfo = array('Database' => 'MyDatabase', 'ReturnDatesAsStrings' => false);
$conn = sqlsrv_connect($serverName, $connectionInfo);
// Here should check if $conn is valid, but let us assume the connection is successful
$options = array('ReturnDatesAsStrings' => true);
$query = "SELECT DateTimeCol FROM $tableName";
$stmt = sqlsrv_prepare($conn, $query, array(), $options);
// Again, assume $stmt is valid
sqlsrv_execute($stmt);
// Expect the fetched value to be a string
$field = sqlsrv_get_field($stmt, 0);
echo $field . PHP_EOL;
sqlsrv_close( $conn);
?>
On the other hand, when using PDO_SQLSRV, datetime, Date, Time, DateTime2, and DateTimeOffset types are by default returned as strings. Starting with 5.4.0-preview, the new flag PDO_STMT_OPTION_FETCHES_DATETIME_TYPE is introduced such that these types can be retrieved as PHP DateTime objects. This flag can be set as a connection attribute or statement attribute, and the latter always overrides the corresponding connection attribute.
Note that for both drivers, retrieving DateTime as objects are only allowed for regular fetching, not as output parameters. In addition, neither PDO::ATTR_STRINGIFY_FETCHES nor PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE affects whether DateTime data types are returned as strings or not.
For example, assuming the connection is successful and $conn a valid PHP resource object:
// To set the connection attribute
$conn->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
$query = "SELECT DateTimeCol FROM $tableName";
$stmt = $conn->prepare($query);
$stmt->execute();
// Expect a DateTimeCol value as a PHP DateTime type
$row = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($row);
Likewise, this example shows how to set the statement attribute:
$query = "SELECT DateTimeCol FROM $tableName";
$stmt = $conn->prepare($query);
$stmt->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
$stmt->execute();
// Expect a DateTimeCol value as a PHP DateTime type
$row = $stmt->fetch(PDO::FETCH_NUM);
var_dump($row);
Alternatively, the user can set the statement attribute as an option:
$dateObj = null;
$query = "SELECT DateTimeCol FROM $tableName";
$options = array(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE => true);
$stmt = $conn->prepare($query, $options);
$stmt->execute();
$stmt->bindColumn(1, $dateObj, PDO::PARAM_LOB);
$row = $stmt->fetch(PDO::FETCH_BOUND);
var_dump($dateObj);
The following example shows how to achieve the opposite:
$dateStr = null;
$query = "SELECT DateTimeCol FROM $tableName";
$options = array(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE => false);
$stmt = $conn->prepare($query, $options);
$stmt->execute();
$stmt->bindColumn(1, $dateStr);
$row = $stmt->fetch(PDO::FETCH_BOUND);
echo $dateStr . PHP_EOL;