SELECT ? FROM table WHERE id = ? not working correctly

Question

I've got a little question. I want to get specific information out of my database via mysqli query:

public function get_searchorder_single_information($soid, $information) {

        global $mysqli;

        $stmt = $mysqli->prepare("SELECT ? FROM pr_hr_searchorders WHERE id = ?");
        $stmt->bind_param('si', $information, $soid);
        $stmt->execute();
        $stmt->bind_result($result);
        $stmt->fetch();
        echo $result;

        $stmt->close();

    }

In my example, $information is set 'job', but it can have other values, too (e.g. 'salary'). When I try to use my query with this variable, echo outputs just 'job' and not the value that is saved in my database. When I write 'job' instead of the '?', echo outputs the correct value.

So now I could make a function for each information I search, but this would end in spaghetti code. So I ask you if there is any possibility to use my search query like above with correct output. How would I do that?

Thanks in advance and sorry for my bad english writing.

Please read all the docs (including the notes) of the functions/methods you use. In specific `mysqli::prepare()` — PeeHaa, Nov 01 '16 at 08:41
Closed this question in hope that the answer won't do too much harm — Your Common Sense, Nov 01 '16 at 09:38

Fky · Accepted Answer · 2016-11-01T09:05:29.343

Read documentation : http://php.net/manual/en/mysqli.prepare.php

The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value. However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign. The latter restriction is necessary because it would be impossible to determine the parameter type. It's not allowed to compare marker with NULL by ? IS NULL too. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.

Modify your code :

$stmt= $mysqli->prepare("SELECT $information FROM pr_hr_searchorders WHERE id = ?");
$stmt->bind_param('i', $soid);

@DanielWohlgemuth I **hope** you are aware of the fact that this solution is **wide** open to **SQL injection** — Your Common Sense, Nov 01 '16 at 09:36
I know but it's not the subject , i just fix his usage , there is a lot of things to do if you want correct all his code conception — Fky, Nov 01 '16 at 12:03

score -3 · Answer 2 · answered Nov 01 '16 at 08:59

-3

Change your code to

public function get_searchorder_single_information($soid, $information) {

        global $mysqli;
        $query = "SELECT".$information." FROM pr_hr_searchorders WHERE id = ?"
        $stmt = $mysqli->prepare($query);
        $stmt->bind_param('si', $soid);
        $stmt->execute();
        $stmt->bind_result($result);
        $stmt->fetch();
        echo $result;

        $stmt->close();

    }

Then you will get the desired result

answered Nov 01 '16 at 08:59

Webdev

617
6
24

You can also use back-ticks to quote field names which is actually sufficient escaping. – apokryfos Nov 01 '16 at 09:02
@apokryfos which is actually NOT – Your Common Sense Nov 01 '16 at 09:25
What is not working? – Webdev Nov 01 '16 at 09:28
@YourCommonSense citation needed (i'm assuming you mean backticks in the field name) – apokryfos Nov 01 '16 at 09:29
@apokryfos here you go, [An SQL injection against which prepared statements won't help. Adding backticks](https://phpdelusions.net/pdo/sql_injection_example#backticks) – Your Common Sense Nov 01 '16 at 09:32
@YourCommonSense there's something off about citing your self for something you said. That being said, even that resource does verify that backticks are enough if you just double up the inner backticks. – apokryfos Nov 01 '16 at 09:38
@apokryfos Q.E.D. – Your Common Sense Nov 01 '16 at 09:39

SELECT ? FROM table WHERE id = ? not working correctly

2 Answers2