php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #74921 Wrong table name in PDO::getColumnMeta when using ATTR_EMULATE_PREPARES = false
Submitted: 2017-07-13 20:01 UTC Modified: -
From: asj dot ricardo at gmail dot com Assigned:
Status: Open Package: PDO MySQL
PHP Version: 7.1.7 OS: ANY
Private report: No CVE-ID: None
 [2017-07-13 20:01 UTC] asj dot ricardo at gmail dot com
Description:
------------
If we set ATTR_EMULATE_PREPARES = false, and create a alias to a view, the name set at getColumnMeta() to the table view is not the view alias, but the original view name

Test script:
---------------
<?php
$host = 'localhost';
$db = 'dbtest';
$user = 'user';
$pass = 'pass';
$pdo = new PDO('mysql:host='.$host.';dbname='.$db,$user,$pass);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$pdo->exec('DROP TABLE IF EXISTS foo');
$pdo->exec('DROP TABLE IF EXISTS bar');
$pdo->exec('DROP VIEW IF EXISTS bar_foo');

$pdo->exec('CREATE TABLE `bar` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `foo` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
)');

$pdo->exec('CREATE TABLE `foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `text` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
)');

$pdo->exec('CREATE VIEW `bar_foo`
AS SELECT
   `bar`.`id` AS `id`,
   `bar`.`foo` AS `foo`,
   `foo`.`id` AS `fooId`,
   `foo`.`text` AS `fooText`
FROM (`bar` join `foo` on((`bar`.`foo` = `foo`.`id`)))');

$values = Array('test1','test2','test3');
foreach($values as $v){
	$stmt = $pdo->prepare("INSERT INTO foo (`text`) VALUES (:y)");
	$stmt->bindParam(':y', $v);
	$stmt->execute();
}

$values = Array(1,2);
foreach($values as $v){
	$stmt = $pdo->prepare("INSERT INTO bar (foo) VALUES (:foo)");
	$stmt->bindParam(':foo', $v);
	$stmt->execute();
}

$result = $pdo->query('SELECT * FROM bar as bar2 join foo as foo2 on bar2.foo = foo2.id');
for($i=0;$i<$result->columnCount();$i++){
	$meta = $result->getColumnMeta($i);
	echo $meta['table'].':'.$meta['name']."\n";
}
echo "\n";
$result = $pdo->query('SELECT * FROM bar as bar2 join bar_foo as bar_foo2 on bar_foo2.id = bar2.id');
for($i=0;$i<$result->columnCount();$i++){
	$meta = $result->getColumnMeta($i);
	echo $meta['table'].':'.$meta['name']."\n";
}


Expected result:
----------------
bar2:id
bar2:foo
foo2:id
foo2:text

bar2:id
bar2:foo
bar_foo2:id
bar_foo2:foo
bar_foo2:fooId
bar_foo2:fooText

Actual result:
--------------
bar2:id
bar2:foo
foo2:id
foo2:text

bar2:id
bar2:foo
bar_foo:id
bar_foo:foo
bar_foo:fooId
bar_foo:fooText

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-04-08 21:00 UTC] dharman@php.net
Interesting problem. I can reproduce it with MySQL 8.0.21, but not with MariaDB. 

I checked the code for PDO, PDO_MySQL and mysqlnd and I see no issues there. Using text protocol the metadata is provided correctly. Using binary protocol the metadata is provided twice, once during prepare and again during execute. The metadata provided during prepare is correct. Once you reach the execute phase the metadata is wrong. 

It looks to me like this is not a bug with PHP but with MySQL itself. It might be worthwhile to report this bug to Oracle and see what they say.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Nov 23 08:01:28 2024 UTC