microsoft
/
msphpsql
зеркало из https://github.com/microsoft/msphpsql.git
1
0
Форкнуть 0
Код Задачи Пакеты Проекты Релизы Вики Активность
Страница: Features
Страницы
FAQ Features Home Install and configuration Install pdo_sqlsrv for PHP 7.0 on Debian in 3 ways Recommendations for improving the performance of PDO_SQLSRV and SQLSRV Survey Results
104 Features
Jenny Tam редактировал(а) эту страницу 2021-11-25 15:36:33 -08:00
Содержание

Contents

Azure Active Directory

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.

Starting with 5.6.0, the support for Azure AD access token (requires ODBC Driver version 17 or above in Linux or macOS) and managed identities for Azure resources (requires ODBC Driver version 17.3.1.1 or above) are introduced. Please check Connect Using Azure Active Directory Authentication for more details.

Emulate prepare in PDO_SQLSRV driver

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.

Idle Connection Resiliency

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.

Querying sql_variant columns

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.

Connection pooling in Linux and Mac

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 NetworkIP Resolution (TNIR)

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).

lastInsertID

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.

Using Always Encrypted with the PHP driver

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.

Prerequisites

  • 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.

Always Encrypted: Limitations

For the lists of limitations in SQL Server and the SQLSRV and PDO_SQLSRV drivers regarding Always Encrypted, please check online docs here.

Optional "Driver" connection option

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:

  1. ODBC Driver 17 for SQL Server
  2. ODBC Driver 13 for SQL Server
  3. 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);

Binding Decimals as Parameters

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:

PDO_SQLSRV

$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();

SQLSRV

$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.

Using Azure Key Vault for Column Encryption

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.

Retrieving DateTime values as strings or PHP DateTime objects

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. The same option is added to the statement level in 5.6.0, which always override the corresponding connection option.

On the other hand, when using PDO_SQLSRV, datetime, Date, Time, DateTime2, and DateTimeOffset types are by default returned as strings. Starting with 5.6.0, 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 details, please read

Configurable options to format fetched decimal or numeric values

Unlike integers and floats, which can be retrieved as numbers, in order to preserve the accuracy of decimal or numeric types, their values are always fetched as strings with exact precisions. However, if the values are less than 1, the leading zeroes are missing. Note that the same applies to money and smallmoney fields because they are a subset of decimal fields with fixed precisions and scales.

Starting with 5.6.0, the option FormatDecimals is added to sqlsrv connection and statement levels, which allows the user to format decimal strings. This option expects a boolean value (i.e. true or false) and only affects the decimal / numeric data of fetched results. In other words, the FormatDecimals option has no effect on other operations like insertion or update.

By default, FormatDecimals is false. If set to true, the leading zeroes to decimal strings will be added, if missing. In addition, another option is provided at connection and statement levels, DecimalPlaces, for users to configure the number of decimal places when displaying money and smallmoney data. It accepts integer values in the range of [0, 4]. The underlying data remains the same, but rounding may occur when shown. For this reason, it is not recommended to use the formatted results as inputs to any calculations.

Note that DecimalPlaces option only affects money data, and FormatDecimals must be set to true for it to take effect. In other words, if FormatDecimals is set to false, formatting will be turned off, regardless of DecimalPlaces value. Also, since money or smallmoney fields have scale 4, setting DecimalPlaces value to any negative number or any value larger than 4 will be ignored.

The equivalent connection and statement attributes in pdo_sqlsrv are PDO::SQLSRV_ATTR_FORMAT_DECIMALS and PDO::SQLSRV_ATTR_DECIMAL_PLACES. For both drivers, the statement attributes always override the corresponding connection attributes. For details, please check

Language Selection

The SQLSRV and PDO_SQLSRV drivers allow the user to specify a preferred language using the Language connection option. The effect of this option is similar to executing SET LANGUAGE. The available languages come from the server's syslanguages table. To see the full list of available languages, execute the following query: SELECT name,alias FROM sys.syslanguages.

Note that this option only affects the language of messages returned from the server. It does not affect the language used by the drivers themselves, as they are currently available only in English, and it does not affect the language of the underlying ODBC driver, whose language is determined by the localised version installed on the client system. Therefore, it is possible that changing the Language setting will result in messages being returned in different languages, depending on whether they come from the PHP driver, the ODBC driver, or SQL Server.

The following example sets Language to German:

$conn = sqlsrv_connect('myserver', array('uid'=>'sa', 
                                        'pwd'=>'mypassword',
                                        'CharacterSet'=>'UTF-8',
                                        'Language'=>'German'));

$stmt = sqlsrv_query($conn, "select *, BadColumn from sys.syslanguages");
if (!$stmt) {
    print_r(sqlsrv_errors());
}

The output is:

Array
(
    [0] => Array
        (
            [0] => 42S22
            [SQLSTATE] => 42S22
            [1] => 207
            [code] => 207
            [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ungültiger Spaltenname "BadColumn".
            [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ungültiger Spaltenname "BadColumn".
        )

)

Data Classification Sensitivity Metadata

Data classification includes two metadata attributes:

  • Sensitivity Labels - define the sensitivity level of the data stored in the column
  • Information Types - provide additional granularity into the type of data stored in the column

Users may use Transact-SQL to classify sensitivity data, and each column may have more than one tuple of (label, information type), but for simplicity the following examples use only one tuple per column.

Take a Patients table for example:

CREATE TABLE Patients 
      [PatientId] int identity,
      [SSN] char(11),
      [FirstName] nvarchar(50),
      [LastName] nvarchar(50),
      [BirthDate] date)

We can classify the SSN and BirthDate columns as shown below:

ADD SENSITIVITY CLASSIFICATION TO [Patients].SSN WITH (LABEL = 'Highly Confidential - GDPR', INFORMATION_TYPE = 'Credentials')
ADD SENSITIVITY CLASSIFICATION TO [Patients].BirthDate WITH (LABEL = 'Confidential Personal Data', INFORMATION_TYPE = 'Birthdays')

To access the classification metadata defined in SQL Server 2019, run the following query:

SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
    information_type,
    label
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
    JOIN sys.columns C 
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id

The results are

schema_name table_name column_name information_type label
dbo Patients BirthDate Birthdays Confidential Personal Data
dbo Patients SSN Credentials Highly Confidential - GDPR

Starting in version 5.7.0-preview of PHP drivers, new statement attributes (options) have been added to the query / prepare methods to specifically request sensitivity classification metadata using the existing metadata functions. The query is a SELECT statement that includes the table columns of interest, which may or may not have sensitivity classification metadata defined.

SQLSRV driver

The sqlsrv_field_metadata() function will return data classification sensitivity metadata if the new DataClassification option is true (false by default). For details and an example, see the docs page for sqlsrv_field_metadata.

PDO_SQLSRV driver

Similarly, one of the fields returned by PDOStatement::getColumnMeta() is "flags", which specifies the flags set for the column (always 0). For details and an example, see the docs page for PDOStatement::getColumnMeta.

RANK retrieval

Beginning with 5.9.0, PHP drivers added classification rank retrieval when using ODBC Driver 17.4.2 or above. The user may define rank when using ADD SENSITIVITY CLASSIFICATION to classify any data column. For more examples, check the aforementioned pages about retrieving metadata.

PHP Extended String Types for PDO

New PDO constants were introduced in PHP 7.2 to add support for binding Unicode and non-Unicode strings. That is, Unicode strings can be surrounded with quotes with an N as a prefix. (i.e. N'string' instead of 'string'.)

  1. PDO::PARAM_STR_NATL - a new type for Unicode strings, to be applied as a bitwise-OR to PDO::PARAM_STR
  2. PDO::PARAM_STR_CHAR - a new type for non-Unicode strings, to be applied as a bitwise-OR to PDO::PARAM_STR
  3. PDO::ATTR_DEFAULT_STR_PARAM - set to either PDO::PARAM_STR_NATL or PDO::PARAM_STR_CHAR to indicate a value to bitwise-OR to PDO::PARAM_STR by default

When using the pdo_sqlsrv driver, these new string constants affect how PDO::quote() works, and affect emulated prepared statements. They do not affect real prepared statements.

For an example of how the new constants affect PDO::quote(), see the Docs page.

Emulate prepare statements

The PDO::ATTR_EMULATE_PREPARES attribute is false by default, so set PDO::ATTR_EMULATE_PREPARES to true when using PDO::prepare() to emulate prepare the statement. For more details, please check the emulate prepare examples.

Note that pdo_sqlsrv behavior is different from pdo_mysql and pdo_dblib, as emulate prepared statements is the default for those drivers.

To ensure that the N prefix is used to quote the string values, previously we can use the UTF8 encoding constant, PDO::SQLSRV_ENCODING_UTF8. With PHP 7.2 and above, we can also use the new constant PDO::PARAM_STR_NATL.

For examples showing how to use the new PDO constants, see the Docs page for PDO::prepare().

Real prepare statements

The emulate prepare attribute is false by default, so the extended string types will be ignored.

Note that the default encoding for pdo_sqlsrv driver is UTF-8. Thus, there is no need to do anything extra for Unicode values. On the other hand, if the bound data is targeted for CHAR/VARCHAR columns rather than NCHAR/NVARCHAR ones, the user can use the option PDO::SQLSRV_ENCODING_SYSTEM to avoid implicit conversions. For examples, see the Docs page for PDO::prepare().

Always Encrypted with Secure Enclaves

Always Encrypted with Secure Enclaves is the second iteration of the Always Encrypted feature for SQL Server. Always Encrypted with Secure Enclaves allows users to perform rich computations against encrypted data by creating a secure enclave - a region of memory on the server where encrypted data in a database is decrypted so that computations may be performed. Support for Always Encrypted with Secure Enclaves is available in the PHP Driver for SQL Server starting with 5.7.1-preview. Always Encrypted with Secure Enclaves requires SQL Server 2019 or later and version 17.4+ of the ODBC driver.

For details and examples, see the docs page on Always Encrypted with Secure Enclaves.

PDO ErrorInfo extended to report additional ODBC messages

When an exception occurs, ODBC Driver sometimes returns more than one error to help users diagnose. Unlike sqlsrv, pdo_sqlsrv always shows the first error only. As a response to this bug report, PDO::errorInfo and PDOStatement::errorInfo have been updated to indicate that drivers should display at least the following three fields:

0	SQLSTATE error code (a five characters alphanumeric identifier defined in the ANSI SQL standard).
1	Driver specific error code.
2	Driver specific error message.

Starting with 5.9.0, the default behavior of pdo_sqlsrv is to show additional ODBC errors, if they are available. See PDO::errorInfo for details and examples.

Table-Valued Parameters

Starting with 5.10.0-beta1, the support for Table-Valued Parameters is introduced. The example of how to use Table-Valued Parameters (TVP) with PHP Drivers is shown below.

First, create tables, a table type, and a stored procedure:

CREATE TABLE TVPOrd(
    OrdNo INTEGER IDENTITY(1,1), 
    OrdDate DATETIME, 
    CustID VARCHAR(10))


CREATE TABLE TVPItem(
    OrdNo INTEGER, 
    ItemNo INTEGER IDENTITY(1,1), 
    ProductCode CHAR(10), 
    OrderQty INTEGER, 
    SalesDate DATE, 
    Label NVARCHAR(30), 
    Price DECIMAL(5,2), 
    Photo VARBINARY(MAX))


--Create TABLE type for use as a TVP
CREATE TYPE TVPParam AS TABLE(
                ProductCode CHAR(10), 
                OrderQty INTEGER, 
                SalesDate DATE, 
                Label NVARCHAR(30), 
                Price DECIMAL(5,2), 
                Photo VARBINARY(MAX))


--Create procedure with TVP parameters
CREATE PROCEDURE TVPOrderEntry(
        @CustID VARCHAR(10), 
        @Items TVPParam READONLY,
        @OrdNo INTEGER OUTPUT, 
        @OrdDate DATETIME OUTPUT)
AS
BEGIN
    SET @OrdDate = GETDATE(); SET NOCOUNT ON; 
    INSERT INTO TVPOrd (OrdDate, CustID) VALUES (@OrdDate, @CustID);
    SELECT @OrdNo = SCOPE_IDENTITY();
    INSERT INTO TVPItem (OrdNo, ProductCode, OrderQty, SalesDate, Label, Price, Photo)
    SELECT @OrdNo, ProductCode, OrderQty, SalesDate, Label, Price, Photo 
    FROM @Items
END

The PHP drivers use row-wise binding for Table-Valued Parameters (TVPs), and the user must provide the TVP type name as a non-empty string. In this example, the name is TVPParam. The TVP is essentially a key-value pair with TVP type name as the key and the input data as a nested array, like this:

$image1 = fopen($pic1, 'rb');
$image2 = fopen($pic2, 'rb');
$image3 = fopen($pic3, 'rb');

$items = [
    ['0062836700', 367, "2009-03-12", 'AWC Tee Male Shirt', '20.75', $image1],
    ['1250153272', 256, "2017-11-07", 'Superlight Black Bicycle', '998.45', $image2],
    ['1328781505', 260, "2010-03-03", 'Silver Chain for Bikes', '88.98', $image3],
];

// Create a TVP input array
$tvpType = 'TVPParam';
$tvpInput = array($tvpType => $items);

// To execute the stored procedure, either execute a direct query or prepare this query: 
$callTVPOrderEntry = "{call TVPOrderEntry(?, ?, ?, ?)}";

If using SQLSRV, the user may call sqlsrv_query(), like this:

$custCode = 'SRV_123';
$ordNo = 0;
$ordDate = null;
$params = array($custCode,
                array($tvpInput, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_TABLE, SQLSRV_SQLTYPE_TABLE), // or simply array($tvpInput),
                array(&$ordNo, SQLSRV_PARAM_OUT),
                array(&$ordDate, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR)));
$stmt = sqlsrv_query($conn, $callTVPOrderEntry, $params);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
sqlsrv_next_result($stmt);

The user may also use sqlsrv_prepare()/sqlsrv_execute() instead. In addition, in SQLSRV, the user can call sqlsrv_send_stream_data() to send TVP data post execution, like this:

$options = array("SendStreamParamsAtExec" => 0);
$stmt = sqlsrv_prepare($conn, $callTVPOrderEntry, $params, $options);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
$res = sqlsrv_execute($stmt);
if (!$res) {
    print_r(sqlsrv_errors());
}

// Now call sqlsrv_send_stream_data in a loop
while (sqlsrv_send_stream_data($stmt)) {
}
sqlsrv_next_result($stmt);

If using PDO_SQLSRV, the user must use prepare/execute with bindParam() and specify the TVP input as a PDO::PARAM_LOB. Otherwise, this error will return: "…Operand type clash: nvarchar is incompatible with …". If the stored procedure only takes input parameters, the user can use bindValue() instead of bindParam().

try {
    $stmt = $conn->prepare($callTVPOrderEntry);
    $stmt->bindParam(1, $custCode);
    $stmt->bindParam(2, $tvpInput, PDO::PARAM_LOB);
    // 3 - OrdNo output
    $stmt->bindParam(3, $ordNo, PDO::PARAM_INT, 10);
    // 4 - OrdDate output
    $stmt->bindParam(4, $ordDate, PDO::PARAM_STR, 20);
    $stmt->execute();
} catch (PDOException $e) {
    ...
}

If the user is not using the default DBO schema, then the schema name must be provided. Even if the schema name contains space character, do not use delimiters like [ or ]. Consider the following example.

    $inputs = [
        ['ABC', 12345, null],
        ['DEF', 6789, 'This is a test']
    ];
    $schema = 'Sales DB';
    $tvpType = 'TestTVP';

    // i.e. the TVP type name is "[Sales DB].[TestTVP]"
    $tvpInput = array($tvpType => $inputs, $schema);

The user may also use Table-Valued Parameters without stored procedures. Consider the following:

CREATE TYPE id_table_type AS TABLE(id INT PRIMARY KEY)

CREATE TABLE test_table (id INT PRIMARY KEY)

Using pdo_sqlsrv, the user may insert into test_table like this:

$tsql = "INSERT INTO test_table SELECT * FROM ?";
$tvpInput = array('id_table_type' => [[1], [2], [3]]);

$stmt = $conn->prepare($tsql);
$stmt->bindParam(1, $tvpInput, PDO::PARAM_LOB);
$result = $stmt->execute();

Similarly, using sqlsrv, except with a user defined schema, is shown below:

$schema = 'my schema';
$tvpName = 'id_table_type';

$tsql = "INSERT INTO [$schema].[test_table] SELECT * FROM ?";
$params = [
[[$tvpname => [[1], [2], [3]], $schema]],
];

$stmt = sqlsrv_query($conn, $tsql, $params);
if (!$stmt) {
    print_r(sqlsrv_errors());
}
sqlsrv_free_stmt($stmt);