How to Use Bind_Param for Order By

Is it possible to use bind_param for ORDER BY?

Only data can be bound with placeholders.

Column/table names are part of the schema and cannot be bound. (The fact that it generates "odd results" instead of simply yielding an error is a peculiarity of the implementation.)

I would suggest using a white-list of column-names and controlled string interpolation.

How to select row using bind_param php mysqli?

Problem description:

The mysqli_result object returned by the method get_result looks something like this:

mysqli_result Object
(
    [current_field] => 0
    [field_count] => 3
    [lengths] => 
    [num_rows] => 1
    [type] => 0
)

As you can see, this object exposes only some properties (number of fields, number of rows, etc) about the record set from which you need to reference your data. So, you can not directly reference field values from it.

Solution:

In order to reach to the needed data you'll have to call one of the methods defined in the mysqli_result class (fetch_all, fetch_array, fetch_assoc, etc):

//...
$result = $stmt->get_result();
$row = $result->fetch_array(MYSQLI_ASSOC);
$page = $row['page'];
//...

with $row representing the fetched record and being an array like this:

Array
(
    [id] => 13
    [page] => 21
    ...
)

For more details read The mysqli_result class.

About error and exception handling:

Please note that a proper error and exception handling system is essential in the developing process. This article describes the steps needed to activate it in an elegant and thoroughly manner.

Extensive example:

For clarity, I prepared an extensive example with all components needed for accessing a database using the mysqli extension. It presents the situation of having to fetch one or more records from a list of users - saved in a db table named users. Each user is described by its id, name and age.

It's up to you to implement the error/exception handling system - as described in the above mentioned article.

index.php:

Option 1) Fetching only one record:

<?php

require 'connection.php';

// Assign the values used to replace the sql statement markers.
$id = 10;

/*
 * The SQL statement to be prepared. Notice the so-called markers, 
 * e.g. the "?" signs. They will be replaced later with the 
 * corresponding values when using mysqli_stmt::bind_param.
 * 
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$sql = 'SELECT 
            id,
            name,
            age 
        FROM users 
        WHERE id = ?';

/*
 * Prepare the SQL statement for execution - ONLY ONCE.
 * 
 * @link http://php.net/manual/en/mysqli.prepare.php
 */
$statement = $connection->prepare($sql);

/*
 * Bind variables for the parameter markers (?) in the 
 * SQL statement that was passed to prepare(). The first 
 * argument of bind_param() is a string that contains one 
 * or more characters which specify the types for the 
 * corresponding bind variables.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.bind-param.php
 */
$statement->bind_param('i', $id);

/*
 * Execute the prepared SQL statement.
 * When executed any parameter markers which exist will 
 * automatically be replaced with the appropriate data.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.execute.php
 */
$statement->execute();

/*
 * Get the result set from the prepared statement.
 * 
 * NOTA BENE:
 * Available only with mysqlnd ("MySQL Native Driver")! If this 
 * is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in 
 * PHP config file (php.ini) and restart web server (I assume Apache) and 
 * mysql service. Or use the following functions instead:
 * mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.get-result.php
 * @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
 */
$result = $statement->get_result();

/*
 * Fetch data and save it into an array:
 * 
 *  Array
 *  (
 *      [id] => 10
 *      [name] => Michael
 *      [age] => 18
 *  )
 * 
 * @link https://secure.php.net/manual/en/mysqli-result.fetch-array.php
 */
$user = $result->fetch_array(MYSQLI_ASSOC);

/*
 * Free the memory associated with the result. You should 
 * always free your result when it is not needed anymore.
 * 
 * @link http://php.net/manual/en/mysqli-result.free.php
 */
$result->close();

/*
 * Close the prepared statement. It also deallocates the statement handle.
 * If the statement has pending or unread results, it cancels them 
 * so that the next query can be executed.
 * 
 * @link http://php.net/manual/en/mysqli-stmt.close.php
 */
$statement->close();

/*
 * Close the previously opened database connection.
 * 
 * @link http://php.net/manual/en/mysqli.close.php
 */
$connection->close();

// Reference the values of the fetched data.
echo 'User id is ' . $user['id'] . '<br/>';
echo 'User name is ' . $user['name'] . '<br/>';
echo 'User age is ' . $user['age'] . '<br/>';

Option 2) Fetching multiple records:

<?php

require 'connection.php';

$id1 = 10;
$id2 = 11;

$sql = 'SELECT 
            id,
            name,
            age 
        FROM users 
        WHERE 
            id = ? 
            OR id = ?';

$statement = $connection->prepare($sql);

$statement->bind_param('ii', $id1, $id2);

$statement->execute();
$result = $statement->get_result();

/*
 * Fetch data and save it into an array:
 * 
 *  Array
 *  (
 *      [0] => Array
 *          (
 *              [id] => 10
 *              [name] => Michael
 *              [age] => 18
 *          )
 *  
 *      [1] => Array
 *          (
 *              [id] => 11
 *              [name] => Harry
 *              [age] => 59
 *          )
 *  )
 * 
 * @link http://php.net/manual/en/mysqli-result.fetch-all.php
 */
$users = $result->fetch_all(MYSQLI_ASSOC);

$result->close();
$statement->close();
$connection->close();

// Reference the values of the fetched data.
foreach ($users as $key => $user) {
    echo 'User id is ' . $user['id'] . '<br/>';
    echo 'User name is ' . $user['name'] . '<br/>';
    echo 'User age is ' . $user['age'] . '<br/>';

    echo '<hr/>';
}

connection.php:

<?php

// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');

/*
 * Error reporting.
 * 
 * Also, define an error handler, an exception handler and, eventually, 
 * a shutdown handler function to handle the raised errors and exceptions.
 * 
 * @link https://phpdelusions.net/articles/error_reporting Error reporting basics
 * @link http://php.net/manual/en/function.error-reporting.php
 * @link http://php.net/manual/en/function.set-error-handler.php
 * @link http://php.net/manual/en/function.set-exception-handler.php
 * @link http://php.net/manual/en/function.register-shutdown-function.php
 */
error_reporting(E_ALL);
ini_set('display_errors', 1); /* SET IT TO 0 ON A LIVE SERVER! */

/*
 * Enable internal report functions. This enables the exception handling, 
 * e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions 
 * (mysqli_sql_exception).
 * 
 * MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
 * MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings. 
 * 
 * @link http://php.net/manual/en/class.mysqli-driver.php
 * @link http://php.net/manual/en/mysqli-driver.report-mode.php
 * @link http://php.net/manual/en/mysqli.constants.php
 */
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Create a new db connection.
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);

// Set the desired connection charset
$connection->set_charset('utf8mb4');

Test data:

id  name    age
---------------
9   Julie   23
10  Michael 18
11  Harry   59

Create table syntax:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysqli_stmt::bind_param() - specify another data type than s for each parameter

The only time I have found it's important to use an integer parameter is in a LIMIT clause.

SELECT
...
LIMIT ?, ?

MySQL does not accept quoted string literals in this context, and does not accept parameters with string type. You have to use an integer.

See Parametrized PDO query and `LIMIT` clause - not working for my tests on this. That was a question about PDO, and I didn't test mysqli, but I believe it's a server-side MySQL requirement to use integer parameters in this case. So it should apply to mysqli too.

In all other cases (AFAIK), MySQL is able to convert strings into integers by reading the leading digits in the string, and ignoring any following characters.

---

@Dharman in a comment below makes reference to MySQL's support for integers in ORDER BY:

SELECT
...
ORDER BY ?

An integer in ORDER BY means to sort by the column in that position, not by the constant value of the number:

SELECT
...
ORDER BY 1 -- sorts by the 1st column

But an equivalent string value containing that number doesn't act the same. It sorts by the constant value of the string, which means every row is tied, and the sort order will be arbitrary.

SELECT
...
ORDER BY '1' -- sorts by a constant value, so all rows are tied

Therefore this is another case where the data type for a query parameter is important.

On the other hand, using ordinal numbers to sort by the column in that position in ORDER BY or GROUP BY is deprecated, and we shouldn't rely on that usage of SQL.

Parameterization in `order by` clause in SQL platforms

you bind the string value 'name' to the parameter in the sql. That means that for each row processed, the SQL will see the same string, namely 'name'.

The point is that 'name' is not interpreted as the Literal name which matches the column name, but a VARCHAR value 'name' which has been set or bound to a replacement variable ?.

In this case, if you have a variable ORDER BY clause, you would have two (or more) versions of your SQL, which you can select with a regular if / then / else.

Another way is to concatenate the sorting column in your string directly rather than using bind variables. I would STRONGLY suggest against as this lays the foundation work for future SQL Injection. Either way, if you concatenate a variable input string to your SQL, then make sure it is sufficiently sanitized and checked to avoid issues.

Concatenation in PHP would be something simple like this:

$orderby = "name";
$stmt = $conn->prepare("SELECT id, name FROM user order by ".$orderby." desc");
$stmt->execute();

See also PHP - concatenate or directly insert variables in string (had to correct, used initially a syntax that only worked for the PHP echo command).

All the other Implementing Languages (C#, Java, etc) combined with any database (oracle, MySQL, TSQL, etc) would face same issues and solutions: You will have to make the distinction between bind variables and literals in your SQL.

If you still want to use a bind variable - there is a way, but you would have to modify your sql as follows:

SELECT id, name FROM user 
ORDER BY CASE ? WHEN 'name' THEN name WHEN 'id' THEN id ELSE NULL END

Actually good thinking by you (in your comment) to still use bind variables somehow. Now I do not have the issue with SQL Injection anymore either. However, now you need to account for every possible sorting that can happen, and have that coded in your SQL instead.

mySQL bind_param with IN(?)

This is one place placeholders fall on their faces. Minus the auto-escaping, they're almost literally just a string replacement operation internally, meaning that if you have WHERE Locations IN (?), and pass in 1,2,3,4, you'll get the equivalent of

WHERE Locations IN ('1,2,3,4')  // note, it's a string, not individual comma-separated integers

logically equivalent to

WHERE Locations = '1,2,3,4' // again, just a string

instead of the intended

WHERE Locations = 1 OR Locations = 2 OR Locations = 3 OR Locations = 4

The only practical solution is to build your own list of comma-separated placeholders (?), e.g:

$placeholders = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT Name FROM Users WHERE Locations IN ($placeholders)";

and then bind your parameters are usual.

When do I call bind_param if using MySQLi prepared statements in a loop?

Because the parameters are passed by reference, you can assign the values to the variables before or after the call to bind_param. Either way will work.

If the variables were passed by value, you would need to bind them each time you changed their value. But since they are passed by reference, you only need to bind them once.

In the case of a single execute, the order of operation doesn't really matter and may depend on where the values are coming from. In the case of a loop, you should definitely call bind_param before the loop.

Using s for int in bind_param

It's always good to be as accurate as possible to reduce the risk of side effects/issues.

In your case, it should be pretty easy to do this.

Example 1

$type = $id_type == 'userID' ? 'i' : 's';
$stmt->bind_param($type, $id);

That would work well if you only have two options.

If you have more, the below method would work.

Example 2

$types = [
    'userID'    => 'i',
    'sessionID' => 's',
];

$stmt->bind_param($types[$id_type], $id);

Then you can keep adding to your $types array if you need more cols.

---

Related Topics