1 | <?php |
||
2 | |||
3 | namespace SilverStripe\ORM\Connect; |
||
4 | |||
5 | use SilverStripe\Core\Config\Config; |
||
6 | use PDO; |
||
7 | use PDOStatement; |
||
8 | use InvalidArgumentException; |
||
9 | |||
10 | /** |
||
11 | * PDO driver database connector |
||
12 | */ |
||
13 | class PDOConnector extends DBConnector |
||
14 | { |
||
15 | |||
16 | /** |
||
17 | * Should ATTR_EMULATE_PREPARES flag be used to emulate prepared statements? |
||
18 | * |
||
19 | * @config |
||
20 | * @var boolean |
||
21 | */ |
||
22 | private static $emulate_prepare = false; |
||
23 | |||
24 | /** |
||
25 | * Default strong SSL cipher to be used |
||
26 | * |
||
27 | * @config |
||
28 | * @var string |
||
29 | */ |
||
30 | private static $ssl_cipher_default = 'DHE-RSA-AES256-SHA'; |
||
31 | |||
32 | /** |
||
33 | * The PDO connection instance |
||
34 | * |
||
35 | * @var PDO |
||
36 | */ |
||
37 | protected $pdoConnection = null; |
||
38 | |||
39 | /** |
||
40 | * Name of the currently selected database |
||
41 | * |
||
42 | * @var string |
||
43 | */ |
||
44 | protected $databaseName = null; |
||
45 | |||
46 | /** |
||
47 | * If available, the row count of the last executed statement |
||
48 | * |
||
49 | * @var int|null |
||
50 | */ |
||
51 | protected $rowCount = null; |
||
52 | |||
53 | /** |
||
54 | * Error generated by the errorInfo() method of the last PDOStatement |
||
55 | * |
||
56 | * @var array|null |
||
57 | */ |
||
58 | protected $lastStatementError = null; |
||
59 | |||
60 | /** |
||
61 | * List of prepared statements, cached by SQL string |
||
62 | * |
||
63 | * @var array |
||
64 | */ |
||
65 | protected $cachedStatements = array(); |
||
66 | |||
67 | /** |
||
68 | * Flush all prepared statements |
||
69 | */ |
||
70 | public function flushStatements() |
||
71 | { |
||
72 | $this->cachedStatements = array(); |
||
73 | } |
||
74 | |||
75 | /** |
||
76 | * Retrieve a prepared statement for a given SQL string, or return an already prepared version if |
||
77 | * one exists for the given query |
||
78 | * |
||
79 | * @param string $sql |
||
80 | * @return PDOStatement |
||
81 | */ |
||
82 | public function getOrPrepareStatement($sql) |
||
83 | { |
||
84 | // Return cached statements |
||
85 | if (isset($this->cachedStatements[$sql])) { |
||
86 | return $this->cachedStatements[$sql]; |
||
87 | } |
||
88 | |||
89 | // Generate new statement |
||
90 | $statement = $this->pdoConnection->prepare( |
||
91 | $sql, |
||
92 | array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY) |
||
93 | ); |
||
94 | |||
95 | // Only cache select statements |
||
96 | if (preg_match('/^(\s*)select\b/i', $sql)) { |
||
97 | $this->cachedStatements[$sql] = $statement; |
||
98 | } |
||
99 | return $statement; |
||
100 | } |
||
101 | |||
102 | /** |
||
103 | * Is PDO running in emulated mode |
||
104 | * |
||
105 | * @return boolean |
||
106 | */ |
||
107 | public static function is_emulate_prepare() |
||
108 | { |
||
109 | return Config::inst()->get('SilverStripe\ORM\Connect\PDOConnector', 'emulate_prepare'); |
||
110 | } |
||
111 | |||
112 | public function connect($parameters, $selectDB = false) |
||
113 | { |
||
114 | $this->flushStatements(); |
||
115 | |||
116 | // Build DSN string |
||
117 | // Note that we don't select the database here until explicitly |
||
118 | // requested via selectDatabase |
||
119 | $driver = $parameters['driver'] . ":"; |
||
120 | $dsn = array(); |
||
121 | |||
122 | // Typically this is false, but some drivers will request this |
||
123 | if ($selectDB) { |
||
124 | // Specify complete file path immediately following driver (SQLLite3) |
||
125 | if (!empty($parameters['filepath'])) { |
||
126 | $dsn[] = $parameters['filepath']; |
||
127 | } elseif (!empty($parameters['database'])) { |
||
128 | // Some databases require a selected database at connection (SQLite3, Azure) |
||
129 | if ($parameters['driver'] === 'sqlsrv') { |
||
130 | $dsn[] = "Database={$parameters['database']}"; |
||
131 | } else { |
||
132 | $dsn[] = "dbname={$parameters['database']}"; |
||
133 | } |
||
134 | } |
||
135 | } |
||
136 | |||
137 | // Syntax for sql server is slightly different |
||
138 | if ($parameters['driver'] === 'sqlsrv') { |
||
139 | $server = $parameters['server']; |
||
140 | if (!empty($parameters['port'])) { |
||
141 | $server .= ",{$parameters['port']}"; |
||
142 | } |
||
143 | $dsn[] = "Server=$server"; |
||
144 | } elseif ($parameters['driver'] === 'dblib') { |
||
145 | $server = $parameters['server']; |
||
146 | if (!empty($parameters['port'])) { |
||
147 | $server .= ":{$parameters['port']}"; |
||
148 | } |
||
149 | $dsn[] = "host={$server}"; |
||
150 | } else { |
||
151 | if (!empty($parameters['server'])) { |
||
152 | // Use Server instead of host for sqlsrv |
||
153 | $dsn[] = "host={$parameters['server']}"; |
||
154 | } |
||
155 | |||
156 | if (!empty($parameters['port'])) { |
||
157 | $dsn[] = "port={$parameters['port']}"; |
||
158 | } |
||
159 | } |
||
160 | |||
161 | // Connection charset and collation |
||
162 | $connCharset = Config::inst()->get('SilverStripe\ORM\Connect\MySQLDatabase', 'connection_charset'); |
||
163 | $connCollation = Config::inst()->get('SilverStripe\ORM\Connect\MySQLDatabase', 'connection_collation'); |
||
164 | |||
165 | // Set charset if given and not null. Can explicitly set to empty string to omit |
||
166 | if (!in_array($parameters['driver'], ['sqlsrv', 'pgsql'])) { |
||
167 | $charset = isset($parameters['charset']) |
||
168 | ? $parameters['charset'] |
||
169 | : $connCharset; |
||
170 | if (!empty($charset)) { |
||
171 | $dsn[] = "charset=$charset"; |
||
172 | } |
||
173 | } |
||
174 | |||
175 | // Connection commands to be run on every re-connection |
||
176 | if (!isset($charset)) { |
||
177 | $charset = $connCharset; |
||
178 | } |
||
179 | |||
180 | $options = []; |
||
181 | if ($parameters['driver'] === 'mysql') { |
||
182 | $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $charset . ' COLLATE ' . $connCollation; |
||
183 | } |
||
184 | |||
185 | // Set SSL options if they are defined |
||
186 | if (array_key_exists('ssl_key', $parameters) && |
||
187 | array_key_exists('ssl_cert', $parameters) |
||
188 | ) { |
||
189 | $options[PDO::MYSQL_ATTR_SSL_KEY] = $parameters['ssl_key']; |
||
190 | $options[PDO::MYSQL_ATTR_SSL_CERT] = $parameters['ssl_cert']; |
||
191 | if (array_key_exists('ssl_ca', $parameters)) { |
||
192 | $options[PDO::MYSQL_ATTR_SSL_CA] = $parameters['ssl_ca']; |
||
193 | } |
||
194 | // use default cipher if not provided |
||
195 | $options[PDO::MYSQL_ATTR_SSL_CIPHER] = array_key_exists('ssl_cipher', $parameters) ? $parameters['ssl_cipher'] : self::config()->get('ssl_cipher_default'); |
||
196 | } |
||
197 | |||
198 | if (self::is_emulate_prepare()) { |
||
199 | $options[PDO::ATTR_EMULATE_PREPARES] = true; |
||
200 | } |
||
201 | |||
202 | // May throw a PDOException if fails |
||
203 | $this->pdoConnection = new PDO( |
||
204 | $driver . implode(';', $dsn), |
||
205 | empty($parameters['username']) ? '' : $parameters['username'], |
||
206 | empty($parameters['password']) ? '' : $parameters['password'], |
||
207 | $options |
||
208 | ); |
||
209 | |||
210 | // Show selected DB if requested |
||
211 | if ($this->pdoConnection && $selectDB && !empty($parameters['database'])) { |
||
212 | $this->databaseName = $parameters['database']; |
||
213 | } |
||
214 | } |
||
215 | |||
216 | public function getVersion() |
||
217 | { |
||
218 | return $this->pdoConnection->getAttribute(PDO::ATTR_SERVER_VERSION); |
||
219 | } |
||
220 | |||
221 | public function escapeString($value) |
||
222 | { |
||
223 | $value = $this->quoteString($value); |
||
224 | |||
225 | // Since the PDO library quotes the value, we should remove this to maintain |
||
226 | // consistency with MySQLDatabase::escapeString |
||
227 | if (preg_match('/^\'(?<value>.*)\'$/', $value, $matches)) { |
||
228 | $value = $matches['value']; |
||
229 | } |
||
230 | return $value; |
||
231 | } |
||
232 | |||
233 | public function quoteString($value) |
||
234 | { |
||
235 | return $this->pdoConnection->quote($value); |
||
236 | } |
||
237 | |||
238 | /** |
||
239 | * Invoked before any query is executed |
||
240 | * |
||
241 | * @param string $sql |
||
242 | */ |
||
243 | protected function beforeQuery($sql) |
||
244 | { |
||
245 | // Reset state |
||
246 | $this->rowCount = 0; |
||
247 | $this->lastStatementError = null; |
||
248 | |||
249 | // Flush if necessary |
||
250 | if ($this->isQueryDDL($sql)) { |
||
251 | $this->flushStatements(); |
||
252 | } |
||
253 | } |
||
254 | |||
255 | /** |
||
256 | * Executes a query that doesn't return a resultset |
||
257 | * |
||
258 | * @param string $sql The SQL query to execute |
||
259 | * @param integer $errorLevel For errors to this query, raise PHP errors |
||
260 | * using this error level. |
||
261 | * @return int |
||
262 | */ |
||
263 | public function exec($sql, $errorLevel = E_USER_ERROR) |
||
264 | { |
||
265 | $this->beforeQuery($sql); |
||
266 | |||
267 | // Directly exec this query |
||
268 | $result = $this->pdoConnection->exec($sql); |
||
269 | |||
270 | // Check for errors |
||
271 | if ($result !== false) { |
||
272 | return $this->rowCount = $result; |
||
273 | } |
||
274 | |||
275 | $this->databaseError($this->getLastError(), $errorLevel, $sql); |
||
276 | return null; |
||
277 | } |
||
278 | |||
279 | public function query($sql, $errorLevel = E_USER_ERROR) |
||
280 | { |
||
281 | $this->beforeQuery($sql); |
||
282 | |||
283 | // Directly query against connection |
||
284 | $statement = $this->pdoConnection->query($sql); |
||
285 | |||
286 | // Generate results |
||
287 | return $this->prepareResults($statement, $errorLevel, $sql); |
||
288 | } |
||
289 | |||
290 | /** |
||
291 | * Determines the PDO::PARAM_* type for a given PHP type string |
||
292 | * @param string $phpType Type of object in PHP |
||
293 | * @return integer PDO Parameter constant value |
||
294 | */ |
||
295 | public function getPDOParamType($phpType) |
||
296 | { |
||
297 | switch ($phpType) { |
||
298 | case 'boolean': |
||
299 | return PDO::PARAM_BOOL; |
||
300 | case 'NULL': |
||
301 | return PDO::PARAM_NULL; |
||
302 | case 'integer': |
||
303 | return PDO::PARAM_INT; |
||
304 | case 'object': // Allowed if the object or resource has a __toString method |
||
305 | case 'resource': |
||
306 | case 'float': // Not actually returnable from get_type |
||
307 | case 'double': |
||
308 | case 'string': |
||
309 | return PDO::PARAM_STR; |
||
310 | case 'blob': |
||
311 | return PDO::PARAM_LOB; |
||
312 | case 'array': |
||
313 | case 'unknown type': |
||
314 | default: |
||
315 | throw new InvalidArgumentException("Cannot bind parameter as it is an unsupported type ($phpType)"); |
||
316 | } |
||
317 | } |
||
318 | |||
319 | /** |
||
320 | * Bind all parameters to a PDOStatement |
||
321 | * |
||
322 | * @param PDOStatement $statement |
||
323 | * @param array $parameters |
||
324 | */ |
||
325 | public function bindParameters(PDOStatement $statement, $parameters) |
||
326 | { |
||
327 | // Bind all parameters |
||
328 | for ($index = 0; $index < count($parameters); $index++) { |
||
0 ignored issues
–
show
|
|||
329 | $value = $parameters[$index]; |
||
330 | $phpType = gettype($value); |
||
331 | |||
332 | // Allow overriding of parameter type using an associative array |
||
333 | if ($phpType === 'array') { |
||
334 | $phpType = $value['type']; |
||
335 | $value = $value['value']; |
||
336 | } |
||
337 | |||
338 | // Check type of parameter |
||
339 | $type = $this->getPDOParamType($phpType); |
||
340 | if ($type === PDO::PARAM_STR) { |
||
341 | $value = strval($value); |
||
342 | } |
||
343 | |||
344 | // Bind this value |
||
345 | $statement->bindValue($index+1, $value, $type); |
||
346 | } |
||
347 | } |
||
348 | |||
349 | public function preparedQuery($sql, $parameters, $errorLevel = E_USER_ERROR) |
||
350 | { |
||
351 | $this->beforeQuery($sql); |
||
352 | |||
353 | // Prepare statement |
||
354 | $statement = $this->getOrPrepareStatement($sql); |
||
355 | |||
356 | // Bind and invoke statement safely |
||
357 | if ($statement) { |
||
358 | $this->bindParameters($statement, $parameters); |
||
359 | $statement->execute($parameters); |
||
360 | } |
||
361 | |||
362 | // Generate results |
||
363 | return $this->prepareResults($statement, $errorLevel, $sql); |
||
364 | } |
||
365 | |||
366 | /** |
||
367 | * Given a PDOStatement that has just been executed, generate results |
||
368 | * and report any errors |
||
369 | * |
||
370 | * @param PDOStatement $statement |
||
371 | * @param int $errorLevel |
||
372 | * @param string $sql |
||
373 | * @param array $parameters |
||
374 | * @return PDOQuery |
||
375 | */ |
||
376 | protected function prepareResults($statement, $errorLevel, $sql, $parameters = array()) |
||
377 | { |
||
378 | |||
379 | // Record row-count and errors of last statement |
||
380 | if ($this->hasError($statement)) { |
||
381 | $this->lastStatementError = $statement->errorInfo(); |
||
382 | } elseif ($statement) { |
||
383 | // Count and return results |
||
384 | $this->rowCount = $statement->rowCount(); |
||
385 | return new PDOQuery($statement); |
||
386 | } |
||
387 | |||
388 | // Ensure statement is closed |
||
389 | if ($statement) { |
||
390 | $statement->closeCursor(); |
||
391 | unset($statement); |
||
392 | } |
||
393 | |||
394 | // Report any errors |
||
395 | if ($parameters) { |
||
396 | $parameters = $this->parameterValues($parameters); |
||
397 | } |
||
398 | $this->databaseError($this->getLastError(), $errorLevel, $sql, $parameters); |
||
399 | return null; |
||
400 | } |
||
401 | |||
402 | /** |
||
403 | * Determine if a resource has an attached error |
||
404 | * |
||
405 | * @param PDOStatement|PDO $resource the resource to check |
||
406 | * @return boolean Flag indicating true if the resource has an error |
||
407 | */ |
||
408 | protected function hasError($resource) |
||
409 | { |
||
410 | // No error if no resource |
||
411 | if (empty($resource)) { |
||
412 | return false; |
||
413 | } |
||
414 | |||
415 | // If the error code is empty the statement / connection has not been run yet |
||
416 | $code = $resource->errorCode(); |
||
417 | if (empty($code)) { |
||
418 | return false; |
||
419 | } |
||
420 | |||
421 | // Skip 'ok' and undefined 'warning' types. |
||
422 | // @see http://docstore.mik.ua/orelly/java-ent/jenut/ch08_06.htm |
||
423 | return $code !== '00000' && $code !== '01000'; |
||
424 | } |
||
425 | |||
426 | public function getLastError() |
||
427 | { |
||
428 | $error = null; |
||
429 | if ($this->lastStatementError) { |
||
430 | $error = $this->lastStatementError; |
||
431 | } elseif ($this->hasError($this->pdoConnection)) { |
||
432 | $error = $this->pdoConnection->errorInfo(); |
||
433 | } |
||
434 | if ($error) { |
||
435 | return sprintf("%s-%s: %s", $error[0], $error[1], $error[2]); |
||
436 | } |
||
437 | return null; |
||
438 | } |
||
439 | |||
440 | public function getGeneratedID($table) |
||
441 | { |
||
442 | return $this->pdoConnection->lastInsertId(); |
||
443 | } |
||
444 | |||
445 | public function affectedRows() |
||
446 | { |
||
447 | return $this->rowCount; |
||
448 | } |
||
449 | |||
450 | public function selectDatabase($name) |
||
451 | { |
||
452 | $this->exec("USE \"{$name}\""); |
||
453 | $this->databaseName = $name; |
||
454 | return true; |
||
455 | } |
||
456 | |||
457 | public function getSelectedDatabase() |
||
458 | { |
||
459 | return $this->databaseName; |
||
460 | } |
||
461 | |||
462 | public function unloadDatabase() |
||
463 | { |
||
464 | $this->databaseName = null; |
||
465 | } |
||
466 | |||
467 | public function isActive() |
||
468 | { |
||
469 | return $this->databaseName && $this->pdoConnection; |
||
470 | } |
||
471 | } |
||
472 |
If the size of the collection does not change during the iteration, it is generally a good practice to compute it beforehand, and not on each iteration: