Mysqli Prepared Statement Column with Variable

Mysqli prepared statement column with variable

It's impossible to use a parameter for a column or a table name. Instead, they must be explicitly filtered out before use, using a white list approach.

// define a "white list"
$allowed = ['Node5', 'Node4'];

// Check the input variable against it 
if (!in_array($server, $allowed)) {
    throw new Exception("Invalid column name");
}

// now $server could be used in the SQL string
$sqlString = "UPDATE staff_members SET $server=? WHERE Username=?";
$stmt = $connection->prepare($sqlString);
$stmt->bind_param("ss", $rank, $username);
$stmt->execute();

Mysqli PHP prepared statement when the query uses SQL variable

It looks like image_uploads.OWNER is a column in a SQL table in your database. If so, then you can just leave it as it is in your first version. You don't need to parameterise it.

Only data values coming from outside the database engine (e.g. user input, or data from a file) need to be parameterised, because that data is potentially unknown and could contain malicious values / injection attacks which need to be sanitised against. Quoting a column name from another table cannot pose such a threat - you are not putting an unknown string value into the query syntax.

N.B. In actual fact none of your original query relies on external input or unknown variables - all of the data is hard-coded into the query text. So you don't actually need to parameterise anything at all in this particular query. Everything is set in advance, so there is no threat from surprise / unknown text becoming part of the executable SQL.

Variable amount of columns returned in mysqli prepared statement

Using VolkerK's suggestion of mysqli_statement->result_metadata() I was able to fashion together the following code that accomplishes what I'm looking for, though the performance isn't any faster than using a standard query. I get the statement->result_metadata() to build an associative array to call bind_result on. I build up a bind_result statement as a string and eval it. I know this isn't particularly safe but it is my first pass.

public function executePreparedStatement()
{
    if($this->statement->execute())
    {
        $this->record = array();
        $md = $this->statement->result_metadata();
        $fields = $md->fetch_fields();

        $bindResult = '$this->statement->bind_result(';
        foreach($fields as $field)
        {
            $bindResult .= "\$this->record['" . $field->name . "'],";
        }

        $bindResult = substr($bindResult,0,strlen($bindResult) - 1) . ');';

        eval($bindResult);
        return true;
    }
    else
    {
        $this->error = $this->db->error;
        return false;
    }
}

    ...
    $this->prepareStatement($query);
    $this->bindParameter('i',$runId);
    if($this->executePreparedStatement())
    {
        $report = new Report();
        while($this->statement->fetch())
        {
            $row = $this->record;
            $line = array();
            foreach($row as $key => &$value)
            {
                array_push($line,$value);
            }
            $report->addLine($line);
        }
        return $report          
    }

Is there a way to use a prepared statement to select a variable column?

It's not possible. The difference is that you are dynamically referencing an object of the database, versus just passing in a string.

Prepared statements work by specifying the SQL statement in full with placeholders for strings/values that you are passing in. Your RDBMS can then parse the query and determine it's full execution path before you pass in the parameters. Once that step is complete, it takes the parameters in and gets the data. This is why prepared statements are so secure. The execution path is predetermined so it's impossible to pass in more SQL and change it.

So if you don't know the column or table, then it can't parse and build the execution path. Instead, you'll have to build the SQL dynamically with concatenation and execute. If you are getting the column or table name from a user input then you'll have to sanitize it as best as you can and pray that your sanitation efforts are better than your sneaky users abilities to inject sql.

How to store a single column of a single row out of a MySQLi prepared statement in a PHP variable?

You need to add to your code the binding of the result to a specific variable

$emailsql->bind_result($emailResult);  

And you fetch it :

while($emailsql->fetch()){   
  printf ($emailResult); 
}

So this should be it:

$emailsql = $conn->prepare("SELECT email FROM User WHERE email = ? limit 1;");
$emailsql->bind_param('s', $email);
$emailsql->execute();
$emailsql->bind_result($emailResult); 
while($emailsql->fetch()){   
  printf ($emailResult); 
} 

In case you need the variable outside the loop I would take this approach:

$theEmail;
$emailsql = $conn->prepare("SELECT email FROM User WHERE email = ? limit 1;");
$emailsql->bind_param('s', $email);
$emailsql->execute();
$emailsql->bind_result($emailResult); 
while($emailsql->fetch()){   
  $theEmail=$emailResult; 
}  

Note that you would need an array in order to query more than one email.

Another cleaner approach as @YourCommonSense suggested would be avoiding the loop like so:

$theEmail;
$emailsql = $conn->prepare("SELECT email FROM User WHERE email = ? limit 1;");
$emailsql->bind_param('s', $email);
$emailsql->execute();
$emailsql->bind_result($emailResult); 
$emailsql->fetch();   
printf($emailResult);

---

Related Topics