CODEWITHSUNDEEP
phpmysqlmysqli
user2997497
user2997497

Reputation: 160

PHP + mySQL: Prepare, bind_param not working as I'd expect it

I'll put here two examples where $stmt = $mysqli->prepare() + $stmt->bind_param() deny to work and I can't see for myself why. Not working:

if ($stmt = $mySQLi->prepare("DROP DATABASE ?")) {
        $stmt->bind_param('s', $db_name);
        $stmt->execute(); 
        $stmt->store_result();
}

Current working workaround:

 if ($stmt = $mySQLi->prepare("DROP DATABASE $db_name")) {
        //$stmt->bind_param('s', $db_name);
        $stmt->execute(); 
        $stmt->store_result();
 }

Not working:

if ($stmt = $strSQLi->prepare("SELECT ? FROM Strings.texts WHERE keyName = ? LIMIT 1")) {
        $stmt->bind_param('ss', strtolower($lang), strtolower("help_".$key));
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($result);
}

Current working workaround:

if ($stmt = $strSQLi->prepare("SELECT {strtolower($lang)} FROM EVEStrings.texts WHERE keyName = ? LIMIT 1")) {
        $stmt->bind_param('s', strtolower("help_".$key));
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($result);
}

Any idea WHY?

Upvotes: 1

Views: 477

Answers (2)

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

This is how mysqli::prepare() is working. It's completely written in the documentation.

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

Note:

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.

This part mostly: they are not allowed for identifiers (such as table or column names)

The idea of binding parameters it that you are sending to the database engine the query and in the runtine it binds the values you have given. If the table is not specified, the engine cannot build a valid statement, thus to continue executing the query and bind params is impossible.

I would not suggest to use dynamic table names in general, whether it is safe or not. However, if you really insist to do it, do not let the user to decide that. Decide the {strtolower($lang)} on application level (i.e. from an array), not from the user input.

Upvotes: 4

Maarkoize
Maarkoize

Reputation: 2621

As far as I know from PDO (and I think it's the same for mysqli) this can't be done.

bind_param and bind_value() can only bind values, not table or column names.

You have to filter your data manually, maybe with a whitelist method.

Upvotes: 0

Related Questions