Skip to content

Commit fe6274d

Browse files
yitamyitam
committed
Elaborate emulate prepare and add an example
1 parent 26e9180 commit fe6274d

1 file changed

Lines changed: 86 additions & 4 deletions

File tree

‎docs/connect/php/pdo-prepare.md‎

Lines changed: 86 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "PDO::prepare | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "07/10/2017"
4+
ms.date: "07/20/2018"
55
ms.prod: sql
66
ms.prod_service: connectivity
77
ms.reviewer: ""
@@ -43,7 +43,7 @@ The following table lists the possible *key_pair* values.
4343
|Key|Description|
4444
|-------|---------------|
4545
|PDO::ATTR_CURSOR|Specifies cursor behavior. The default is PDO::CURSOR_FWDONLY. PDO::CURSOR_SCROLL is a static cursor.<br /><br />For example, `array( PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY )`.<br /><br />If you use PDO::CURSOR_SCROLL, you can use PDO::SQLSRV_ATTR_CURSOR_SCROLL_TYPE, which is described below.<br /><br />See [Cursor Types &#40;PDO_SQLSRV Driver&#41;](../../connect/php/cursor-types-pdo-sqlsrv-driver.md) for more information about result sets and cursors in the PDO_SQLSRV driver.|
46-
|PDO::ATTR_EMULATE_PREPARES|When PDO::ATTR_EMULATE_PREPARES is on, the placeholders in a prepared statement is replaced by bound parameters. A complete SQL statement with no placeholders is then sent to the database at Execute. <br /><br />PDO::ATTR_EMULATE_PREPARES can be used to bypass some restrictions in SQL Server. For example, SQL Server does not support named or positional parameters in some Transact-SQL clauses. Also, SQL Server has a limit of binding 2100 parameters.<br /><br />You can set the PDO::ATTR_EMULATE_PREPARES attribute to true. For example:<br /><br />`PDO::ATTR_EMULATE_PREPARES => true`<br /><br />By default, this attribute is set to false.<br /><br />**Note:** The security of parameterized queries is not in effect when you use `PDO::ATTR_EMULATE_PREPARES => true`. Your application should ensure that the data that is bound to the parameter(s) does not contain malicious Transact-SQL code.<br /><br />**Limitations:**: Because the parameters are not bound using the database's parameterized query feature, input_output and output parameters are not supported.|
46+
|PDO::ATTR_EMULATE_PREPARES|By default, this attribute is false, which can be changed by this `PDO::ATTR_EMULATE_PREPARES => true`. See [Emulate Prepare](#emulate-prepare) for details and example.|
4747
|PDO::SQLSRV_ATTR_ENCODING|PDO::SQLSRV_ENCODING_UTF8 (default)<br /><br />PDO::SQLSRV_ENCODING_SYSTEM<br /><br />PDO::SQLSRV_ENCODING_BINARY|
4848
|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](../../connect/php/direct-statement-execution-prepared-statement-execution-pdo-sqlsrv-driver.md).|
4949
|PDO::SQLSRV_ATTR_QUERY_TIMEOUT|For more information, see [PDO::setAttribute](../../connect/php/pdo-setattribute.md).|
@@ -91,7 +91,7 @@ print $stmt->rowCount();
9191
$stmt = null
9292
?>
9393
```
94-
94+
9595
## Example
9696
This example shows how to use the PDO::prepare method with a client-side cursor. For a sample showing a server-side cursor, see [Cursor Types &#40;PDO_SQLSRV Driver&#41;](../../connect/php/cursor-types-pdo-sqlsrv-driver.md).
9797

@@ -131,7 +131,89 @@ $row = $stmt->fetch( PDO::FETCH_NUM, PDO::FETCH_ORI_LAST );
131131
print_r($row);
132132
?>
133133
```
134-
134+
135+
<a name="emulate-prepare" />
136+
137+
## Example
138+
139+
This example shows how to use the PDO::prepare method with PDO::ATTR_EMULATE_PREPARES set to true.
140+
141+
<?php
142+
$serverName = "yourservername";
143+
$username = "yourusername";
144+
$password = "yourpassword";
145+
$database = "tempdb";
146+
$conn = new PDO("sqlsrv:server = $serverName; Database = $database", $username, $password);
147+
148+
$pdo_options = array();
149+
$pdo_options[PDO::ATTR_EMULATE_PREPARES] = true;
150+
$pdo_options[PDO::SQLSRV_ATTR_ENCODING] = PDO::SQLSRV_ENCODING_UTF8;
151+
152+
$stmt = $conn->prepare("CREATE TABLE TEST([id] [int] IDENTITY(1,1) NOT NULL,
153+
[name] nvarchar(max))",
154+
$pdo_options);
155+
$stmt->execute();
156+
157+
$prefix = '가각';
158+
$name = '가각ácasa';
159+
$name2 = '가각sample2';
160+
161+
$stmt = $conn->prepare("INSERT INTO TEST(name) VALUES(:p0)", $pdo_options);
162+
$stmt->execute(['p0' => $name]);
163+
unset($stmt);
164+
165+
$stmt = $conn->prepare("SELECT * FROM TEST WHERE NAME LIKE :p0", $pdo_options);
166+
$stmt->execute(['p0' => "$prefix%"]);
167+
foreach ($stmt as $row) {
168+
echo "\n" . 'FOUND: ' . $row['name'];
169+
}
170+
171+
unset($stmt);
172+
unset($conn);
173+
?>
174+
175+
The PDO_SQLSRV driver internally replaces all the placeholders with the parameters that are bound by [PDOStatement::bindParam()](../../connect/php/pdostatement-bindparam.md). Therefore, a SQL query string with no placeholders is sent to the server. Consider this example,
176+
177+
```
178+
$statement = $PDO->prepare("INSERT into Customers (CustomerName, ContactName) VALUES (:cus_name, :con_name)");
179+
$statement->bindParam(:cus_name, "Cardinal");
180+
$statement->bindParam(:con_name, "Tom B. Erichsen");
181+
$statement->execute();
182+
```
183+
184+
With emulate prepare set to false (the default case), the data sent to the database is:
185+
186+
```
187+
"INSERT into Customers (CustomerName, ContactName) VALUES (:cus_name, :con_name)"
188+
Information on :cus_name parameter
189+
Information on :con_name parameter
190+
```
191+
192+
The server will execute the query using its parameterized query feature for binding parameters. On the other hand, with emulate prepare set to true, the data sent to the server is essentially this:
193+
194+
```
195+
"INSERT into Customers (CustomerName, ContactName) VALUES ('Cardinal', 'Tom B. Erichsen')"
196+
```
197+
198+
### Encoding
199+
200+
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.
201+
202+
The PDO_SQLSRV driver first checks the encoding specified in `PDO::bindParam()` (e.g., `$statement->bindParam(:cus_name, "Cardinal", PDO::PARAM_STR, 10, PDO::SQLSRV_ENCODING_UTF8)`).
203+
204+
If not found, the driver checks if any encoding is set in `PDO::prepare()` or `PDOStatement::setAttribute()`. Otherwise, the driver will use the encoding specified in `PDO::__construct()` or `PDO::setAttribute()`.
205+
206+
### Limitations
207+
208+
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 is not in use.
209+
210+
1. It does not work for parameters that are bound as `PDO::PARAM_INPUT_OUTPUT`.
211+
- When the user specifies `PDO::PARAM_INPUT_OUTPUT` in `PDO::bindParam()`, a PDO exception is thrown.
212+
2. It does not work for parameters that are bound as output parameters.
213+
- When the user creates a prepared statement with placeholders that are meant for output parameters (i.e., having an equal sign immediately after a placeholder (e.g., `SELECT ? = COUNT(*) FROM Table1`)), a PDO exception is thrown.
214+
- When the user creates a prepared statement that calls a procedure and with a placeholder as argument for an output parameter, no exception is thrown since the driver cannot detect the output parameter. However, the variable that the user provides for the output parameter will not be changed as expected in a non-emulate-prepared statement.
215+
3. Duplicated placeholders for a binary encoded parameter will not work
216+
135217
## See Also
136218
[PDO Class](../../connect/php/pdo-class.md)
137219

0 commit comments

Comments
 (0)