Can I use name of the column as parameter in PDO?

Question

I'm trying to execute this:

$colparam = 'abcd'; $stmt = $db->prepare("SELECT DISTINCT ? AS kol FROM katalog ORDER BY kol ASC"); $stmt->execute(array($colparam)); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

and it's not working (no errors, just empty array as result).

Instead this works fine:

$stmt = $db->prepare("SELECT DISTINCT abcd AS kol FROM katalog ORDER BY kol ASC"); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

So is there any catch with the use of parameter as a name of the column in PDO?

Is there any good reason why you would want to do this? Accessing columns dynamically usually indicates bad database design. Could you go more into detail in what you want to achieve? — thpl
– thpl, Commented Apr 23, 2014 at 20:49
I need a menu with dropdowns for every column so I was thinking to make single function to call for every dropdown. Because the position of dropdowns changes, I can't make it for all dropdowns at once. — mrserge
– mrserge, Commented Apr 23, 2014 at 20:54
and you store it like that? menus(id, choice1, choice2, choice3, ...) — thpl
– thpl, Commented Apr 23, 2014 at 20:56
As I guessed your database is not normalized properly (en.wikipedia.org/wiki/Database_normalization) You might want to change the structure a bit so that you can access your cols statically. gist.github.com/spazecookie/11232321 — thpl
– thpl, Commented Apr 23, 2014 at 21:06

Mike Brant · Accepted Answer · 2014-04-23 21:00:12Z

No, you can't use parameter replacements for any database objects (tables, columns, etc.) in MySQL.

When you think about what a prepared statement actually is, this makes complete sense. As how can MySQL prepare a query execution plan when it does not even know the database objects involved.

I certainly wish that more documentation would actually cover what a prepared statement actually does (beyond it's obvious use for parametrization).

Here is link to MySQL prepared statement documentation for more reading:

https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html

is there any workaround to pass the value as a name of the column in PDO or it's just disabled by default?
@mrserge. No there isn't. PDO treats parameters as values and will quote them. Is the column name provided from user input?
You can manually build you statement string like: $db->prepare("SELECT " . $col . " FROM ...") but this value would not be parametrized and you must then manually escape the value to prevent SQL injection unless you know the string is safe.

Collectives™ on Stack Overflow

Can I use name of the column as parameter in PDO?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related