[PHP] Get Column Data Instead of Name

Status
Not open for further replies.

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
Hey,
so I want to get data from column by this:
PHP:
Stats::userData($_SESSION['username'],'role');
But instead, it shows column name, 'Role'. My code:
PHP:
public static function userData($user,$column = "*")
    {
        global $conn;
        $stmt = $conn->prepare("SELECT :c FROM users WHERE username = :u ");
        $stmt->bindParam(":c", $column);
        $stmt->bindParam(':u',$user);
        $stmt->execute();
        $stmt = $stmt->fetchAll();
        foreach($stmt as $data)
        {
            return $data[$column];
        }
    }
What am I doing wrong? What should I do?
Thanks
 

Weasel

👄 I'd intercept me
Nov 25, 2011
4,128
2,456
You can't use parameter binding for column names, as it always casts it to a value with single quotes (or integers when using PDO:: PARAMT_INT). So what you are trying to do won't work. You either have to inject $role directly in to the query (which could be considered unsafe) or find another solution.
 

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
You can't use parameter binding for column names, as it always casts it to a value with single quotes (or integers when using PDO:: PARAMT_INT). So what you are trying to do won't work. You either have to inject $role directly in to the query (which could be considered unsafe) or find another solution.
Thanks for the suggestion, I've done it by this code and it works perfect:
PHP:
public static function userData($user,$column = "*")
    {
        global $conn;
        $stmt = $conn->prepare("SELECT ". $column." FROM users WHERE username = :u ");
        $stmt->bindParam(':u',$user);
        $stmt->execute();
        $stmt = $stmt->fetchAll();
        foreach($stmt as $data)
        {
            return $data[$column];
        }
    }
 

Weasel

👄 I'd intercept me
Nov 25, 2011
4,128
2,456
Thanks for the suggestion, I've done it by this code and it works perfect:
PHP:
public static function userData($user,$column = "*")
    {
        global $conn;
        $stmt = $conn->prepare("SELECT ". $column." FROM users WHERE username = :u ");
        $stmt->bindParam(':u',$user);
        $stmt->execute();
        $stmt = $stmt->fetchAll();
        foreach($stmt as $data)
        {
            return $data[$column];
        }
    }

Few pointers:
- As you are returning one column of one row, use $stmt->fetchColumn(); which you can return directly.
- Don't use "*" as default fallback, because it will fetch the row which your code cannot return.
- Always check if your query actually has a result before you return something, otherwise you'll get undefined errors or unexpected behavior.
- Make sure that what you pass to $column CANNOT BE USER INPUT, otherwise you open yourself to SQL injections.
 
Last edited:

Berk

berkibap#4233
Developer
Oct 17, 2015
863
190
Few pointers:
- As you are returning one column of one row, use $stmt->fetchColumn(); which you can return directly.
- Don't use "*" as default fallback, because it will fetch the row which your code cannot return.
- Always check if your query actually has a result before you return something, otherwise you'll get undefined errors or unexpected behavior.
- Make sure that what you pass too $column CANNOT BE USER INPUT, otherwise you open yourself to SQL injections.
Thanks, much appreciated!
 

M8than

yes
Mar 16, 2012
463
102
You can't use parameter binding for column names, as it always casts it to a value with single quotes (or integers when using PDO:: PARAMT_INT). So what you are trying to do won't work. You either have to inject $role directly in to the query (which could be considered unsafe) or find another solution.
This is incorrect. It isn't because prepared statements cast values to single quote strings. It's because mysql literally just doesn't support it. When you are using prepared statements, the statement and variables are sent separately.
 

Weasel

👄 I'd intercept me
Nov 25, 2011
4,128
2,456
This is incorrect. It isn't because prepared statements cast values to single quote strings. It's because mysql literally just doesn't support it. When you are using prepared statements, the statement and variables are sent separately.
I am aware, I am also able to read docs. As for our novice developer, I tried giving a simple example.
 

M8than

yes
Mar 16, 2012
463
102
I am aware, I am also able to read docs. As for our novice developer, I tried giving a simple example.
Even if it's not important to the code, it's not worth simplifying things in a way which is literally wrong. If you are gonna explain how something works please do it right.
 

Weasel

👄 I'd intercept me
Nov 25, 2011
4,128
2,456
Even if it's not important to the code, it's not worth simplifying things in a way which is literally wrong. If you are gonna explain how something works please do it right.
There's nothing wrong about what I said, the deeper theoretical reason might be different, there is no lie or fault in what I said. However, you're right, I should have included extra information.

Anyways, problem solved, thread closed.
 
Status
Not open for further replies.

Users who are viewing this thread

Top