1--TEST--
2MySQL PDO->prepare(), native PS
3--SKIPIF--
4<?php
5require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'skipif.inc');
6require_once(dirname(__FILE__) . DIRECTORY_SEPARATOR . 'mysql_pdo_test.inc');
7MySQLPDOTest::skip();
8$db = MySQLPDOTest::factory();
9?>
10--FILE--
11<?php
12	require_once(dirname(__FILE__) . 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');
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 dirname(__FILE__) . '/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