1--TEST--
2MySQL PDO->prepare(), emulated 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) {
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 dirname(__FILE__) . '/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--EXPECTF--
331array(1) {
332  [%u|b%"one"]=>
333  %unicode|string%(1) "1"
334}
335array(1) {
336  [0]=>
337  array(1) {
338    [%u|b%"label"]=>
339    %unicode|string%(12) ":placeholder"
340  }
341}
342array(1) {
343  [0]=>
344  array(1) {
345    [%u|b%"label"]=>
346    %unicode|string%(12) ":placeholder"
347  }
348}
349array(2) {
350  [0]=>
351  array(1) {
352    [%u|b%"label"]=>
353    %unicode|string%(9) "first row"
354  }
355  [1]=>
356  array(1) {
357    [%u|b%"label"]=>
358    %unicode|string%(10) "second row"
359  }
360}
361array(2) {
362  [0]=>
363  array(2) {
364    [%u|b%"id"]=>
365    %unicode|string%(1) "1"
366    [%u|b%"label"]=>
367    %unicode|string%(3) "row"
368  }
369  [1]=>
370  array(2) {
371    [%u|b%"id"]=>
372    %unicode|string%(1) "2"
373    [%u|b%"label"]=>
374    %unicode|string%(3) "row"
375  }
376}
377array(1) {
378  [0]=>
379  array(1) {
380    [%u|b%"label"]=>
381    %unicode|string%(1) "?"
382  }
383}
384array(1) {
385  [0]=>
386  array(1) {
387    [%u|b%"label"]=>
388    %unicode|string%(1) "?"
389  }
390}
391array(2) {
392  [0]=>
393  array(1) {
394    [%u|b%"label"]=>
395    %unicode|string%(9) "first row"
396  }
397  [1]=>
398  array(1) {
399    [%u|b%"label"]=>
400    %unicode|string%(10) "second row"
401  }
402}
403array(2) {
404  [0]=>
405  array(2) {
406    [%u|b%"id"]=>
407    %unicode|string%(1) "1"
408    [%u|b%"label"]=>
409    %unicode|string%(3) "row"
410  }
411  [1]=>
412  array(2) {
413    [%u|b%"id"]=>
414    %unicode|string%(1) "2"
415    [%u|b%"label"]=>
416    %unicode|string%(3) "row"
417  }
418}
419done!
420