Passed-in parameter used in GROUP BY still returns 1 row?

Question

I stumbled on this behavior and now I'm curious what's actually going on. If I try to bind a parameter to a column name in the GROUP BY clause, data is actually returned, but only one row. I know you're not allowed to use table or column names as parameters, but I'm wondering what's causing this to happen behind the scenes. This was buried deep in my (amateur) code and it was hard to troubleshoot, because it didn't throw an error, and it actually returned data. I would love more insight into this!

Sample table:

| artist          | album          | label       |
|-----------------|----------------|-------------|
| John Coltrane   | A Love Supreme | MCA Records |
| John McLaughlin | Extrapolation  | Marmalade   |
| John Coltrane   | A Love Supreme | Impulse!    |
| John McLaughlin | Extrapolation  | Polydor     |

Example code:

$field = 'artist';
$artist = '%john%';
$sql = 'SELECT artist, album
    FROM record_collection
    WHERE artist LIKE :artist
    GROUP BY :field';
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':artist', $artist);
$stmt->bindParam(':field', $field);
$stmt->execute();
echo 'Row count: '. $stmt->rowCount();

This returns: "Row count: 1"

I noticed:

Using "GROUP BY artist" instead, and commenting out the bindParam line for :field, I get the expected "Row count: 2". This is the right way to do it.
Using the parameter :field in the WHERE clause as a column name (e.g., "WHERE :field LIKE :artist") gives you "Row count: 0".

So what I'm wondering is:

What is SQL/PDO doing beind the scenes that's causing 1 row to get returned?
If parameters in the GROUP BY aren't supported, why not return nothing, or better yet, throw an error? I assume not, but is there any legitimate use for passing a parameter into GROUP BY?

Try this plain SQL: `select ... group by 'whatever'` (note the quotes to make it a string literal) — Mat, Aug 14 '16 at 15:33

score 2 · Accepted Answer · answered Aug 14 '16 at 15:41

2

When you pass :field in, then you are passing in a string value. So, the result is group by <constant>, which returns one row.

You cannot parameterize the name of a column, so you have to put it directly into the SQL statement:

$sql = 'SELECT artist, album
        FROM record_collection
        WHERE artist LIKE :artist
        GROUP BY '.$field'

:artist is fine because it is a value, not an identifier in SQL.

answered Aug 14 '16 at 15:41

Gordon Linoff

1,242,037
58
646
786

Thank you--based on your answer I tested and confirmed the same behavior to be true for GROUP BY 'someRandomString' and GROUP BY NULL. The latter led me to [this question](http://stackoverflow.com/questions/7404578/what-does-mysql-group-by-null-do), which is basically the same thing. I also checked SQL Server, which _does_ throw an error for grouping by something not in the select clause, as I expect mysql would do if ONLY_FULL_GROUP_BY were enabled in my environment, based on the [documentation](http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) – LinkDinkin Aug 15 '16 at 21:29
But if `$field` come from user input, then wont it will be security issue like SQL injection?? Any suggestion to do it parameter? – santoshe61 Oct 01 '19 at 04:38

Passed-in parameter used in GROUP BY still returns 1 row?

1 Answers1