1--TEST-- 2MySQL PDO->prepare(), emulated PS 3--EXTENSIONS-- 4pdo_mysql 5--SKIPIF-- 6<?php 7require_once(__DIR__ . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc'); 8MySQLPDOTest::skip(); 9$db = MySQLPDOTest::factory(); 10?> 11--FILE-- 12<?php 13 require_once(__DIR__ . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc'); 14 $db = MySQLPDOTest::factory(); 15 $db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true); 16 17 function prepex($offset, &$db, $query, $input_params = null, $error_info = null) { 18 19 try { 20 21 if (is_array($error_info) && isset($error_info['prepare'])) 22 $stmt = @$db->prepare($query); 23 else 24 $stmt = $db->prepare($query); 25 26 if (is_array($error_info) && isset($error_info['prepare'])) { 27 $tmp = $db->errorInfo(); 28 29 if (isset($error_info['prepare']['sqlstate']) && 30 ($error_info['prepare']['sqlstate'] !== $tmp[0])) { 31 printf("[%03d] prepare() - expecting SQLSTATE '%s' got '%s'\n", 32 $offset, $error_info['prepare']['sqlstate'], $tmp[0]); 33 return false; 34 } 35 36 if (isset($error_info['prepare']['mysql']) && 37 ($error_info['prepare']['mysql'] !== $tmp[1])) { 38 printf("[%03d] prepare() - expecting MySQL Code '%s' got '%s'\n", 39 $offset, $error_info['prepare']['mysql'], $tmp[0]); 40 return false; 41 } 42 43 return false; 44 } 45 46 if (is_null($input_params)) 47 $input_params = array(); 48 49 if (is_array($error_info) && isset($error_info['execute'])) 50 $ret = @$stmt->execute($input_params); 51 else 52 $ret = $stmt->execute($input_params); 53 54 if (!is_bool($ret)) 55 printf("[%03d] PDO::execute() should return a boolean value, got %s/%s\n", 56 var_export($ret, true), $ret); 57 58 if (is_array($error_info) && isset($error_info['execute'])) { 59 $tmp = $stmt->errorInfo(); 60 61 if (isset($error_info['execute']['sqlstate']) && 62 ($error_info['execute']['sqlstate'] !== $tmp[0])) { 63 printf("[%03d] execute() - expecting SQLSTATE '%s' got '%s'\n", 64 $offset, $error_info['execute']['sqlstate'], $tmp[0]); 65 return false; 66 } 67 68 if (isset($error_info['execute']['mysql']) && 69 ($error_info['execute']['mysql'] !== $tmp[1])) { 70 printf("[%03d] execute() - expecting MySQL Code '%s' got '%s'\n", 71 $offset, $error_info['execute']['mysql'], $tmp[0]); 72 return false; 73 } 74 75 return false; 76 } 77 78 } catch (PDOException $e) { 79 printf("[%03d] %s, [%s} %s\n", 80 $offset, $e->getMessage(), 81 $db->errorCode(), implode(' ', $db->errorInfo())); 82 return false; 83 } 84 85 return $stmt; 86 } 87 88 try { 89 $db->setAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY, 1); 90 if (1 != $db->getAttribute(PDO::MYSQL_ATTR_DIRECT_QUERY)) 91 printf("[002] Unable to switch to emulated prepared statements, test will fail\n"); 92 93 try { 94 prepex(3, $db, '', [], ['execute' => ['sqlstate' => '42000']]); 95 } catch (\ValueError $e) { 96 echo $e->getMessage(), \PHP_EOL; 97 } 98 99 // lets be fair and do the most simple SELECT first 100 $stmt = prepex(4, $db, 'SELECT 1 as "one"'); 101 var_dump($stmt->fetch(PDO::FETCH_ASSOC)); 102 103 prepex(5, $db, 'DROP TABLE IF EXISTS test'); 104 prepex(6, $db, sprintf('CREATE TABLE test(id INT, label CHAR(255)) ENGINE=%s', PDO_MYSQL_TEST_ENGINE)); 105 prepex(7, $db, "INSERT INTO test(id, label) VALUES(1, ':placeholder')"); 106 $stmt = prepex(8, $db, 'SELECT label FROM test'); 107 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 108 109 prepex(9, $db, 'DELETE FROM test'); 110 prepex(10, $db, "INSERT INTO test(id, label) VALUES(1, ':placeholder')", 111 array(':placeholder' => 'first row')); 112 $stmt = prepex(11, $db, 'SELECT label FROM test'); 113 114 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 115 prepex(12, $db, 'DELETE FROM test'); 116 prepex(13, $db, 'INSERT INTO test(id, label) VALUES(1, :placeholder)', 117 array(':placeholder' => 'first row')); 118 prepex(14, $db, 'INSERT INTO test(id, label) VALUES(2, :placeholder)', 119 array(':placeholder' => 'second row')); 120 $stmt = prepex(15, $db, 'SELECT label FROM test'); 121 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 122 123 // Is PDO fun? 124 prepex(16, $db, 'SELECT label FROM test WHERE :placeholder > 1', 125 array(':placeholder' => 'id')); 126 prepex(17, $db, 'SELECT :placeholder FROM test WHERE id > 1', 127 array(':placeholder' => 'id')); 128 prepex(18, $db, 'SELECT :placeholder FROM test WHERE :placeholder > :placeholder', 129 array(':placeholder' => 'test')); 130 131 for ($num_params = 2; $num_params < 100; $num_params++) { 132 $params = array(':placeholder' => 'a'); 133 for ($i = 1; $i < $num_params; $i++) { 134 $params[str_repeat('a', $i)] = 'some data'; 135 } 136 prepex(19, $db, 'SELECT id, label FROM test WHERE label > :placeholder', 137 $params, array('execute' => array('sqlstate' => 'HY093'))); 138 } 139 140 prepex(20, $db, 'DELETE FROM test'); 141 prepex(21, $db, 'INSERT INTO test(id, label) VALUES (1, :placeholder), (2, :placeholder)', 142 array(':placeholder' => 'row')); 143 $stmt = prepex(22, $db, 'SELECT id, label FROM test'); 144 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 145 146 $stmt = prepex(23, $db, 'SELECT id, label FROM test WHERE :placeholder IS NOT NULL', 147 array(':placeholder' => 1)); 148 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2) 149 printf("[024] '1' IS NOT NULL evaluates to true, expecting two rows, got %d rows\n", $tmp); 150 151 $stmt = prepex(25, $db, 'SELECT id, label FROM test WHERE :placeholder IS NULL', 152 array(':placeholder' => 1)); 153 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0) 154 printf("[026] '1' IS NOT NULL evaluates to true, expecting zero rows, got %d rows\n", $tmp); 155 156 prepex(27, $db, 'DROP TABLE IF EXISTS test'); 157 prepex(28, $db, 'CREATE TABLE test(id INT, label CHAR(255)) ENGINE=MyISAM'); 158 if (is_object(prepex(29, $db, 'CREATE FULLTEXT INDEX idx1 ON test(label)'))) { 159 prepex(30, $db, 'INSERT INTO test(id, label) VALUES (1, :placeholder)', 160 array(':placeholder' => 'MySQL is the best database in the world!')); 161 prepex(31, $db, 'INSERT INTO test(id, label) VALUES (1, :placeholder)', 162 array(':placeholder' => 'If I have the freedom to choose, I would always go again for the MySQL Server')); 163 $stmt = prepex(32, $db, 'SELECT id, label FROM test WHERE MATCH label AGAINST (:placeholder)', 164 array(':placeholder' => 'mysql')); 165 /* 166 Lets ignore this 167 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2) 168 printf("[033] Expecting two rows, got %d rows\n", $tmp); 169 */ 170 } 171 prepex(34, $db, 'DELETE FROM test'); 172 prepex(35, $db, 'INSERT INTO test(id, label) VALUES (1, :placeholder), (2, :placeholder)', 173 array(':placeholder' => 'row')); 174/* 175 $stmt = prepex(36, $db, 'SELECT id, label FROM "test WHERE MATCH label AGAINST (:placeholder)', 176 array(':placeholder' => 'row'), 177 array('execute' => array('sqlstate' => '42000', 'mysql' => 1064))); 178*/ 179 $stmt = prepex(37, $db, 'SELECT id, label FROM \'test WHERE MATCH label AGAINST (:placeholder)', 180 array(':placeholder' => 'row'), 181 array('execute' => array('sqlstate' => '42000', 'mysql' => 1064))); 182 183 $stmt = prepex(38, $db, 'SELECT id, label AS "label" FROM test WHERE label = :placeholder', 184 array(':placeholder' => 'row')); 185 186 $sql = sprintf("SELECT id, label FROM test WHERE (label LIKE %s) AND (id = :placeholder)", 187 $db->quote('%ro%')); 188 $stmt = prepex(39, $db, $sql, array('placeholder' => -1)); 189 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0) 190 printf("[040] Expecting zero rows, got %d rows\n", $tmp); 191 192 193 $sql = sprintf("SELECT id, label FROM test WHERE (id = :placeholder) OR (label LIKE %s)", 194 $db->quote('%ro%')); 195 $stmt = prepex(41, $db, $sql, array('placeholder' => 1)); 196 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2) 197 printf("[042] Expecting two rows, got %d rows\n", $tmp); 198 199 $sql = "SELECT id, label FROM test WHERE id = :placeholder AND label = (SELECT label AS 'SELECT' FROM test WHERE id = :placeholder)"; 200 $stmt = prepex(43, $db, $sql, array('placeholder' => 1)); 201 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 1) 202 printf("[044] Expecting onw row, got %d rows\n", $tmp); 203 204 // and now, the same with anonymous placeholders... 205 prepex(45, $db, 'DROP TABLE IF EXISTS test'); 206 prepex(46, $db, sprintf('CREATE TABLE test(id INT, label CHAR(255)) ENGINE=%s', PDO_MYSQL_TEST_ENGINE)); 207 prepex(47, $db, "INSERT INTO test(id, label) VALUES(1, '?')"); 208 $stmt = prepex(48, $db, 'SELECT label FROM test'); 209 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 210 211 prepex(49, $db, 'DELETE FROM test'); 212 prepex(50, $db, "INSERT INTO test(id, label) VALUES(1, '?')", 213 array('first row')); 214 $stmt = prepex(51, $db, 'SELECT label FROM test'); 215 216 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 217 prepex(52, $db, 'DELETE FROM test'); 218 prepex(53, $db, 'INSERT INTO test(id, label) VALUES(1, ?)', 219 array('first row')); 220 prepex(54, $db, 'INSERT INTO test(id, label) VALUES(2, ?)', 221 array('second row')); 222 $stmt = prepex(55, $db, 'SELECT label FROM test'); 223 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 224 225 // Is PDO fun? 226 prepex(56, $db, 'SELECT label FROM test WHERE ? > 1', 227 array('id')); 228 prepex(57, $db, 'SELECT ? FROM test WHERE id > 1', 229 array('id')); 230 prepex(58, $db, 'SELECT ? FROM test WHERE ? > ?', 231 array('test'), array('execute' => array('sqlstate' => 'HY093'))); 232 233 prepex(59, $db, 'SELECT ? FROM test WHERE ? > ?', 234 array('id', 'label', 'value')); 235 236 for ($num_params = 2; $num_params < 100; $num_params++) { 237 $params = array('a'); 238 for ($i = 1; $i < $num_params; $i++) { 239 $params[] = 'some data'; 240 } 241 prepex(60, $db, 'SELECT id, label FROM test WHERE label > ?', 242 $params, array('execute' => array('sqlstate' => 'HY093'))); 243 } 244 245 prepex(61, $db, 'DELETE FROM test'); 246 prepex(62, $db, 'INSERT INTO test(id, label) VALUES (1, ?), (2, ?)', 247 array('row', 'row')); 248 $stmt = prepex(63, $db, 'SELECT id, label FROM test'); 249 var_dump($stmt->fetchAll(PDO::FETCH_ASSOC)); 250 251 $stmt = prepex(64, $db, 'SELECT id, label FROM test WHERE ? IS NOT NULL', 252 array(1)); 253 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2) 254 printf("[065] '1' IS NOT NULL evaluates to true, expecting two rows, got %d rows\n", $tmp); 255 256 $stmt = prepex(66, $db, 'SELECT id, label FROM test WHERE ? IS NULL', 257 array(1)); 258 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0) 259 printf("[067] '1' IS NOT NULL evaluates to true, expecting zero rows, got %d rows\n", $tmp); 260 261 prepex(68, $db, 'DROP TABLE IF EXISTS test'); 262 prepex(69, $db, 'CREATE TABLE test(id INT, label CHAR(255)) ENGINE=MyISAM'); 263 if (is_object(prepex(70, $db, 'CREATE FULLTEXT INDEX idx1 ON test(label)'))) { 264 prepex(71, $db, 'INSERT INTO test(id, label) VALUES (1, ?)', 265 array('MySQL is the best database in the world!')); 266 prepex(72, $db, 'INSERT INTO test(id, label) VALUES (1, ?)', 267 array('If I have the freedom to choose, I would always go again for the MySQL Server')); 268 $stmt = prepex(73, $db, 'SELECT id, label FROM test WHERE MATCH label AGAINST (?)', 269 array('mysql')); 270 /* 271 Lets ignore that 272 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2) 273 printf("[074] Expecting two rows, got %d rows\n", $tmp); 274 */ 275 } 276 277 prepex(74, $db, 'DELETE FROM test'); 278 prepex(75, $db, 'INSERT INTO test(id, label) VALUES (1, ?), (2, ?)', 279 array('row', 'row')); 280 281 $stmt = prepex(76, $db, 'SELECT id, label FROM "test WHERE MATCH label AGAINST (?)', 282 array('row'), 283 array('execute' => array('sqlstate' => '42000', 'mysql' => 1064))); 284 285 /* 286 TODO enable after fix 287 $stmt = prepex(37, $db, 'SELECT id, label FROM \'test WHERE MATCH label AGAINST (:placeholder)', 288 array(':placeholder' => 'row'), 289 array('execute' => array('sqlstate' => '42000', 'mysql' => 1064))); 290 */ 291 292 $stmt = prepex(78, $db, 'SELECT id, label AS "label" FROM test WHERE label = ?', 293 array('row')); 294 295 $sql = sprintf("SELECT id, label FROM test WHERE (label LIKE %s) AND (id = ?)", 296 $db->quote('%ro%')); 297 $stmt = prepex(79, $db, $sql, array(-1)); 298 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0) 299 printf("[080] Expecting zero rows, got %d rows\n", $tmp); 300 301 302 $sql = sprintf("SELECT id, label FROM test WHERE (id = ?) OR (label LIKE %s)", 303 $db->quote('%ro%')); 304 $stmt = prepex(81, $db, $sql, array(1)); 305 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 2) 306 printf("[082] Expecting two rows, got %d rows\n", $tmp); 307 308 $sql = "SELECT id, label FROM test WHERE id = ? AND label = (SELECT label AS 'SELECT' FROM test WHERE id = ?)"; 309 $stmt = prepex(83, $db, $sql, array(1, 1)); 310 if (count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 1) 311 printf("[084] Expecting one row, got %d rows\n", $tmp); 312 313 $sql = "SELECT id, label FROM test WHERE id = :placeholder AND label = (SELECT label AS 'SELECT' FROM test WHERE id = ?)"; 314 $stmt = prepex(85, $db, $sql, array(1, 1), array('execute' => array('sqlstate' => 'HY093'))); 315 if (is_object($stmt) && count(($tmp = $stmt->fetchAll(PDO::FETCH_ASSOC))) != 0) 316 printf("[086] Expecting no rows, got %d rows\n", $tmp); 317 318 } catch (PDOException $e) { 319 printf("[001] %s [%s] %s\n", 320 $e->getMessage(), $db->errorCode(), implode(' ', $db->errorInfo())); 321 } 322 323 print "done!"; 324?> 325--CLEAN-- 326<?php 327require __DIR__ . '/mysql_pdo_test.inc'; 328$db = MySQLPDOTest::factory(); 329$db->exec('DROP TABLE IF EXISTS test'); 330?> 331--EXPECTF-- 332PDO::prepare(): Argument #1 ($query) cannot be empty 333array(1) { 334 ["one"]=> 335 string(1) "1" 336} 337array(1) { 338 [0]=> 339 array(1) { 340 ["label"]=> 341 string(12) ":placeholder" 342 } 343} 344 345Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in %s on line %d 346array(0) { 347} 348array(2) { 349 [0]=> 350 array(1) { 351 ["label"]=> 352 string(9) "first row" 353 } 354 [1]=> 355 array(1) { 356 ["label"]=> 357 string(10) "second row" 358 } 359} 360array(2) { 361 [0]=> 362 array(2) { 363 ["id"]=> 364 string(1) "1" 365 ["label"]=> 366 string(3) "row" 367 } 368 [1]=> 369 array(2) { 370 ["id"]=> 371 string(1) "2" 372 ["label"]=> 373 string(3) "row" 374 } 375} 376array(1) { 377 [0]=> 378 array(1) { 379 ["label"]=> 380 string(1) "?" 381 } 382} 383 384Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in %s on line %d 385array(0) { 386} 387array(2) { 388 [0]=> 389 array(1) { 390 ["label"]=> 391 string(9) "first row" 392 } 393 [1]=> 394 array(1) { 395 ["label"]=> 396 string(10) "second row" 397 } 398} 399array(2) { 400 [0]=> 401 array(2) { 402 ["id"]=> 403 string(1) "1" 404 ["label"]=> 405 string(3) "row" 406 } 407 [1]=> 408 array(2) { 409 ["id"]=> 410 string(1) "2" 411 ["label"]=> 412 string(3) "row" 413 } 414} 415done! 416
