PDO::prepare

Download PHP driver

Prepares a statement for execution.

Syntax

PDOStatement PDO::prepare ( $statement [ , array(key_pair) ] )

Parameters

$statement

A string containing the SQL statement.

key_pair

An array containing an attribute name and value. For more information, see the Remarks section.

Return value

Returns a PDOStatement object on success. On failure, returns a PDOException object, or false depending on the value of PDO::ATTR_ERRMODE.

Remarks

The Microsoft Drivers for PHP for SQL Server doesn't evaluate prepared statements until execution.

The following table lists the possible key_pair values.

Key Description
PDO::ATTR_CURSOR Specifies cursor behavior. The default is PDO::CURSOR_FWDONLY, a non-scrollable forward cursor. PDO::CURSOR_SCROLL is a scrollable cursor.

For example, array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY ).

When set to PDO::CURSOR_SCROLL, you can then use PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE to set the type of scrollable cursor, which is described later in this article.

See Cursor Types (PDO_SQLSRV Driver) for more information about result sets and cursors in the PDO_SQLSRV driver.
PDO::ATTR_EMULATE_PREPARES By default, this attribute is false, which can be changed by this PDO::ATTR_EMULATE_PREPARES => true. See Emulate Prepare for details and example.
PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE Specifies the type of scrollable cursor. Only valid when PDO::ATTR_CURSOR is set to PDO::CURSOR_SCROLL. See later in this article for the values this attribute can take.
PDO::SQLSRV_ATTR_DECIMAL_PLACES Specifies the number of decimal places when formatting fetched money values. This option works only when PDO::SQLSRV_ATTR_FORMAT_DECIMALS is true. For more information, see Formatting Decimal Strings and Money Values (PDO_SQLSRV Driver).
PDO::SQLSRV_ATTR_DIRECT_QUERY When True, specifies direct query execution. False means prepared statement execution. For more information about PDO::SQLSRV_ATTR_DIRECT_QUERY, see Direct Statement Execution and Prepared Statement Execution in the PDO_SQLSRV Driver.
PDO::SQLSRV_ATTR_ENCODING PDO::SQLSRV_ENCODING_UTF8 (default)
PDO::SQLSRV_ENCODING_SYSTEM
PDO::SQLSRV_ENCODING_BINARY
PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE Specifies whether to retrieve date and time types as PHP DateTime objects. For more information, see How to: Retrieve Date and Time Types as PHP DateTime Objects Using the PDO_SQLSRV Driver.
PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE Handles numeric fetches from columns with numeric SQL types. For more information, see PDO::setAttribute.
PDO::SQLSRV_ATTR_FORMAT_DECIMALS Specifies whether to add leading zeroes to decimal strings when appropriate. If set, this option enables the PDO::SQLSRV_ATTR_DECIMAL_PLACES option for formatting money types. For more information, see Formatting Decimal Strings and Money Values (PDO_SQLSRV Driver).
PDO::SQLSRV_ATTR_QUERY_TIMEOUT For more information, see PDO::setAttribute.

When using PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, you can use PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE to specify the type of cursor. For example, pass the following array to PDO::prepare to set a dynamic cursor:

array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL, PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_DYNAMIC));

The following table shows the possible values for PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE. For more information about scrollable cursors, see Cursor Types (PDO_SQLSRV Driver).

Value Description
PDO::SQLSRV_CURSOR_BUFFERED Creates a client-side (buffered) static cursor, which buffers the result set in memory on the client machine.
PDO::SQLSRV_CURSOR_DYNAMIC Creates a server-side (unbuffered) dynamic cursor, which lets you access rows in any order and reflect changes in the database.
PDO::SQLSRV_CURSOR_KEYSET Creates a server-side keyset cursor. A keyset cursor doesn't update the row count if a row is deleted from the table (a deleted row is returned with no values).
PDO::SQLSRV_CURSOR_STATIC Creates a server-side static cursor, which lets you access rows in any order but doesn't reflect changes in the database.

PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL implies PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE => PDO::SQLSRV_CURSOR_STATIC.

You can close a PDOStatement object by calling unset:

unset($stmt);

Forward-only example

This example shows how to use PDO::prepare with parameter markers and a forward-only cursor.

<?php
$database = "Test";
$server = "(local)";
$conn = new PDO( "sqlsrv:server=$server ; Database = $database", "", "");

$col1 = 'a';
$col2 = 'b';

$query = "insert into Table1(col1, col2) values(?, ?)";
$stmt = $conn->prepare( $query, array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY, PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 1  ) );
$stmt->execute( array( $col1, $col2 ) );
print $stmt->rowCount();
echo "\n";

$query = "insert into Table1(col1, col2) values(:col1, :col2)";
$stmt = $conn->prepare( $query, array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY, PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 1  ) );
$stmt->execute( array( ':col1' => $col1, ':col2' => $col2 ) );
print $stmt->rowCount();

unset($stmt);
?>

Static cursor example

This example shows how to use PDO::prepare with a server-side static cursor. For an example showing a client-side cursor, see Cursor Types (PDO_SQLSRV Driver).

<?php
$database = "AdventureWorks";
$server = "(local)";
$conn = new PDO( "sqlsrv:server=$server ; Database = $database", "", "");

$query = "select * from Person.ContactType";
$stmt = $conn->prepare( $query, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();

echo "\n";

while ( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){
   print "$row[Name]\n";
}
echo "\n..\n";

$row = $stmt->fetch( PDO::FETCH_BOTH, PDO::FETCH_ORI_FIRST );
print_r($row);

$row = $stmt->fetch( PDO::FETCH_ASSOC, PDO::FETCH_ORI_REL, 1 );
print "$row[Name]\n";

$row = $stmt->fetch( PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT );
print "$row[1]\n";

$row = $stmt->fetch( PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR );
print "$row[1]..\n";

$row = $stmt->fetch( PDO::FETCH_NUM, PDO::FETCH_ORI_ABS, 0 );
print_r($row);

$row = $stmt->fetch( PDO::FETCH_NUM, PDO::FETCH_ORI_LAST );
print_r($row);
?>

Targeted example

The following two snippets show how to use PDO::prepare with data targeted for char / varchar columns. Because the default encoding for PDO::prepare is UTF-8, the user can use the option PDO::SQLSRV_ENCODING_SYSTEM to avoid implicit conversions.

Option 1

$options = array(PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_SYSTEM);
$statement = $pdo->prepare(
  'SELECT *
   FROM myTable
   WHERE myVarcharColumn = :myVarcharValue',
  $options
);

$statement->bindValue(':myVarcharValue', 'my data', PDO::PARAM_STR);

Option 2

$statement = $pdo->prepare(
  'SELECT *
   FROM myTable
   WHERE myVarcharColumn = :myVarcharValue'
);
$p = 'my data';
$statement->bindParam(':myVarcharValue', $p, PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_SYSTEM);

Prepare example

This example shows how to use PDO::prepare with PDO::ATTR_EMULATE_PREPARES set to true.

<?php
$serverName = "yourservername";
$username = "yourusername";
$password = "yourpassword";
$database = "tempdb";
$conn = new PDO("sqlsrv:server = $serverName; Database = $database", $username, $password);

$pdo_options = array();
$pdo_options[PDO::ATTR_EMULATE_PREPARES] = true;
$pdo_options[PDO::SQLSRV_ATTR_ENCODING] = PDO::SQLSRV_ENCODING_UTF8;

$stmt = $conn->prepare("CREATE TABLE TEST([id] [int] IDENTITY(1,1) NOT NULL,
                                          [name] nvarchar(max))",
                                          $pdo_options);
$stmt->execute();

$prefix = '가각';
$name = '가각ácasa';
$name2 = '가각sample2';

$stmt = $conn->prepare("INSERT INTO TEST(name) VALUES(:p0)", $pdo_options);
$stmt->execute(['p0' => $name]);
unset($stmt);

$stmt = $conn->prepare("SELECT * FROM TEST WHERE NAME LIKE :p0", $pdo_options);
$stmt->execute(['p0' => "$prefix%"]);
foreach ($stmt as $row) {
    echo "\n" . 'FOUND: ' . $row['name'];
}

unset($stmt);
unset($conn);
?>

The PDO_SQLSRV driver internally replaces all the placeholders with the parameters that are bound by PDOStatement::bindParam(). Therefore, a SQL query string with no placeholders is sent to the server. Consider this example:

$statement = $PDO->prepare("INSERT into Customers (CustomerName, ContactName) VALUES (:cus_name, :con_name)");
$statement->bindParam(:cus_name, "Cardinal");
$statement->bindParam(:con_name, "Tom B. Erichsen");
$statement->execute();

With PDO::ATTR_EMULATE_PREPARES set to false (the default case), the data sent to the database is:

"INSERT into Customers (CustomerName, ContactName) VALUES (:cus_name, :con_name)"
Information on :cus_name parameter
Information on :con_name parameter

The server executes the query using its parameterized query feature for binding parameters. On the other hand, with PDO::ATTR_EMULATE_PREPARES set to true, the query sent to the server is essentially:

"INSERT into Customers (CustomerName, ContactName) VALUES ('Cardinal', 'Tom B. Erichsen')"

Setting PDO::ATTR_EMULATE_PREPARES to true can bypass some restrictions in SQL Server. For example, SQL Server doesn't support named or positional parameters in some Transact-SQL clauses. Moreover, SQL Server has a limit of binding 2,100 parameters.

Note

With emulate prepares set to true, the security of parameterized queries isn't in effect. Therefore, your application should ensure that the data that is bound to the parameters doesn't contain malicious Transact-SQL code.

Encoding

If user wishes to bind parameters with different encodings (for instance, UTF-8 or binary), user should clearly specify the encoding in the PHP script.

The PDO_SQLSRV driver first checks the encoding specified in PDO::bindParam() (for example, $statement->bindParam(:cus_name, "Cardinal", PDO::PARAM_STR, 10, PDO::SQLSRV_ENCODING_UTF8)).

If not found, the driver checks if any encoding is set in PDO::prepare() or PDOStatement::setAttribute(). Otherwise, the driver uses the encoding specified in PDO::__construct() or PDO::setAttribute().

In addition, beginning with version 5.8.0, when using PDO::prepare with PDO::ATTR_EMULATE_PREPARES set to true, the user can use the extended string types introduced in PHP 7.2 to ensure that the N prefix is used. The following snippets display various alternatives.

Note

By default, emulate prepares is set to false, in which case the extended PDO string constants will be ignored.

Use driver option PDO::SQLSRV_ENCODING_UTF8 when binding

$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
$stmt = $conn->prepare($sql, $options);
$stmt->bindParam(':value', $p, PDO::PARAM_STR, 0, PDO::SQLSRV_ENCODING_UTF8);
$stmt->execute();

Use the PDO::SQLSRV_ATTR_ENCODING attribute

$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true, PDO::SQLSRV_ATTR_ENCODING => PDO::SQLSRV_ENCODING_UTF8);
$stmt = $conn->prepare($sql, $options);
$stmt->execute([':value' => $p]);

Use the PDO constant PDO::PARAM_STR_NATL

$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
$stmt = $conn->prepare($sql, $options);
$stmt->bindParam(':value', $p, PDO::PARAM_STR | PDO::PARAM_STR_NATL);
$stmt->execute();

Set the default string param type PDO::PARAM_STR_NATL

$conn->setAttribute(PDO::ATTR_DEFAULT_STR_PARAM, PDO::PARAM_STR_NATL);
$p = '가각';
$sql = 'SELECT :value';
$options = array(PDO::ATTR_EMULATE_PREPARES => true);
$stmt = $conn->prepare($sql, $options);
$stmt->execute([':value' => $p]);

Limitations

As you can see, binding is done internally by the driver. A valid query is sent to the server for execution without any parameter. Compared to the regular case, some limitations result when the parameterized query feature isn't in use.

  • It doesn't work for parameters that are bound as PDO::PARAM_INPUT_OUTPUT.
    • When the user specifies PDO::PARAM_INPUT_OUTPUT in PDO::bindParam(), a PDO exception is thrown.
  • It doesn't work for parameters that are bound as output parameters.
    • When the user creates a prepared statement with placeholders that are meant for output parameters (that is, having an equal sign immediately after a placeholder, like SELECT ? = COUNT(*) FROM Table1), a PDO exception is thrown.
    • When a prepared statement invokes a stored procedure with a placeholder as the argument for an output parameter, no exception is thrown because the driver can't detect the output parameter. However, the variable that the user provides for the output parameter remains unchanged.
  • Duplicated placeholders for a binary encoded parameter don't work.