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