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