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