Is there a way to execute a prepared DESCRIBE query?

Question

The problem I'm having is this: when I attempt to prepare a query that's comprised of DESCRIBE table_name or SHOW COLUMNS FROM table_name, I get an error that states the following:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''users'' at line 1' in /Applications/MAMP/htdocs/test.php:28 Stack trace: #0 /Applications/MAMP/htdocs/test.php(28): PDOStatement->execute() #1 /Applications/MAMP/htdocs/test.php(6): show_columns_yes_prepared('users') #2 {main} thrown in /Applications/MAMP/htdocs/test.php on line 28

The function show_columns_not_prepared() returns the following, as expected/hoped:

Array
(
    [0] => uid
    [1] => name
    [2] => pass
    [3] => mail
    [4] => mode
    [5] => sort
    [6] => threshold
    [7] => theme
    [8] => signature
    [9] => signature_format
    [10] => created
    [11] => access
    [12] => login
    [13] => status
    [14] => timezone
    [15] => language
    [16] => picture
    [17] => init
    [18] => data
    [19] => timezone_name
    [20] => timezone_id
)

... while the above error occurs as a result of the function show_columns_yes_prepared(). Both can be found below along with the rest of the source code (verbatim) used in the file test.php which lives in my localhost's root directory.

Is preparing a DESCRIBE SQL query an impossible task? Or am I doing something wrong? Neither? Both?

Database / table creation:

CREATE DATABASE `testdatabaseforpdo` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `testdatabaseforpdo`;

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL DEFAULT '',
  `pass` varchar(32) NOT NULL DEFAULT '',
  `mail` varchar(64) DEFAULT '',
  `mode` tinyint(4) NOT NULL DEFAULT '0',
  `sort` tinyint(4) DEFAULT '0',
  `threshold` tinyint(4) DEFAULT '0',
  `theme` varchar(255) NOT NULL DEFAULT '',
  `signature` varchar(255) NOT NULL DEFAULT '',
  `signature_format` smallint(6) NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL DEFAULT '0',
  `access` int(11) NOT NULL DEFAULT '0',
  `login` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `timezone` varchar(8) DEFAULT NULL,
  `language` varchar(12) NOT NULL DEFAULT '',
  `picture` varchar(255) NOT NULL DEFAULT '',
  `init` varchar(64) DEFAULT '',
  `data` longtext,
  `timezone_name` varchar(50) NOT NULL DEFAULT '',
  `timezone_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`),
  KEY `picture` (`picture`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Source code (adjust the connection info inside getConnection() as necessary):

<?php

$table = 'users';

print_r(show_columns_not_prepared($table)); 
print_r(show_columns_yes_prepared($table)); 

function show_columns_not_prepared($table) {
  $db = getConnection();
  // Not prepared, not safe...
  $sql = "DESCRIBE $table";
  $stmt = $db->query($sql);
  $out = array();
  while ($res = $stmt->fetchAll(PDO::FETCH_ASSOC)) {
    for ($i = 0; $i < count($res); $i++) {
      $out[] = $res[$i]['Field'];
    }
  }
  return $out;
}

function show_columns_yes_prepared($table) {
  $db = getConnection();
  // Ready to be prepared...
  $sql = "DESCRIBE :table";
  $stmt = $db->prepare($sql);
  $stmt->bindValue(':table', $table, PDO::PARAM_STR);
  $stmt->execute();
  $out = array();
  while($res = $stmt->fetch(PDO::FETCH_ASSOC)) {
    for ($i = 0; $i < count($res); $i++) {
      $out[] = $res[$i]['Field'];
    }
  }
  return $out;
}

function getConnection() {
  $dbhost="localhost";
  $dbuser="someuser";
  $dbpass="somepass";
  $dbname="testdatabaseforpdo";
  $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $dbh->exec("set names utf8");
  return $dbh;
}

Why would you even need to run that query? Just query from the `information_schema`. — Ja͢ck, Oct 23 '13 at 04:17
You cannot bind parameters to database identifiers (table / column names) as this would be equivalent to executing `DESCRIBE 'users'`. Use the `information_schema` as Jack said in order to do so or inject the identifier name into the query directly. — Phil, Oct 23 '13 at 04:20
Some developers don't actually know that, hence the mention. — Ja͢ck, Oct 23 '13 at 04:22
possible duplicate of [Can PHP PDO Statements accept the table name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) — Phil, Oct 23 '13 at 04:25

score 4 · Accepted Answer · edited May 23 '17 at 11:50

With safeMysql it's a chicken:

function show_columns_yes_prepared($table)
{
    $db = getConnection(); // I HOPE it's SINGLETON. Yikes, it is not
    return $db->getCol( "DESCRIBE ?n", $table);
}

with ugly PDO it could be a problem, so, you'd better stick to information_schema query as Jack suggested:

function show_columns_yes_prepared($pdo, $table)
{
    $sql = "SELECT column_name FROM information_schema.columns WHERE table_name =?";
    $stm = $pdo->prepare($sql);
    $stm->execute([$table]);
    return $stm->fetchAll(PDO::FETCH_COLUMN);
}

But your main problem is not that useless query

but getConnection(); function which will literally kill your db server in a more or less production environment.

Because you should connect only ONCE per application and use that single connection throughout the script, instead of connecting every time you need to query a database.

Is there a way to execute a prepared DESCRIBE query?

1 Answers1

But your main problem is not that useless query