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