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