1--TEST--
2Prepared Statements and SELECT UNION
3--EXTENSIONS--
4mysqli
5--SKIPIF--
6<?php
7require_once('skipifconnectfailure.inc');
8?>
9--FILE--
10<?php
11    require_once("connect.inc");
12    require_once("table.inc");
13
14    // Regular (non-prepared) queries
15    print "Using CAST('somestring' AS CHAR)...\n";
16    if (!($res = $link->query("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST('two' AS CHAR)")))
17        printf("[001] [%d] %s\n", $link->errno, $link->error);
18
19    $data = array();
20    while ($row = $res->fetch_assoc()) {
21        $data[] = $row['column1'];
22        var_dump($row['column1']);
23    }
24    $res->free();
25
26    // Prepared Statements
27    if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST('two' AS CHAR)")))
28        printf("[002] [%d] %s\n", $link->errno, $link->error);
29
30    $column1 = null;
31    if (!$stmt->execute() || !$stmt->bind_result($column1))
32        printf("[003] [%d] %s\n", $stmt->errno, $stmt->error);
33
34    $index = 0;
35    while ($stmt->fetch()) {
36        if ($data[$index] != $column1) {
37            printf("[004] Row %d, expecting %s/%s got %s/%s\n",
38                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
39        }
40        $index++;
41    }
42    $stmt->close();
43
44    if ($IS_MYSQLND) {
45        /*
46        Advantage mysqlnd -
47        The metadata mysqlnd has available after prepare is better than
48        the one made available by the MySQL Client Library (libmysql).
49        "libmysql" will give wrong results and that is OK -
50        http://bugs.mysql.com/bug.php?id=47483
51        */
52        if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST('two' AS CHAR)")))
53            printf("[005] [%d] %s\n", $link->errno, $link->error);
54
55        $column1 = null;
56        /* Note: bind_result before execute */
57        if (!$stmt->bind_result($column1) || !$stmt->execute())
58            printf("[006] [%d] %s\n", $stmt->errno, $stmt->error);
59
60        $index = 0;
61        while ($stmt->fetch()) {
62            if ($data[$index] != $column1) {
63                printf("[007] Row %d, expecting %s/%s got %s/%s\n",
64                    $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
65            }
66            $index++;
67        }
68        $stmt->close();
69    }
70
71    // Regular (non-prepared) queries
72    print "Mixing CAST('somestring'AS CHAR), integer and CAST(integer AS CHAR)...\n";
73    if (!($res = $link->query("SELECT 1 AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST(2 AS CHAR)")))
74        printf("[008] [%d] %s\n", $link->errno, $link->error);
75
76    $data = array();
77    while ($row = $res->fetch_assoc()) {
78        $data[] = $row['column1'];
79    }
80    $res->free();
81
82    // Prepared Statements
83    if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST(2 AS CHAR)")))
84        printf("[009] [%d] %s\n", $link->errno, $link->error);
85
86    $column1 = null;
87    if (!$stmt->execute() || !$stmt->bind_result($column1))
88        printf("[010] [%d] %s\n", $stmt->errno, $stmt->error);
89
90    $index = 0;
91    while ($stmt->fetch()) {
92        if ($data[$index] != $column1) {
93            printf("[011] Row %d, expecting %s/%s got %s/%s\n",
94                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
95        }
96        var_dump($column1);
97        $index++;
98    }
99    $stmt->close();
100
101    if ($IS_MYSQLND) {
102        /* Advantage mysqlnd - see above... */
103        if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT CAST('three' AS CHAR) UNION SELECT CAST(2 AS CHAR)")))
104            printf("[012] [%d] %s\n", $link->errno, $link->error);
105
106        $column1 = null;
107        if (!$stmt->bind_result($column1) || !$stmt->execute())
108            printf("[013] [%d] %s\n", $stmt->errno, $stmt->error);
109
110        $index = 0;
111        while ($stmt->fetch()) {
112            if ($data[$index] != $column1) {
113                printf("[014] Row %d, expecting %s/%s got %s/%s\n",
114                    $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
115            }
116            $index++;
117        }
118        $stmt->close();
119    }
120
121    print "Using integer only...\n";
122    if (!($res = $link->query("SELECT 1 AS column1 UNION SELECT 303 UNION SELECT 2")))
123        printf("[015] [%d] %s\n", $link->errno, $link->error);
124
125    $data = array();
126    while ($row = $res->fetch_assoc()) {
127        $data[] = $row['column1'];
128    }
129    $res->free();
130
131    // Prepared Statements
132    if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT 303 UNION SELECT 2")))
133        printf("[016] [%d] %s\n", $link->errno, $link->error);
134
135    $column1 = null;
136    if (!$stmt->execute() || !$stmt->bind_result($column1))
137        printf("[017] [%d] %s\n", $stmt->errno, $stmt->error);
138
139    $index = 0;
140    while ($stmt->fetch()) {
141        if ($data[$index] != $column1) {
142            printf("[018] Row %d, expecting %s/%s got %s/%s\n",
143                $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
144        }
145        var_dump($column1);
146        $index++;
147    }
148    $stmt->close();
149
150    if ($IS_MYSQLND) {
151        /* Advantage mysqlnd - see above */
152        if (!($stmt = $link->prepare("SELECT 1 AS column1 UNION SELECT 303 UNION SELECT 2")))
153            printf("[019] [%d] %s\n", $link->errno, $link->error);
154
155        $column1 = null;
156        if (!$stmt->bind_result($column1) || !$stmt->execute())
157            printf("[020] [%d] %s\n", $stmt->errno, $stmt->error);
158
159        $index = 0;
160        while ($stmt->fetch()) {
161            if ($data[$index] != $column1) {
162                printf("[021] Row %d, expecting %s/%s got %s/%s\n",
163                    $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
164            }
165            $index++;
166        }
167        $stmt->close();
168    }
169
170    print "Testing bind_param(), strings only...\n";
171    $two = 'two';
172    $three = 'three';
173    if (!($stmt = $link->prepare("SELECT 'one' AS column1 UNION SELECT ? UNION SELECT ?")))
174        printf("[022] [%d] %s\n", $stmt->errno, $stmt->error);
175
176    $column1 = null;
177    if (!$stmt->bind_param('ss', $three, $two) || !$stmt->execute() || !$stmt->bind_result($column1))
178        printf("[023] [%d] %s\n", $stmt->errno, $stmt->error);
179
180    $index = 0;
181    $data = array();
182    while ($stmt->fetch()) {
183        $data[$index++] = $column1;
184        var_dump($column1);
185    }
186    $stmt->close();
187
188    if ($IS_MYSQLND) {
189        /* Advantage mysqlnd - see above */
190        $two = 'two';
191        $three = 'three';
192        if (!($stmt = $link->prepare("SELECT 'one' AS column1 UNION SELECT ? UNION SELECT ?")))
193            printf("[024] [%d] %s\n", $stmt->errno, $stmt->error);
194
195        $column1 = null;
196        if (!$stmt->bind_param('ss', $three, $two) || !$stmt->bind_result($column1) || !$stmt->execute())
197            printf("[025] [%d] %s\n", $stmt->errno, $stmt->error);
198
199        $index = 0;
200        while ($stmt->fetch()) {
201            if ($data[$index] != $column1) {
202                printf("[26] Row %d, expecting %s/%s, got %s/%s\n",
203                    $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
204            }
205            $index++;
206        }
207        $stmt->close();
208    }
209
210    print "Testing bind_param(), strings only, with CAST AS CHAR...\n";
211    $two = 'two';
212    $three = 'three beers are more than enough';
213    if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST(? AS CHAR) UNION SELECT CAST(? AS CHAR)")))
214        printf("[027] [%d] %s\n", $stmt->errno, $stmt->error);
215
216    $column1 = null;
217    if (!$stmt->bind_param('ss', $three, $two) || !$stmt->execute() || !$stmt->bind_result($column1))
218        printf("[028] [%d] %s\n", $stmt->errno, $stmt->error);
219
220    $index = 0;
221    $data = array();
222    while ($stmt->fetch()) {
223        $data[$index++] = $column1;
224        var_dump($column1);
225    }
226    $stmt->close();
227
228    if ($IS_MYSQLND) {
229        /* Advantage mysqlnd - see above */
230        $two = 'two';
231        $three = 'three beers are more than enough';
232        if (!($stmt = $link->prepare("SELECT CAST('one' AS CHAR) AS column1 UNION SELECT CAST(? AS CHAR) UNION SELECT CAST(? AS CHAR)")))
233            printf("[029] [%d] %s\n", $stmt->errno, $stmt->error);
234
235        $column1 = null;
236        if (!$stmt->bind_param('ss', $three, $two) || !$stmt->bind_result($column1) || !$stmt->execute())
237            printf("[030] [%d] %s\n", $stmt->errno, $stmt->error);
238
239        $index = 0;
240        while ($stmt->fetch()) {
241            if ($data[$index] != $column1) {
242                printf("[31] Row %d, expecting %s/%s, got %s/%s\n",
243                    $index + 1, gettype($data[$index]), $data[$index], gettype($column1), $column1);
244            }
245            $index++;
246        }
247        $stmt->close();
248    }
249
250    $link->close();
251
252    print "done!";
253?>
254--EXPECT--
255Using CAST('somestring' AS CHAR)...
256string(3) "one"
257string(5) "three"
258string(3) "two"
259Mixing CAST('somestring'AS CHAR), integer and CAST(integer AS CHAR)...
260string(1) "1"
261string(5) "three"
262string(1) "2"
263Using integer only...
264int(1)
265int(303)
266int(2)
267Testing bind_param(), strings only...
268string(3) "one"
269string(5) "three"
270string(3) "two"
271Testing bind_param(), strings only, with CAST AS CHAR...
272string(3) "one"
273string(32) "three beers are more than enough"
274string(3) "two"
275done!
276