How to prefix / suffix column names in a MySQL query?

Question

I'm storing the user type (seller or buyer) in one of my session variables as $_SESSION['user_type'].

Some of my select / insert / update queries require the columns by the name of seller_id or buyer_id.

I want to know if there's a way by which I can add the _id suffix to $_SESSION['user_type'] in my queries.

For example: If I want to select all the columns from my order table where buyer_id is equal to 7, my query should look something like this:

SELECT *
FROM `order`
WHERE ( $_SESSION['user_type'] + "_id" ) = '7'

Note: I know I can use a variable and generate the corresponding column name, but was wondering whether this is possible without any extra variable.

doesn't that mean we can add the suffix in the query (since it's a string)? — Nikunj Madhogaria, Sep 09 '15 at 20:42
you want to **concatenate**. you do this with a period `.` not a plus `+` — CodeGodie, Sep 09 '15 at 20:43
Beside the main problem, if you're including user input, to avoid SQL injection hacks, you should consider using prepared statements. — Jonathan M, Sep 09 '15 at 20:52
@Dagon All my queries had `$_SESSION['user_type']` withing the string query like `SELECT * FROM order WHERE $_SESSION['user_id'] ) = '7'` without use of `.`. Didn't realise that I could have used them. — Nikunj Madhogaria, Sep 09 '15 at 20:55
ok -- this has nothing to do with mysql or sessions, its basic php strings. — , Sep 09 '15 at 20:57
@Dagon I understand that, but I always wonder how come queries directly read the variable values within the `" "` itself. I mean why don't they need separators? Shouldn't it be like `"SELECT * FROM order WHERE" . $_SESSION['user_id'] . "= '7'"` always? However it works without separators. — Nikunj Madhogaria, Sep 09 '15 at 21:01
I mean in general, while concatinating string with variables we separate them by `"`, however in queries we don't need to do that. That is, the variable name is a part of the string query itself. — Nikunj Madhogaria, Sep 09 '15 at 21:05
no, its no difference, its all php strings http://php.net/manual/en/language.types.string.php nothing to do with being used as a query — , Sep 09 '15 at 21:09
Technically (by string format), the query `"SELECT * FROM $table_name"` (say `$table_name = "seller"`) should be `"SELECT * FROM " . $table_name`. But the former works in case of php (which is not allowed in other languages). — Nikunj Madhogaria, Sep 09 '15 at 21:13
no both are valid php strings, any variable inside double quotes is evaluated by the server. — , Sep 09 '15 at 21:16
@theScorpion, I think the syntax you were looking for was: `"SELECT * FROM order WHERE {$_SESSION['user_id']} ) = '7'"`. Notice that `$_SESSION['user_id']` is wrapped in `{}`'s. — HPierce, Sep 09 '15 at 21:25
@Dagon I've seen that in php only like I said before. I wonder how can we just print the variable names (without allowing the server to process it). — Nikunj Madhogaria, Sep 09 '15 at 21:39
?!?!?!?!!? nope i give up read the manual for the love of Buddha. — , Sep 09 '15 at 21:40
@Dagon how would you echo the string "$dog" (not variable) without storing it another variable? I will for the love of Buddha. :) — Nikunj Madhogaria, Sep 09 '15 at 21:43
I meant echoing "$dog" when $dog = "dog". Saw the documentation -- it would be `echo "\$dog";` instead. See, I do love Buddha. :) (PS: Sorry for the trouble caused.) http://ideone.com/s4YHjQ — Nikunj Madhogaria, Sep 09 '15 at 21:59
Should have [read this](http://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php#answer-3446286). — Nikunj Madhogaria, Sep 09 '15 at 22:17
should of read the link to the manual i gave you an hour ago — , Sep 09 '15 at 22:45

score 1 · Accepted Answer · answered Sep 09 '15 at 20:41

1

Just concatenate query as string and use it then.

$query = "SELECT *
    FROM `order`
    WHERE " . $_SESSION['user_type'] . "_id = '7'";

But make sure that you'll not include anything from user input in such way.

answered Sep 09 '15 at 20:41

Elon Than

9,603
4
27
37

score 0 · Answer 2 · answered Jul 26 '19 at 10:09

If i see old post with possible SQL-Injections i have post ...

If you have to use the value of a variable in a query then

use a whitelist - Please!

Example:

// even if its a session stored on your server and the values set by your self ...
// do NOT trust it and handle it as any other value from any other varible.
// example input:
// $_SESSION['user_type'] = 'seller';

// define the map of valid session user types and column relations (hardcoded)
$columnMap = [
    // user_type => column name
    'buyer'  => 'buyer_id',
    'seller' => 'seller_id',
];

// check if you got a valid session type (if you find it on the white list)
if (!isset($columnMap[$_SESSION['user_type']])) {
    throw new \InvalidArgumentException("Invalid session[user_type].");
}
// use the value from the white list
$columnName = $columnMap[$_SESSION['user_type']];

// create proper query
$query = "SELECT * FROM `order` WHERE `{$columnName}` = :id;";

// Note:
// "SELECT * FROM `order` WHERE
//     `{$columnName}`  -- use ` to enclose the column name
//      = :id           -- use placeholder (prepared statements f.e. PHP PDO)
//     ;                -- finish statement with semicolon
// "

PHP PDO: https://www.php.net/manual/de/pdo.prepared-statements.php

Why?

Because code may change over years and you get the (i.e.) user_type from a POST or GET request.

Still - why?

Go search for "SQL-Injections".

How to prefix / suffix column names in a MySQL query?

2 Answers2