[PHP] Get Column Data Instead of Name

[Berk]

Hey,
so I want to get data from column by this:

Stats::userData($_SESSION['username'],'role');

But instead, it shows column name, 'Role'. My code:

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]

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]

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:

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]

Thanks for the suggestion, I've done it by this code and it works perfect:

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.

 

[Berk]

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]

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]

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]

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]

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.